10个索引失效的坑，你踩中几个

欢迎关注MySQL专栏 MySQL历险记
强烈建议收藏本导航文【MySQL历险记】MySQL的核心特性汇总

前言

索引可以用来提高查询性能，这个大家都知道。但是很多时候，明明有索引了，但查询还是很慢，用执行计划一看发现没有走索引，这时候你可能懵逼了。其实关键，你对使用索引的细节不够了解，本文将带你深入分析索引失效的底层原理，然后通过几个索引失效的场景进行加强，相信再也不用担心为什么你的SQL慢了。

索引失效原理

可能大家多多少少都听过，查询没遵循最左前缀法则、范围查询的右边会导致索引失效等等，但是有想过为什么吗？

基于索引查询流程

谈到索引失效的原理前，我们需要先弄明白MySQL是怎么利用索引去查询数据的。

索引的底层数据结构是B+树，如果对于这块内容不了解的，强烈建议大家先阅读：一步步带你设计MySQL索引数据结构

数据准备：

一张用户表user, 数据如下：

id	name	age	sex
1	a	18	男
2	b	8	男
3	a	16	女
4	b	8	男
5	c	5	女
6	e	6	男

id是主键，建立聚簇索引
在name和age上建立了联合索引，idx_name_age

最终得到的索引如下图所示：

聚簇索引中的叶子节点存放整行数据，同时数据根据id是从小到大排序。
联合索引中的叶子节点存放了name, age，id的数据，其中数据是按照name和age组合生成的结果从小到大排序。
注意我这里的B+树只有两层，实际情况一般会有3~4层。

现在如果要执行select * from user where name='a' and age = 8语句，它的整个查询执行流程是怎么样的？

MySQL会把根目录节点所在页加载到内存中，因为是排好序了，根据'(a,8)'通过二分法快速找到它的下一层目录节点所在的页。
MySQL再次把下一层目录节点所在的页加载到内存中，通过二分法找到对应的页。
最终找到叶子节点，得到的它的主键id。
然后根据主键id回到聚簇索引的B+树种，按照上面类似的流程再次定位到具体的行数据。这个过程也叫做回表。

失效原因分析

前面分析了一个基于索引进行查询的执行流程，简单来说，它是拿着你索引字段的查询值去B+树中匹配，快速的定位它所在的数据行。

那如果我们的查询值，不是按照B+树中存储的顺序去查，那MySQL显然就无法通过索引去快速查找了，直接上例子。

例子： 查找select * from user where age = 8

因为联合索引B+树中的顺序是根据先对name排序，其次在name确定的情况下，在name下对age排序，所以age=8可能出现在任何一个目录节点中。现在name都没有，MySQL就无法快速通过二分法查找了，难不成让它对B+树中的每个节点，那我还不如直接全表查询呢。

同理，like 'aaaa%' 支持索引， like '%aaaa'不支持，因为like 'aaaa%' 前面部分是确定的，可以利用B+树去搜索。

这也就是我们通常说的 “最左前缀法则” 。

其他注意点

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

索引失效情况

准备数据：

CREATE TABLE `student` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `stuno` INT NOT NULL ,
    `name` VARCHAR(20) DEFAULT NULL,
    `age` INT(3) DEFAULT NULL,
    `classId` INT(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
    #CONSTRAINT `fk_class_id` FOREIGN KEY (`classId`) REFERENCES `t_class` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;

1. 不遵守最左前缀法则索引失效

存在联合索引：

CREATE INDEX idx_age_classid_name ON student(age,classId,name);

例子：没有走索引

联合索引的顺序是age, classId, name, where条件中没有calssId, 不满足最左前缀法则，不走索引。
注意一点，这里的最左前缀匹配是只要在where中有对应的索引查询，和顺序无关，因为MySQL优化器会优化顺序。

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

存在联合索引：

CREATE INDEX idx_age_classid_name ON student(age,classId,name);

例子：

正常情况下：

范围查询> :

范围查询右边的列不能使用索引，比如上图的name。针对的范围索引有>,<等, 主要是因为classId无法确定，所以name字段在B+树中的查找无法确定导致无法走索引。
实践发现，使用>=, <=, between等范围查找不会导致索引失效，如果谁知道，可以在评论区留言下。

3. 函数、计算导致索引失效

存在索引：

CREATE INDEX idx_name ON student(NAME);

例子：

原因也很简单，因为对字段用了函数以后得到的值无法确定，就无法和B+树种的索引结构比较，同理对索引字段进行计算。

4. 类型转换导致索引失效

存在索引：

CREATE INDEX idx_name ON student(NAME);

例子：

name是varchar类型，和数值类型123比较，实际上MySQL会通过函数进行隐式处理，相当于转换成where CAST(name AS signed int) = 123。

5. 不等于可能导致索引失效

存在索引：

CREATE INDEX idx_name ON student(NAME);

例子：

B+树中判断不等于的情况，时间复杂度是O(n),如果查找的列不在索引树上，也就是不满足覆盖索引的情况，MySQL会不走索引，直接全表扫描。

6. is not null可能导致索引失效

存在索引：

CREATE INDEX idx_name ON student(NAME);

例子：

原理类似前面的不等于情况，所以在平时的开发过程中，我们尽量设置字段为Not Null,比如可以用0等特殊值表示null 的情况。
当然，is null的情况是走索引的。

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

存在索引：

CREATE INDEX idx_name ON student(NAME);

例子：

因为查询值的前面部分无法确定，无法在B+树中匹配

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

存在索引：

CREATE INDEX idx_name ON student(NAME);

例子：

因为age没有建立索引，有因为用or连接，需要全表扫描，所有name字段走索引没有任何意义了。

9. 数据分布导致索引失效

表中的字段很集中，比如性别，这时候MySQL评估使用索引比全表更慢，则不使用索引。有一种说法是当查询的数据量超过全表的30%，就不再走索引，而直接全表扫描。

10. 隐式字符编码转换导致索引失效

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

总结

本文讲解了索引失效的深层次原因，以及常见的一些索引失效的情况，大家可以反查下项目中的SQL, 是否有踩雷的。如果本文对你有帮助的话，请留下一个赞吧。

本文正在参加「金石计划 . 瓜分6万现金大奖」