故障案例 | 一次慢SQL优化分析全过程

欢迎来到 GreatSQL社区分享的MySQL技术文章,如有疑问或想学习的内容,可以在下方评论区留言,看到后会进行解答
  • GreatSQL社区原创内容未经授权不得随意使用,转载请联系小编并注明来源。

客户发给我一个SQL,让我看看,为什么执行几分钟没有执行完。

我第一眼看到SQL的时候,我也觉得很简单,优化过程也比较简单,但是带来的分析过程与经验还是值得分享的。

SQL语句如下:

update ap_receive_benefits_log
  set orderstate= i_orderstate where
  requestid = i_orderid;

但是这个SQL执行时被严重阻塞了

故障案例 | 一次慢SQL优化分析全过程_第1张图片

但是这个SQL执行时被严重阻塞了

故障案例 | 一次慢SQL优化分析全过程_第2张图片

该SQL的执行计划

故障案例 | 一次慢SQL优化分析全过程_第3张图片

疑问1

发现执行计划key走的主键,但是细看行数,会发现是全表扫描了数据。

如果没有可用索引的情况下,执行计划为什么显示走的主键,而不是空的呢?

疑问2

这个SQL里的 i_orderid 字段会不会是表中的一个字段呢,如果不是字段,是哪里来的?

分析解决步骤如下

1.确认表中的数据量约75万,这个UPDATE语句每天都需要执行很多次。

2.查看表后,发现并没有 i_orderid 字段,很是奇怪。就想这个SQL怎么来的,让开发确认一下这个SQL的来源,我来确认执行计划。如果是mysql 5.6以上版本可以直接查看UPDATE的执行计划,发现这个语句没有利用任何索引。

3.开发找了半天,确认程序没有这个语句。怎么办,好像成了‘没人认领的死尸’,开发确认不了,只能自己查了。

4.使用 pt-query-digest 分析最近几个小时的慢查询,发现问题如下:

故障案例 | 一次慢SQL优化分析全过程_第4张图片

5.有一个存储过程执行次数很多,响应时间也是也是最大,分析此过程,查看该存储过程:

故障案例 | 一次慢SQL优化分析全过程_第5张图片

6.此存储过程执行情况:

故障案例 | 一次慢SQL优化分析全过程_第6张图片

7.到此,通过查看存储过程,可以确认发生阻塞的语句是过程里调用的,把存储过程发给开发,再次得到确认。

8.知道语句了,优化就简单了,先确认字段的基数(唯一性)。

故障案例 | 一次慢SQL优化分析全过程_第7张图片

9.加上索引 alter table ap_receive_benefits_log idx_requestId(requestId); 后,这个UPDATE就可以走索引了,问题解决。

再次回应上面的疑问

疑问1

为什么update执行计划没有索引情况下,使用的主键?

这个因为update语句查看执行计划的时候显示用的是主键,全表更新就是聚集索引,把这个update语句换成select,执行计划就是key显示的空。以下是测试的例子:

update的时候执行计划

file

转成select语句执行计划

file

疑问2

为什么show processlist显示的是字段或者是变量名,误导了我的思路?

测试一下:

创建过程并调用,延迟结果

故障案例 | 一次慢SQL优化分析全过程_第8张图片

查看processlist的结果

故障案例 | 一次慢SQL优化分析全过程_第9张图片

从测试结果看到,过程调用有变量传参的时候,在processlist中显示的变量传参名,而不是表的字段名。

Enjoy GreatSQL :)

文章推荐:

GreatSQL MGR FAQ
https://mp.weixin.qq.com/s/J6...

万答#12,MGR整个集群挂掉后,如何才能自动选主,不用手动干预
https://mp.weixin.qq.com/s/07...

『2021数据技术嘉年华·ON LINE』:《MySQL高可用架构演进及实践》
https://mp.weixin.qq.com/s/u7...

一条sql语句慢在哪之抓包分析
https://mp.weixin.qq.com/s/AY...

万答#15,都有哪些情况可能导致MGR服务无法启动
https://mp.weixin.qq.com/s/in...

技术分享 | 为什么MGR一致性模式不推荐AFTER
https://mp.weixin.qq.com/s/rN...

关于 GreatSQL

GreatSQL是由万里数据库维护的MySQL分支,专注于提升MGR可靠性及性能,支持InnoDB并行查询特性,是适用于金融级应用的MySQL分支版本。

Gitee:
https://gitee.com/GreatSQL/Gr...

GitHub:
https://github.com/GreatSQL/G...

Bilibili:
https://space.bilibili.com/13...

微信&QQ群:
可搜索添加GreatSQL社区助手微信好友,发送验证信息“加群”加入GreatSQL/MGR交流微信群

QQ群:533341697
微信小助手:wanlidbc

本文由博客一文多发平台 OpenWrite 发布!

你可能感兴趣的