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

1. 使用or操作符

當where語句中使用or操作符并且or兩邊的條件涉及到至少兩個字段時，MySQL無法使用索引，會轉向全表掃描。因此，應盡量避免使用or操作符。

原因：因為MySQL中的索引是根據(jù)某個字段進行排序建立的。當使用or操作符，說明有兩個條件其中某個條件成立即可，而我們使用某個索引時只能判斷出對應字段的條件是否成立，即使不成立，另一個條件成立時該記錄也符合我們要查詢的結果。所以使用索引無法做出判斷。

例：

-- 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. 復合索引失效

如果使用了復合索引，但查詢時未使用索引的第一列，索引也會失效。

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

例：

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

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

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

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

3. like查詢

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

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

例：

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

可以看出type為range：使用二級索引進行范圍查詢。

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

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

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

原因：因為索引保存的是索引字段的原始值，而不是經(jīng)過函數(shù)計算后的值，自然就沒辦法走索引了。

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

5. 隱式類型轉換

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

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

隱式類型轉換會導致索引失效，比如當字段類型為字符串且建有索引，而查詢條件類型為數(shù)值時，會將字符串類型隱式轉換為浮點型，此時索引會失效。

原因：字符串類型轉換為浮點數(shù)會使用cast函數(shù)，此時索引列上使用函數(shù)，導致索引失效。

EXPLAIN SELECT * FROM test WHERE t1 = 1.1;

6. 對索引進行表達式計算

原因：因為索引保存的是索引字段的原始值，而不是 id + 1 表達式計算后的值，所以無法走索引，只能通過把索引字段的取值取出來，然后進行表達式的計算來進行條件判斷，因此采用的就是全表掃描的方式。

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

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

補充知識：索引設計建議

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

為常用查詢字段建索引

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

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

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

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

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

刪除沒用的索引，因為它會占一定空間

總結 

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

最新評論