一条慢SQL引发的索引翻车血案……

雷小帅 2021-12-13 09:46:21

前一阵子生产环境出现了接口响应慢的问题,最终定位是由于一条慢 SQL 导致的。分析了一下这条慢 SQL,发现对应数据库表也加了索引,最终结论是某些场景下索引失效了。

 

索引失效导致全表扫描,执行速度非常慢,导致大量的 SQL 处于阻塞状态。

 

下面总结一下使用索引翻车的常见名场面,希望能帮助到大家,拿个好年终奖!

 

图片

使用索引翻车场景

 

为方便演示,我们提前先建立一张数据库表。新建一个用户表,id 为主键,user_id 为唯一索引,name 为普通索引,address 为普通索引:

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
CREATE TABLE `t_user` (  `id` int NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `age` int DEFAULT NULL,  `address` varchar(255) DEFAULT NULL,  `user_id` int DEFAULT NULL,  PRIMARY KEY (`id`),  UNIQUE KEY `index_user_id` (`user_id`) USING BTREE,  KEY `index_name` (`name`) USING BTREE,  KEY `index_address` (`address`) USING BTREE) ENGINE=InnoDB;

 

一、查询条件包含or,可能导致索引失效

 

执行一条简单的 SQL

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE user_id = 123456;

 

查看执行计划的结果,很显然走了索引:

 

图片

 

加一个 or 条件,把 age 字段加上

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE user_id = 123456 or age = 18;

 

查看执行计划的结果,很显然是全表扫描:

 

图片

 

分析结论:

 

  • 对于条件中出现 or 的情况,user_id 列加了索引,age 列是没有加索引的,假设 MySQL 一定要走索引,可能需要三步:索引扫描+全表扫描+合并;

  • 如果它一开始就走全表扫描,直接一遍扫描就完事了;

  • MySQL是有优化器的,处于效率与成本,遇到 or条件,索引可能失效,看起来也合情合理。

 

敲黑板: 上面说的这种情况索引有可能失效,没有说一定会失效;如果 or 条件的列都加了索引,索引可能会走的,大家可以自己试一试。

 

二、like通配符可能导致索引失效

 

在业务中我们喜欢用模糊搜索的方式去做查询,举个例子,我们想搜索名称为 leixiaoshuai 小伙子:

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE name = '%leixiaoshuai%';

 

查看执行计划的结果,发现是全表扫描,说明索引失效了:

 

图片

 

我们试着把前面的%去掉,再查一遍:

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE name = 'leixiaoshuai%';

 

执行计划告诉我们正常走索引了:

 

图片

 

我们再把%加回来,这次不查询所有的字段,只查询 id 和 name:

 

  •  
  •  
EXPLAINSELECT id, name FROM t_user WHERE name = '%leixiaoshuai%';

 

你会惊奇的发现居然又走索引了:

 

图片

 

惊不惊喜意不意外,这是因为覆盖索引的原因。

 

分析结论:

 

like查询以%开头,会导致索引失效。可以有两种方式优化:

 

  • 使用覆盖索引

  • 把%放后面

 

敲黑板:索引包含所有满足查询需要的数据的索引,称为覆盖索引(Covering Index)。

 

三、字符串列使用 where 一定用引号括起来,否则索引失效

 

从上面的建表语句中我们知道 name 列是 varchar 可变字符类型,如果我们在查询时忘了加引号会发生呢?

 

我们做一个简单的查询,此时 name 列没有加引号:

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE name = 123;

 

name 列是建了普通索引的,理论上是应该走索引的,实际上是全表扫描:

 

图片

 

惊不惊喜意不意外,吓得我赶紧把引号加起来了:

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE name = '123';

 

这次果然乖乖走索引了:

 

图片

 

分析结论:如果某一列是字符类型,但是使用 where 不加单引号时,是字符串跟数字的比较,它们类型不匹配,MySQL会做隐式的类型转换,把它们转换为浮点数再做比较。

 

 

四、在索引列上使用MySQL的内置函数,索引失效

 

name 列已经加了普通索引,但是在查询的时候给加了内置函数,作用是将 name 列变成全大写:

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE UPPER(name) = 'LEIXIAOSHUAI';

 

索引直接 GG 了,套了一层壳子,我就不认识你了,我直接全表扫描:

 

图片

 

五、索引字段上使用is null或is not null,可能导致索引失效

 

根据前面的建表语句我们知道 name 列,address 列都加了普通索引。

 

查找 name 不为空的所有列

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE name is not null;

 

意料之中,正常走了索引:

 

图片

 

查找 address 不为空的所有列

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE address is not null;

 

意料之中,正常走了索引:

 

图片

 

查找 name 不为空或者 address 不为空

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE name is not null or address is not null;

 

咦???索引好像失效了:

 

图片

 

六、对索引列运算索引失效

 

对索引列进行四则运算(如,+、-、*、/),索引会失效。

 

user_id 是索引列,在查询的时候进行+1

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE user_id+1= 456789;

 

索引直接迷路了:

 

图片

 

七、联合索引ABC问题导致索引失效

 

我们重新建一张表,id 是主键,name 和 age 列加了一个联合索引。

 

  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
CREATE TABLE `t_user` (  `id` int NOT NULL AUTO_INCREMENT,  `name` varchar(255) DEFAULT NULL,  `age` int DEFAULT NULL,  `address` varchar(255) DEFAULT NULL,  `user_id` int DEFAULT NULL,  PRIMARY KEY (`id`),  KEY `index_name_age` (`name`,`age`) USING BTREE) ENGINE=InnoDB;

 

特别要注意一下联合索引的顺序,name 在前,age 在后。

 

where 条件同时满足 name 和 age

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE name = 'leixiaoshuai' AND age = 18;

 

毫无疑问肯定是走了索引:

 

图片

 

where 条件只有 name 列,这个能否走索引呢?

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE name = 'leixiaoshuai';

 

看结果,依然是走了联合索引,开心~

 

图片

 

where 条件只有 age 列,这个应该也可以走索引吧?!

 

  •  
  •  
EXPLAINSELECT * FROM t_user WHERE age = 18;

 

完蛋,翻车了,没走索引……

 

图片

 

分析结论:

 

  • 当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。

  • 联合索引不满足最左原则,索引一般会失效,但是这个还跟Mysql优化器有关的。

 

八、总结

 

以上就是使用索引时最常见的 7 种翻车场景,大家有没有中过招?

 

最后,我向领导也坦白了,我是因为联合索引 ABC 问题中招的,实在是草率了…

 

作者丨雷小帅
来源丨公众号:爱笑的架构师(ID:DancingOnYourCode)
dbaplus社群欢迎广大技术人员投稿,投稿邮箱:editor@dbaplus.cn
最新评论
访客 2021年09月03日

有没有1000多张表

访客 2021年08月28日

metrics =》 metrix 错误

访客 2021年08月25日

只看到如何避免,如何减少书写慢 sql

访客 2021年08月25日

没看到如何治理呀

访客 2021年07月23日

果然k8s不是神!

活动预告