5.索引失效的原因(11种情况,详讲)

1)没有查询条件,或者查询条件的列没有索引,则不走索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId=4 AND NAME = 'abcd';

2)最佳左前缀法则

索引文件具有 B-Tree 的最左前缀匹配特性,如果左边的值未确定,那么无法使用此索引

3)主键插入顺序

对于一个使用InnoDB存储引擎的表来说,在我们没有显式的创建索引时,表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的,数据页和记录又是按照记录主键值从小到大的顺序进行排序,所以如果我们插入的记录的主键值是依次增大的话,那我们每插满一个数据页就换到下一个数据页继续插,而如果我们插入的主键值忽大忽小的话,就比较麻烦了,假设某个数据页存储的记录经满了,它存储的主键值在1~100之间:

773117b56e2d4f14a60dced40593b3b0.png

如果此时再插入一条主键值为 9 的记录,那它插入的位置就如下图:

de1193a3f23f45a4a1d43c1392ef1e87.png

可这个数据页已经满了,再插进来咋办呢?

  • 我们需要把当前 页面分裂 成两个页面,把本页中的一些记录移动到新创建的这个页中。
  • 页面分裂和记录移位意味着什么?意味着: 性能损耗 !
  • 所以如果我们想尽量避免这样无谓的性能损耗,最好让插入的记录的 主键值依次递增 ,这样就不会发生这样的性能损耗了。
  • 建议:让主键具有 AUTO_INCREMENT ,让存储引擎自己为表生成主键,而不是我们手动插入

#4)计算、函数、类型转换(自动或手动)导致索引失效

能够使用上索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
#函数运算,索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

5)类型转换导致索引失效

如果不带引号,就是数字,字符类型不匹配,不走索引。

#索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123;
#走索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME = '123';

6)范围条件右边的列索引失效

student.classId>20; 范围索引失效

#走了age索引,范围后面的索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId>20 AND student.name = 'abc' ;
EXPLAIN SELECT SQL_NO_CACHE * FROM student
WHERE student.age=30 AND student.name = 'abc' AND student.classId>20;

7)不等于(!= 或者<>)索引失效

#走索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name = 'abc' ;
#索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name <> 'abc' ;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name != 'abc' ;

8)is null可以使用索引,is not null无法使用索引

#使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
#无法使用索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;

9)like以通配符%开头索引失效

#走索引
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE 'ab%';
#索引失效
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE NAME LIKE '%ab%';

10)查询条件包含or且字段列不含索引

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;

辅助索引+or+无索引的列:会先走索引列,但无索引的列会进行全表扫描,所以还不如不走索引,直接都全表扫描完事。

如果or前后都有索引,那么可能走索引,也可能不走索引。

11)数据库和表的字符集统一使用utf8mb4

统一使用utf8mb4( 5.5.3版本以上支持)兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的 字符集 进行比较前需要进行 转换 会造成索引失效。

一般性建议:

●对于单列索引,尽量选择针对当前query过滤性更好的索引

●在选择组合索引的时候,当前query中过滤性最好的字段在索引字段顺序中,位置越靠前越好。

●在选择组合索引的时候,尽量选择能够包含当前query中的where子句中更多字段的索引。

●在选择组合索引的时候,如果某个字段可能出现范围查询时,尽量把这个字段放在索引次序的最后面。

总之,书写SQL语句时,尽量避免造成索引失效的情况。