復(fù)盤(pán)一次MySQL選錯(cuò)索引

場(chǎng)景

在 MySQL 中一張表是可以支持多個(gè)索引的。但是，在寫(xiě) SQL 語(yǔ)句的時(shí)候，很少使用 force index(idx_xxx)主動(dòng)指定使用哪個(gè)索引。也就是說(shuō)，使用哪個(gè)索引是由 MySQL來(lái)確定的。

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

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萬(wàn)行記錄，取值按整數(shù)遞增，如：(1,1,1)，(2,2,2)，(3,3,3) 直到百萬(wàn)。

-- 存儲(chǔ)方式
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í)行語(yǔ)句 explain select * from table_test where a_field=1 and b_field=1 and c_field=1 and d_field=1 得到結(jié)果

mysql選錯(cuò)索引01.png

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

SessionA SessionB
start transation with consistent snapshot;
select sleep(20); delete from table_test; call idata();
commit;
explain select * from table_test where a_field=1 and b_field=1 and c_field=1 and d_field=1

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

優(yōu)化器的邏輯

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

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

我們這個(gè)簡(jiǎn)單的查詢(xún)語(yǔ)句并沒(méi)有涉及到臨時(shí)表和排序，所以 MySQL 選錯(cuò)索引肯定是在判斷掃描 行數(shù)的時(shí)候出問(wèn)題了。

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

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

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

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

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

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

為什么要采樣統(tǒng)計(jì)呢？因?yàn)榘颜麖埍砣〕鰜?lái)一行行統(tǒng)計(jì)，雖然可以得到精確的結(jié)果，但是代價(jià)太 高了，所以只能選擇“采樣統(tǒng)計(jì)”。

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

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

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

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

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

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

其實(shí)索引統(tǒng)計(jì)只是一個(gè)輸入，對(duì)于一個(gè)具體的語(yǔ)句來(lái)說(shuō)，優(yōu)化器還要判斷，執(zhí)行這個(gè)語(yǔ)句本身要 掃描多少行。 接下來(lái)，我們?cè)僖黄鹂纯磧?yōu)化器預(yù)估的，這兩個(gè)語(yǔ)句的掃描行數(shù)是多少。

mysql選錯(cuò)索引03.png

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

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

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

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

而如果選擇掃描 10 萬(wàn)行，是直接在主鍵索引上掃描的，沒(méi)有額外的代價(jià)。

優(yōu)化器會(huì)估算這兩個(gè)選擇的代價(jià)，從結(jié)果看來(lái)，優(yōu)化器認(rèn)為直接掃描主鍵索引更快。當(dāng)然，從執(zhí)行時(shí)間看來(lái)，這個(gè)選擇并不是最優(yōu)的。

使用普通索引需要把回表的代價(jià)算進(jìn)去，在執(zhí)行 explain 的時(shí)候，也考慮了這個(gè)策略的代價(jià) ，但之前圖1的選擇是對(duì)的。也就是說(shuō)，這個(gè)策略并沒(méi)有問(wèn)題。

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

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

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

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

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

mysql選錯(cuò)索引04.png

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

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

索引選擇異常和處理

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

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

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

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

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

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

第三種方法是，新建一個(gè)更合適的索引，來(lái)提供給優(yōu)化器做選擇，或刪 掉誤用的索引。 當(dāng)然是最優(yōu)先推薦第三種方式，不過(guò)有的時(shí)候做這種ddl的時(shí)候可能會(huì)造成額外的問(wèn)題，需要謹(jǐn)慎處理。

小結(jié)

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

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時(shí)請(qǐng)結(jié)合常識(shí)與多方信息審慎甄別。
平臺(tái)聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點(diǎn)，簡(jiǎn)書(shū)系信息發(fā)布平臺(tái)，僅提供信息存儲(chǔ)服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容