1.前導(dǎo)模糊查詢(xún)不能利用索引(like '%XX'或者like '%XX%')

假如有這樣一列code的值為'AAA','AAB','BAA','BAB' ,如果where code like '%AB'條件，由于前面是

模糊的，所以不能利用索引的順序，必須一個(gè)個(gè)去找，看是否滿(mǎn)足條件。這樣會(huì)導(dǎo)致全索引掃描或者全表掃

描。如果是這樣的條件where code like 'A % '，就可以查找CODE中A開(kāi)頭的CODE的位置，當(dāng)碰到B開(kāi)頭的

數(shù)據(jù)時(shí)，就可以停止查找了，因?yàn)楹竺娴臄?shù)據(jù)一定不滿(mǎn)足要求。這樣就可以利用索引了。

2.如果是組合索引的話(huà)，如果不按照索引的順序進(jìn)行查找，比如直接使用第三個(gè)位置上的索引而忽略第一二個(gè)位置上的索引時(shí)，則會(huì)進(jìn)行全表查詢(xún)

索引為c1,c2,c3,c4

上圖結(jié)果顯示直接使用c3是全表查詢(xún)，無(wú)法使用該索引的，所以c3字段使用索引的前提是c1，c2兩字段均使用了索引。

3.條件中有or

應(yīng)盡量避免在 where 子句中使用 or 來(lái)連接條件，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：

select id from t where num=10 or num=20

可以這樣查詢(xún)：

select id from t where num=10 union all select id from t where num=20

4.索引無(wú)法存儲(chǔ)null值，所以where的判斷條件如果對(duì)字段進(jìn)行了null值判斷，將導(dǎo)致數(shù)據(jù)庫(kù)放棄索引而進(jìn)行全表查詢(xún)，如

select id from t where num is null

可以在num上設(shè)置默認(rèn)值0，確保表中num列沒(méi)有null值，然后這樣查詢(xún)：

select id from t where num=0

a.單列索引無(wú)法儲(chǔ)null值，復(fù)合索引無(wú)法儲(chǔ)全為null的值。

b.查詢(xún)時(shí)，采用is null條件時(shí)，不能利用到索引，只能全表掃描。

為什么索引列無(wú)法存儲(chǔ)Null值？

a.索引是有序的。NULL值進(jìn)入索引時(shí)，無(wú)法確定其應(yīng)該放在哪里。（將索引列值進(jìn)行建樹(shù)，其中必然涉及到諸多的比較操作，null 值是不確定值無(wú)法比較，無(wú)法確定null出現(xiàn)在索引樹(shù)的葉子節(jié)點(diǎn)位置。）

5.應(yīng)盡量避免在 where 子句中使用!=或>操作符，否則將引擎放棄使用索引而進(jìn)行全表掃描。

6.in 和 not in 也要慎用，否則會(huì)導(dǎo)致全表掃描，如：

select id from t where num in(1,2,3)

對(duì)于連續(xù)的數(shù)值，能用 between 就不要用 in 了：

select id from t where num between 1 and 3

7. 應(yīng)盡量避免在where子句中對(duì)字段進(jìn)行函數(shù)操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

–name以abc開(kāi)頭的id

select id from t where substring(name,1,3)='abc'

8.應(yīng)盡量避免在 where 子句中對(duì)字段進(jìn)行表達(dá)式操作，這將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描。如：

select id from t where num/2=100

應(yīng)改為:

select id from t where num=100*2

總結(jié)

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