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

創(chuàng)新互聯(lián)公司是一家專業(yè)從事網(wǎng)站建設(shè)、成都網(wǎng)站設(shè)計(jì)、網(wǎng)頁設(shè)計(jì)的品牌網(wǎng)絡(luò)公司。如今是成都地區(qū)具影響力的網(wǎng)站設(shè)計(jì)公司,作為專業(yè)的成都網(wǎng)站建設(shè)公司,創(chuàng)新互聯(lián)公司依托強(qiáng)大的技術(shù)實(shí)力、以及多年的網(wǎng)站運(yùn)營經(jīng)驗(yàn),為您提供專業(yè)的成都網(wǎng)站建設(shè)、營銷型網(wǎng)站建設(shè)及網(wǎng)站設(shè)計(jì)開發(fā)服務(wù)！

背景

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

# 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千萬數(shù)據(jù)，慢查詢的查詢字段包括 start_date、station_id、status，按照索引設(shè)計(jì)初衷會走但實(shí)際上失效的索引是：

聯(lián)合索引	字段1	字段2	字段3
idx_date_station_driver	start_date	station_id	driver_id

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查詢在索引樹上的范圍。一個(gè)范圍包括起始和終止，Index First Key用于定位索引查詢的起始范圍，Index Last Key用于定位索引查詢的終止范圍。

• Index First Key

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

• Index Last Key

  與Index First Key正好相反，提取規(guī)則：從索引的第一個(gè)字段開始，檢查其在where條件中是否存在，若存在并且條件是=、<=，則將對應(yīng)條件加入到Index Last Key中，繼續(xù)提取索引的下一個(gè)字段；若存在并且條件是 < ，則將條件加入到Index Last Key中，然后終止提?。蝗舨淮嬖?，也終止Index Last Key的提取。

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

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

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

Index Filter

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

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

Index Filter的提取規(guī)則：從索引的第一個(gè)字段開始，檢查其在where條件中是否存在，若存在且條件僅為 =，則跳過第一字段繼續(xù)檢查索引下一字段，下一索引列采取相同的提取規(guī)則（解釋：條件為=的字段已經(jīng)在Index Key中過濾掉了）；若存在且條件為 >=、>、<、<= 其中的幾種，則跳過當(dāng)前索引字段，將其余where條件中索引相關(guān)字段全部加入到Index Filter之中。

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

Table Filter

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

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

總結(jié)和補(bǔ)充

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

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層進(jìn)行過濾。

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

分析索引失效原因

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

接下來分析where語句。假設(shè)此慢查詢會使用了二級索引idx_date_station_driver，按照上面where條件查詢的執(zhí)行過程，該慢查詢的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+樹上使用二分搜索方法快速定位符合查詢條件的起始葉子節(jié)點(diǎn)。通過上文Where條件查詢執(zhí)行過程，我們知道該慢查詢的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)的第一個(gè)字段，即只匹配了idx_date_station_driver(start_date)，station_id='1809‘精確查詢并沒有作用到匹配索引上，而是在Index Filter即索引下推過程中發(fā)揮了作用。實(shí)際上這里是因?yàn)?strong>范圍查詢使聯(lián)合索引停止匹配。

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

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

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

查詢條件	數(shù)據(jù)量	占比
所有數(shù)據(jù)	6367萬	100%
start_timestamp_of_date>'1628442000' and start_timestamp_of_date<'1631120399'	1742萬	27.35%
station_id='1809'	8萬	0.16%

回表操作的開銷

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

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

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

• 磁盤隨機(jī)I/O的性能低于順序I/O?；乇聿樵冊谥麈I索引上是隨機(jī)I/O，全表掃描在主鍵索引上是順序I/O。

做實(shí)驗(yàn)分析回表操作的開銷是否是索引失效的直接原因？

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

總結(jié)

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

優(yōu)化索引

該慢查詢索引失效的罪魁禍?zhǔn)资欠秶樵兪孤?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字段。

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

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

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

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

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

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

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

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

Explain分析

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

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

Type		Extra
ALL	全表掃描	Using index	使用覆蓋索引，不需要回表，不需要Mysql服務(wù)層過濾
index	索引樹全掃描	Using where	從存儲引擎層獲取數(shù)據(jù)，在Mysql服務(wù)層用where查詢條件過濾數(shù)據(jù)。
range	索引樹范圍掃描	Using where; Using index	索引范圍掃描。索引掃描和全表掃描類似，只是發(fā)生的層面不一樣。
ref	非唯一性索引掃描，比如非唯一索引和唯一索引的非唯一前綴	Using index condition	使用索引下推，在存儲引擎層充分利用查詢索引字段過濾數(shù)據(jù)
eq_ref	唯一性索引掃描，比如唯一索引、主鍵索引	Using temporary	臨時(shí)表存儲結(jié)果，用于排序和分組查詢
const	將查詢轉(zhuǎn)化成常量	Using filesort	文件排序，用于排序
NULL	不用訪問表或索引	NULL	回表

以上是“Mysql索引失效的解決方法”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注創(chuàng)新互聯(lián)行業(yè)資訊頻道！

分享題目：Mysql索引失效的解決方法
URL網(wǎng)址：http://jinyejixie.com/article4/jjpjoe.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供Google、營銷型網(wǎng)站建設(shè)、網(wǎng)站改版、服務(wù)器托管、企業(yè)網(wǎng)站制作、ChatGPT

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來源： 創(chuàng)新互聯(lián)