MySQL 优化 - index_merge 导致查询偶发变慢

前言

今天遇到了一个有意思的问题，线上数据库 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，执行过程大致是：

1. 从 idx_user_id 索引中读取满足条件的数据。
2. 从 idx_status 索引中读取满足条件的数据。
3. 将 步骤 1、步骤 2 获取到的记录求交集。
4. 根据步骤3 的得到的 rowid 回表获取数据。
5. 判断记录是否满足其它额外的条件。

相信看到这里，就知道为什么两种执行计划差别这么大的原因了。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 能带来性能提升，某些情况下不会带来提升，反而会耗费更长的时间，属于优化器的缺陷，可以通过调整表中的索引来解决。