MySQL 索引失效的几种情况

前言

MySQL 中提升性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并加快查询的速度，因此索引对查询的速度有着至关重要的影响。

• 使用索引可以快速定位表中的某条记录，从而提高数据库的查询的速度，提高数据库的性能
• 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost 开销(CostBaseOptimizer) ，它不是基于规则 (Rule-BasedOptimizer)，也不是基于语义。怎么样开销小就怎么来。另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

1、最佳左前缀法则

MySQL 建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
MySQL 可以为多个字段创建索引，一个索引可以包括 16 个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第 1 个字段时，多列（或联合）索引不会被使用。

2、主键插入顺序

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

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

3、计算、函数、类型转换（自动或手动）导致索引失效

4、范围条件右边的列索引失效（这里的右边指的是创建联合索引时，各个字段的顺序)

举例：

1. 创建索引

create index idx_age_classid_name on student(age,classid,name);

2. 此时下面的查询语句中，name 索引是失效的，因为在设置索引时，classid 在 name 的前面。

SELECT SQL_NO_CACHE * FROM student
WHERE student.age = 30 AND student.classId > 20 AND student.name = 'abc' ;

3. 若想生效，则需要把索引改成如下格式，让 name 在 classid 的前面

create index idx_age_name_classid on student(age,name,classid);

5、不等于（!= 或 <>）索引失效

使用 != 或者 <> 的条件会使索引失效

6、like 以通配符 % 开头的索引失效

Java 开发手册中提到如下内容

页面搜索严禁左模糊或者全模糊，如有需要，请走搜索引擎来解决。

7、OR 前后存在非索引的列，索引失效

在 WHERE 子句中，如果在 OR 前的条件列进行了索引，而在 OR 后的条件列没有进行索引，那么索引会失效。也就是说，OR 前后的两个条件中的列都是索引时，查询中才使用索引。
因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。

8、数据库和表的字符集统一使用 utf8mb4

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

建议:

• 对于单列索引，尽量选择针对当前 query 过滤性更好的索引
• 在选择组合索引的时候，当前 query 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 在选择组合索引的时候，尽量选择能够包含当前 query 中的 where 子句中更多字段的索引。
• 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。
  总之，书写 SQL 语句时，尽量避免造成索引失效的情况。

题外话：个人编程交流群：474651496，望与君共同进步！