快捷導(dǎo)航

MySQL索引失效原理

更新時(shí)間：2021年12月12日 14:48:42 作者：bkpp976

索引可以加快查找速度是因?yàn)?，在每一層的兄弟?jié)點(diǎn)之間，索引是有序的，因此可以通過二分法快速定位到相應(yīng)位置。如果一些操作破壞了索引排列的有序性或者不能利用索引的有序性，這個(gè)索引自然就失效了，下文更詳細(xì)說明,需要的朋友可以參考一下

1、索引失效原因

首先看看哪些情況下，將會(huì)導(dǎo)致查找不能利用索引的有序性。

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

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

可以得到的規(guī)律是：優(yōu)先按a字段從小到大排序，a字段相等的按b字段從小到大排序；

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

條件只包含b字段

select * from test where  b=2;

索引失效：

顯然，走的時(shí)候全文掃描，并沒有使用索引。因?yàn)橹豢碽字段的索引，是2,4,1,3,4,5，并不能利用索引的有序性快速定位。

對a字段范圍查詢：

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

索引失效：

可以看到，索引并沒有完全失效，而是先利用索引定位到a的位置。因?yàn)檫@里的key_len是4，而聯(lián)合索引的key_len是8。

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

索引失效：

可以看到是using index并且key_len是8，也就是兩個(gè)字段的索引都用到了，這也對應(yīng)著聯(lián)合索引排列的規(guī)律：a字段相同的情況下，b字段有序排列。

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

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

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

- 對索引字段做函數(shù)操作

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

- 隱式類型轉(zhuǎn)換

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

- 隱式字符編碼轉(zhuǎn)換

字符串編碼轉(zhuǎn)換的本質(zhì)是使用了CONVERT() 函數(shù)。

3、總結(jié)

索引失效的原因是優(yōu)化器發(fā)現(xiàn)不能利用索引的有序性，因此在使用索引時(shí)，要盡量滿足最左前綴匹配原則、范圍查詢放在最后、不使用%like、 %like%等模糊查詢，就是在最大程度利用索引的有序性；但是在某些情況下，優(yōu)化器只是放棄索引樹的搜索功能，可能還是會(huì)選擇掃描這個(gè)索引。

到此這篇關(guān)于MySQL索引失效原理的文章就介紹到這了,更多相關(guān)MySQL索引失效內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: