记录一次MYSQL慢查询分析经历
eplain执行计划于实际执行结果不一致,大概率因为某种原因导致在执行时索引失效
eplain结果与实际结果差异较大,大概率还有索引优化空间
联合索引优于多个单字段索引,最左匹配原则要牢记
今天收到运维反馈业务库线上有条SQL平均执行时间10多秒,扫描了20多万条数据,刚好是近期困扰很久的盘中标签更新堵塞的问题,一直没有找到明确的优化点,于是进行了一系列的排查。
运维反馈的慢日志如下:
1 | # Time: 2020-10-29T07:43:16.238940Z |
慢日志分析显示,该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 | # stock 表 清除无用索引 |
优化后 执行计划分析结果如下:
只需要扫描10条数据,与返回结果一致,数据库闲时负载也明显降低,右边的红框是优化后的负载,优化效果明显。
该SQL是在数据更新期间,用于获取现有数据,在写相关的代码是并没有都该查询进行仔细查看,只是记得相关的字段都有索引,相关的表还有一条更复杂的SQL,关联了5张表,该SQL在上线前进行了仔细的优化,线上也没有发现慢查询,因此开始并没有定位到这个问题,今天终于解决了一个心病,可以睡个安稳觉了,明天继续关注,希望一切正常,哦也~