場景

在 MySQL 中一張表是可以支持多個索引的。但是，在寫 SQL 語句的時候，很少使用 force index(idx_xxx)主動指定使用哪個索引。也就是說，使用哪個索引是由 MySQL來確定的。

在此基礎(chǔ)上，有的時候會遇到一種情況，一條本來可以執(zhí)行得很快的語句，卻由于 MySQL 選錯了索 引，而導(dǎo)致執(zhí)行速度變得很慢。舉個例子，假設(shè)創(chuàng)建一個表

CREATE TABLE `table_test` (
    `id` int(11) NOT NULL PRIMARY KEY AUTO_INCREMENT, 
    `a_field` int(11) DEFAULT NULL, 
    `b_field` int(11) DEFAULT NULL, 
    `c_field` int(11) DEFAULT NULL, 
    `d_field` int(11) DEFAULT NULL, 
    `e_field` int(11) DEFAULT NULL, 
    `f_field` int(11) DEFAULT NULL, 
KEY `idx_a` (`a_field`),
KEY `idx_a_others` (`a_field`, `b_field`, `c_field`) 
) ENGINE=InnoDB;

然后，我們往表中插入100萬行記錄，取值按整數(shù)遞增，如：(1,1,1)，(2,2,2)，(3,3,3) 直到百萬。

-- 存儲方式
delimiter //
create procedure idata() 
begin 
    declare i int; 
    set i=1; 
    while(i<=10000000)do 
        insert into table_test values(null, 1, i, i, i, i, i); 
        set i=i+1;
    end while;
end;
//
-- 調(diào)用
call idata(); 

完成這之后，執(zhí)行語句 explain select * from table_test where a_field=1 and b_field=1 and c_field=1 and d_field=1 得到結(jié)果

mysql選錯索引01.png

從上圖看上去，key 使用的是 idx_d_others，表示優(yōu)化器選 擇了索引 idx_d_others，這條查詢語句的執(zhí)行確實符合預(yù)期，看起來很和諧沒什么問題，但是當使用些極端的騷操作的時候，如下。

為了更能夠簡單的查看選擇器選擇的結(jié)果，之后將引入使用 foce index(idx_a)的方式建立對照組，同時需要將慢查詢?nèi)罩镜拈撝翟O(shè)置為0，把所有的語句都記錄到日志之中set global slow_query_log=1; set long_query_time=0;，分別執(zhí)行以下兩個語句對比結(jié)果。如下圖所示會概率出現(xiàn)選錯索引的問題。當然這里出現(xiàn)的問題也主要是區(qū)分度較低的問題，當然idx_a_others修改為沒有a，是bcd的聯(lián)合索引也可能會出現(xiàn)這類問題

explain select * from table_test where a_field=1 and b_field=1 and c_field=1 and d_field=1;
explain select * from table_test force index (idx_a_others) where a_field=1 and b_field=1 and c_field=1 and d_field=1;
explain select * from table_test force index (idx_a) where a_field=1 and b_field=1 and c_field=1 and d_field=1;

mysql選錯索引02.png

優(yōu)化器的邏輯

優(yōu)化器選擇索引的目的，是找到一個最優(yōu)的執(zhí)行方案，并用最小的代價去執(zhí)行語句。在數(shù)據(jù)庫里面，掃描行數(shù)是影響執(zhí)行代價的因素之一。掃描的行數(shù)越少，意味著訪問磁盤數(shù)據(jù)的次數(shù)越少，消耗的 CPU 資源越少。

當然，掃描行數(shù)并不是唯一的判斷標準，優(yōu)化器還會結(jié)合是否使用臨時表、是否排序等因素進行綜合判斷。

我們這個簡單的查詢語句并沒有涉及到臨時表和排序，所以 MySQL 選錯索引肯定是在判斷掃描 行數(shù)的時候出問題了。

那么掃描行數(shù)是怎么判斷的？

MySQL 在真正開始執(zhí)行語句之前，并不能精確地知道滿足這個條件的記錄有多少條，而只能根 據(jù)統(tǒng)計信息來估算記錄數(shù)。

這個統(tǒng)計信息就是索引的“區(qū)分度”。一個索引上不同的值越多，這個索引的區(qū)分度就越好。而一個索引上不同的值的個數(shù)，我們稱之為“基數(shù)”。也就是說，這個基數(shù)（cardinality）越大，索引的區(qū)分度越好。

我們可以使用 show index from table_test 方法，看到一個索引的基數(shù)。如下圖所示，就是表show index的結(jié)果 。雖然這個表的每一行的兩個字段都是一樣的，但是在統(tǒng)計信息中，這兩個索引的基數(shù)值并不同，而且其實都不準確。

MySQL 是怎樣得到索引的基數(shù)的呢？

這里，我給你簡單介紹一下 MySQL 采樣統(tǒng)計的方法。

為什么要采樣統(tǒng)計呢？因為把整張表取出來一行行統(tǒng)計，雖然可以得到精確的結(jié)果，但是代價太 高了，所以只能選擇“采樣統(tǒng)計”。

采樣統(tǒng)計的時候，InnoDB 默認會選擇 N 個數(shù)據(jù)頁，統(tǒng)計這些頁面上的不同值，得到一個平均 值，然后乘以這個索引的頁面數(shù)，就得到了這個索引的基數(shù)。

而數(shù)據(jù)表是會持續(xù)更新的，索引統(tǒng)計信息也不會固定不變。所以，當變更的數(shù)據(jù)行數(shù)超過 1/M 的時候，會自動觸發(fā)重新做一次索引統(tǒng)計。

在 MySQL 中，有兩種存儲索引統(tǒng)計的方式，可以通過設(shè)置參數(shù) innodb_stats_persistent 的值 來選擇：

設(shè)置為 on 的時候，表示統(tǒng)計信息會持久化存儲。這時，默認的 N 是 20，M 是 10。

設(shè)置為 off 的時候，表示統(tǒng)計信息只存儲在內(nèi)存中。這時，默認的 N 是 8，M 是 16。

由于是采樣統(tǒng)計，所以不管 N 是 20 還是 8，這個基數(shù)都是很容易不準的。 但，這還不是全部。 你可以從圖 4 中看到，這次的索引統(tǒng)計值（cardinality 列）雖然不夠精確，但大體上還是差不 多的，選錯索引一定還有別的原因。

其實索引統(tǒng)計只是一個輸入，對于一個具體的語句來說，優(yōu)化器還要判斷，執(zhí)行這個語句本身要 掃描多少行。 接下來，我們再一起看看優(yōu)化器預(yù)估的，這兩個語句的掃描行數(shù)是多少。

mysql選錯索引03.png

rows 這個字段表示的是預(yù)計掃描行數(shù)。

其中，1 的結(jié)果還是符合預(yù)期的，rows 的值是 3；但是 2 的 rows 值是 637602，偏差就大了。而我們用 explain 命令看到的 rows 是只有 1 行，是這個偏差誤導(dǎo)了優(yōu)化器的判斷。

到這里，可能你的第一個疑問不是為什么不準，而是優(yōu)化器為什么放著掃描 3 行的執(zhí)行計 劃不用，卻選擇了掃描行數(shù)是 637602 的執(zhí)行計劃呢？

這是因為，如果使用索引 a，每次從索引 a 上拿到一個值，都要回到主鍵索引上查出整行數(shù)據(jù)， 這個代價優(yōu)化器也要算進去的。

而如果選擇掃描 10 萬行，是直接在主鍵索引上掃描的，沒有額外的代價。

優(yōu)化器會估算這兩個選擇的代價，從結(jié)果看來，優(yōu)化器認為直接掃描主鍵索引更快。當然，從執(zhí)行時間看來，這個選擇并不是最優(yōu)的。

使用普通索引需要把回表的代價算進去，在執(zhí)行 explain 的時候，也考慮了這個策略的代價 ，但之前圖1的選擇是對的。也就是說，這個策略并沒有問題。

所以冤有頭債有主，MySQL 選錯索引，這件事兒還得歸咎到?jīng)]能準確地判斷出掃描行數(shù)。至于 為什么會得到錯誤的掃描行數(shù)，這個原因就作為課后問題，留給你去分析了。

既然是統(tǒng)計信息不對，那就修正。analyze table t 命令，可以用來重新統(tǒng)計索引信息。

其實，如果只是索引統(tǒng)計不準確，通過 analyze 命令可以解決很多問題，但是前面我們說了， 優(yōu)化器可不止是看掃描行數(shù)?，F(xiàn)在簡化下表table_test,讓其只有字段a和b并單獨建立索引

select * from table_test where (a_field between 1 and 1000) and (b_field between 50000 and 100000) order by b

從條件上看，這個查詢沒有符合條件的記錄，因此會返回空集合。 在開始執(zhí)行這條語句之前，你可以先設(shè)想一下，如果你來選擇索引，會選擇哪一個呢？ 為了便于分析，我們先來看一下 a、b 這兩個索引的結(jié)構(gòu)圖。

mysql選錯索引04.png

如果使用索引 a 進行查詢，那么就是掃描索引 a 的前 1000 個值，然后取到對應(yīng)的 id，再到主 鍵索引上去查出每一行，然后根據(jù)字段 b 來過濾。顯然這樣需要掃描 1000 行。

如果使用索引 b 進行查詢，那么就是掃描索引 b 的最后 50001 個值，與上面的執(zhí)行過程相同， 也是需要回到主鍵索引上取值再判斷，所以需要掃描 50001 行。從這個結(jié)果中，你將可以得到兩個結(jié)論： 1. 掃描行數(shù)的估計值依然不準確； 2. 這個例子里 MySQL又選錯了索引。

索引選擇異常和處理

其實大多數(shù)時候優(yōu)化器都能找到正確的索引，但偶爾你還是會碰到我們上面舉例的這兩種情況： 原本可以執(zhí)行得很快的 SQL 語句，執(zhí)行速度卻比預(yù)期的慢很多，應(yīng)該怎么辦呢？

一種方法是，像我們第一個例子一樣，采用 force index 強行選擇一個索引。MySQL 會根據(jù)詞 法解析的結(jié)果分析出可能可以使用的索引作為候選項，然后在候選列表中依次判斷每個索引需要 掃描多少行。如果 force index 指定的索引在候選索引列表中，就直接選擇這個索引，不再評估 其他索引的執(zhí)行代價

不過很多程序員不喜歡使用 force index，一來這么寫不優(yōu)美，二來如果索引改了名字，這個語 句也得改，顯得很麻煩。而且如果以后遷移到別的數(shù)據(jù)庫的話，這個語法還可能會不兼容。

但其實使用 force index 最主要的問題還是變更的及時性。因為選錯索引的情況還是比較少出現(xiàn) 的，所以開發(fā)的時候通常不會先寫上 force index。而是等到線上出現(xiàn)問題的時候，你才會再去修改 SQL 語句、加上 force index。但是修改之后還要測試和發(fā)布，對于生產(chǎn)系統(tǒng)來說，這個 過程不夠敏捷。 所以，數(shù)據(jù)庫的問題最好還是在數(shù)據(jù)庫內(nèi)部來解決。那么，在數(shù)據(jù)庫里面該怎樣解決呢？

第二種方法就是，我們可以考慮修改語句，引導(dǎo) MySQL 使用我們期望的索引。還是用上述的例子來說明，把order by b limit 1改成 order by b,a limit 1 ，語義的邏輯是相同的。

當然，這種修改并不是通用的優(yōu)化手段，只是剛好在這個語句里面有 limit 1，因此如果有滿足 條件的記錄， order by b limit 1 和 order by b,a limit 1 都會返回 b 是最小的那一行，邏輯上 一致，才可以這么做。

第三種方法是，新建一個更合適的索引，來提供給優(yōu)化器做選擇，或刪 掉誤用的索引。 當然是最優(yōu)先推薦第三種方式，不過有的時候做這種ddl的時候可能會造成額外的問題，需要謹慎處理。

小結(jié)

要記住，設(shè)計好索引才是王道