背景

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

现象描述

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

慢查的表名都是sbtest1，没有其他的表
大部分的慢查都是查表最新的数据，例如select * from sbtest1 limit 1;
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: 如果表没有显示定义主键，则采用MySQL自己生成的ROW_ID，为48-bit，否则表示的是用户自定义的主键值。
DATA_TRX_ID：表示这条记录的事务ID。如果是二级索引，只在page里面保存trx_id。
DATA_ROLL_PTR: 指向对应的回滚段的指针。

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那一刻所有的活跃事务列表。

判断记录可见

当记录的DATA_TRX_ID小于read vew的up_limit_id，说明该记录在创建read view之前就已经提交，记录可见
如果记录的DATA_TRX_ID和事务创建者的TRX_ID一样，记录可见
当记录的DATA_TRX_ID大于read vew的up_limit_id，说明该记录在创建read view之后进行的新建事务修改提交的，记录不可见
在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。

通过主键，扫描到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 limit 1。

由于删除的是老数据，当从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，就不会有本文上面出现的问题。

欢迎关注我们的公众号