MySQL 索引失效的幾種類型以及解決方式

作者：Java編程鴨 2022-06-27 09:45:22

MySQL索引失效的情況有哪些呢？本文就列舉下面七種類型以及解決方式。

1. ?索引列不獨(dú)立

是指被索引的這列不能是表達(dá)式的一部分，不能是函數(shù)的參數(shù)，比如下面的這種情況：

select id,name,age,salary from table_name where salary + 1000 = 6000;

salary 列被用戶表達(dá)式的計(jì)算了，這種情況下索引就會(huì)失效，解決方式就是提前計(jì)算好條件值，不要讓索引列參與表達(dá)式計(jì)算。

索引字段作為函數(shù)的參數(shù)：

select id,name,age,salary from table_name where substring(name,1,3)= 'luc';

解決方式是什么呢，可以提前計(jì)算好條件，不要使用索引，或者可以使用其他的 sql 替換上面的，比如，上面的sql 可以使用 like 來(lái)代替：

select id,name,age,salary from table_name where name like 'luc%';

2. 使用了左模糊

select id,name,age,salary from table_name where name like '%lucs%';

平時(shí)盡可能避免用到左模糊，可以這樣寫：

select id,name,age,salary from table_name where name like 'lucs%';

如果實(shí)在避免不了左模糊查詢的話，考慮一下搜索引擎比如 ES。

3. or 查詢部分字段沒有使用索引

select id,name,age,salary from table_name where name ='lucs' and age >25

這種情況，可以為 name 和 age 都建立索引，否則會(huì)走全表掃描。

4. 字符串條件沒有使用 ''

select id,name,age,salary from table_name where phone=13088772233

上面的這條 sql phone 字段類型是字符串類型的，但是沒有使用 '13088772233 ', SQL 就全表掃描了，所以字符串索引要使用 ‘’：

select id,name,age,salary from table_name where phone='13088772233 '

5. 不符合最左前綴原則的查詢

例如有這樣一個(gè)組合索引 index(a,b,c)：

select * from table_name where b='1'and c='2'
select * from table_name where c='2'

// 上面這兩條 SQL 都是無(wú)法走索引執(zhí)行的

最左原則，就是要最左邊的優(yōu)先存在，我不在的話，你們自己就玩不動(dòng)了，除非你自己?jiǎn)为?dú)創(chuàng)立一個(gè)索引，下面這幾條 SQL 就可以走索引執(zhí)行：

select * from table_name where a = 'asaa' and b='1'and c='2'
select * from table_name where a = 'asda' and b='1231' 
// 上面這兩條是走索引的，但是下面這條你覺得索引應(yīng)該怎么走，是全部走，還是部分走索引？
select * from table_name where a = 'asda' and c='dsfsdafsfsd'

6. 索引字段沒有添加 not null 約束：

select * from table_name where a is null;
// 這條sql就無(wú)法走索引執(zhí)行了，is null 條件 不能使用索引，只能全表掃描了
// mysql 官方建議是把字段設(shè)置為 not null

所以針對(duì)這個(gè)情況，在mysql 創(chuàng)建表字段的時(shí)候，可以將需要索引的字符串設(shè)置為 not null default '' 默認(rèn)空字符串即可

7. 隱式轉(zhuǎn)換

關(guān)聯(lián)表的兩個(gè)字段類型不一致會(huì)發(fā)生隱式轉(zhuǎn)換?：

select * from table_name t1 left join table_name2 t2 on t1.id=t2.tid;
// 上面這條語(yǔ)句里，如果 t1 表的id 類型和 t2 表的tid 類型不一致的時(shí)候，就無(wú)法
// 按索引執(zhí)行了。
// 解決方式就是統(tǒng)一設(shè)置字段類型。

責(zé)任編輯：趙寧寧來(lái)源： Java編程鴨

MySQL 索引