Mysql索引失效的解決方法

小編給大家分享一下Mysql索引失效的解決方法，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

背景

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

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

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

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 Last Key中，繼續提取索引的下一個字段；若存在并且條件是 < ，則將條件加入到Index Last Key中，然后終止提取；若不存在，也終止Index Last Key的提取。

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

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中過濾掉了）；若存在且條件為 >=、>、<、<= 其中的幾種，則跳過當前索引字段，將其余where條件中索引相關字段全部加入到Index Filter之中。

按照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<'1631120399'，Index Filter為：station_id='1809'，Table Filter為：status=‘2’。

提取Index First Key后在索引B+樹上定位索引起始范圍就是索引匹配的過程，在索引B+樹上使用二分搜索方法快速定位符合查詢條件的起始葉子節點。通過上文Where條件查詢執行過程，我們知道該慢查詢的where條件（start_date>'1628442000' and start_date<'1631120399' and status='2' and station_id='1809'），只匹配了索引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%的數據量。

回表操作的開銷

由于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以通配符開頭'%abc'。注意like ‘abc%’ 是可以走索引的。

• order by 違反最左匹配原則，含非索引字段排序，會產生文件排序。

• group by 違反最左匹配原則，含非索引字段分組，會導致產生臨時表。

Explain分析

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

Type表示訪問數據的方式，Extra表示過濾和整理數據的方式。這里列舉出來方便查找。

以上是“Mysql索引失效的解決方法”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！