面试突击：什么情况会导致 MySQL 索引失效？

我不是女神ヾ 2023-10-01 22:29 1阅读 0赞

为了验证 MySQL 中哪些情况下会导致索引失效，我们可以借助 explain 执行计划来分析索引失效的具体场景。

explain 使用如下，只需要在查询的 SQL 前面添加上 explain 关键字即可，如下图所示：

![format_png][]

**而以上查询结果的列中，我们最主要观察 key 这一列，key 这一列表示实际使用的索引，如果为 NULL 则表示未使用索引，反之则使用了索引。**

以上所有结果列说明如下：

*  id — 选择标识符，id 越大优先级越高，越先被执行；
 *  select\_type — 表示查询的类型；
 *  table — 输出结果集的表；
 *  partitions — 匹配的分区；
 *  type — 表示表的连接类型；
 *  possible\_keys — 表示查询时，可能使用的索引；
 *  **key — 表示实际使用的索引；**
 *  key\_len — 索引字段的长度；
 *  ref— 列与索引的比较；
 *  rows — 大概估算的行数；
 *  filtered — 按表条件过滤的行百分比；
 *  Extra — 执行情况的描述和说明。

其中最重要的就是 type 字段，type 值类型如下：

*  all — 扫描全表数据；
 *  index — 遍历索引；
 *  range — 索引范围查找；
 *  index\_subquery — 在子查询中使用 ref；
 *  unique\_subquery — 在子查询中使用 eq\_ref；
 *  ref\_or\_null — 对 null 进行索引的优化的 ref；
 *  fulltext — 使用全文索引；
 *  ref — 使用非唯一索引查找数据；
 *  eq\_ref — 在 join 查询中使用主键或唯一索引关联；
 *  const — 将一个主键放置到 where 后面作为条件查询， MySQL 优化器就能把这次查询优化转化为一个常量，如何转化以及何时转化，这个取决于优化器，这个比 eq\_ref 效率高一点。
    
    ### 创建测试表和数据 ###
    
    为了演示和测试那种情况下会导致索引失效，我们先创建一个测试表和相应的数据：
    
        -- 创建表
        drop table if exists student;
        create table student(
          id int primary key auto_increment comment '主键',
          sn varchar(32) comment '学号',
          name varchar(250) comment '姓名',
          age int comment '年龄',
          sex bit comment '性别',
          address varchar(250) comment '家庭地址',
          key idx_address (address),
          key idx_sn_name_age (sn,name,age)
        )ENGINE=InnoDB DEFAULT CHARSET=utf8;
        -- 添加测试数据
        insert into student(id,sn,name,age,sex,address) 
          values(1,'cn001','张三',18,1,'高老庄'),
          (2,'cn002','李四',20,0,'花果山'),
          (3,'cn003','王五',50,1,'水帘洞');
    
    当前表中总共有 3 个索引，如下图所示：
    
    ![format_png 1][]
    
    > PS：本文以下内容基于 MySQL 5.7 InnoDB 数据引擎下。

### 索引失效情况1：非最左匹配 ###

**最左匹配原则指的是，以最左边的为起点字段查询可以使用联合索引，否则将不能使用联合索引。** 我们本文的联合索引的字段顺序是 sn + name + age，我们假设它们的顺序是 A + B + C，以下联合索引的使用情况如下：

![format_png 2][]

从上述结果可以看出，如果是以最左边开始匹配的字段都可以使用上联合索引，比如：

*  A+B+C
 *  A+B
 *  A+C
    
    > 其中：A 等于字段 sn，B 等于字段 name，C 等于字段 age。

而 B+C 却不能使用到联合索引，这就是最左匹配原则。

### 索引失效情况2：错误模糊查询 ###

模糊查询 like 的常见用法有 3 种：

1.  模糊匹配后面任意字符：like '张%'
2.  模糊匹配前面任意字符：like '%张'
3.  模糊匹配前后任意字符：like '%张%'

而这 3 种模糊查询中只有第 1 种查询方式可以使用到索引，具体执行结果如下：

![format_png 3][]

### 索引失效情况3：列运算 ###

如果索引列使用了运算，那么索引也会失效，如下图所示：

![format_png 4][]

### 索引失效情况4：使用函数 ###

查询列如果使用任意 MySQL 提供的函数就会导致索引失效，比如以下列使用了 ifnull 函数之后的执行计划如下：

![format_png 5][]

### 索引失效情况5：类型转换 ###

如果索引列存在类型转换，那么也不会走索引，比如 address 为字符串类型，而查询的时候设置了 int 类型的值就会导致索引失效，如下图所示：

![format_png 6][]

### 索引失效情况6：使用 is not null ###

当在查询中使用了 is not null 也会导致索引失效，而 is null 则会正常触发索引的，如下图所示：

![format_png 7][]

### 总结 ###

导致 MySQL 索引失效的常见场景有以下 6 种：

1.  联合索引不满足最左匹配原则。
2.  模糊查询最前面的为不确定匹配字符。
3.  索引列参与了运算。
4.  索引列使用了函数。
5.  索引列存在类型转换。
6.  索引列使用 is not null 查询。

![fe11867110cb472297bb1aafe2c3d15d.jpeg][]

[format_png]: https://img-blog.csdnimg.cn/img_convert/3c02292582a5c33f502cbc27eb7797cb.webp?x-oss-process=image/format,png
[format_png 1]: https://img-blog.csdnimg.cn/img_convert/5e8caaea1340c7a2d61cfc2a1e1c9885.webp?x-oss-process=image/format,png
[format_png 2]: https://img-blog.csdnimg.cn/img_convert/f16df46406404b441c45e30df7088939.webp?x-oss-process=image/format,png
[format_png 3]: https://img-blog.csdnimg.cn/img_convert/38988be180589162d4f4d20d45e8b0dc.webp?x-oss-process=image/format,png
[format_png 4]: https://img-blog.csdnimg.cn/img_convert/54c018e712635eae98a82681f7767bcd.webp?x-oss-process=image/format,png
[format_png 5]: https://img-blog.csdnimg.cn/img_convert/3752e34ad2e9571c2f20069ca204fbef.webp?x-oss-process=image/format,png
[format_png 6]: https://img-blog.csdnimg.cn/img_convert/d4ab92bcd576a7538318dac77957d13e.webp?x-oss-process=image/format,png
[format_png 7]: https://img-blog.csdnimg.cn/img_convert/67bf503f327105f3aa91a26b5a724a48.webp?x-oss-process=image/format,png
[fe11867110cb472297bb1aafe2c3d15d.jpeg]: https://img-blog.csdnimg.cn/fe11867110cb472297bb1aafe2c3d15d.jpeg