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

案例的背景是，当时有人删除了千万级的数据，结果导致了频繁的慢查询，接下来讲一下这个案例整个排查、定位以及解决的一个过程。

这个案例的开始，当时是从线上收到大量的慢查询告警开始的，收到大量的慢查询告警之后，就去检查慢查询的SQL，结果发现不是什么特别的SQL，这些SQL语句主要都是针对一个表的，同时也比较简单，而且基本都是单行查询，看起来似乎不应该会慢查询。

所以这个时候感觉极为奇怪的，因为SQL本身完全不应该有慢查询，按说那种SQL语句，基本上都是直接根据索引查找出来的，性能应该是极高的。

那么有没有另外一种可能，慢查询不是SQL的问题，而是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的负载会超高。此时一个正常SQL执行下去，短时间内一定会慢查询的，针对类似的问题，优化手段更多的是控制导致MySQL负载过高的那些行为，比如灌入大量数据，最好在凌晨低峰期灌入，别影响线上系统运行。

结果奇怪的是，当时看了下MySQL服务器的磁盘、网络以及CPU负载，一切正常，似乎也不是这个问题导致的。这个时候，似乎看起来有点无解了是不是？

此时就必须用上一个SQL调优的利器了，也就是profiling工具，这个工具可以对SQL语句的执行耗时进行非常深入和细致的分析，使用这个工具的过程，大致如下所示：

首先要打开这个profiling，使用set profiling=1这个命令，接着MySQL就会自动记录查询语句的profiling信息了。

此时如果执行show profiles命令，就会列出各种查询语句的profiling信息，这里很关键的一点，就是它会记录下来每个查询语句的query id，所以要针对需要分析的query找到对它的query id，当时就是针对慢查询的那个SQL语句找到了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这个指标，他、它的值特别高，达到了上万这个级别。

解释一下这个指标，当然如果在调优的时候发现了类似的情况，不知道一个指标什么意思，直接google一下就可以了，很快就会查到，这里直接给一个结论了。

MVCC和隔离级别的实现原理，跟一个Read View机制是有关系的，同时还有一个至关重要的机制，就是数据的undo多版本快照链条。必须对一个数据得有一个多版本快照链条，才能实现MVCC和各种隔离级别，这个具体的原理，这里不多说了。

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

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

第二个线索Get！基本可以肯定的一点是，经过两个线索的推测，在大量简单SQL语句变成慢查询的时候，SQL是因为Sending Data环节异常耗时过高，同时此时出现了一些长事务长时间运行，大量的频繁更新数据，导致有大量的undo多版本快照链条，还无法purge清理。

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

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

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

启动一个事务，在事务里查询，凭什么就要去扫描之前那个长事务标记为删除状态的上千万的垃圾数据呢？按说那些数据都被删除了，跟你没关系了，你可以不用去扫描它们啊！这个问题的关键点就在于，那个删除千万级数据的事务是个长事务！也就是说，当启动新事务查询的时候，那个删除千万级数据的长事务一直在运行，是活跃的！所以还记得MVCC的时候，提到的一个Read View的概念么？MVCC是如何实现的？不就是基于一个Read View机制来实现的么？

当启动一个新事务查询的时候，会生成一个Read View，里面包含了当前活跃事务的最大id、最小id和事务id集合，然后它有一个判定规则。总之就是，新事务查询的时候，会根据ReadView去判断哪些数据是你可见的，以及你可见的数据版本是哪个版本，因为一个数据有一个版本链条，有的时候你可能可见的仅仅是这个数据的一个历史版本而已。

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

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

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