MySQL什么情況下會(huì)導(dǎo)致索引失效

索引（Index）是數(shù)據(jù)庫中一種用于快速查找和訪問表中數(shù)據(jù)的結(jié)構(gòu)，它類似于書的目錄，通過索引可以快速定位到目標(biāo)數(shù)據(jù)，而無需遍歷整個(gè)表，索引的存在可以顯著提高查詢速度，尤其是在處理大量數(shù)據(jù)時(shí)

有時(shí)我們?yōu)榱吮苊獬霈F(xiàn)回表查詢，我們就會(huì)以多個(gè)字段來創(chuàng)建索引，即覆蓋索引，具體可看：聚簇索引、非聚簇索引、覆蓋索引

使用覆蓋索引最容易遇到的問題就是索引失效問題，那么哪些情況下會(huì)出現(xiàn)索引失效，又該如何避免索引失效呢？

索引命中

以一個(gè)tb_users表為例子：

我們以其中三個(gè)字段建立索引：

CREATE INDEX tb_user_index ON tb_users(name, status,username)

可以利用show語句可以查看我們剛剛為tb_users建立的索引：

SHOW INDEX FROM tb_users

索引字段也是有順序的，順序就是我們創(chuàng)建索引時(shí)的順序，使用explain可以查看SQL語句的執(zhí)行計(jì)劃：

1.單個(gè)條件

EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英'

命中索引的長(zhǎng)度是43，此時(shí)我們的查詢條件只有name，因此命中的索引也只有name

此時(shí)查詢條件中添加一個(gè)status字段：

2.兩個(gè)字段

EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status = 1

此時(shí)命中索引的長(zhǎng)度為48，表示命中了name和status

再次添加一個(gè)字段：

3.三個(gè)字段條件

EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status = 1 AND username = 'Joe Edwards'

此時(shí)三個(gè)索引字段全部命中，索引長(zhǎng)度為131

因此命中索引的數(shù)量不一樣，key_len也不一樣，可以利用它來判斷索引是否失效

索引失效

1.違反最左前綴法則會(huì)導(dǎo)致索引失效

指的是查詢從索引的最左前列開始，中間不跳過索引中的列，比如：

EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND username = 'Joe Edwards'

這條查詢語句中，我們用兩個(gè)索引字段作為了查詢條件，那么理論上應(yīng)該命中兩個(gè)索引

但是key_len的值為43，只命中了name字段，username字段索引失效了，因?yàn)橹虚g跳過了索引中的一列status

2.范圍查詢某個(gè)索引，其右邊索引失效

當(dāng)where條件中用索引范圍查詢，這個(gè)索引右邊的字段會(huì)失效

EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status > 1 AND username = 'Joe Edwards'

這條查詢語句使用了三個(gè)索引條件，理論上key_len為131

但是實(shí)際上命中索引長(zhǎng)度為48，username字段未命中，原因是status使用了范圍查詢，因此他右邊的username失效了

3.在索引列上進(jìn)行運(yùn)算操作，會(huì)導(dǎo)致索引失效

EXPLAIN SELECT * FROM tb_users WHERE SUBSTRING(0,1,name) = '崔秀英'

這里對(duì)name字段進(jìn)行了截取運(yùn)算操作

因此name字段并未命中，導(dǎo)致索引失效

4.字符串不加單引號(hào)，導(dǎo)致索引失效

比如說status字段，上表中我們使用的是int類型，假如我們使用了varchar類型，在查詢的時(shí)候就要加單引號(hào)，如：

EXPLAIN SELECT * FROM tb_users WHERE name = '崔秀英' AND status = '1'

如果沒有加單引號(hào)，MySQL就會(huì)進(jìn)行多余的類型轉(zhuǎn)換步驟，該步驟會(huì)導(dǎo)致索引失效

5.以%開頭的like模糊查詢，會(huì)導(dǎo)致索引失效

如果僅僅是尾部模糊匹配，索引不會(huì)失效，如果是頭部模糊匹配，索引會(huì)失效

EXPLAIN SELECT * FROM tb_users WHERE name LIKE '%秀英'

可以看到索引命中長(zhǎng)度為null，索引失效了

總結(jié)

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論