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语句时，尽量避免造成索引失效的情况。