知識星球來自：譚某人（微訊號：tanstory）
大家都知道，mysql 一個表中可以建立多個索引，但是在執行一條查詢陳述句的時候，mysql 只能選一個索引，如果我們沒有指定 mysql 使用某個索引，那麼就是由 mysql 的最佳化器來決定要使用哪個索引了，然而，mysql 也是會有選錯的時候。
前面的文章，我們有介紹過執行一條查詢 sql 陳述句分別會經歷那些過程，執行一條sql陳述句都經歷了什麼？ 存在多個索引的情況下，最佳化器一般會透過比較掃描行數、是否需要臨時表以及是否需要排序等，來作為選擇索引的判斷依據。
我們先來新建一個表，建立兩個普通索引。
CREATE TABLE `t` (
`id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `a` (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB;
這裡我們使用儲存過程往表裡插入 10w 測試資料，如果對 mysql 的儲存過程不熟悉，請看我在程式碼中的註釋，應該能看得懂得。
#定義分割符號，mysql 預設分割符為分號;，這裡定義為 //
#分隔符的作用主要是告訴mysql遇到下一個 // 符號即執行上面這一整段sql陳述句
delimiter //
#建立一個儲存過程，並命名為 testData
create procedure testData()
#下麵這段就是表示迴圈往表裡插入10w條資料
begin
declare i int;
set i=1;
while(i<=100000)do
insert into t values(i, i, i);
set i=i+1;
end while;
end // #這裡遇到//符號，即執行上面一整段sql陳述句
delimiter ; #恢復mysql分隔符為;
call testData(); #呼叫儲存過程
資料插入完成後，我們來看下麵這條 sql 陳述句。
select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
由於主鍵id、a、b 三個欄位的值其實都是一樣的，所以其實這條 sql 陳述句的結果集為空，沒有符合條件的記錄。
我們來看看 mysql 該是怎麼選擇索引的，這裡有三個索引可用，分別是主鍵索引、索引a、索引b。
如果選擇主鍵索引雖然可以減少回表過程，但是隻能走全表掃描，需要掃描 10w 條記錄。
如果選擇索引 a，則只需在 a 索引上掃描 1k 條記錄，然後回到主鍵索引上過濾掉不滿足 b 條件的記錄，最後再按 b 排序即可。
如果選擇索引 b，則需要在 b 索引上掃描 5w 條記錄，然後同樣回到主鍵索引上過濾掉不滿足 a 條件的記錄，因為索引有序，所以使用 b 索引不需要額外排序。
我們來使用執行計劃看下 mysql 究竟會選擇哪個索引。
mysql> explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
+----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+
| 1 | SIMPLE | t | range | a,b | b | 5 | NULL | 50128 | Using index condition; Using where |
+----+-------------+-------+-------+---------------+------+---------+------+-------+------------------------------------+
1 row in set (0.12 sec)
可以看出 mysql 是選擇使用索引 b，雖然掃描行數要多一些，但因為索引本身是有序的，使用索引 b 可以避免排序，mysql 認為這個排序的代價高於掃描行數。
上面這個選擇是 mysql 最佳化器內部的分析，那麼實際情況又如何呢，我們可以分別執行一下 sql 陳述句，使用 force index(a) 強制使用索引 a 來對比下，看下兩者具體花費的時間。
mysql> select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.65 sec)
mysql> select * from t force index(a) where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;
Empty set (0.05 sec)
從結果可以看到其實使用索引 a 顯然速度會更快，所以這就是屬於 mysql 選錯了索引的情況，那我們怎麼避免這種情況呢，我們可以把 sql 陳述句改成下麵這樣的，即把 order by b 改成 order by b,a 。
select * from t where a between 1 and 1000 and b between 50000 and 100000 order by b,a limit 1;
這樣的話，在 mysql 看來無論是使用索引 a 還是索引 b 都需要排序了，那就只能選擇掃描行更少的索引了，所以 mysql 會選擇索引 a，從而達到避免 mysql 選錯索引的目的，我們可以看下最佳化後的這條 sql 的執行計劃。
mysql> explain select * from t where a between 1 and 1000 and b between 50000 and 100000 order by b,a limit 1;
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+
| 1 | SIMPLE | t | range | a,b | a | 5 | NULL | 999 | Using index condition; Using where; Using filesort |
+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+
1 row in set (0.00 sec)
大多數情況下，mysql 都會選擇正確的索引，選錯索引算是比較少見的特殊情況了，文中的例子也是個特例，僅是給大家提供一個分析思路，當你遇到一些已經使用了索引但依然比較慢的 sql 陳述句的時候，可以嘗試分析是否是 mysql 選錯了索引的原因。
其實還有一些情況，會導致 mysql 選錯索引，就是 mysql 預估掃描行的資料不夠準確，而這個不準確通常是資料表有頻繁的刪除或更新操作導致的資料空洞造成的，關於這個原因，我會在後面再詳細講。
這篇文章如果對你有些啟發，不妨點在在看吧，感謝支援，當然如果對文中有不太明白的地方，歡迎留言。另外，不知道大家對 explain 這個命令熟悉不，可以看看《在 MySQL 中使用 explain 查詢 SQL 的執行計劃》。
朋友會在“發現-看一看”看到你“在看”的內容