MySQL索引失效原理

Posted on by

1、索引失效原因

首先看看哪些情況下，將會導致查找不能利用索引的有序性。

假設一個表test中有a,b,c,d四個字段，c是主鍵。

在a,b字段上建立聯合索引(a,b)：CREATE index idx_a_b on test(a,b); B+樹聯合索引.JPG

可以得到的規律是：優先按a字段從小到大排序，a字段相等的按b字段從小到大排序；

分析以下情況，索引是否會失效以及失效的原因：

條件隻包含b字段 

select * from test where  b=2;

索引失效：

顯然，走的時候全文掃描，並沒有使用索引。因為隻看b字段的索引，是2,4,1,3,4,5，並不能利用索引的有序性快速定位。

對a字段范圍查詢： 

select * from test where  a>1 and b=2;

索引失效：

可以看到，索引並沒有完全失效，而是先利用索引定位到a的位置。因為這裡的key_len是4，而聯合索引的key_len是8。

對a字段等值查詢，b字段范圍查詢：

索引失效：

可以看到是using index並且key_len是8，也就是兩個字段的索引都用到瞭，這也對應著聯合索引排列的規律：a字段相同的情況下，b字段有序排列。

以上幾種情況可以總結為：不符合最左前綴匹配原則導致索引失效。

最左匹配前綴保證可以利用到索引排序的有序性，而把等值查詢放在前面，范圍查詢放在後面，是利用瞭[前綴字段相等的情況下，後面的索引字段有序]這個特性，是特殊意義下的最左前綴匹配原則。

2、再來看看哪些情況會破壞索引的有序性。

– 對索引字段做函數操作

對索引字段做函數操作，比如y=f(x),並不能保證得到的y的值依然是有序的，在這種弄個情況下，優化器會放棄樹的搜索功能，但是不排除優化器在發現該索引樹比主鍵索引小很多的情況下，選擇掃描這個索引。

– 隱式類型轉換

在 MySQL 中，字符串和數字做比較的話，是將字符串轉換成數字。隱式類型轉換的本質是對索引字段使用瞭CAST()函數，原理同上。

– 隱式字符編碼轉換

字符串編碼轉換的本質是使用瞭CONVERT() 函數。

3、總結

索引失效的原因是優化器發現不能利用索引的有序性，因此在使用索引時，要盡量滿足最左前綴匹配原則、范圍查詢放在最後、不使用%like、 %like%等模糊查詢，就是在最大程度利用索引的有序性；但是在某些情況下，優化器隻是放棄索引樹的搜索功能，可能還是會選擇掃描這個索引。

到此這篇關於MySQL索引失效原理的文章就介紹到這瞭,更多相關MySQL索引失效內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！

推薦閱讀：