文章目录
- 前言
- 问题描述
- 原因分析
- 总结
前言
今天遇到了一个有意思的问题,线上数据库 CPU 出现了偶发的抖动。定位到原因是一条查询语句偶发变慢造成的,随后通过调整表中的索引解决。
问题描述
下方是脱敏后的 SQL 语句:
select
oss_path
from
table_name
where
status = 2
and enabled = 1
and user_id = 12324215
表中除了主键外,还有两个索引,分别是 status 字段的二级索引和 user_id 字段的中二级索引。经过观察这类 SQL 的执行计划有两种:
- SQL 偶发会使用 index_merge 通过使用两个字段的索引过滤,然后取交集,再返回数据,耗时 120 秒。
- SQL 会使用 user_id 字段的索引进行过滤,耗时 50ms。
SQL 的执行耗时差别非常大,究竟是为何呢?见下文分析。
原因分析
SQL 变慢的原因就是使用了 index_merge,可以通过 explain format = json 查看执行计划,access_type = index_merge 表示使用了两个索引。index_merge 也叫索引合并是优化器想利用两个索引,取交集或并集操作后,再回表获取数据。从而优化一些 SQL 表中字段有多个 and 或者 or 的查询,刚好这些 and 和 or 字段上有索引。
index_merge 分三种类型:
- intersect:多个索引的条件使用 AND
- union:多个索引的条件使用 OR
- sort_union:多个索引的条件使用 OR
如何确认是哪种类型的呢?explain format = json 中的 key 字段中 intersect(idx_user_id, idx_status) 会显示 merge 的索引和类型。
在上方案例中的 SQL 使用的是 intersect 类型的 merge,执行过程大致是:
- 从 idx_user_id 索引中读取满足条件的数据。
- 从 idx_status 索引中读取满足条件的数据。
- 将 步骤 1、步骤 2 获取到的记录求交集。
- 根据步骤3 的得到的 rowid 回表获取数据。
- 判断记录是否满足其它额外的条件。
相信看到这里,就知道为什么两种执行计划差别这么大的原因了。idx_status 字段的索引选择性非常差,通过该字段过滤后的结果集有 80w 行,而 idx_user_id 字段选择性非常好,过滤后只有 5 行。通过 idx_status 字段过滤一次数据就需要几十秒的时间,再加上取交集的时间,耗费直接 100 多秒了。属于优化器的缺陷,也反映了表中的索引建立的不规范,因为 status 字段的选择性非常差,因为它只有 0,1,2,3 四种取值,当然也会有特殊情况。
优化的方法也非常简单,既然优化器走了 intersect(idx_user_id, idx_status) 我们就创建一个 user_id、status 的复合索引,创建完成后 idx_user_id 索引就变成了冗余索引,需要在复合索引创建完成后,删除掉。
索引调整完成后,就再也没有出现这类查询偶发变慢的情况了。
另外,值得注意的是,使用了 index_merge 的 SQL,慢日志中记录的扫描行数是取交集时的扫描行数,这部分扫描行数可能会很小,容易造成干扰,为什么只扫描了 9w 行,反而花费了几百秒。我们只需要把 index_merge 中的索引字段分别拆出来执行一遍,就知道慢在哪里了。
总结
优化器通过某种机制检测到 index_merge 能带来性能提升,某些情况下不会带来提升,反而会耗费更长的时间,属于优化器的缺陷,可以通过调整表中的索引来解决。