本文更新于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：在資料檔案中讀下一個行的次數，高表示索引不經常使用，進行大量的表掃描，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/16378.html
標籤：MySQL
上一篇：MySQL學習筆記（8）：字符集