记录一次长时间未提交事务造成的慢SQL

目录问题描述问题分析1、了解前后信息2、分析执行计划3、分析生产环境系统负载4、分析数据库性能5、初步锁定根因为长时间未提交事务导致6、最终根因定位7、原理分析问题描述开发反馈执行某条select语句的时候生产环境和测试环境耗时相差非常大生产耗时要18S测试环境耗时不到1秒钟。问题分析1、了解前后信息a、该SQL是近期新上的b、生产执行耗时要18S测试环境耗时不到1秒钟c、该SQL属于业务人员点击页面发起的查询d、普通select语句2、分析执行计划比较了生产和测试环境的执行计划执行计划完全相同比较数据量也相差不大因此排除执行计划不同导致的耗时差异。3、分析生产环境系统负载检查生产环境io、cpu、内存负载情况系统负载不高未发现瓶颈。4、分析数据库性能a、计算缓冲池命中率可以达到99.99%说明数据库内存配置合理b、检查数据库会话信息活跃连接数量、连接数使用率不高未发现瓶颈c、检查数据库异常会话依次检查数据库是否有锁等待、长时间未提交事务发现数据库有一条insert into select 的SQL语句已经执行超过半个月还没有提交该insert语句中的select子查询为多表关联其中一张表与耗时慢的SQL涉及的表相同故怀疑耗时慢的select语句是受到长时间未提交事务影响。d、查看该慢SQL在主库和从库的执行计划、耗时发现主从执行计划相同主库执行耗时18S从库执行耗时11S从库执行明显比主库快。5、初步锁定根因为长时间未提交事务导致a、手动将长时间未提交事务杀掉b、再次查看该慢SQL在主库的耗时发现耗时已经降到11S和从库一致。至此已经解决了主从执行耗时不一致的问题。还剩下一个问题就是生产和测试执行耗时不同。c、继续分析慢SQL该select语句用到了group by和order by并且生产和测试都用到了file sort去掉order by发现测试用于排序的行记录数比生产少很多测试返回给排序的行记录数为几百条而生产上返回给排序的行记录是几百万行故生产耗时比测试多也是意料之中。毕竟测试数据是做过脱敏处理并且测试和生成数据也有区别。6、最终根因定位即长时间未提交事务导致主库对应的select语句性能下降解决方案为杀掉长时间未提交的事务。7、原理分析给大家留个思考我们下次再展开讨论大家可以在评论区发表自己的看法。