本文更新於2019-07-27，使用MySQL 5.7，操作系統為Deepin 15.4。 在創建一個n列的複合索引時，實際是創建了n個索引。可利用索引中最左邊的列集來匹配行，這樣的列集稱為最左首碼。 InnoDB表中的記錄會按一定順序存儲。如果有主鍵，則按主鍵順序；如果沒有主鍵但有唯一索引，則按唯 ...
本文更新於2019-07-27，使用MySQL 5.7，操作系統為Deepin 15.4。
目錄
在創建一個n列的複合索引時，實際是創建了n個索引。可利用索引中最左邊的列集來匹配行，這樣的列集稱為最左首碼。
InnoDB表中的記錄會按一定順序存儲。如果有主鍵，則按主鍵順序；如果沒有主鍵但有唯一索引，則按唯一索引順序；如果既沒有主鍵也沒有唯一索引，則會生成內部列，按內部列順序。InnoDB的普通索引都會保存主鍵的值。
索引是在存儲引擎層中實現的，而不是在伺服器層實現的，所以每種存儲引擎的索引不一定相同，也不是所有的存儲引擎都支持所有的索引類型。
索引按存儲數據結構可分為：
- BTREE索引：適用於全關鍵字、關鍵字範圍、關鍵字首碼查詢。最左首碼匹配原則是BTREE索引使用的首要原則。大部分存儲引擎都支持BTREE索引，MyISAM和InnoDB預設使用BTREE索引。
- HASH索引：適用於全關鍵字查詢，不適用於範圍查詢。只有MEMORY存儲引擎支持HASH索引，預設使用HASH索引，也支持BTREE索引。
- RTREE索引：即空間（SPATIAL）索引，主要用於地理空間數據類型。只有MyISAM存儲引擎支持RTREE索引。
- FULLTEXT索引：即全文索引。只有MyISAM存儲引擎支持FULLTEXT索引，只限於
CHAR、VARCHAR、TEXT列，索引總是對整個列進行的，不支持首碼索引。
索引也可以具有以下作用：
- 主鍵（PRIMARY）索引
- 唯一（UNIQUE）索引
- 首碼索引：對列的前面一部分進行索引。
ORDER BY和GROUP BY無法使用首碼索引。
註意，索引的長度限制以位元組為單位，DDL語句中的長度表示字元數，在使用多位元組字元集時，欄位長度不能超過索引的最大位元組長度限制。
能夠使用索引的典型場景
- 匹配全值：對索引中的所有列都指定具體的值。如對索引
a, b, c，執行WHERE a=1 AND b=2 AND c=3。 - 匹配值的範圍查詢：對索引的值能夠進行範圍查找。如對索引
a，執行WHERE a>1。 - 匹配最左首碼：僅僅使用索引最左邊的列進行查找。如對索引
a, b, c，執行WHERE a=1。 - 僅僅對索引進行查詢，效率更高。如對索引
a, b, c，執行SELECT c FROM tbl WHERE a=1。 - 匹配列首碼：僅僅使用索引中的第一列，並且只包含索引第一列開頭一部分進行查找。如對索引
a, b, c，執行WHERE a like 'xxx%'。 - 能夠實現索引部分精確匹配而其他部分進行範圍匹配。如對索引
a, b, c，執行WHERE a=1 AND b>1。 - 如果列名是索引，使用
IS NULL就會使用索引（區別於Oracle）。如對索引a，執行WHERE a IS NULL。 - 使用ICP（Index Condition Pushdown）特性，可將某些情況下的條件過濾操作下放到存儲引擎層完成，降低不必要的IO訪問。
存在索引但不能使用索引的典型場景
- 以
%開頭的LIKE查詢不能利用BTREE索引。一般推薦使用全文索引。或利用InnoDB都是聚簇表的特點，採取一種輕量級的解決方式：索引通常比表小，InnoDB表上的二級索引除存儲欄位值外，還有主鍵值。通過掃描二級索引獲取滿足條件的主鍵列表後，根據主鍵回表檢索記錄，可避開全表掃描。 - 數據類型出現隱式轉換時也不會使用索引。
- 複合索引的情況下，如果查詢條件不包含索引列最左邊的部分，即不滿足最左首碼，則不會使用複合索引。
- 如果MySQL估計使用索引比全表掃描更慢，則不使用索引。
- 用
OR分隔的條件，如前面的列有索引，後面的列沒有索引，那麼所有索引都不會被使用。因為後面的條件沒有索引，肯定需要全表掃描，沒必要增加索引的IO訪問。
查看索引使用情況
可以通過SHOW STATUS查看索引使用情況：
- Handler_read_key：一個行被索引值讀的次數。高表示索引被經常使用。
- Handler_read_rnd_next：在數據文件中讀下一個行的次數。高表示索引不經常使用，進行大量的表掃描。
