Mysql索引失效怎麼辦？失效原因淺析

這篇文章給大家記錄一次Mysql索引失效，分析一下Mysql索引失效原因，希望對大家有幫助！

這篇文章包含Mysql的Where條件查詢執行過程、範圍查詢使聯合索引停止匹配、回表操作分析、常見索引失效場景、Extra分析等知識。 【相關推薦：mysql影片教學】

背景

6千萬資料量的資料表出現了一個滿查詢，復現sql語句發現查詢並沒有走索引而是走全表查詢，找出索引失效原因。

# sql语句
EXPLAIN SELECT count(*) FROM order_recipient_extend_tab WHERE start_date>&#39;1628442000&#39; and start_date<&#39;1631120399&#39; and station_id=&#39;1809&#39; and status=&#39;2&#39;;

order_recipient_extend_tab 表有6千萬數據，慢查詢的查詢欄位包括start_date、station_id、status，依照索引設計初衷會走但實際上失效的索引是：

#start_datestation_iddriver_id

聯合索引	#欄位1	#欄位2	欄位3
	idx_date_station_driver

##########

Where條件查詢執行程序

了解Mysql怎麼執行where條件查詢，能更快速清楚地洞見索引失效的原因。此慢查詢中符合度高的索引是idx_date_station_driver，分析此慢查詢中where條件查詢的執行過程。

Mysql對where條件提取規則主要可以歸納為三大類：Index Key (First Key & Last Key)，Index Filter，Table Filter 。

Index Key

Index Key用於決定此sql查詢在索引樹上的範圍。一個範圍包括起始和終止，Index First Key用於定位索引查詢的起始範圍，Index Last Key用於定位索引查詢的終止範圍。

• Index First Key

  提取規則：從索引的第一個欄位開始，檢查該欄位在where條件中是否存在，若存在且條件是=、 >=，則將對應的條件加入Index First Key之中，繼續讀取索引的下一個字段；若存在且條件是>，則將對應的條件加入Index First Key中，然後終止Index First Key的提取；若不存在，也終止Index First Key的提取。

• Index Last Key

  與Index First Key正好相反，擷取規則：從索引的第一個欄位開始，檢查其在where條件中是否存在，若存在且條件是=、

依照Index Key的提取規則，在此次慢查詢中提取出來的Index Last Key為：start_date>'1628442000'，Index Last Key為： start_date

Index First Key只是用來定位索引的起始範圍，使用Index First Key條件，從索引B 樹的根節點開始，使用二分搜尋方法快速索引到正確的葉節點位置。 Where查詢過程中Index First Key只做了一次判斷。

Index Last Key，用來定位索引的終止範圍，因此對於起始範圍之後讀到的每一條索引記錄，均需要判斷是否已經超過了Index Last Key的範圍，若超過，則當前查詢結束。

Index Filter

在Index Key確定的索引範圍中，並不是所有的索引記錄都滿足查詢條件。例如Index Last Key和Index Last Key範圍中，不是所有索引記錄都滿足 station_id = '1809'。這時候就需要用到Index Filter了。

Index Filter，又稱為索引下推，用於過濾索引查詢範圍中不滿足查詢條件的記錄。對於索引範圍中的每一筆記錄，均需要與Index Filter進行對比，若不滿足Index Filter則直接丟棄，繼續讀取索引下一筆記錄。

Index Filter的提取規則：從索引的第一個字段開始，檢查其在where條件中是否存在，若存在且條件僅為=，則跳過第一字段繼續檢查索引下一字段，下一索引列採取相同的提取規則（解釋：條件為=的字段已經在Index Key中過濾掉了）；若存在且條件為>=、>、

依照Index Filter的提取規則，在此次慢查詢中提取出來的Index Filter為：station_id='1809'。在Index Key所確定的索引查詢範圍中，遍歷索引記錄時都需要比較 station_id='1809'，不滿足該條件則直接遺失，繼續讀取索引下一記錄。

Table Filter

Table Filter用於過濾掉索引無法過濾的資料。在二級索引中以主鍵回表查詢到整行記錄後，判斷該記錄是否符合Table Filter條件，不符合則遺失，繼續判斷下一筆記錄。

提取規則很簡單：所有不屬於索引欄位的查詢條件，都歸為Table Filter之中。依照Table Filter的擷取規則，在本次查詢中Table Filter為：status=‘2’。

總結和補充

Index Key用於確定索引掃描的範圍；Index Filter用於在索引中進行過濾；Table Filter需要回表後在Mysql伺服器進行過濾。

Index Key和Index Filter發生在InnoDB儲存層，Table Filter發生在Mysql Server層。

在MySQL5.6 之前，不區分Index Filter與Table Filter，統統將Index First Key與Index Last Key範圍內的索引記錄，回表讀取完整記錄，然後傳回給MySQL Server層進行過濾。

在MySQL 5.6及之後，Index Filter與Table Filter分離，Index Filter下降到InnoDB的儲存引擎層進行過濾，減少了回表與傳回MySQL Server層的記錄交互開銷，提高了SQL的執行效率。

分析索引失效原因

首先是count()，此時通配符* 經最佳化並不會拓展所有列，實際上會忽略所有的列直接統計行數。所以只想收集行數最好使用count()。

接下來分析where語句。假設此慢查詢會使用了二級索引idx_date_station_driver，按照上面where條件查詢的執行過程，該慢查詢的Index First Key為start_date>'1628442000'，Index Last Key為： start_date

提取Index First Key後在索引B 樹上定位索引起始範圍就是索引匹配的過程，在索引B 樹上使用二分搜尋方法快速定位符合查詢條件的起始葉子節點。透過上文Where條件查詢執行過程，我們知道該慢查詢的where條件（start_date>'1628442000' and start_date，只匹配了索引<code>idx_date_station_driver(start_date, station_id, driver_id)的第一個字段，即只匹配了idx_date_station_driver(start_date)，station_id=查詢'1809'精確查詢並沒有作用到精確匹配索引上，而是在Index Filter即索引下推過程中發揮了作用。實際上這裡是因為範圍查詢使聯合索引停止匹配。

範圍查詢導致聯合索引停止匹配

為什麼範圍查詢會使聯合索引停止匹配？這裡涉及到最左前綴匹配原理。假設建立一個聯合索引 index(a, b)，會先對a進行排序，在a相等的情況下對b進行排序，如下圖所示。在這個索引樹上，a是全域有序的，而b則處於全域無序、局部有序狀態。從全域來看，b的值為1、2、1、4、1、2，只有b=2 查詢條件無法直接使用該索引；從局部來看，當a的值確定時，b則是有序狀態，a=2 && b=4可以使用該索引。所以範圍查詢使聯合索引停止匹配的根本原因是，索引樹上非首字段的有序狀態依賴前一個字段相等情況，而範圍查詢破壞了下一個索引字段局部有序狀態，導致索引停止匹配。

範圍查詢使聯合索引停止匹配，並不能在索引匹配的時候就過濾掉station_id不等於'1809' 的數據，導致Mysql在索引上的掃描範圍Index First Key和Index Last Key完全由start_timestamp_of_date時間決定。 start_timestamp_of_date範圍查詢可以過濾73%資料量，而station_id='1809'精確查詢能過濾掉99%的資料量。

# #所有資料6367萬100%27.35%

查詢條件	資料量	佔比

#########station_id='1809'#######8萬#######0.16%########## ###

回表操作的開銷

由於status欄位不在索引idx_date_station_driver欄位上，所以需要回表查詢索引過濾的數據，在Mysql服務層判資料是否符合查詢條件。

Mysql的最佳化器在執行sql語句時會先估算走匹配度高的索引的開銷，如果走索引的開銷比查全表還大，那麼Mysql會選擇全表掃描。這個結論可能反常識，在我們印像中索引就是用來提高查詢效率的。這裡主要涉及兩個因素：

• 當查詢條件或尋找的欄位不在二級索引的欄位上時，會執行回表操作，會走：二級索引 主鍵索引。

• 磁碟隨機I/O的效能低於順序I/O。回表查詢在主鍵索引上是隨機I/O，全表掃描在主鍵索引上是順序I/O。

做實驗分析回表運算的開銷是否是索引失效的直接原因？

移除status='0'查詢條件，explain查看該查詢是否使用到了索引idx_date_station_driver。結果如下圖所示，少了回表操作的開銷，索引並未失效。

總結

結合上述分析總結索引失效原因是：範圍查詢使聯合索引停止匹配，索引匹配過濾的數據不夠多，導致Mysql優化器估算出Table Filter的回表操作開銷大於全表查詢，所以選擇了全表查詢。範圍查詢使聯合索引停止匹配是索引失效的罪魁禍首，而回表操作的開銷是索引失效的直接原因。

優化索引

該慢查詢索引失效的罪魁禍首是範圍查詢使聯合索引停止匹配，只需要把範圍查詢的字段調整到精確查詢的字段後面，即將

聯合索引idx_date_station_driver(start_date, station_id, driver_id) 修改為idx_station_date_driver(station_id, start_date, driver_id) 。優化後的結果如下圖所示。

拓展

#索引失效常見場景

• 違反最左前綴匹配原則。例如有索引index(a,b)，但查詢條件只有b欄位。

• 在索引列上做任何操作，包括計算、函數、型別轉換等。

• 範圍查詢使聯合索引停止匹配。

• 減少select*的使用。避免不必要的回表操作開銷，盡量使用覆蓋索引。

• 使用不等於（!=、），使用or運算。

• 字串不加單引號索引失效。

• like以萬用字元開頭'�c'。注意like ‘abc%’ 是可以走索引的。

• order by 違反最左符合原則，含非索引欄位排序，會產生檔案排序。

• group by 違反最左符合原則，含非索引欄位分組，會導致產生臨時表。

Explain分析

慢查詢的分析離不開mysql的explain語句，explain主要關注兩個欄位Type和Extra。

Type表示存取資料的方式，Extra表示過濾和整理資料的方式。這裡列舉出來方便找。

##將查詢轉換成常數Using filesort檔案排序，用於排序#NULL不用存取表或索引NULL回表

Type		Extra
ALL	全表掃描	Using index	使用覆蓋索引，不需要回表，不需要Mysql服務層過濾
index	索引樹全掃描	Using where	從儲存引擎層取得數據，在Mysql服務層用where查詢條件過濾數據。
range	索引樹範圍掃描	Using where; Using index	索引範圍掃描。索引掃描和全表掃描類似，只是發生的層面不一樣。
ref	非唯一性索引掃描，例如非唯一索引和唯一索引的非唯一前綴	Using index condition	使用索引下推，在儲存引擎層充分利用查詢索引欄位過濾資料
eq_ref	唯一性索引掃描，例如唯一索引、主鍵索引	Using temporary	暫存表儲存結果，用於排序和分組查詢
#const

更多程式相關知識，請造訪：

程式設計入門！ ！

以上是Mysql索引失效怎麼辦？失效原因淺析的詳細內容。更多資訊請關注PHP中文網其他相關文章！