我們都知道一張表可以建很多索引，我們?cè)趫?zhí)行一次查詢時(shí)并沒有執(zhí)行使用哪個(gè)索引，是誰在幫助我們決定使用什么索引？是mysql優(yōu)化器在判斷該使用什么索引，優(yōu)化器判斷的有的時(shí)候未必是正確的。

優(yōu)化器的邏輯

優(yōu)化器選擇索引的目的，是找到一個(gè)最優(yōu)的執(zhí)行方案，并用最小的代價(jià)去執(zhí)行語句。所謂小的代價(jià)衡包括掃描的行數(shù)、是否使用臨時(shí)表、是否排序等因素。

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

MySQL在真正開始執(zhí)行語句之前，并不能精確地知道滿足這個(gè)條件的記錄有多少條，而只能根據(jù)統(tǒng)計(jì)信息來估算記錄數(shù)。這個(gè)統(tǒng)計(jì)信息就是索引的“區(qū)分度”。顯然，一個(gè)索引上不同的值越多，這個(gè)索引的區(qū)分度就越好。而一個(gè)索引上不同的值的個(gè) 數(shù)，我們稱之為“基數(shù)”(cardinality)。也就是說，這個(gè)基數(shù)越大，索引的區(qū)分度越好。比如問在現(xiàn)在有張表，有性別字段，在性別上建索引行不行？行當(dāng)然是行的，只是這個(gè)索引建的意義不是很大，這個(gè)索引基數(shù)太小，區(qū)分度不大。我們可以使用show index方法，看到一個(gè)索引的基數(shù)。我們可以使用show index方法，看到一個(gè)索引的基數(shù)。

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

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

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

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

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

• 設(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。

沒有場(chǎng)景的討論都是耍流氓，我們就來假設(shè)一種場(chǎng)景，先建一張表，表里有a，b兩個(gè)字段，并分別建立索引：

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;

我們往表t中插入10萬行記錄，取值按整數(shù)遞增，即:(1,1,1)，(2,2,2)，(3,3,3) 直到(100000,100000,100000)。

delimiter ;;
create procedure idata() 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;;
delimiter ;
call idata();

我們先來分析第一條sql

select * from t where a between 10000 and 20000;

就這？就這還用分析？a上有索引，明顯使用索引a的。

image.png

這條查詢語句的執(zhí)行也確實(shí)符合預(yù)期，key這個(gè)字段值是’a’，表示優(yōu)化器選擇了索引a。我們使用復(fù)雜點(diǎn)的場(chǎng)景

session A的操作你已經(jīng)很熟悉了，它就是開啟了一個(gè)事務(wù)。隨后，session B把數(shù)據(jù)都刪除后，又調(diào)用了 idata這個(gè)存 儲(chǔ)過程，插入了10萬行數(shù)據(jù)。

這時(shí)候，session B的查詢語句select * from t where a between 10000 and 20000就不會(huì)再選擇索引a了。我們可以通過慢查 詢?nèi)罩?slow log)來查看一下具體的執(zhí)行情況。

為了說明優(yōu)化器選擇的結(jié)果是否正確，我增加了一個(gè)對(duì)照，即:使用force index(a)來讓優(yōu)化器強(qiáng)制使用索引a(這部分內(nèi)容， 我還會(huì)在這篇文章的后半部分中提到)。

下面的三條SQL語句，就是這個(gè)實(shí)驗(yàn)過程。

set long_query_time=0;
select * from t where a between 10000 and 20000; /*Q1*/
select * from t force index(a) where a between 10000 and 20000 /*Q2*/

• 第一句，是將慢查詢?nèi)罩镜拈撝翟O(shè)置為0，表示這個(gè)線程接下來的語句都會(huì)被記錄入慢查詢?nèi)罩局?
• 第二句，Q1是session B原來的查詢;
• 第三句，Q2是加了force index(a)來和session B原來的查詢語句執(zhí)行情況對(duì)比。

image.png

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

其中，Q1的結(jié)果還是符合預(yù)期的，rows的值是104620;但是Q2的rows值是37116，偏差就大了。而第一張圖我們用explain命令

rows這個(gè)字段表示的是預(yù)計(jì)掃描行數(shù)。 看到的rows是只有10001行，是這個(gè)偏差誤導(dǎo)了優(yōu)化器的判斷。

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

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

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

MySQL選錯(cuò)索引，這件事兒還得歸咎到?jīng)]能準(zhǔn)確地判斷出掃描行數(shù)。

既然是統(tǒng)計(jì)信息不對(duì)，那就修正。analyze table t 命令，可以用來重新統(tǒng)計(jì)索引信息。如果只是索引統(tǒng)計(jì)不準(zhǔn)確，通過analyze命令可以解決很多問題，但是前面我們說了，優(yōu)化器可不止是看掃描行數(shù)。依然是基于這個(gè)表t，我們看看另外一個(gè)語句:

select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1;

為了便于分析，我們先來看一下a、b這兩個(gè)索引的結(jié)構(gòu)圖。

image.png

使用索引a進(jìn)行查詢，那么就是掃描索引a的前1000個(gè)值，然后取到對(duì)應(yīng)的id，再到主鍵索引上去查出每一行，然后根據(jù)

字段b來過濾。顯然這樣需要掃描1000行。

如果使用索引b進(jìn)行查詢，那么就是掃描索引b的最后50001個(gè)值，與上面的執(zhí)行過程相同，也是需要回到主鍵索引上取值再判

斷，所以需要掃描50001行。

explain select * from t where (a between 1 and 1000) and (b between 50000 and 100000) order by b limit 1

可以看到，返回結(jié)果中key字段顯示，這次優(yōu)化器選擇了索引b，而rows字段顯示需要掃描的行數(shù)是50198。

從這個(gè)結(jié)果中，你可以得到兩個(gè)結(jié)論:

1. 掃描行數(shù)的估計(jì)值依然不準(zhǔn)確;
2. 這個(gè)例子里MySQL又選錯(cuò)了索引。

索引選擇異常和處理

其實(shí)大多數(shù)時(shí)候優(yōu)化器都能找到正確的索引，但偶爾你還是會(huì)碰到我們上面舉例的這兩種情況:

1. 一種方法是，像我們第一個(gè)例子一樣，采用force index強(qiáng)行選擇一個(gè)索引。
2. 第二種方法就是，我們可以考慮修改語句，引導(dǎo)MySQL使用我們期望的索引。比如，在這個(gè)例子里，顯然把“order by b limit 1” 改成 “order by b,a limit 1” ，語義的邏輯是相同的。
3. 第三種方法是，在有些場(chǎng)景下，我們可以新建一個(gè)更合適的索引，來提供給優(yōu)化器做選擇，或刪掉誤用的索引。