MySQL：千万级数据删除导致的慢查询优化实践

实际上个别特殊情况下，MySQL出现慢查询并不是SQL语句的问题，而是它自己生成服务器的负载太高了，导致SQL语句执行很慢。

• 举个例子，比如现在MySQL服务器的磁盘IO负载特别高，也就是每秒执行大量的高负载的随机IO，但是磁盘本身每秒能执行的随机IO是有限的。结果就导致你正常的SQL语句去磁盘上执行的时候，如何要跑一些随机IO，你的磁盘太繁忙了，顾不上你，导致你本来很快的一个SQL，要等很久才能执行完毕，这个时候就可能导致正常SQL语句也会变成慢查询。
• 除了磁盘以外，还有一个例子就是网络，也许网络负载很高，就可能导致你一个SQL语句要发送到MySQL上去，光是等待一个跟MySQL的连接，都很难，要等很久，或者MySQL自己网络负载太高了，带宽打满，带宽打满之后，你一个SQL也许执行很快，但是它查出来的数据返回给你，网络都发送不出去，此时也会变成慢查询。
• 另外一个是CPU负载，如果CPU负载过高的话，也会导致CPU过于繁忙去执行别的任务了，每时间执行你这个SQL语句，此时也有可能导致SQL语句出现问题的

所以说慢查询本身不一定是SQL导致的，如果你觉得SQL不应该慢查询，结果他那个时间段跑这个SQL就是慢，此时你应该排查一下当时MySQL服务器的负载，尤其看看磁盘、网络以及CPU的负载，是否正常

如果你发现那个时间段MySQL生产服务器的磁盘、网络或者CPU负载特别高，那么可能是服务器负载导致的问题

举个例子，当某个作业瞬间把大批量数据往MySQL里插入，它一瞬间服务器磁盘、网络、CPU负载都会超高。此时你一个正常SQL执行下去，短时间内一定会慢查询的，针对类似的问题，优化手段更多的是控制你导致MySQL覆盖过高的行为，这些行为最好在凌晨低峰期灌入，别影响线上系统运行。

但是如果MySQL服务器的磁盘、网络以及CPU复制正常呢？也就是说不是这个原因导致的。那怎么排查呢？

一方面可以通过执行计划检测SQL是否有问题，另一个就是检测服务器的负载，如果还是不行，第三步就是可以用MySQL profilling工具去细致分析SQL语句的执行过程和耗时。

• 首先打开这个profiling，使用用set profiling=1这个命令，接着MySQL就会自动记录查询语句的profiling信息了。
• 此时如果执行show profiles命令，就会给你列出各种查询语句的profiling信息，这里很关键的一点是，就是它会记录下来每个查询语句的query id，所以你要针对你需要分析的query找到它对应的query id。
• 然后就可以针对单个查询语句，看一下它的profiling具体信息，使用show profile cpu, block io for query xx，这里的xx是数字，此时就可以看到具体的profile信息了
• 除了cpu以及block io以外，你还可以指定去看这个SQL语句执行时候的其他各项负载和耗时。他这里会给你展示出来SQL语句执行时候的各种耗时，比如磁盘IO的耗时，CPU等待耗时，发送数据耗时，拷贝数据到临时表的耗时，等等吧，反正SQL执行过程中的各种耗时都会展示出来的。

针对上面的这个SQL语句的profiling信息，重点发现了一个问题，他的Sending Data的耗时是最高的，几乎使用了1s的时间，占据了SQL执行耗时的99%，这就很坑爹了

因为其他环节耗时低是可以理解的，毕竟这种简单SQL执行速度真的很快，基本就是10ms级别的，结果跑成了1s，那肯定Sending Data就是罪魁祸首了！

个Sending Data是在干什么呢？MySQL的官方释义如下：为一个SELECT语句读取和处理数据行，同时发送数据给客户端的过程，简单来说就是为你的SELECT语句把数据读出来，同时发送给客户端。

可是为什么这个过程会这么慢呢？profiling确实是提供给我们更多的线索了，但是似乎还是没法解决掉问题。但是毕竟我们已经捕获到了第一个比较异常的点了，就是Sending Data的耗时很高！请大家记住这个线索。

有时候针对MySQL这种复杂数据库软件的调优过程，就跟福尔摩斯破案一样，你要通过各种手段和工具去检查MySQL的各种状态，然后把有异常的一些指标记下来，作为一个线索，当你线索足够多的时候，往往就能够汇总大量的线索整理出一个思路了，那也就是一个破案的时刻了！

接着我们又用了一个命令：show engine innodb status，看一下innodb存储引擎的一些状态，此时发现了一个奇怪的指标，就是history list length这个指标，他的值特别高，达到了上万这个级别。

MySQL中有一个MVCC机制，就是多个事务在对同一个数据，有人写，有人读，此时可以有多种隔离级别。你必须对一个数据都一个多版本快照链条，才能实现各种MVCC和隔离机制。

所以当你有大量事务执行的时候，就会构建这种undo多版本快照链条，此时history list length的值就会很高。然后在事务提交之后，会有一个多版本快照链表的自动purge清理机制，只要有清理，那么这个值就会降低。

一般来说，这个值是不应该过于高的，所以我们在这里注意到了第二个线索，history list length值过高！大量的undo多版本链条数据没被清理！推测很可能就是有的事务长时间运行，所以他的多版本快照不能被purge清理，进而导致了这个history list length的值过高！

也就是说，在大量简单SQL语句变成慢查询的时候，SQL是因为Sending Data环节耗时异常高，同时此时出现了一些长事务长时间运行，大量的频繁更新数据，导致有大量的undo 的多版本快照链条，还无法purge清理。

但是这两个线索之间的关系是什么呢？是第二个线索推导出的事务长时间运行现象的发生，进而导致了第一个线索发现的Sending Data耗时过高的问题吗？可是二者之间的关系是什么呢？是不是还得找到更多的线索还行呢？

后来发现有大量的更新语句在活跃，而且有那种长期活跃的超长事务一直在跑没有结束，结果一问系统负责人，发现他在后台跑了一个定时任务，定时清理数据，结果清理的时候一下子清理了上千万的数据。

这个清理是怎么做的呢？他居然开了一个事务，然后在一个事务里删除上千万数据，导致这个事务一直在运行，然后这种长事务的运行会导致一个问题，那就是你删除的时候仅仅只是对数据加了一个删除标记，事实上并没有彻底删除掉。此时你如果跟长事务同时运行的其他事务里在查询，它在查询的时候可能会把那上千万被标记为删除的数据都扫描一遍的。因为每次扫描到一批数据，都发现标记为删除了，接着就会再继续往下扫描，所以才导致一些查询语句会那么的慢。

那么为什么你启动一个事务，在事务里查询，凭什么就要去扫描之前那个长事务标记
为删除状态的上千万的垃圾数据呢？按说那些数据都被删除了，跟你没关系了，你可以不用去扫描他们啊！

这个问题的关键点就在于，那个删除千万级数据的事务是个长事务！当你启动一个新事务查询的时候，会生成一个Read View，里面包含了大量当前活跃事务的最大id、最小id和事务id集合，然后它有一个判定规则。

总之，你的新事务在查询的时候，会根据Read View去判断哪些数据是你可见的，以及你可见的数据时哪个版本，因为一个数据有一个版本链条，有的时候你可能可见的仅仅是这个数据的一个历史版本而已。

所以正是因为这个长事务一直在运行还在删除大量的数据，而且这些数据仅仅是标记为删除，实际还没删除，所以此时你新开事务的查询是会读到所有被标记为删除的数据的，就会出现千万级的数据扫描，才会造成慢查询！

针对这个问题，其实大家要知道的一点是，永远不要在业务高峰期去运行那种删除大量数据的语句，因为这可能导致一些正常的SQL都变慢查询，因为那些SQL也许会不断扫描你标记为删除的大量数据，好不容易扫描到一批数据，结果发现是标记为删除的，于是继续扫描下去，导致了慢查询！

所以解决方案也很简单，直接kill那个正在删除千万级数据的长事务，所有SQL很快会恢复正常，从此以后，对于大量数据清理全部放在凌晨去执行，那个时候就没什么人使用系统了，所以查询也很少。