记一次MYSQL慢查询分析经历

记录一次MYSQL慢查询分析经历

eplain执行计划于实际执行结果不一致,大概率因为某种原因导致在执行时索引失效

eplain结果与实际结果差异较大,大概率还有索引优化空间

联合索引优于多个单字段索引,最左匹配原则要牢记

今天收到运维反馈业务库线上有条SQL平均执行时间10多秒,扫描了20多万条数据,刚好是近期困扰很久的盘中标签更新堵塞的问题,一直没有找到明确的优化点,于是进行了一系列的排查。

运维反馈的慢日志如下:

1
2
3
4
5
6
7
# Time: 2020-10-29T07:43:16.238940Z
# User@Host: stocktagger[stocktagger] @ [*.*.*.*] Id: 246256322
# Query_time: 13.660244 Lock_time: 0.000035 Rows_sent: 10 Rows_examined: 267588

SET timestamp=1603957396;
SELECT `a`.`id`, `a`.`seq`, `a`.`relate_mark_md5`, `a`.`is_customize` FROM `stock_tag_value_access` AS `a`
INNER JOIN `stock` AS `s` ON s.id=a.stock_id WHERE (a.tag_id = 374098) AND (a.tag_value_id = 66297) AND (s.domain = 'astock');

慢日志分析显示,该SQL实际执行时间约13秒,返回结果10行,实际扫描26万行

对该SQL 执行 explain 得到以下结果 :

执行计划需要扫描 3万多行 , 执行计划与慢日志结果差异较大,同时执行计划的扫描条数也明显偏高。

数据库情况:其中 tag_id 为 374098 的数据在目前access表中有 236461条记录 tag_value_id 的记录为10条, stock表约3万条。

根据慢日志与执行计划不一致,怀疑tag_value_id 没有走索引,查阅mysql索引失效相关记录资料,发现隐式类型转换会导致索引失效,查看数据库字段,发现tag_value_id 字段类型应该为int 型,实际为 varchar, 在查询时传入的 是整形 ,问题基本定位应该是发生了隐式类型转换导致索引失效。修改数据字段类型

1
ALTER  TABLE stock_tag_value_access  MODIFY COLUMN tag_value_id int(11) not null comment "标签值ID"

继续分析,执行计划显示该记录需要扫描3万余条记录,与stock表数据量相当,刚好 有一个查询条件,s.domain='astock' , 检查索引发现,stock表关于domain字段存在code_domain索引,按照最左匹配原则,该索引不会命中 , 因此调整stock表增加索引 domain

1
ALTER table stock add index idx_domain(domain)

同时对表中其他索引的合理性进行了统一的排查,进行了如下操作:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
# stock 表 清除无用索引   
DROP INDEX Index_code_isconcept ON stock
DROP INDEX Index_isconcept ON stock

# 存在联合索引 idx_stock_id_tag_value_id stockid 可以命中最左匹配
DROP INDEX Index_stockid ON stock_tag_value_access

# 存在联合索引 uniq_tagid_stockid_value tagid 可以命中最左匹配
DROP INDEX Index_tagid ON stock_tag_value_access

# 基于最左匹配原则, 调整联合索引顺序,先新增再删除避免脏数据写入
ALTER TABLE stock_tag_value_access
add unique index uniq_tagid_stockid_value(tag_id,stock_id,tag_value),
add index idx_stock_id_tag_value_id(stock_id,tag_value_id)

# 删除该唯一索引修改联合排序
DROP INDEX Index_stockid_tagid_value ON stock_tag_value_access

优化后 执行计划分析结果如下:

image-20201029221900480

只需要扫描10条数据,与返回结果一致,数据库闲时负载也明显降低,右边的红框是优化后的负载,优化效果明显。

image-20201029222015493

该SQL是在数据更新期间,用于获取现有数据,在写相关的代码是并没有都该查询进行仔细查看,只是记得相关的字段都有索引,相关的表还有一条更复杂的SQL,关联了5张表,该SQL在上线前进行了仔细的优化,线上也没有发现慢查询,因此开始并没有定位到这个问题,今天终于解决了一个心病,可以睡个安稳觉了,明天继续关注,希望一切正常,哦也~