mysql查詢效率設置，mysql多個索引怎么選擇？

mysql多索引查詢選擇

MySQL選擇索引-引入

我們知道我們一個表里面可以有多個索引的，那么我們查詢數據的時候不指定索引，MySQL就會幫我們自動選擇。既然是MySQL程序幫我們自動選擇的那么會不會有問題的呢？答案是會的，MySQL的優化器也有bug，有時候選擇的索引并不是最優的。

案例1

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

select * from t where a between 10000 and 20000;

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

在一定的前提下

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

執行第二句，強制使用a索引，只掃描了10001行

為啥會出現這種情況呢？我們就從優化器的邏輯開始研究

優化器的邏輯

優化器優化判斷的指標

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

掃描行數判斷

上面的案例明顯就是掃描行數的問題

那么優化器是怎么獲取掃描的總行數的，其實就和抽樣檢查類似，因為索引是有序的，就可以使用采樣統計這種算法算出大概的掃描行數，可以通過show index查看索引的Cardinality預估值。

案例分析

我們通過explain來查看案例的掃描行數的預估值

rows字段就是預計的掃描行數，可見第二個選擇a索引查詢的預估掃描行數存在比較大的偏差

問題？

根據結果我們發現走a索引就算是掃描3w7行，也還是比10w快啊，為啥還是選擇了全表掃描，因為我們只考慮了掃描行數卻沒有考慮到回表這個操作，如果加上回表的一些操作那么優化器就會認為還不如走全表查詢來的快，所以優化器選擇了全表查詢。

解決

我們知道問題出在了掃描行的預估不正確，要是出現預估和現實差別比較大的情況的就可以使用analyze table zx的命令來重新預估來改變。

案例2

還是上面的表數據的格式是(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

又又又選擇錯了

原因

為啥會選錯呢，其實主要就是時排序的問題，優化器認為按索引a查詢出來的數據b不是有序的，還要排序(其實是有序的)，所以它選擇了b索引，查詢出來的數據直接就是有序的，效率會更高

怎么避免這些錯誤選擇索引呢

1.直接force index直接強制指定查詢使用的索引

2.analyze table zx重新計算預估的掃描行

3.引導sql的索引選擇，比如order by

4.合理設置索引