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.合理設定索引