索引的存儲(chǔ)結(jié)構(gòu)

首先了解一下索引的存儲(chǔ)結(jié)構(gòu)，知道了索引的存儲(chǔ)結(jié)構(gòu)，才方便我們更好地理解索引失效的問(wèn)題。

索引的存儲(chǔ)結(jié)構(gòu)跟MySQL的存儲(chǔ)引擎有關(guān)，存儲(chǔ)引擎的不同采用的結(jié)構(gòu)也會(huì)不同。

MySQL默認(rèn)的存儲(chǔ)引擎InnoDB采用B+Tree作為索引的數(shù)據(jù)結(jié)構(gòu)，在創(chuàng)建表時(shí)，InnoDB會(huì)默認(rèn)創(chuàng)建一個(gè)主鍵索引，這是一個(gè)聚簇索引，其他索引都屬于二級(jí)索引。

MyISAM存儲(chǔ)引擎在創(chuàng)建表時(shí)，默認(rèn)是用的是B+樹(shù)索引。

雖然和InnoDB一樣都支持B+樹(shù)索引，但是他們存儲(chǔ)數(shù)據(jù)的方式不同；

InnoDB是聚簇索引（B+樹(shù)索引的葉子結(jié)點(diǎn)保存數(shù)據(jù)本身）

MyISAM是非聚集索引（B+樹(shù)的葉子結(jié)點(diǎn)保存數(shù)據(jù)的物理地址）

如下圖所示：

InnoDB存儲(chǔ)引擎可以分為【聚簇索引】和【二級(jí)索引】，它們的區(qū)別在于聚簇索引的葉子結(jié)點(diǎn)存放的是實(shí)際數(shù)據(jù)，所有完整的數(shù)據(jù)都存放在聚簇索引的葉子結(jié)點(diǎn)，二級(jí)索引的葉子結(jié)點(diǎn)存放的是主鍵值。

在使用二級(jí)索引字段作為查詢條件，查詢數(shù)據(jù)在聚簇索引上的時(shí)候，

會(huì)先根據(jù)條件在二級(jí)索引上找到對(duì)應(yīng)的葉子結(jié)點(diǎn)得到主鍵值，

再根據(jù)主鍵值去聚簇索引上找到對(duì)應(yīng)的葉子結(jié)點(diǎn)然后查詢到對(duì)應(yīng)的數(shù)據(jù)，

這個(gè)過(guò)程叫回表

使用二級(jí)索引作為查詢條件，查詢的數(shù)據(jù)在二級(jí)索引的葉子結(jié)點(diǎn)上的時(shí)候，那么只需找到二級(jí)索引的B+樹(shù)對(duì)應(yīng)的葉子結(jié)點(diǎn)，讀取數(shù)據(jù)，這個(gè)過(guò)程叫覆蓋索引

上面這些查詢條件都用到了索引列，但并不表示用到索引列索引就一定會(huì)生效，我們?cè)賮?lái)看一看索引失效的情況

不合理的模糊查詢條件

使用左或左右模糊查詢的時(shí)候，也就是like "%張"或like "%張%"這兩種模糊查詢方式都會(huì)導(dǎo)致索引失效

因?yàn)锽+樹(shù)是根據(jù)索引值進(jìn)行排列的，前綴不確定的時(shí)候可能是，“小張”，"二張"之類(lèi)的所有的情況，就只能通過(guò)全表掃描的方式來(lái)查詢

對(duì)索引使用函數(shù)

例如：SELECT * FROM sys_user WHERE LENGTH(user_id) = 3 ;

因?yàn)樗饕４娴氖撬饕侄蔚脑贾担皇墙?jīng)過(guò)函數(shù)計(jì)算后的值，所以使用函數(shù)的時(shí)候就不會(huì)走索引了

不過(guò)從MySQL8.0開(kāi)始，索引特性增加了函數(shù)索引，也就是針對(duì)該函數(shù)計(jì)算后的值建立一個(gè)索引，這樣就可以通過(guò)掃描索引來(lái)查詢數(shù)據(jù)了；

alter table t_user add key idx_name_length ((length(name)));

對(duì)索引進(jìn)行表達(dá)式計(jì)算

例如：select * from sys_user where user_id+1 =3;

但是如果是SELECT * FROM sys_user WHERE user_id = 1+1 ;這樣的不在索引字段上進(jìn)行計(jì)算，就又會(huì)走索引了

原因跟對(duì)索引使用函數(shù)差不多，索引保存的是索引字段的原始值，而不是運(yùn)算后的值，所以無(wú)法走索引

對(duì)索引使用隱式轉(zhuǎn)換

這里的phone字段是二級(jí)索引，且是varchar類(lèi)型的

使用整型作為查詢參數(shù)的時(shí)候，執(zhí)行計(jì)劃中type為ALL，也就是通過(guò)全表掃描查詢的，但如果是字符串類(lèi)型，還是走索引查詢的

我們?cè)倏匆粋€(gè)例子

這里user_id是bigint類(lèi)型，但是使用字符串作為查詢參數(shù)還是走了索引的

為什么第一個(gè)例子導(dǎo)致了索引失效，而第二個(gè)不會(huì)呢？

這里就要了解一下MySQL的字符轉(zhuǎn)換規(guī)則了，看是數(shù)字轉(zhuǎn)字符串，還是字符串轉(zhuǎn)數(shù)字

我們可以用select "10">9來(lái)測(cè)試一下

如果是數(shù)字轉(zhuǎn)字符串，那么就相當(dāng)于select "10">"9"結(jié)果應(yīng)該是0

如果是字符串轉(zhuǎn)數(shù)字，那么就相當(dāng)于select 10>9，結(jié)果是1

在MySQL中的執(zhí)行結(jié)果如下：

這就說(shuō)明，MySQL在遇到數(shù)字與字符串的比較的時(shí)候，會(huì)自動(dòng)把字符串轉(zhuǎn)換為數(shù)字，然后進(jìn)行比較

也就是說(shuō)，在第一個(gè)例子中

SELECT * FROM sys_user WHERE phone = 18200000000 ;

相當(dāng)于

SELECT * FROM sys_user WHERE CAST(phone AS UNSIGNED) = 18200000000 ;

這就在索引字段上使用了函數(shù)，所以導(dǎo)致索引失效

而在第二個(gè)例子中

SELECT * FROM sys_user WHERE user_id = "1" ;

相當(dāng)于

SELECT * FROM sys_user WHERE user_id = CAST("1" AS UNSIGNED) ;

函數(shù)式作用在查詢參數(shù)上的，并沒(méi)有作用在索引字段上，所以還是走索引的

聯(lián)合索引非最左匹配

多個(gè)普通字段組合在一起創(chuàng)建的索引叫做聯(lián)合索引（組合索引）

在使用聯(lián)合索引的時(shí)候，一定要注意順序問(wèn)題，聯(lián)合索引的使用需要遵循最左匹配原則，也就是按照最左優(yōu)先的方式進(jìn)行索引匹配。

例如，創(chuàng)建了一個(gè)(a,b,c)聯(lián)合索引，那么如果查詢條件是一下幾種，就可以匹配上聯(lián)合索引

where a = 1
where a = 1 and b = 2
where a = 1 and b = 2 and c = 3

需要注意的是，因?yàn)橛胁樵儍?yōu)化器，所以a字段在where子句中的順序不重要

但是必須要有a字段，如果像下面幾種，因?yàn)椴环献钭笃ヅ湓瓌t，就無(wú)法匹配上聯(lián)合索引，聯(lián)合索引就會(huì)失效：

where b = 2
where c = 3
where b = 2 and c = 3

還有一個(gè)比較特殊的查詢條件：where a = 1 and c = 3

在MySQL5.5的話，前面的a 會(huì)走索引，在聯(lián)合索引找到主鍵值，然后回表，到主鍵索引讀取數(shù)據(jù)行，然后在比對(duì)c字段的值

在MySQL5.6之后，有一個(gè)索引下推的功能，

下推就是將部分上層(服務(wù)層)負(fù)責(zé)的事情，交給了下層(引擎層)處理

存儲(chǔ)引擎直接在聯(lián)合索引里按照c=3過(guò)濾，按照過(guò)濾后的數(shù)據(jù)在進(jìn)行回表掃描，減少了回表的次數(shù)，從而提升了性能

在執(zhí)行計(jì)劃中Extra = Using index condition就表示使用了索引下推

聯(lián)合索引不遵循最左匹配原則的原因：在聯(lián)合索引中，數(shù)據(jù)按照第一列索引進(jìn)行排序，第一列數(shù)據(jù)相同時(shí)，才會(huì)按照第二列進(jìn)行排序，以此類(lèi)推，所以直接使用第二列進(jìn)行查詢的時(shí)候，聯(lián)合索引就會(huì)失效

where子句中的or

where子句中or的條件列有不是索引列會(huì)導(dǎo)致索引失效

例如：下圖中id是索引列，email不是索引列，從執(zhí)行計(jì)劃來(lái)看，進(jìn)行了全文掃描并沒(méi)有使用到索引

因?yàn)閛r關(guān)鍵字只滿足一個(gè)條件就可以，因此只要有一個(gè)列不是索引列，其他索引列也就沒(méi)有意義了，就會(huì)進(jìn)行全表掃描

在email列上建立索引之后，可以看到執(zhí)行計(jì)劃中使用到了兩個(gè)索引

type = index_merge表示對(duì)id 和email都進(jìn)行了掃描，然后進(jìn)行了合并

總結(jié)

導(dǎo)致索引失效的情況有：

不合理的使用模糊查詢：like "%張"或like %張%

對(duì)索引列使用函數(shù)

對(duì)索引使用表達(dá)式計(jì)算

對(duì)索引使用隱式轉(zhuǎn)換，這三個(gè)都是引起了索引列值的變化導(dǎo)致索引失效

聯(lián)合索引非最左匹配

where子句中or條件列沒(méi)有使用索引

到此這篇關(guān)于MySQL細(xì)數(shù)發(fā)生索引失效的情況的文章就介紹到這了,更多相關(guān)MySQL索引失效內(nèi)容請(qǐng)搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！

美國(guó)穩(wěn)定服務(wù)器

版權(quán)聲明：本站文章來(lái)源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請(qǐng)保持原文完整并注明來(lái)源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來(lái)源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來(lái)，僅供學(xué)習(xí)參考，不代表本站立場(chǎng)，如有內(nèi)容涉嫌侵權(quán)，請(qǐng)聯(lián)系alex-e#qq.com處理。