如何避免索引失效
1、全值匹配
系統(tǒng)中經(jīng)常出現(xiàn)的sql語句如下：
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = 'abcd'
優(yōu)化后
CREATE INDEX idx_age_deptid_name ON emp(age,deptid,NAME)
建立索引前
索引后
2、最佳左前綴法則
如果系統(tǒng)經(jīng)常出現(xiàn)的sql如下：
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd'
或者
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd'
那原來的idx_age_deptid_name 還能否正常使用？
如果索引了多列，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。
3、不在索引列上做任何操作（計算、函數(shù)、(自動or手動)類型轉(zhuǎn)換），會導(dǎo)致索引失效而轉(zhuǎn)向全表掃描
4、存儲引擎不能使用索引中范圍條件右邊的列
如果系統(tǒng)經(jīng)常出現(xiàn)的sql如下：
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ;
那么索引 idx_age_deptid_name這個索引還能正常使用么？
如果這種sql 出現(xiàn)較多
應(yīng)該建立：
create index idx_age_name_deptid on emp(age,name,deptid)
效果
# drop index idx_age_name_deptid on emp
5、mysql 在使用不等于(!= 或者<>)的時候無法使用索引會導(dǎo)致全表掃描
CREATE INDEX idx_name ON emp(NAME)
EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name <> 'abc'
6、is not null 也無法使用索引,但是is null是可以使用索引的
UPDATE emp SET age =NULL WHERE id=123456;
下列哪個sql語句可以用到索引
EXPLAIN SELECT * FROM emp WHERE age IS NULL
EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL
7、like以通配符開頭(’%abc…’)mysql索引失效會變成全表掃描的操作
文章來源：http://www.zghlxwxcb.cn/news/detail-721245.html
8、字符串不加單引號索引失效
文章來源地址http://www.zghlxwxcb.cn/news/detail-721245.html
9、總結(jié)
假設(shè)index(a,b,c)
where a = 3 索引是否被使用：Y,使用到a
where a = 3 and b = 5 索引是否被使用：Y,使用到a，b
where a = 3 and b = 5 and c = 4 索引是否被使用：Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4 或者 where c = 4 索引是否被使用：N
where a = 3 and c = 5 索引是否被使用：使用到a， 但是c不可以，b中間斷了
where a = 3 and b > 4 and c = 5 索引是否被使用：使用到a和b， c不能用在范圍之后，b斷了
where a is null and b is not null 索引是否被使用： is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3 索引是否被使用：不能使用索引
where abs(a) =3 索引是否被使用：不能使用 索引
where a = 3 and b like 'kk%' and c = 4 索引是否被使用：Y,使用到a,b,c
where a = 3 and b like '%kk' and c = 4 索引是否被使用：Y,只用到a
where a = 3 and b like '%kk%' and c = 4 索引是否被使用：Y,只用到a
where a = 3 and b like 'k%kk%' and c = 4 索引是否被使用：Y,使用到a,b,c
到了這里，關(guān)于SQL查詢優(yōu)化---單表使用索引及常見索引失效優(yōu)化的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！
