mysql 查詢優(yōu)化器，mysql多個(gè)索引怎么選擇？

mysql多索引查詢選擇

MySQL選擇索引-引入

我們知道我們一個(gè)表里面可以有多個(gè)索引的，那么我們查詢數(shù)據(jù)的時(shí)候不指定索引，MySQL就會(huì)幫我們自動(dòng)選擇。既然是MySQL程序幫我們自動(dòng)選擇的那么會(huì)不會(huì)有問(wèn)題的呢？答案是會(huì)的，MySQL的優(yōu)化器也有bug，有時(shí)候選擇的索引并不是最優(yōu)的。

案例1

假如一張表有10w的數(shù)據(jù)，有id主鍵和a，b普通索引,執(zhí)行以下SQL

select * from t where a between 10000 and 20000;

select * from t force index(a) where a between 10000 and 20000;

在一定的前提下

執(zhí)行第一句代碼走的是全表查詢，掃描了10w行

執(zhí)行第二句，強(qiáng)制使用a索引，只掃描了10001行

為啥會(huì)出現(xiàn)這種情況呢？我們就從優(yōu)化器的邏輯開始研究

優(yōu)化器的邏輯

優(yōu)化器優(yōu)化判斷的指標(biāo)

有需要掃描的行數(shù)，是否使用臨時(shí)表，是否排序等因素

掃描行數(shù)判斷

上面的案例明顯就是掃描行數(shù)的問(wèn)題

那么優(yōu)化器是怎么獲取掃描的總行數(shù)的，其實(shí)就和抽樣檢查類似，因?yàn)樗饕怯行虻模涂梢允褂貌蓸咏y(tǒng)計(jì)這種算法算出大概的掃描行數(shù)，可以通過(guò)show index查看索引的Cardinality預(yù)估值。

案例分析

我們通過(guò)explain來(lái)查看案例的掃描行數(shù)的預(yù)估值

rows字段就是預(yù)計(jì)的掃描行數(shù)，可見(jiàn)第二個(gè)選擇a索引查詢的預(yù)估掃描行數(shù)存在比較大的偏差

問(wèn)題？

根據(jù)結(jié)果我們發(fā)現(xiàn)走a索引就算是掃描3w7行，也還是比10w快啊，為啥還是選擇了全表掃描，因?yàn)槲覀冎豢紤]了掃描行數(shù)卻沒(méi)有考慮到回表這個(gè)操作，如果加上回表的一些操作那么優(yōu)化器就會(huì)認(rèn)為還不如走全表查詢來(lái)的快，所以優(yōu)化器選擇了全表查詢。

解決

我們知道問(wèn)題出在了掃描行的預(yù)估不正確，要是出現(xiàn)預(yù)估和現(xiàn)實(shí)差別比較大的情況的就可以使用analyze table zx的命令來(lái)重新預(yù)估來(lái)改變。

案例2

還是上面的表數(shù)據(jù)的格式是(1,1,1)，10w條

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

explain

又又又選擇錯(cuò)了

原因

為啥會(huì)選錯(cuò)呢，其實(shí)主要就是時(shí)排序的問(wèn)題，優(yōu)化器認(rèn)為按索引a查詢出來(lái)的數(shù)據(jù)b不是有序的，還要排序(其實(shí)是有序的)，所以它選擇了b索引，查詢出來(lái)的數(shù)據(jù)直接就是有序的，效率會(huì)更高

怎么避免這些錯(cuò)誤選擇索引呢

1.直接force index直接強(qiáng)制指定查詢使用的索引

2.analyze table zx重新計(jì)算預(yù)估的掃描行

3.引導(dǎo)sql的索引選擇，比如order by

4.合理設(shè)置索引