快捷導(dǎo)航

MySQL索引失效的幾種情況匯總

更新時(shí)間：2020年09月24日 15:09:48 作者：Achilles_Heel

這篇文章主要介紹了MySQL索引失效的幾種情況，幫助大家更好的理解和使用MySQL索引，感興趣的朋友可以了解下

一.索引不存儲(chǔ)null值

更準(zhǔn)確的說，單列索引不存儲(chǔ)null值，復(fù)合索引不存儲(chǔ)全為null的值。索引不能存儲(chǔ)Null，所以對(duì)這列采用is null條件時(shí)，因?yàn)樗饕细?br /> 沒Null值，不能利用到索引，只能全表掃描。
為什么索引列不能存Null值？
將索引列值進(jìn)行建樹，其中必然涉及到諸多的比較操作。Null值的特殊性就在于參與的運(yùn)算大多取值為null。
這樣的話，null值實(shí)際上是不能參與進(jìn)建索引的過程。也就是說，null值不會(huì)像其他取值一樣出現(xiàn)在索引樹的葉子節(jié)點(diǎn)上。

二.不適合鍵值較少的列（重復(fù)數(shù)據(jù)較多的列）

假如索引列TYPE有5個(gè)鍵值，如果有1萬條數(shù)據(jù)，那么 WHERE TYPE = 1將訪問表中的2000個(gè)數(shù)據(jù)塊。
再加上訪問索引塊，一共要訪問大于200個(gè)的數(shù)據(jù)塊。
如果全表掃描，假設(shè)10條數(shù)據(jù)一個(gè)數(shù)據(jù)塊，那么只需訪問1000個(gè)數(shù)據(jù)塊，既然全表掃描訪問的數(shù)據(jù)塊少一些，肯定就不會(huì)利用索引了。

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

假如有這樣一列code的值為'AAA','AAB','BAA','BAB' ,如果where code like '%AB'條件，由于前面是
模糊的，所以不能利用索引的順序，必須一個(gè)個(gè)去找，看是否滿足條件。這樣會(huì)導(dǎo)致全索引掃描或者全表掃
描。如果是這樣的條件where code like 'A % '，就可以查找CODE中A開頭的CODE的位置，當(dāng)碰到B開頭的
數(shù)據(jù)時(shí)，就可以停止查找了，因?yàn)楹竺娴臄?shù)據(jù)一定不滿足要求。這樣就可以利用索引了。

四.索引失效的幾種情況

1.如果條件中有or，即使其中有條件帶索引也不會(huì)使用(這也是為什么盡量少用or的原因)
要想使用or，又想讓索引生效，只能將or條件中的每個(gè)列都加上索引

select * from tb1 where nid = 1 or name = 'seven@live.com';
 
 
 --特別的：當(dāng)or條件中有未建立索引的列才失效，以下會(huì)走索引
   select * from tb1 where nid = 1 or name = 'seven';
   select * from tb1 where nid = 1 or name = 'seven@live.com' and email = 'alex'

2.對(duì)于多列索引，不是使用的第一部分，則不會(huì)使用索引

3.like查詢以%開頭

select * from tb1 where email like '%cn';

4.如果列類型是字符串，那一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來,否則不使用索引

elect * from tb1 where email = 999;

5.如果mysql估計(jì)使用全表掃描要比使用索引快,則不使用索引

6.普通索引的不等于不會(huì)走索引

- !=
 select * from tb1 where email != 'alex'
 
 --特別的：如果是主鍵，則還是會(huì)走索引
  select * from tb1 where nid != 123
- >
 select * from tb1 where email > 'alex'
 
 
 --特別的：如果是主鍵或索引是整數(shù)類型，則還是會(huì)走索引
  select * from tb1 where nid > 123
  select * from tb1 where num > 123

7.組合索引最左前綴

如果組合索引為：(name,email)

name and email -- 使用索引
name -- 使用索引
email -- 不使用索引

以上就是MySQL索引失效的幾種情況匯總的詳細(xì)內(nèi)容，更多關(guān)于MySQL索引失效的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章: