前言

日常工作中索引失效原因很多，這個(gè)需要平時(shí)的日積月累，不斷學(xué)習(xí)，才能更正確的發(fā)揮索引的作用，下面簡(jiǎn)單總結(jié)一些索引失效原因。

1. 隱式的類(lèi)型轉(zhuǎn)換，索引失效

select * from test where num=13911111111; # 失效，num字段是varchar類(lèi)型，沒(méi)有加引號(hào)

假設(shè)某手機(jī)號(hào)列創(chuàng)建時(shí)是num varchar(15)

如果上面的手機(jī)號(hào)沒(méi)有加引號(hào)，查詢(xún)的時(shí)候是字符串跟數(shù)字的比較，它們類(lèi)型不匹配，MySQL 會(huì)做隱式的類(lèi)型轉(zhuǎn)換，把它們轉(zhuǎn)換為浮點(diǎn)數(shù)再做比較。隱式的類(lèi)型轉(zhuǎn)換，索引會(huì)失效。

2. 查詢(xún)條件包含 or，可能導(dǎo)致索引失效

select * from test where mul=1 or noidx=2; # 可能失效，當(dāng)mul設(shè)為索引列而noidx不是索引列時(shí)

索引+or+無(wú)索引的列：會(huì)先走索引列，但無(wú)索引的列會(huì)進(jìn)行全表掃描，所以還不如不走索引，直接都全表掃描完事。如果or前后都有索引，那么可能走索引，也可能不走索引。

如果它一開(kāi)始就走全表掃描，直接一遍掃描就完事。Mysql 優(yōu)化器出于效率與成本考慮，遇到 or 條件，讓索引失效，看起來(lái)也合情合理。

用or連接的兩個(gè)含null索引字段，不走索引。但是，單個(gè)索引含null字段，是走索引的。

注意：如果 or 條件的列都加了索引，索引可能會(huì)走也可能不走，平時(shí)大家使用的時(shí)候，還是要注意一下這個(gè) or，學(xué)會(huì)用 explain 分析。遇到不走索引的時(shí)候，考慮拆開(kāi)兩條 SQL。

3. like 通配符可能導(dǎo)致索引失效

并不是用了 like 通配符，索引一定會(huì)失效，而是 like 查詢(xún)是以 % 開(kāi)頭，才會(huì)導(dǎo)致索引失效。

4. 查詢(xún)條件不滿(mǎn)足聯(lián)合索引的最左匹配原則

MySQl 建立聯(lián)合索引時(shí)，會(huì)遵循最左前綴匹配的原則，即最左優(yōu)先。如果你建立一個(gè)（a,b,c）的聯(lián)合索引，相當(dāng)于建立了 (a)、(a,b)、(a,b,c) 三個(gè)索引。

5. 在索引列l(wèi)ogin_time上使用 mysql 的內(nèi)置函數(shù)

select * from user where DATE_ADD(login_time,INTERVAL 1 DAY) = '2022-11-08 00:00:00'; # 失效
select * from user where login_time = DATE_ADD('2022-11-08 00:00:00',INTERVAL 1 DAY); # 有效

6. 對(duì)索引列age進(jìn)行列運(yùn)算（如，+、-、*、/）, 索引不生效

select * from user where age-1 = 39; # 失效

7. 索引字段age上使用（！= 或者 < >, not in），索引可能失效

select * from user where age != 18; # 有可能失效

其實(shí)這個(gè)也是跟 mySQL優(yōu)化器有關(guān)，如果優(yōu)化器覺(jué)得即使走了索引，還是需要掃描很多很多行的哈，它覺(jué)得不劃算，不如直接不走索引。平時(shí)我們用！= 或者 < >，not in 的時(shí)候，要留點(diǎn)心眼。

8. 索引字段上使用 is null， is not null，索引可能失效 （查詢(xún)結(jié)果行數(shù)）

很多時(shí)候，是因?yàn)閿?shù)據(jù)量問(wèn)題，導(dǎo)致了 MySQL 優(yōu)化器放棄走索引。同時(shí)，平時(shí)我們用 explain 分析 SQL 的時(shí)候，如果 type=range, 要注意一下哈，因?yàn)檫@個(gè)可能因?yàn)閿?shù)據(jù)量問(wèn)題，導(dǎo)致索引無(wú)效。

9. 左右join連接，關(guān)聯(lián)的字段編碼格式不一樣

如user 表的 name 字段編碼是 utf8mb4，而 user_job 表的 name 字段編碼為 utf8。

10. 索引自身失效

雖然索引有自我維護(hù)的能力，但數(shù)據(jù)表內(nèi)容修改和更新頻繁的情況下，也有可能索引失效，此時(shí)需要?jiǎng)h除索引，重新建立索引。

總結(jié)

關(guān)于索引失效原因有很多，以上也只是簡(jiǎn)單介紹了一下，具體失效原因，還得去自己分析，具體方法就是SQL的執(zhí)行計(jì)劃 EXPLAIN 關(guān)鍵字了。
Mysql提供了這個(gè)關(guān)鍵字讓我們優(yōu)化索引，使查詢(xún)更快，分析優(yōu)化器的表連接，使它采用最優(yōu)的順序。使用這個(gè) explain 關(guān)鍵字可以查看查詢(xún)語(yǔ)句是否走索引了以及走了哪個(gè)索引。

# 命令行執(zhí)行以下語(yǔ)句即可查看查詢(xún)語(yǔ)句是否走了索引，在查詢(xún)語(yǔ)句最前面加上 explain 即可
mysql> explain select * from sampleInfo where agents = "XXX中心有限公司";

如下圖的 key 即表示該語(yǔ)句使用了索引 agents 。如果下圖 key 那里的為NULL或者 type 那里為ALL，則表示該語(yǔ)句沒(méi)有走索引，需要進(jìn)行優(yōu)化了。

到此這篇關(guān)于MySQL索引失效原因以及SQL查詢(xún)語(yǔ)句不走索引原因的文章就介紹到這了,更多相關(guān)MySQL索引失效原因內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論