MySQL高级第七篇：什么情况下会导致索引失效？

一、概述

• MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。
• 索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。
  • 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
  • 如果查询时没有使用索引，查询语句就会扫描表中的所有记录，在数据量大的情况下，这样查询的速度会很
    慢。
• 大多数情况下都(默认)采用B+树来构建索引。
• 其实用不用索引，最终都是优化器说了算。
• 优化器是基于什么的优化器？基于cost开销，它不是基于规则，也不是基于语义，怎么样开销小就怎么来。
• 另外，SQL语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。

二、索引失效情况总结

1. 最佳左前缀规则

• 在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。
• 如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

结论：MySQL可以为多个字段创建索引，一个索引可以包括16个字段(默认)。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第1个字段时，多列(或联合)索引不会被使用。

2. 主键插入顺序

• 对于一个使用InnoDB存储引擎的表来说，在我们没有显式的创建索引时，表中的数据实际上都是存储在聚簇索 引的叶子节点的。
• 而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果我们插入的记录的主键值是依次增大的话，那我们每插满一个数据页就换到下一个数据页继续插
• 而如果我们插入的主键值忽大忽小的话，就会造成把一条数据插入一个已经满了的数据页，产生页分裂，造成性能损耗，所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增。

3. 计算，函数，类型转换导致索引失效

比如：

SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc'; 

SELECT SQL_NO_CACHE id, stuno, NAME FROM student WHERE stuno+1 = 900001;
# NAME 为VARCHAR，转成 123 int
SELECT SQL_NO_CACHE * FROM student WHERE NAME = 123; 

这种SQL必须经过全表扫描，然后才能那每条数据来计算转换，根本使用不到索引

4. 范围条件右边的列索引失效

比如：

# age,classId,NAME 联合索引
CREATE INDEX idx_age_classId_name ON student(age,classId,NAME);

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

下边这条查询语句就只能用到age,classId索引，name就没有用到，因为 classId>20 为范围条件，它右边的索引列会失效。

所以在创建索引时，务必把涉及范围查找的字段放在最后

5. 不等于索引失效

这个很简单，对于不等于查询，我们只能一个个比较，使用不到索引

6. is null可以使用索引，is not null无法使用索引

和等于不等于有点像，is null 就相当于等于，is not null 就相当于不等于

结论：最好在设计数据表的时候就将字段设置为NOT NULL约束，如果有些字段就是要为null，可以设置默认值，比如你可以将 INT 类型的字段，默认值设置为0，将字符类型的默认值设置为空字符串(‘’)。

7. like以通配符%开头索引失效

like以确定字符开头，是可以使用索引的，但是以通配符%开头无法使用，因为它上来就不知道怎么查，就只能先全表查了

Alibaba 开发手册页强制了，严禁 左模糊 或者 全模糊 搜索，如果需要请走搜索引擎。

8. OR 前后存在非索引的列，索引失效

比如：

# age 索引
CREATE INDEX idx_age ON student(age);

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

下边这条查询语句，一个有索引，一个没索引，OR 还是要全表扫描，索性就没有用。

9. 数据库和表的字符集要统一

• 统一字符集可以避免由于字符集转换产生的乱码。
• 不同的字符集进行比较前需要进行转换会造成索失效。

三、索引使用建议

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