MySQL索引及進階用法，MySQL索引進階用法

MySQL索引及進階用法，MySQL索引進階用法

http://tech.meituan.com/mysql-index.html

http://www.cnblogs.com/hustcat/archive/2009/10/28/1591648.html

http://www.cnblogs.com/mailingfeng/archive/2012/09/26/2704344.html

所有MySQL列類型可以被索引。根據儲存引擎定義每個表的最大索引數和最大索引長度。所有儲存引擎支援每個表至少16個索引，總索引長度至少為256位元組。大多數儲存引擎有更高的限制。索引的儲存類型目前只有兩種（btree和hash），具體和儲存引擎模式相關：MyISAM        btreeInnoDB        btreeMEMORY/Heap   hash，btree預設情況MEMORY/Heap儲存引擎使用hash索引MySQL的btree索引和hash索引的區別hash 索引結構的特殊性，其檢索效率非常高，索引的檢索可以一次定位，不像btree(B-Tree)索引需要從根節點到枝節點，最後才能訪問到頁節點這樣多次的IO訪問，所以 hash 索引的查詢效率要遠高於 btree(B-Tree) 索引。雖然 hash 索引效率高，但是 hash 索引本身由於其特殊性也帶來了很多限制和弊端，主要有以下這些。（1）hash 索引僅僅能滿足=，<=>，IN，IS NULL或者IS NOT NULL查詢，不能使用範圍查詢。由於 hash 索引比較的是進行 hash 運算之後的 hash 值，所以它只能用於等值的過濾，不能用於基於範圍的過濾，因為經過相應的 hash 演算法處理之後的 hash 值的大小關係，並不能保證和hash運算前完全一樣。（2）hash 索引無法被用來避免資料的排序操作。由於 hash 索引中存放的是經過 hash 計算之後的 hash 值，而且hash值的大小關係並不一定和 hash 運算前的鍵值完全一樣，所以資料庫無法利用索引的資料來避免任何排序運算；（3）hash 索引不能利用部分索引鍵查詢。對於複合式索引，hash 索引在計算 hash 值的時候是複合式索引鍵合并後再一起計算 hash 值，而不是單獨計算 hash 值，所以通過複合式索引的前面一個或幾個索引鍵進行查詢的時候，hash 索引也無法被利用。（4）hash 索引在任何時候都不能避免表掃描。前面已經知道，hash 索引是將索引鍵通過 hash 運算之後，將 hash運算結果的 hash 值和所對應的行指標資訊存放於一個 hash 表中，由於不同索引鍵存在相同 hash 值，所以即使取滿足某個 hash 鍵值的資料的記錄條數，也無法從 hash 索引中直接完成查詢，還是要通過訪問表中的實際資料進行相應的比較，並得到相應的結果。（5）hash 索引遇到大量hash值相等的情況後效能並不一定就會比B-Tree索引高。對於選擇性比較低的索引鍵，如果建立 hash 索引，那麼將會存在大量記錄指標資訊存於同一個 hash 值相關聯。這樣要定位某一條記錄時就會非常麻煩，會浪費多次表資料的訪問，而造成整體效能低下B-Tree 索引是 MySQL 資料庫中使用最為頻繁的索引類型，除了 Archive 儲存引擎之外的其他所有的儲存引擎都支援 B-Tree 索引。不僅僅在 MySQL 中是如此，實際上在其他的很多資料庫管理系統中B-Tree 索引也同樣是作為最主要的索引類型，這主要是因為 B-Tree 索引的儲存結構在資料庫的資料檢 索中有非常優異的表現。    一般來說， MySQL 中的 B-Tree 索引的物理檔案大多都是以 Balance Tree 的結構來儲存的，也就是所有實際需要的資料都存放於 Tree 的 Leaf Node ，而且到任何一個 Leaf Node 的最短路徑的長度都是完全相同的，所以我們大家都稱之為 B-Tree 索引當然，可能各種資料庫（或 MySQL 的各種儲存引擎）在存放自己的 B-Tree 索引的時候會對儲存結構稍作改造。如 Innodb 儲存引擎的 B-Tree 索引實際使用的儲存結構實際上是 B+Tree ，也就是在 B-Tree 資料結構的基礎上做了很小的改造，在每一個Leaf Node 上面出了存放索引鍵的相關資訊之外，還儲存了指向與該 Leaf Node 相鄰的後一個 LeafNode 的指標資訊，這主要是為了加快檢索多個相鄰 Leaf Node 的效率考慮。     在 Innodb 儲存引擎中，存在兩種不同形式的索引，一種是 Cluster 形式的主鍵索引（ Primary Key ），另外一種則是和其他儲存引擎（如 MyISAM 儲存引擎）存放形式基本相同的普通 B-Tree 索引，這種索引在 Innodb 儲存引擎中被稱為 Secondary Index 。    在 Innodb 中如果通過主鍵來訪問資料效率是非常高的，而如果是通過 Secondary Index 來訪問資料的話， Innodb 首先通過 Secondary Index 的相關資訊，通過相應的索引鍵檢索到 Leaf Node之後，需要再通過 Leaf Node 中存放的主鍵值再通過主鍵索引來擷取相應的資料行。MyISAM 儲存引擎的主鍵索引和非主鍵索引差別很小，只不過是主鍵索引的索引鍵是一個唯一且非空 的鍵而已。而且 MyISAM 儲存引擎的索引和 Innodb 的 Secondary Index 的儲存結構也基本相同，主要的區別只是 MyISAM 儲存引擎在 Leaf Nodes 上面出了存放索引鍵資訊之外，再存放能直接定位到 MyISAM 資料檔案中相應的資料行的資訊（如 Row Number ），但並不會存放主鍵的鍵值資訊。我們這裡建表*/CREATE TABLE mytable(   id INT,    username VARCHAR(16),city VARCHAR(16),age INT );/*索引分單列索引和複合式索引。單列索引，即一個索引只包含單個列，一個表可以有多個單列索引，但這不是複合式索引。複合式索引，即一個索包含多個列。MySQL索引類型包括：（1）普通索引，這是最基本的索引，它沒有任何限制。它有以下幾種建立方式：*/-- 建立索引CREATE INDEX indexName ON mytable(username(10));               -- 單列索引-- CREATE INDEX indexName ON mytable(username(10),city(10));   -- 複合式索引-- indexName為索引名，mytable表名，username和city為列名，10為前置長度，即索引在該列從最左字元開始儲存的資訊長度，單位位元組-- 如果是CHAR，VARCHAR類型，前置長度可以小於欄位實際長度；如果是BLOB和TEXT類型，必須指定 前置長度，下同。-- 修改表結構來建立索引ALTER TABLE mytable ADD INDEX indexName (username(10));-- ALTER TABLE mytable ADD INDEX indexName (username(10),city(10));-- 此處 indexName 索引名可不寫，系統自動賦名 username ，username_2 ，username_3，...-- 建立表的時候直接指定CREATE TABLE mytable(id INT,username VARCHAR(16),city VARCHAR(16),age INT,INDEX indexName (username(10))-- INDEX indexName (username(10),city(10)));-- 此處 indexName 索引名同樣可以省略/*（2）唯一索引，它與前面的普通索引類似，不同的就是：索引列的值必須唯一，但允許有空值。如果是複合式索引，則列值的組合必須唯一。它有以下幾種建立方式（僅僅在建立普通索引時關鍵字 INDEX 前加 UNIQUE）：*/-- 建立索引CREATE UNIQUE INDEX indexName ON mytable(username(10));-- 修改表結構來建立索引ALTER TABLE mytable ADD UNIQUE INDEX indexName (username(10));-- 也可簡寫成 ALTER TABLE mytable ADD UNIQUE indexName (username(10));-- 建立表的時候直接指定CREATE TABLE mytable(id INT,username VARCHAR(16),city VARCHAR(16),age INT,UNIQUE INDEX indexName (username(10)) -- 也可簡寫成 UNIQUE indexName (username(10)));/*（3）主鍵索引，它是一種特殊的唯一索引，不允許有空值。在建表的時候同時建立的主鍵即為主鍵索引主鍵索引無需命名，一個表只能有一個主鍵。主鍵索引同時可是唯一索引或者全文索引，但唯一索引或全文索引不能共存在同一索引*/-- 修改表結構來建立索引ALTER TABLE mytable ADD PRIMARY KEY (id);-- 建立表的時候直接指定CREATE TABLE mytable(id INT,username VARCHAR(16),city VARCHAR(16),age INT,PRIMARY KEY(id) );/*（4）全文索引，InnoDB儲存引擎不支援全文索引*/-- 建立索引CREATE FULLTEXT INDEX indexName ON mytable(username(10));-- 修改表結構來建立索引ALTER TABLE mytable ADD FULLTEXT INDEX indexName (username(10));-- 也可簡寫成 ALTER TABLE mytable ADD FULLTEXT indexName (username(10));-- 建立表的時候直接指定CREATE TABLE mytable(id INT,username VARCHAR(16),city VARCHAR(16),age INT,FULLTEXT INDEX indexName (username(10)) -- 也可簡寫成 FULLTEXT indexName (username(10)))ENGINE=MYISAM;-- 建表時建立全文索引，要設定該表的儲存引擎為MYISAM,新版mysql預設InnoDB儲存引擎不支援全文索引-- 刪除索引DROP INDEX indexName ON mytable;/*Mysql自動使用索引規則：btree索引當使用 <，<=，=，>=，>，BETWEEN，IN，!=或者<>，以及某些時候的LIKE才會使用btree索引，因為在以萬用字元%和_開頭作查詢時，MySQL不會使用索引。btree索引能用於加速ORDER BY操作hash索引當使用=，<=>，IN，IS NULL或者IS NOT NULL操作符時才會使用hash索引，並且不能用於加速ORDER BY操作，並且條件值必須是索引列尋找某行該列的整個值對where後邊條件為字串的一定要加引號，字串如果為數字mysql會自動轉為字串，但是不使用索引。mysql目前不支援函數索引，只能對列的前一部分（指定長度首碼）進行索引，對於char和varchar列，使用首碼索引（該列從起始字元到指定長度字元位置建立索引）將大大節省空間的。mysql列建議列是非null的。說是如果是允許null的列，對索引會有影響（索引不會包括有NULL值）。因為它們使得索引、索引的統計資訊以及比較運算更加複雜。應該用0、一個特殊的值或者一個空串代替空值。盡量不使用NOT IN和<>操作username,city,age建立這三列的複合式索引，其實是相當於分別建立了下面三組複合式索引：username,city,ageusername,cityusername使用複合式索引，比如where等條件，列名必須從複合式索引最左列至右連續的列名做條件才可以，複合式索引類似單一索引首碼*/-- 調用複合式索引SELECT * FROM mytable WHERE username = 'admin' AND city = 'DaLian';-- 多表聯查中的條件也可運用索引-- 全文索引的使用SELECT * , MATCH (username,city) AGAINST ('name100 name200 city500 thisisname') FROM mytable WHERE MATCH (username,city) AGAINST ('name100 name200 city500 thisisname');-- 返回 mytable 表在 MATCH 的參數中的任意列中包含 AGAINST 字串參數中被 "空格" "," 和 "." 分割的任意單詞的行(斷字的字元： "空格" "," 和 "." 但是不用這些符號斷字的語言，如中文，就得自行手動斷字。)-- 表列中的單詞也是以空格分割來區分-- 這裡指定 MATCH...AGAINST 兩次。這不會引起附加的開銷，因為 MySQL 最佳化器會注意到兩次是同樣的 MATCH...AGAINST 調用，並只調用一次全文檢索搜尋代碼。/*函數 MATCH() 對照一個列名集(一個 FULLTEXT 索引中的一個或多個列的列名)。搜尋字串做為 AGAINST() 的參數給定。搜尋以忽略字母大小寫方式執行，預設搜尋是不分大小寫，若要分大小寫，列的字元集設定要從utf8改成utf8_bin。雖然同一個表格可以有不同字元集的欄位，但是同一個FULLTEXT 索引裡的欄位必須是同一個字元集與collation。任何在 stopword 列表上出現的，或太短的(3 個字元或更少的)的單詞將被忽略。(可以覆寫內建的 stopword 列表。可以修改最少四個字元的設定。 )搜尋的詞有一個權重性，如果搜尋的詞在表中包含它的行太多，則這個搜尋字詞將有較低的權重(可能甚至有一個零權重)，否則，它將得到一個較高的權重。然後，權重將被結合用於計算行的相似性。如果搜尋字詞在表中超過一半的行中出現。則它被有效地處理為一個 stopword (即，一個零語義值的詞)，不搜尋該詞。MATCH...AGAINST可以跟所有MySQL文法搭配使用，像是JOIN或是加上其他過濾條件。對於表中的每行記錄，MATCH...AGAINST 返回一個相關性值。即，返回的每行與搜尋條件之間的相似性尺度。當 MATCH() 被使用在一個 WHERE 子句中時，返回的結果被自動地以相關性從高到底的次序排序。如果即沒有 WHERE 也沒有 ORDER BY 子句，返回行是不排序的。 相關性值是正值的浮點數字。零相關性意味著不相似。相關性的計算是基於：尋找單詞在表行中的數目、在行中唯一詞的數目、在集中詞的全部數目和包含一個特殊詞的行的數目。到 4.0.1 時，MySQL 也可以使用 IN BOOLEAN MODE 修飾語來執行一個邏輯全文檢索搜尋。IN BOOLEAN MODE的特色： 不剔除50%以上符合的row。 不自動以相關性反向排序。 可以對沒有FULLTEXT 索引的欄位進行搜尋，但會非常慢。 限制最長與最短的字串。 套用stopwords。邏輯全文檢索搜尋支援下面的操作符：+ 一個領頭的加號表示，返回的結果的每行都必須包含有該單詞。- 一個領頭的減號表示，包含該單詞的行不能出現在返回的結果中。> 操作符增加包含該單詞返回行相似性值的基值。< 操作符減少包含該單詞返回行相似性值的基值。() 被括弧包含的多個詞只相當一個詞，即在查詢時括弧裡只有一個詞可代表該括弧與括弧外的詞相結合做查詢，但括弧中每個詞都會依次被輪到代表該括弧，所以與括弧外單詞會產生多種結合形式，依次做查詢條件。~ 將其相關性由正轉負，表示擁有該字會降低相關性，但不像 - 將之排除，只是排在較後面。* 一個星號是截斷操作符，它應該被追加到一個詞後，不加在前面。作用類似 LIKE 語句中的 %"" 把被雙引號包含的多個詞作為一個詞這裡是一些樣本，在返回結果中：1.+apple +juice ... 兩個詞均在被包含2.+apple macintosh ... 包含詞 “apple”，但是如果同時包含 “macintosh”，它的排列將更高一些3.+apple -macintosh ... 包含 “apple” 但不包含 “macintosh”4.+apple +(>pie <strudel) ... 包含 “apple” 和 “pie”，或者包含的是 “apple” 和 “strudel” (以任何次序)，但是“apple pie” 排列得比 “apple strudel” 要高一點5.apple* ... 包含 “apple”，“apples”，“applesauce” 和 “applet”6."some words" ... 可以包含 “some words of wisdom”，但不是 “some noise words”*/SELECT *,MATCH (username,city) AGAINST ('>>name300 +thisisname -city100' IN BOOLEAN MODE) FROM mytable WHERE MATCH (username,city) AGAINST ('>>name300 +thisisname -city100' IN BOOLEAN MODE);/*全文索引的限制MATCH() 函數的所有參數必須是從來自於同一張表的列，同時必須是同一個FULLTEXT 索引中的一部分，除非 MATCH() 是 IN BOOLEAN MODE 的。MATCH() 列必須確切地匹配表的某一 FULLTEXT 索引中定義的列，除非 MATCH() 是 IN BOOLEAN MODE 的。AGAINST() 的參數必須是一個常量字串。MySQL全文搜尋設定： 大部分的參數都是啟動參數，也就是修改後必須重新啟動MySQL。 有些參數修改必須重新產生索引檔案。 mysql> SHOW VARIABLES LIKE 'ft%'; ft_boolean_syntax    + -><()~*:""&| ft_min_word_len    4 ft_max_word_len    84 ft_query_expansion_limit   20 ft_stopword_file    (built-in) ft_min_word_len：最短的索引字串，預設值為4，修改後必須重建索引檔案。 ft_max_word_len：最長的索引字串，預設值因版本而不同，餘同上一點。 [mysqld] ft_min_word_len=1 ft_stopword_file：stopword檔案路徑，若留空白不設定表示要停用stopword過濾，修改後必須重新啟動MySQL和重建索引；stopword檔案內容可以用分行空白與逗號區隔stopword，但底線和單引號視為合法的字串字元。 50%的門坎限制：設定檔在storage/myisam/ftdefs.h，將 #define GWS_IN_USE GWS_PROB 改為 #define GWS_IN_USE GWS_FREQ，然後重新編譯MySQL，因為近低門坎會影響資料的精準度，所以不建議如此，可用IN BOOLEAN MODE即可以避開50%的限制。 ft_boolean_syntax：改變IN BOOLEAN MODE的查詢字元，不用重新啟動MySQL也不用重建索引。 修改字串字元的認定，譬如說將「-」認定為字串的合法字元： 方法一：修改storage/myisam/ftdefs.h的true_word_char()與misc_word_char()，然後重新編譯MySQL，最後重建索引。 方法二：修改字元集檔，然後在FULLTEXT index的欄位使用該字元集，最後重建索引。 重建索引： 每個有FULLTEXT index的表格都要這麼做。 mysql> REPAIR TABLE tbl_name QUICK; 要注意如果用過myisamchk，會導致上述的設定值回複成預設值，因為myisamchk不是用MySQL的設定值。 解法一：將修改過得設定值加到myisamchk的參數裡。 shell> myisamchk --recover --ft_min_word_len=1 tbl_name.MYI 解法二：兩邊都要設定。 [mysqld] ft_min_word_len=1 [myisamchk] ft_min_word_len=1 解法三：用REPAIR TABLE、ANALYZE TABLE、OPTIMIZE TABLE與ALTER TABLE取代myisamchk文法，因為這些文法是由MySQL執行的。中文全文索引可以建兩個表，一個表欄位裡存中文，一個表對應欄位存漢語拼音，兩表行必須對應，資料一致，插入時中文轉化下漢語拼音，兩表都插入查詢時也轉化下，全文索引查漢語拼音，然後找到中文表對應行或者使用mysqlcft中文全文索引外掛程式查看索引使用方式如果索引正在工作，Handler_read_key的值將很高，這個值代表了一個行被索引值讀的次數，很低的值表明增加索引得到的效能改善不高，因為索引並不經常使用。Handler_read_rnd_next的值高則意味著查詢運行低效，並且應該建立索引補救。這個值的含義是在資料檔案中讀下一行的請求數。如果你正進行大量的表掃描，該值較高。通常說明表索引不正確或寫入的查詢沒有利用索引。文法：SHOW STATUS LIKE 'Handler_read%';MyISAM表的資料檔案和索引檔案是自動分開的InnoDB的資料和索引是儲存在同一個資料表空間裡面，但可以有多個檔案組成雖然索引大大提高了查詢速度，同時卻會降低更新表的速度，如對錶進行INSERT、UPDATE和DELETE。因為更新表時，MySQL不僅要儲存資料，還要儲存一下索引檔案。建立索引會佔用磁碟空間的索引檔案。一般情況這個問題不太嚴重，但如果你在一個大表上建立了多種複合式索引，索引檔案的會膨脹很快。