一次大量删除导致MySQL慢查的分析

背景

监控上收到了大量慢查的告警，业务也反馈查询很慢，随即打开电脑确认慢查的原因。

现象描述

通过平台的慢查分析之后，我们发现慢查有以下特征：

1. 慢查的表名都是sbtest1，没有其他的表
2. 大部分的慢查都是查表最新的数据，例如select * from sbtest1 limit 1;
3. rows examined 为1，没有扫描大量的数据

问题分析

通对慢查的大致分析，SQL本身没有发现问题。那么是不是主机或者网络等有问题呢？

经过对网络和主机磁盘的IO等的分析，负载均正常，没有丢包的现象。

回到数据库本身，慢查还在，确认下慢查到底是慢在哪里。

当慢查在执行的时候，大部分的都是表现在 Sending data的状态，我们通过profiling去确认下慢查的时间分布：

从图中，我们可以看到 sending data 耗费的时间为0.945秒，基本占据了SQL执行时间的99%。

那么 sending data 是什么意思呢，我们从官方文档里面了解下。

The thread is reading and processing rows for a SELECT statement, and sending data to the client. Because operations occurring during this state tend to perform large amounts of disk access (reads), it is often the longest-running state over the lifetime of a given query.

Sending data表示在读取以及处理行数据以及发送数据到客户端，由于数据只有一行，且当时网络确认正常，那么时间就是耗费在读取和处理select的数据。

那为啥只取limit 1，而且没有where条件的SQL执行扫描一行数据会这么慢呢？

打开监控，看看有没有啥指标异常。

我们注意到数据库的 History list length 这个指标一直在升高，达到了几万。慢查的执行时间是随着 History list length 升高而变的更慢。当 History list length 一直居高不下的时候，说明了有大量的UNDO没有被purge。由于当前数据库的隔壁级别是RR，开始比较早的事务，如果还没提交，就需要通过UNDO去构建对应版本历史时，保证数据库的可重复读（跟MVCC有关）。

既然 History list length 那么高，可能是有历史事务出现异常没有提交，也有可能是一致性快照的备份。可以通过information_schema.innodb_trx 表去确认对应的事务信息。经过查询，的确发现一个事务执行了4个小时左右，没有提交，且不是备份用户。手动将该线程执行kill操作，慢查消失。

聊一下MVCC

MySQL InnoDB 支持 MVCC 多版本，可以在普通的 SELECT 时不加锁。利用多版本读取指定版本的行记录，降低加锁的次数，能极大提高数据库的并发读写能力。

Innodb 在事务的某个时刻记录下MySQL所有的活跃事务列表，保存到 read view 里面。在之后的查询中，通过比较记录的事务ID和 read view 里面的事务列表，判断记录是否可见。

Innodb 行记录

在Innodb的行结构中，还存在三个系统列，分别是 DATA_ROW_ID 、 DATA_TRX_ID 、 DATA_ROLL_PTR 。

DATA_ROW_ID
DATA_TRX_ID
DATA_ROLL_PTR

read view

read view 是在SQL语句执行之前申请的，其中RC隔离级别是每个SELECT都会申请，RR隔离级别的 read view 是事务开始之后的第一个SQL申请，之后事务内的其他SQL都使用该 read view 。

read view 中有三个变量需要重点关注：

1. low_limit_id ： 表示的是创建 read view 那一刻活跃的事务列表的最大的事务ID

2. up_limit_id ：表示的是创建 read view 那一刻活跃的事务列表的最小的事务ID

3. trx_ids ：表示的创建 read view 那一刻所有的活跃事务列表。

判断记录可见

1. 当记录的 DATA_TRX_ID 小于 read vew 的 up_limit_id ，说明该记录在创建 read view 之前就已经提交，记录可见

2. 如果记录的 DATA_TRX_ID 和事务创建者的 TRX_ID 一样，记录可见

3. 当记录的 DATA_TRX_ID 大于 read vew 的 up_limit_id ，说明该记录在创建 read view 之后进行的新建事务修改提交的，记录不可见

4. 在RR隔离级别，如果A事务在B事务创建 read view 之前开始的，那么B事务里面的SQL是不能看到A事务执行的修改的。因此还有一条规则：如果记录对应的 DATA_TRX_ID 在 read view 的 trx_ids 里面，那么该记录也是不可见的。

DATA_ROLL_PTR

UNDO日志是MVCC的重要组成部分，当一条数据被修改时，UNDO日志里面保存了记录的历史版本。当事务需要查询记录的历史版本时，可以通过UNDO日志构建特定版本的数据

每条行记录上面都有一个指针 DATA_ROLL_PTR ，指向最近的UNDO记录。同时每条UNDO记录包含一个指向前一个UNDO记录的指针，这样就构成了一条记录的所有UNDO历史的链表。当UNDO的记录还存在，那么对应的记录的历史版本就能被构建出来。

当记录对应的版本通过 DATA_TRX_ID 比对发现不可见时，通过系统列 DATA_ROLL_PTR ，找到对应的回滚段记录，继续通过上述判断记录可见的规则，进行判断，如果记录依旧不可见，继续通过回滚段查找之前的版本，直到找到对应可见的版本。

慢查问题复现

经过和业务方沟通，得知该表每天都有定时任务，会删除历史数据。大致了解到整个过程后，我们搭建模拟环境进行测试。

测试分为三个session，其中Sess1执行长事务，没有提交。Sess2对表的历史数据做清理，清理了2000万的数据。此时在Sess3执行查询，快慢情况如上图所示。 select * from sbtest1 limit 1 跟预期表现一样，为很慢。但是 select * from sbtest1 order by id desc limit 1 执行很快，这是为什么呢？

上图为主键的记录格式，在每条主键记录的前面有个删除标志位，然后是主键ID,事务ID，回滚段指针，最后是行记录。

当记录被执行删除的时候，MySQL只是将记录标记为已删除，同时更新 DATA_TRX_ID 为自己删除会话的事务ID，并没有将记录真正删除。当被删除的记录不再被其他事务需要的时候，会被 purge线程 删除。 purge线程 负责清理这些真正被删除的记录以及不再需要的UNDO日志。

回到慢查本身，我们来看看慢查的执行过程。

SQL为 select * from sbtest1 limit 1 。

1. 通过主键，扫描到ID=1的记录，根据MVCC比对，发现自己的事务ID大于记录的DATA TRX ID，匹配可见规则1，记录可见

2. 由于ID=1已经被标记为DELETED，删除记录可见

3. 由于表数据还没有全部扫描完成，未满足limit 1，继续扫描下一条记录

4. 扫描到ID=2的记录，根据MVCC比对，发现自己的事务ID大于记录的 DATA_TRX_ID ，匹配可见规则1，记录可见

5. 由于ID=2已经被标记为DELETED，删除记录可见

6. 由于表数据还没有全部扫描完成，未满足limit 1，继续扫描下一条记录

7. 重复4-6步骤，直到满足找到一条记录，或者全表扫描完成

由于被删除的记录有2000万，Innodb 需要扫描2000万的记录，才能找到符合条件的第一条记录，然后返回到MySQL的Server层。

当SQL为 select * from sbtest1 order by id desc limit 1 。

由于删除的是老数据，当从ID最大的方向开始扫描时，通过MVCC 判断可见，然后判断记录是否被标记为删除的时候，记录没有被删除，因此就可以快速返回到Server层，SQL执行效率就会很高。

总结

1. 当新的会话执行SQL的时候，如果扫描区间范围内有大量被标记为删除的记录，会导致SQL执行效率变低，通过二级索引去查询也是类似的。例如当 where c1=1 有10万match 的记录时，其中扫描方向的90%的记录都被标记为以及删除，但是还没purge，执行 where c1 =1 limit 1 一样会慢。

2. 如果一个会话没有开启自动提交，那么查到的记录就是会话开始的时候的第一个SQL执行的快照。如果查询的行记录的历史版本过多，将导致通过UNDO去构建历史版本的时间过长，对于高负载的OLTP系统来说是个灾难。

3. 尽可能的避免长事务，监控长事务的个数，进行告警，及时kill会话

4. 已经提交的事务的UNDO只有在隔离级别是RR的情况下才会被比自己早的事务使用，如果隔离级别是RC，提交的事务的UNDO会很快被purge，就不会有本文上面出现的问题。

欢迎关注我们的公众号