社区编辑申请
注册/登录
面试突击:什么情况会导致 MySQL 索引失效?
数据库 MySQL
MySQL 优化器就能把这次查询优化转化为一个常量,如何转化以及何时转化,这个取决于优化器,这个比 eq_ref 效率高一点。

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

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

图片

而以上查询结果的列中,我们最主要观察 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 个索引,如下图所示:

图片

PS:本文以下内容基于 MySQL 5.7 InnoDB 数据引擎下。

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

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

图片

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

  • A+B+C
  • A+B
  • A+C其中:A 等于字段 sn,B 等于字段 name,C 等于字段 age。

其中:A 等于字段 sn,B 等于字段 name,C 等于字段 age。

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

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

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

  • 模糊匹配后面任意字符:like '张%'
  • 模糊匹配前面任意字符:like '%张'
  • 模糊匹配前后任意字符:like '%张%'

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

图片

索引失效情况3:列运算

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

图片

索引失效情况4:使用函数

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

图片

索引失效情况5:类型转换

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

图片

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

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

图片

总结

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

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

责任编辑:武晓燕 来源: Java面试真题解析
相关推荐

2022-06-27 09:45:22

2022-02-28 08:55:31

数据库MySQL索引

2022-06-06 07:35:26

2022-07-12 09:36:18

数据库查询

2022-06-23 12:52:53

数据库方案

2022-02-11 19:06:29

2022-05-26 08:23:05

MySQL索引数据库

2020-12-08 09:45:07

2022-05-27 21:56:55

2022-04-14 08:21:35

2020-02-20 13:39:24

MySQL索引数据库

2020-05-12 11:05:54

MySQL索引数据库

2021-02-06 09:21:17

MySQL索引面试

2022-05-19 08:28:19

索引数据库

2019-10-10 11:20:22

MySQL索引数据库

2019-07-15 16:35:43

MySQL索引阿里

2020-10-16 17:20:21

索引MySQL数据库

2022-01-09 18:32:03

2021-12-29 07:01:53

2020-04-26 09:48:11

MySQL数据库架构

同话题下的热门内容

明明加了唯一索引,为什么还是产生重复数据?推荐几款好用的MySQL开源客户端,建议收藏字节二面,两个事务执行 SQL 语句的过程中,导致死锁不同数据库存储引擎技术的优劣势分析MySQL源码解析之执行计划

编辑推荐

MySQL集群搭建详解如果对MySQL还停留在这个印象,就out了防止服务器宕机时MySQL数据丢失的几种方案MySQL innodb引擎备份工具XtraBackup之二(数据库全备)MySQL优化: Slave延迟很大的优化方法总结
我收藏的内容
点赞
收藏

51CTO技术栈公众号