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

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

此文章包含mysql的Where條件查詢執(zhí)行過程、范圍查詢使聯(lián)合索引停止匹配、回表操作分析、常見索引失效場景、Extra分析等知識。【相關(guān)推薦：mysql】

背景

6千萬數(shù)據(jù)量的數(shù)據(jù)表出現(xiàn)了一個滿查詢，復現(xiàn)sql語句發(fā)現(xiàn)查詢并沒有走索引而是走全表查詢，找出索引失效原因。

#?sql語句 EXPLAIN?SELECT?count(*)?FROM?order_recipient_extend_tab?WHERE?start_date&gt;'1628442000'?and?start_date<p><img src="https://img.php.cn/upload/image/482/650/895/1635823223918696.png" title="1635823223918696.png" alt="1.png"></p><p>order_recipient_extend_tab 表有6千萬數(shù)據(jù)，慢查詢的查詢字段包括 start_date、station_id、status，按照索引設(shè)計初衷會走但實際上失效的索引是：</p>

Where條件查詢執(zhí)行過程

了解Mysql怎么執(zhí)行where條件查詢，能更快速清晰地洞見索引失效的原因。此次慢查詢中匹配度高的索引是idx_date_station_driver，分析此次慢查詢中where條件查詢的執(zhí)行過程。

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

Index Key

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

• Index First Key

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

• Index Last Key

  與Index First Key正好相反，提取規(guī)則：從索引的第一個字段開始，檢查其在where條件中是否存在，若存在并且條件是=、

按照Index Key的提取規(guī)則，在此次慢查詢中提取出來的Index Last Key為：start_date>’1628442000’，Index Last Key為： start_date

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

Index Last Key，用來定位索引的終止范圍，因此對于起始范圍之后讀到的每一條索引記錄，均需要判斷是否已經(jīng)超過了Index Last Key的范圍，若超過，則當前查詢結(jié)束。

Index Filter

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

Index Filter，又名索引下推，用于過濾索引查詢范圍中不滿足查詢條件的記錄。對于索引范圍中的每一條記錄，均需要與Index Filter進行對比，若不滿足Index Filter則直接丟棄，繼續(xù)讀取索引下一條記錄。

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

按照Index Filter的提取規(guī)則，在此次慢查詢中提取出來的Index Filter為：station_id=’1809’。在Index Key確定的索引查詢范圍中，遍歷索引記錄時都需要比較 station_id=’1809’，不滿足該條件則直接丟失，繼續(xù)讀取索引下一條記錄。

Table Filter

Table Filter用于過濾掉索引無法過濾的數(shù)據(jù)。在二級索引中通過主鍵回表查詢到整行記錄后，判斷該記錄是否符合Table Filter條件，不符合則丟失，繼續(xù)判斷下一條記錄。

提取規(guī)則很簡單：所有不屬于索引字段的查詢條件，均歸為Table Filter之中。按照Table Filter的提取規(guī)則，在此次查詢中Table Filter為：status=‘2’。

總結(jié)和補充

Index Key用于確定索引掃描的范圍；Index Filter用于在索引中進行過濾；Table Filter需要回表后在Mysql服務(wù)器進行過濾。

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

在 MySQL5.6 之前，并不區(qū)分Index Filter與Table Filter，統(tǒng)統(tǒng)將Index First Key與Index Last Key范圍內(nèi)的索引記錄，回表讀取完整記錄，然后返回給MySQL Server層進行過濾。

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

分析索引失效原因

首先是count()，此時通配符 * 經(jīng)優(yōu)化并不會拓展所有列，實際上會忽略所有的列直接統(tǒng)計行數(shù)。所以只想收集行數(shù)最好使用count()。

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

提取Index First Key后在索引B+樹上定位索引起始范圍就是索引匹配的過程，在索引B+樹上使用二分搜索方法快速定位符合查詢條件的起始葉子節(jié)點。通過上文Where條件查詢執(zhí)行過程，我們知道該慢查詢的where條件（start_date>’1628442000′ and start_date范圍查詢使聯(lián)合索引停止匹配。

范圍查詢導致聯(lián)合索引停止匹配

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

范圍查詢使聯(lián)合索引停止匹配，并不能在索引匹配的時候就過濾掉 station_id不等于’1809′ 的數(shù)據(jù)，導致Mysql在索引上的掃描范圍Index First Key和Index Last Key完全由start_timestamp_of_date時間決定。start_timestamp_of_date范圍查詢可以過濾73%數(shù)據(jù)量，而station_id=’1809’精確查詢能過濾掉99%的數(shù)據(jù)量。

回表操作的開銷

由于status字段不在索引idx_date_station_driver字段上，所以需要回表查詢索引過濾的數(shù)據(jù)，在Mysql服務(wù)層判數(shù)據(jù)是否符合查詢條件。

Mysql的優(yōu)化器在執(zhí)行sql語句時會先估算走匹配度高的索引的開銷，如果走索引的開銷比查全表還大，那么Mysql會選擇全表掃描。這個結(jié)論可能反常識，在我們印象中索引就是用來提高查詢效率的。這里主要涉及兩個因素：

• 當查詢條件或查找的字段不在二級索引的字段上時，會執(zhí)行回表操作，會走：二級索引+主鍵索引。

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

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

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

總結(jié)

結(jié)合以上分析總結(jié)索引失效原因是：范圍查詢使聯(lián)合索引停止匹配，索引匹配過濾的數(shù)據(jù)不夠多，導致Mysql優(yōu)化器估算出Table Filter的回表操作開銷大于全表查詢，所以選擇了全表查詢。范圍查詢使聯(lián)合索引停止匹配是索引失效的罪魁禍首，而回表操作的開銷是索引失效的直接原因。

優(yōu)化索引

該慢查詢索引失效的罪魁禍首是范圍查詢使聯(lián)合索引停止匹配，只需要把范圍查詢的字段調(diào)整到精確查詢的字段后面，即將

聯(lián)合索引 idx_date_station_driver(start_date, station_id, driver_id) 修改為 idx_station_date_driver(station_id, start_date, driver_id) 。優(yōu)化后的結(jié)果如下圖所示。

拓展

索引失效常見場景

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

• 在索引列上做任何操作，包括計算、函數(shù)、類型轉(zhuǎn)換等。

• 范圍查詢使聯(lián)合索引停止匹配。

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

• 使用不等于（!=、），使用or操作。

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

• like以通配符開頭’%abc’。注意like ‘a(chǎn)bc%’ 是可以走索引的。

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

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

Explain分析

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

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

更多編程相關(guān)知識，請訪問：mysql！！