引言
- 在一個(gè)列或多個(gè)列上建立索引，其本質(zhì)是為這些列上的數(shù)據(jù)組織成平衡二叉樹(B+Tree)之后，將基于全表掃描的
時(shí)間復(fù)雜度優(yōu)化為基于二分查找的
時(shí)間復(fù)雜度，以大大提升效率。
- 每一條sql語句在提交到Mysql服務(wù)端后，在真正執(zhí)行前都會(huì)經(jīng)過優(yōu)化器優(yōu)化，優(yōu)化器會(huì)判斷能否使用索引，如果有多個(gè)索引可以使用，還會(huì)進(jìn)行成本計(jì)算來選擇一個(gè)較優(yōu)的執(zhí)行方式。
- 不恰當(dāng)?shù)膕ql語句往往無法利用索引來進(jìn)行查詢優(yōu)化，反而為創(chuàng)建索引白白浪費(fèi)磁盤空間。
- 下面介紹幾種會(huì)造成索引失效的不恰當(dāng)查詢方法。
最左前綴匹配原則
- 多數(shù)索引失效的情況都發(fā)生在聯(lián)合索引的情況，比如在使用聯(lián)合索引時(shí)沒有遵守最左前綴匹配原則。
- 先說下什么是最左前綴匹配原則，首先創(chuàng)建一個(gè)簡(jiǎn)單的表t1，并在a, b列上添加聯(lián)合索引idx_a_b。
CREATE TABLE t1(
id INT NOT NULL auto_increment,
a INT NOT NULL,
b Int NOT NULL,
PRIMARY KEY(id),
KEY idx_a_b(a, b)
);
- 再給表中添加一些演示數(shù)據(jù)
INSERT INTO t1 VALUES(1, 1, 1),(2,1,2),(3,2,2),(4,2,3),(5,2,4),(6,3,1),(7,3,2);
SELECT * FROM t1;
+----+---+---+
| id | a | b |
+----+---+---+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 2 |
| 4 | 2 | 3 |
| 5 | 2 | 4 |
| 6 | 3 | 1 |
| 7 | 3 | 2 |
+----+---+---+
- 我們先給出一些符合最左匹配原則和一些不符合最左匹配原則的示例，再分析具體原因。
SELECT * FROM t1 WHERE a = xxx AND b = xxx; # 符合
SELECT * FROM t1 WHERE a = xxx AND b = xxx; # 符合
SELECT * FROM t1 WHERE b = xxx; # 不符合
可以看到最后一條查詢語句跳過了a直接對(duì)b進(jìn)行查詢，不符合最左前綴匹配原則。（以上只是一些示例，還有很多復(fù)雜的情況。）
- 因?yàn)槲覀冊(cè)诹?code>a和
b上添加了聯(lián)合索引idx_a_b,Mysql會(huì)為我們?cè)诖疟P上建立一個(gè)二級(jí)索引(非聚簇索引)，我們假設(shè)這個(gè)二級(jí)索引如下所示（只是一個(gè)簡(jiǎn)單的示意圖，葉子結(jié)點(diǎn)之間還有雙向鏈表相連，且葉節(jié)點(diǎn)還會(huì)存儲(chǔ)主鍵值）
idx_a_b索引結(jié)構(gòu) - 這顆
B+樹中會(huì)根據(jù)列a進(jìn)行排序，在列a值相同的情況下，再根據(jù)列b的值進(jìn)行排序。 - 現(xiàn)在我們查詢(2,2)
SELECT * FROM t1 WHERE a=2 AND b=2;
+----+---+---+
| id | a | b |
+----+---+---+
| 3 | 2 | 2 |
+----+---+---+
- 再使用EXPLAIN對(duì)該條語句進(jìn)行分析
EXPLAIN SELECT * FROM t1 WHERE a=2 AND b=2;
+------+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+
| 1 | SIMPLE | t1 | ref | idx_a_b | idx_a_b | 8 | const,const | 1 | Using index |
+------+-------------+-------+------+---------------+---------+---------+-------------+------+-------------+
- 發(fā)現(xiàn)其確實(shí)是利用到了我們?cè)O(shè)置的索引
idx_a_b，其具體步驟:
- 在
B+Tree的第一層與非葉子結(jié)點(diǎn)(2,4)比較，因?yàn)榱?code>a值都相同，此時(shí)再比較列b的值。- 因?yàn)?
b的值,此時(shí)確定到左側(cè)的葉節(jié)點(diǎn)上繼續(xù)查找。
- 在左側(cè)的頁節(jié)點(diǎn)內(nèi)部繼續(xù)使用同上二分查找的方式定位到目的節(jié)點(diǎn)，最后再根據(jù)主鍵值（未畫出）進(jìn)行回表操作。
- 同理，我們分析一下
SELECT * FROM t1 WHERE a = 2;
EXPLAIN SELECT * FROM t1 WHERE a=2;
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
| 1 | SIMPLE | t1 | ref | idx_a_b | idx_a_b | 4 | const | 3 | Using index |
+------+-------------+-------+------+---------------+---------+---------+-------+------+-------------+
可以看到也成功利用索引查找到列第一個(gè)滿足a=2的記錄，然后再通過記錄間的前后指針將滿足要求的記錄全部查找出來。
- 再看一下違反了最左前綴匹配原則的查詢
SELECT * FROM t1 WHERE b = 2
EXPLAIN SELECT * FROM t1 WHERE b=2;
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
| 1 | SIMPLE | t1 | index | NULL | idx_a_b | 8 | NULL | 13 | Using where; Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+--------------------------+
那么問題來了，雖然possible_keys確實(shí)等于null，但是實(shí)際上key里卻用到了idx_a_b索引，不是說違反了最左前綴匹配原則，會(huì)發(fā)生索引失效么？其實(shí)使用了索引和以二分查找的方式使用索引是不一樣的，這里雖然用到了idx_a_b索引，也只是因?yàn)檫@個(gè)聯(lián)合索引上有b這個(gè)列，并且在這個(gè)索引上使用了掃描的方式來獲取滿足條件的主鍵值再進(jìn)行回表操作，與之前利用二分查找的方式相比是很慢的。 我們還可以注意到ref這一列的值為NULL,也就是說確實(shí)沒用到任何等值匹配條件，索引確實(shí)失效了。
- 為什么違反了最左前綴匹配原則的查詢語句就會(huì)導(dǎo)致索引失效呢？我們把上圖葉子節(jié)點(diǎn)的值中
a列不考慮，只看b列，則其為1、2、2、3、4、1、2。很顯然是無序的，而能夠使用二分查找的最根本條件就是有序，所以自然也就無法利用二分查找來提升效率，正確的使用索引了。
利用索引優(yōu)化排序操作
- 前面我們說了索引已經(jīng)為我們將某些列上的數(shù)據(jù)組織成有序，那么當(dāng)在查詢中涉及排序操作時(shí)，很多時(shí)候可以避免在內(nèi)存中排序，提升效率。比如以下三條語句。
EXPLAIN SELECT * FROM t1 ORDER BY a, b;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | idx_a_b | 8 | NULL | 13 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
EXPLAIN SELECT * FROM t1 ORDER BY a;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| 1 | SIMPLE | t1 | index | NULL | idx_a_b | 8 | NULL | 13 | Using index |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
EXPLAIN SELECT * FROM t1 ORDER BY b;
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------+
| 1 | SIMPLE | t1 | index | NULL | idx_a_b | 8 | NULL | 13 | Using index; Using filesort |
+------+-------------+-------+-------+---------------+---------+---------+------+------+-----------------------------+
前兩條因?yàn)樽饛牧俗钭笄熬Y匹配原則，取出的數(shù)據(jù)本身就是有序的，而最后一條可以看到了使用了Using filesort，因?yàn)橐詁列中的元素本身不是有序的，所以需要在內(nèi)存中再進(jìn)行一次排序。
