专业做网站设计公司价格,百度云盘下载,网店代运营公司方案,网页免费模板下载文章目录 前言问题描述原因分析总结 前言
今天遇到了一个有意思的问题#xff0c;线上数据库 CPU 出现了偶发的抖动。定位到原因是一条查询语句偶发变慢造成的#xff0c;随后通过调整表中的索引解决。
问题描述
下方是脱敏后的 SQL 语句#xff1a;
select oss_path
f… 文章目录 前言问题描述原因分析总结 前言
今天遇到了一个有意思的问题线上数据库 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多个索引的条件使用 ANDunion多个索引的条件使用 ORsort_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 字段的选择性非常差因为它只有 0123 四种取值当然也会有特殊情况。
优化的方法也非常简单既然优化器走了 intersect(idx_user_id, idx_status) 我们就创建一个 user_id、status 的复合索引创建完成后 idx_user_id 索引就变成了冗余索引需要在复合索引创建完成后删除掉。
索引调整完成后就再也没有出现这类查询偶发变慢的情况了。
另外值得注意的是使用了 index_merge 的 SQL慢日志中记录的扫描行数是取交集时的扫描行数这部分扫描行数可能会很小容易造成干扰为什么只扫描了 9w 行反而花费了几百秒。我们只需要把 index_merge 中的索引字段分别拆出来执行一遍就知道慢在哪里了。
总结
优化器通过某种机制检测到 index_merge 能带来性能提升某些情况下不会带来提升反而会耗费更长的时间属于优化器的缺陷可以通过调整表中的索引来解决。
