MySQL索引是提高查詢效率的重要手段。索引失效會(huì)導(dǎo)致查詢效率下降，甚至全表掃描，影響數(shù)據(jù)庫性能。以下是可能導(dǎo)致MySQL索引失效的情況：

1. 使用or操作符

當(dāng)where語句中使用or操作符并且or兩邊的條件涉及到至少兩個(gè)字段時(shí)，MySQL無法使用索引，會(huì)轉(zhuǎn)向全表掃描。因此，應(yīng)盡量避免使用or操作符。

原因：因?yàn)镸ySQL中的索引是根據(jù)某個(gè)字段進(jìn)行排序建立的。當(dāng)使用or操作符，說明有兩個(gè)條件其中某個(gè)條件成立即可，而我們使用某個(gè)索引時(shí)只能判斷出對(duì)應(yīng)字段的條件是否成立，即使不成立，另一個(gè)條件成立時(shí)該記錄也符合我們要查詢的結(jié)果。所以使用索引無法做出判斷。

例：

-- id為主鍵索引
EXPLAIN SELECT * FROM test WHERE id > 1 OR `name` = 'zs';

可以看出type為ALL：全表掃描

EXPLAIN SELECT * FROM test WHERE id > 3 OR id < 1;

可以看出type為PRIMARY：使用了主鍵索引；

2. 復(fù)合索引失效

如果使用了復(fù)合索引，但查詢時(shí)未使用索引的第一列，索引也會(huì)失效。

原因：比如我們根據(jù)字段（t1，t2，t3）建立了復(fù)合索引，則排序規(guī)則是先按t1字段進(jìn)行排序，t1字段相同再按t2字段排序，當(dāng)t1、t2字段都相同時(shí)再按t3字段進(jìn)行排序。如果我們的查詢條件中沒有使用到第一列，那么該索引也就沒有辦法使用。

例：

-- t1、t2列建立了符合索引
EXPLAIN SELECT * FROM test WHERE t1 = '1' AND t2 = '2';

可以看出type為ref：使用了二級(jí)索引；（當(dāng)使用二級(jí)索引列于常數(shù)進(jìn)行等值比較時(shí)，訪問方法為ref）

-- 未使用索引的第一列t1
EXPLAIN SELECT * FROM test WHERE t2 = '1';

可以看出type為ALL：全表掃描

3. like查詢

如果使用了like且以%開頭，則索引會(huì)失效。

原因：模糊查詢一般用在字符串的字段上，而字符串的排序規(guī)則為按字母字典序排序，如果以%開頭，表示前面的字符取啥都行，則無法使用索引。

例：

EXPLAIN SELECT * FROM test WHERE t1 LIKE '1%';

可以看出type為range：使用二級(jí)索引進(jìn)行范圍查詢。

EXPLAIN SELECT * FROM test WHERE t1 LIKE '%1';

可以看出type為ALL：全表掃描

4. 索引列上使用函數(shù)

原因：因?yàn)樗饕４娴氖撬饕侄蔚脑贾?，而不是?jīng)過函數(shù)計(jì)算后的值，自然就沒辦法走索引了。

explain select * from test where length(t1) = 2;

5. 隱式類型轉(zhuǎn)換

隱式類型轉(zhuǎn)換規(guī)則：

1. 如果一個(gè)或兩個(gè)參數(shù)都是NULL，比較的結(jié)果是NULL，除了安全的<=>相等比較運(yùn)算符。對(duì)于NULL <=> NULL，結(jié)果為true。不需要轉(zhuǎn)換。
2. 如果比較操作中的兩個(gè)參數(shù)都是字符串，則將它們作為字符串進(jìn)行比較。
3. 如果兩個(gè)參數(shù)都是整數(shù)，則將它們作為整數(shù)進(jìn)行比較。
4. 十六進(jìn)制值如果不與數(shù)字進(jìn)行比較，則被視為二進(jìn)制字符串。
5. 如果其中一個(gè)參數(shù)是十進(jìn)制值，則比較取決于另一個(gè)參數(shù)。 如果另一個(gè)參數(shù)是十進(jìn)制或整數(shù)值，則將參數(shù)與十進(jìn)制值進(jìn)行比較，如果另一個(gè)參數(shù)是浮點(diǎn)值，則將參數(shù)作為浮點(diǎn)值進(jìn)行比較（但沒有將整數(shù)類型轉(zhuǎn)換為浮點(diǎn)類型）。
6. 如果其中一個(gè)參數(shù)是TIMESTAMP或DATETIME列，另一個(gè)參數(shù)是常量，則在執(zhí)行比較之前將常量轉(zhuǎn)換為時(shí)間戳。
7. 在所有其他情況下，參數(shù)都是作為浮點(diǎn)數(shù)（雙精度）比較的。

隱式類型轉(zhuǎn)換會(huì)導(dǎo)致索引失效，比如當(dāng)字段類型為字符串且建有索引，而查詢條件類型為數(shù)值時(shí)，會(huì)將字符串類型隱式轉(zhuǎn)換為浮點(diǎn)型，此時(shí)索引會(huì)失效。

原因：字符串類型轉(zhuǎn)換為浮點(diǎn)數(shù)會(huì)使用cast函數(shù)，此時(shí)索引列上使用函數(shù)，導(dǎo)致索引失效。

EXPLAIN SELECT * FROM test WHERE t1 = 1.1;

6. 對(duì)索引進(jìn)行表達(dá)式計(jì)算

原因：因?yàn)樗饕４娴氖撬饕侄蔚脑贾?，而不?id + 1 表達(dá)式計(jì)算后的值，所以無法走索引，只能通過把索引字段的取值取出來，然后進(jìn)行表達(dá)式的計(jì)算來進(jìn)行條件判斷，因此采用的就是全表掃描的方式。

-- num字段有二級(jí)索引
EXPLAIN SELECT * FROM test WHERE num = 1 + 10;

EXPLAIN SELECT * FROM test WHERE num + 1 = 10;

補(bǔ)充知識(shí)：索引設(shè)計(jì)建議

優(yōu)先使用唯一索引，能夠快速定位

為常用查詢字段建索引

為排序、分組和聯(lián)合查詢字段建索引

一張表的索引數(shù)量不超過5個(gè)

表數(shù)據(jù)量少，可以不用建索引

盡量使用占用空間小的字段建索引

用idx_或unx_等前綴命名索引，方面查找

刪除沒用的索引，因?yàn)樗鼤?huì)占一定空間

總結(jié) 

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

最新評(píng)論