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

落日映苍穹つ 2022-12-30 07:37 154阅读 0赞

导读：监控上收到了大量慢查的告警，业务也反馈查询很慢，本文记录整个慢查的原因，以及解决方案。

**一、背景**

--------------------

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

**二、现象描述**

--------------------

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

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

**三、问题分析**

--------------------

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

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

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

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

![6f6339db714396d78cb1b9534bf71576.png][]

从图中，我们可以看到 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 操作，慢查消失。

**3.1 聊一下 MVCC**

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

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

**3.1.1 Innodb 行记录**

在 Innodb 的行结构中，还存在三个系统列，分别是 DATA\_ROW\_ID、DATA\_TRX\_ID、DATA\_ROLL\_PTR。

*  DATA\_ROW\_ID: 如果表没有显示定义主键，则采用 MySQL 自己生成的 ROW\_ID，为 48-bit，否则表示的是用户自定义的主键值；
 *  DATA\_TRX\_ID：表示这条记录的事务 ID。如果是二级索引，只在 page 里面保存 trx\_id；
 *  DATA\_ROLL\_PTR: 指向对应的回滚段的指针。

**3.1.2 read view**

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

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

*  low\_limit\_id：表示的是创建 read view 那一刻活跃的事务列表的最大的事务 ID；
 *  up\_limit\_id：表示的是创建 read view 那一刻活跃的事务列表的最小的事务 ID；
 *  trx\_ids：表示的创建 read view 那一刻所有的活跃事务列表。

**3.1.3 判断记录可见**

*  当记录的 DATA\_TRX\_ID 小于 read vew 的 up\_limit\_id，说明该记录在创建 read view 之前就已经提交，记录可见；
 *  如果记录的 DATA\_TRX\_ID 和事务创建者的 TRX\_ID 一样，记录可见；
 *  当记录的 DATA\_TRX\_ID 大于 read view 的 up\_limit\_id，说明该记录在创建 read view 之后进行的新建事务修改提交的，记录不可见；
 *  在 RR 隔离级别，如果 A 事务在 B 事务创建 read view 之前开始的，那么 B 事务里面的 SQL 是不能看到 A 事务执行的修改的。因此还有一条规则：如果记录对应的 DATA\_TRX\_ID 在 read view 的 trx\_ids 里面，那么该记录也是不可见的。

**3.1.4 DATA\_ROLL\_PTR**

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

![0557afbed6c5d3eaccf6308e69d6f313.png][]

![fe955b91aa35fcbd9c9d8347d0fbe41e.png][]

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

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

**3.2 慢查问题复现**

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

![47d9f2620007132d3bb74d95713db8d8.png][]

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

![ea444a3122326a9ef11b0b95b2371056.png][]

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

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

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

SQL 为 select \* from sbtest1 limit1。

*  通过主键，扫描到 ID=1 的记录，根据 MVCC 比对，发现自己的事务 ID 大于记录的 DATA\_TRX\_ID，匹配可见规则 1，记录可见；
 *  由于 ID=1 已经被标记为 DELETED，删除记录可见；
 *  由于表数据还没有全部扫描完成，未满足 limit 1，继续扫描下一条记录；
 *  扫描到 ID=2 的记录，根据 MVCC 比对，发现自己的事务 ID 大于记录的 DATA\_TRX\_ID，匹配可见规则 1，记录可见；
 *  由于 ID=2 已经被标记为 DELETED，删除记录可见；
 *  由于表数据还没有全部扫描完成，未满足 limit 1，继续扫描下一条记录；
 *  重复 4-6 步骤，直到满足找到一条记录，或者全表扫描完成。

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

当 SQL 为 select \* from sbtest1 order by id desc limit1。

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

**四、总结**

--------------------

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

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

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

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

Q&A

> Q1：慢的原因是遍历很多数据，因为多数判断为删除了吗？和History那个指标高没有直接关系，指标高只是一个表现而已。A1：是的，History高还会导致有些SQL在构建一致快照的时候大量遍历UNDO而变慢，本案例的SQL是因为要遍历很多delete marked 的数据变慢的。Q2：想咨询下，文中写的这种场景，有删除大量数据，并且也有查询的话，该怎么处理呢？A2：查询可以采用小事务，避免长时间的查询，及时终止长时间的异常事务。

[6f6339db714396d78cb1b9534bf71576.png]: /images/20221120/df741c60cd6a4684a7318fd3ed54aaa7.png
[0557afbed6c5d3eaccf6308e69d6f313.png]: https://img-blog.csdnimg.cn/img_convert/0557afbed6c5d3eaccf6308e69d6f313.png
[fe955b91aa35fcbd9c9d8347d0fbe41e.png]: https://img-blog.csdnimg.cn/img_convert/fe955b91aa35fcbd9c9d8347d0fbe41e.png
[47d9f2620007132d3bb74d95713db8d8.png]: https://img-blog.csdnimg.cn/img_convert/47d9f2620007132d3bb74d95713db8d8.png
[ea444a3122326a9ef11b0b95b2371056.png]: https://img-blog.csdnimg.cn/img_convert/ea444a3122326a9ef11b0b95b2371056.png