别再掉入索引失效的陷阱啦

Goland猫
429 阅读6分钟

常见的现象就是：明明在字段上添加了索引，但却并未生效。很多文章和教程只是告诉你，在什么情况下索引会失效。

比如：没遵循最佳左前缀法则、范围查询的右边会失效、like查询用不到索引等等

但是没人告诉你为什么会失效，以及明明满足了失效的场景，却没有失效。

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

数据库及索引准备

为了逐项验证索引的使用情况，我们先准备一张表student：

-- 创建表 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,'001','张三',18,1,'高老庄'), 
(2,'002','李四',20,0,'花果山'), 
(3,'003','王五',50,1,'水帘洞');

在上述表结构中有三个索引：

image.png

  • id：为数据库主键；
  • idx_sn_name_age：为sn,name,age构成的联合索引；
  • idx_address：是由address构成的普通索引；

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

联合索引不满足最左匹配原则

联合索引遵从最左匹配原则，顾名思义，在联合索引中，最左侧的字段优先匹配。因此，在创建联合索引时，where子句中使用最频繁的字段放在组合索引的最左侧。

而在查询时，要想让查询条件走索引，则需满足：最左边的字段要出现在查询条件中。 实例中，idx_sn_name_age联合索引组成：

KEY `idx_sn_name_age` (`sn`,`name`,`age`)

最左边的字段为sn，一般情况下，只要保证sn出现在查询条件中，则走联合索引。 示例一：

explain SELECT * FROM student WHERE sn =  '001'

explain结果：

image.png 通过explain执行结果可以看出，上述SQL语句走了idx_sn_name_age这条索引。

示例二：

explain SELECT * FROM student WHERE sn =  '001' and age = 18

image.png 很显然，依旧走了idx_sn_name_age索引，根据上面key_len的分析，大胆猜测，在使用索引时，不仅使用了sn列，还使用了age列。

反向示例：

explain SELECT * FROM student WHERE name = '张三' and age = 18

explain结果：

image.png 此时，可以看到未有走任何索引，也就是说索引失效了。 同样的，只要没出现最左条件的组合，索引也是失效的

索引列参与运算或函数

explain SELECT * FROM student WHERE  age + 1 = 18

image.png 建议的使用方式是：先在内存中进行计算好预期的值，或者在SQL语句条件的右侧进行参数值的计算。

explain SELECT * FROM student WHERE  substr(sn,1,3) = '1'

image.png 上述示例中，索引列使用了函数（SUBSTR，字符串截取），导致索引失效。

此时，索引失效的原因与第三种情况一样，都是因为数据库要先进行全表扫描，获得数据之后再进行截取、计算，导致索引索引失效。同时，还伴随着性能问题。

Like的错误使用

示例：

explain SELECT * FROM student where address like "%高%"

explain结果：

image.png

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

其中方式一和方式三，由于占位符出现在首部，导致无法走索引。这种情况不做索引的原因很容易理解，索引本身就相当于目录，从左到右逐个排序。而条件的左侧使用了占位符，导致无法按照正常的目录进行匹配，导致索引失效就很正常了。

类型转换

示例:

explain SELECT * FROM student where sn="001"

explain结果： image.png

explain SELECT * FROM student where sn=001

explain结果：

image.png 参数类型与字段类型不匹配，导致类型发生了隐式转换，索引失效。

这种情况还有一个特例，如果字段类型为int类型，而查询条件添加了单引号或双引号，则Mysql会参数转化为int类型，虽然使用了单引号或双引号：

explain select * from student where id = '2';

上述语句是依旧会走索引的。

使用 is not null

explain SELECT * FROM student where address is not  null

explain结果：

image.png 查询条件使用is null时正常走索引，使用is not null时，不走索引。

order by导致索引失效

explain select * from student order by sn

explain结果：

image.png 其实这种情况的索引失效很容易理解，毕竟需要对全表数据进行排序处理。 那么，添加删limit关键字是否就走索引了呢？

explain select * from student order by sn limit 2

image.png 结果依旧不走索引。在网络上看到有说如果order by条件满足最左匹配则会正常走索引， 在当前8.0.18版本中并未出现。所以，在基于order by和limit进行使用时，要特别留意。是否走索引不仅涉及到数据库版本，还要看Mysql优化器是如何处理的。

这里还有一个特例，就是主键使用order by时，可以正常走索引。

explain select * from student order by id

另外，以下三条SQL语句都是走索引的，也就是说覆盖索引的场景也是可以正常走索引的。

explain select id from student order by sn;
explain select id , username from student order by sn; 
explain select sn from t_user order by sn;

使用了select *

这是最重要的，因为select * 不会走覆盖索引。所以在《阿里巴巴开发手册》的ORM映射章节中有一条【强制】的规范：

【强制】在表查询中，一律不要使用 * 作为查询的字段列表，需要哪些字段必须明确写明。 说明:1)增加查询分析器解析成本。2)增减字段容易与 resultMap 配置不一致。3)无用字段增加网络 消耗，尤其是 text 类型的字段。

比如，在上面的联合索引中，如果查询条件是sn或address，当使用了select * ，肯定是不会走索引的。 如果不是使用select * 以上的什么最左匹配，所有条件都不再适用。都会走上索引

比如

explain SELECT id,address FROM student where address like "%高%"

explain结果：

image.png

总结

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

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

判断是否有用索引，需要看一下是不是select *。 select * 会让覆盖索引失效

avatar
文章
阅读
粉丝