线上使用索引翻车了,绩效凉凉

小林coding

共 3616字,需浏览 8分钟

 ·

2021-12-14 21:16


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

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

领导:你好好反省一下,为啥出这么低级的问题。

我:好的,今晚通宵整一篇总结出来,年底不让我扛绩效了吧?!

领导:……看你这篇文章点赞能不能超过 100

我:……没戏,他们喜欢白嫖

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

使用索引翻车场景

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

CREATE TABLE `t_user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(255DEFAULT NULL,
  `age` int DEFAULT NULL,
  `address` varchar(255DEFAULT 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,可能导致索引失效

(1)执行一条简单的 SQL

EXPLAIN
SELECT * FROM t_user WHERE user_id = 123456;

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


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

EXPLAIN
SELECT * FROM t_user WHERE user_id = 123456 or age = 18;

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


分析结论:

  • 对于条件中出现 or 的情况,user_id 列加了索引,age 列是没有加索引的,假设 MySQL 一定要走索引,可能需要三步:索引扫描+全表扫描+合并;
  • 如果它一开始就走全表扫描,直接一遍扫描就完事了;
  • mysql是有优化器的,处于效率与成本,遇到 or条件,索引可能失效,看起来也合情合理。

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

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

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

EXPLAIN
SELECT * FROM t_user WHERE name = '%leixiaoshuai%';

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


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

EXPLAIN
SELECT * FROM t_user WHERE name = 'leixiaoshuai%';

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


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

EXPLAIN
SELECT idname FROM t_user WHERE name = '%leixiaoshuai%';

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


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

分析结论:

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

  • 使用覆盖索引
  • 把%放后面

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

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

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

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

EXPLAIN
SELECT * FROM t_user WHERE name = 123;

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


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

EXPLAIN
SELECT * FROM t_user WHERE name = '123';

这次果然乖乖走索引了:


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

在索引列上使用mysql的内置函数,索引失效

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

EXPLAIN
SELECT * FROM t_user WHERE UPPER(name) = 'LEIXIAOSHUAI';

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


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

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

(1)查找 name 不为空的所有列

EXPLAIN
SELECT * FROM t_user WHERE name is not null;

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


(2)查找 address 不为空的所有列

EXPLAIN
SELECT * FROM t_user WHERE address is not null;

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


(3)查找 name 不为空或者 address 不为空

EXPLAIN
SELECT * FROM t_user WHERE name is not null or address is not null;

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


对索引列运算索引失效

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

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

EXPLAIN
SELECT * FROM t_user WHERE user_id+1456789;

索引直接迷路了:


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

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

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

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

(1)where 条件同时满足 name 和 age

EXPLAIN
SELECT * FROM t_user WHERE name = 'leixiaoshuai' AND age = 18;

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


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

EXPLAIN
SELECT * FROM t_user WHERE name = 'leixiaoshuai';

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


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

EXPLAIN
SELECT * FROM t_user WHERE age = 18;

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


分析结论:

  • 当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则
  • 联合索引不满足最左原则,索引一般会失效,但是这个还跟Mysql优化器有关的。

总结:

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

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

-- End --

图解系列文章:

图解文章汇总
计算机基础学习路线
为了拿捏 Redis 数据结构,我画了 40 张图(完整版)
小林的图解系统,大曝光!
不鸽了,小林的「图解网络 3.0 」发布!
浏览 19
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报