什么情况会导致索引失效？ | Javaᶜⁿ 面试突击

索引失效是指在 SQL 查询中，索引没有被使用到，这种情况下，MySQL 会进行全表扫描，这种情况下，查询效率会很低，甚至会导致服务器负载过高，甚至宕机。

导致索引失效的场景有以下这些：

未遵循最左匹配原则
使用列运算
使用函数方法
类型转换
使用 is not null
错误的模糊匹配

接下来，我们一个一个来看。

准备工作

为了验证 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 数据引擎下。

未遵循最左匹配原则

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

A+B+C
A+B
A+C

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

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

列运算

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

函数方法

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

类型转换

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

使用 is not null

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

错误的模糊查询

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

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

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

特殊说明

以上内容来自我的《Java 面试突击训练营》，这门课程是有着十几年工作经验（前 360 开发工程师），10 年面试官经验的我，花费 4 年时间打磨完成的一门视频面试课。学完训练营的课程之后，基本可以应对目前市面上绝大部分公司的面试了，并且课程配备了 9 大就业服务，帮助上千人找到 Java 工作，其中上百人拿到大厂 Offer，学员最高薪资 70W 年薪，面试课目录和 9 大服务如下：

加我微信咨询：vipStone【备注：训练营】

# 准备工作

# 创建测试表和数据

# 未遵循最左匹配原则

# 列运算

# 函数方法

# 类型转换

# 使用 is not null

# 错误的模糊查询

# 特殊说明