MySQL什么情況下會導致索引失效

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

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

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

索引命中

以一個tb_users表為例子：

我們以其中三個字段建立索引：

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

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

SHOW INDEX FROM tb_users

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

1.單個條件

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

命中索引的長度是43，此時我們的查詢條件只有name，因此命中的索引也只有name

此時查詢條件中添加一個status字段：

2.兩個字段

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

此時命中索引的長度為48，表示命中了name和status

再次添加一個字段：

3.三個字段條件

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

此時三個索引字段全部命中，索引長度為131

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

索引失效

1.違反最左前綴法則會導致索引失效

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

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

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

但是key_len的值為43，只命中了name字段，username字段索引失效了，因為中間跳過了索引中的一列status

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

當where條件中用索引范圍查詢，這個索引右邊的字段會失效

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

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

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

3.在索引列上進行運算操作，會導致索引失效

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

這里對name字段進行了截取運算操作

因此name字段并未命中，導致索引失效

4.字符串不加單引號，導致索引失效

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

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

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

5.以%開頭的like模糊查詢，會導致索引失效

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

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

可以看到索引命中長度為null，索引失效了

總結(jié)

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

最新評論