本文主要是介绍【MySQL进阶之路】千万级数据删除导致的慢查询SQL调优实战，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
欢迎关注公众号（通过文章导读关注：【11来了】），及时收到 AI 前沿项目工具及新技术的推送！
在我后台回复 「资料」 可领取
编程高频电子书！
在我后台回复「面试」可领取硬核面试笔记！文章导读地址：点击查看文章导读！
感谢你的关注！
千万级数据删除导致的慢查询SQL调优实战
先说一下案例背景：
刚开始，线上系统收到了大量的慢查询告警，检查之后，发现慢查询的都是一些比较简单的 SQL 语句，基本上都是单行查询，因此理论上性能应该是极高的，但是却变成了慢查询，因此考虑可能不是 SQL 语句性能的问题，而是 MySQL 服务器负载过高从而导致 SQL 语句执行过慢！
MySQL 服务器负载过高所导致的问题：
- 如果 MySQL 的 磁盘 IO 负载过高 ，那么此时你的 SQL 语句来执行的时候，那么 MySQL 可能就顾不上你的磁盘请求了，导致你的 SQL 执行速度过慢
- 如果 MySQL 的 网络 IO 负载过高 ，会导致你的应用与 MySQL 建立连接以及数据发送的速度过慢，从而导致 SQL 执行速度过慢的现象出现
- 如果 MySQL 的 CPU 负载过高 也会导致这种情况出现，CPU 负责的任务太多了，导致轮不到你的 SQL 执行任务，从而导致 SQL 执行过慢
1、因此，第一步就是先排查 MySQL 服务器的问题
那么在查看服务器负载之后，发现磁盘 IO、网络 IO、内存、CPU 负载都处于正常水平，并没有出现高负载的情况，因此慢查询的原因应该不是服务器负载过高所导致的
2、第二步是对 SQL 语句的执行耗时进行分析
使用 MySQL 中的 profilling 工具来对 SQL 语句进行性能分析，profilling 工具记录了 SQL 语句的各种资源使用情况，包括 CPU、IO、上下文切换、内存使用等情况
- 先通过命令开启会话级别的 profilling 功能，之后 MySQL 就会记录 SQL 语句的 profilling 信息了：
SET profiling = 1;
- 分析 SQL 语句
使用 SHOW PROFILES 命令可以查看当前会话中所有已执行的SQL语句的Profiling信息
要查看特定SQL语句的详细信息，可以使用 SHOW PROFILE 命令，指定相应的查询ID（Query_ID）
# 查看所有 SQL 语句的 profilling 信息以及 query id
show profiles;
# 查看指定 SQL 语句信息
show profile cpu, block io for query [query_id]
当分析了 SQL 语句的 profilling 信息之后，发现它的 Sending Data 耗时最高，SQL 执行耗时 1s 左右，Sending Data 就占用了几乎 1s，因此 SQL 执行速度慢的原因找到了，是因为 Sending Data
3、进一步分析
但是仅仅凭借 Sending Data 还找不出为什么 SQL 执行这么慢，因此使用了 show engine innodb status 命令来查看 InnoDB 存储引擎当前的状态，此时发现 history list length 值特别大，达到了上万的级别
那么通过查询相关资料发现 history list length 值特别大表明数据库中有着大量事务正在执行，大量事务在执行的时候，会去构建 MVCC 的 undo log 版本链，如果大量事务一直不结束，就会导致这个 undo log 版本链过长，最后导致 history list length 值特别大
因此猜测原因可能与 MySQL 中正在执行的大量事务有关
4、找到原因
那么经过排查，发现后台在跑一个定时任务，这个定时任务开启了一个事务去删除上千万条数据，并且这个事务运行的时间也很长，因此导致这个事务在运行期间，产生了大量的 undo log 版本链
那么此时其他事务来查询数据的时候，会把这些上千万条删除的数据都扫描一遍，因为这些数据是在记录版本链中的，而查询的话通过 MVCC 机制是需要去扫描记录版本链，因此 SQL 慢查询的原因就找到了：长事务中大量的删除操作，导致记录版本链过长，其他事务来查询数据时，需要扫描很多的记录版本链，导致普通的查询都会变得非常慢！
5、解决问题
针对这种问题，直接将删除很多数据的长事务给 kill 掉就可以了
之后要避免在业务高峰期的时候运行大量删除数据的语句，放在业务低峰期去执行，比如凌晨，这样会好一些
这篇关于【MySQL进阶之路】千万级数据删除导致的慢查询SQL调优实战的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
