MySQL大规模删除导致慢查询实例分析

分类：编程技术时间：2024-02-20 15:43 浏览：0 评论：0

本文将详细讲解MySQL大规模删除导致慢查询的实例分析。小编觉得还是比较实用的，所以分享给大家，作为参考。希望您读完本文后有所收获。

1.背景

监控系统收到大量慢查询告警。商家还反映查询速度非常慢。然后我打开电脑确认查询很慢。检查原因。

2.现象描述

经过对平台慢查的分析，我们发现慢查有以下几个特点：

慢查询的表名是所有sbtest1，并且没有其他表；

慢查询大多查询表中的最新数据，如select * from sbtest1 limit 1；

rows Examination为1 ，且大量数据未扫描。

3.问题分析

通过粗略分析慢查询，没有发现SQL本身有问题。那么是主机或者网络有问题吗？

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

回到数据库本身，慢检查依然存在。确认慢检查在哪里。

执行慢速检查时，大部分处于正在发送数据的状态。我们通过profiling来确认慢检查的时间分布：

从图中我们可以看到，发送数据耗时0.945秒，基本上占了SQL的99%执行时间处理时间。

那么发送数据是什么意思呢？我们通过官方文档来了解一下。

线程正在读取和处理 SELECT 语句的行，并将数据发送到客户端。由于在此状态期间发生的操作往往会执行大量磁盘访问（读取），因此它通常是给定生命周期中运行时间最长的状态。n 查询。

发送数据代表读取并处理行数据并将数据发送给客户端。由于只有一行数据，且当时确认网络正常，所以时间花在读取和处理select数据上。

那为什么只用limit 1、没有where条件的SQL扫描一行数据那么慢呢？

打开监控，看看是否有异常指标。

我们注意到数据库的历史列表长度指标一直在上升，达到了数万条。随着历史列表长度的增加，慢速搜索的执行时间会变慢。当History列表长度仍然很高时，意味着还有大量的UNDO没有被清除。由于当前数据库的下一级是RR，如果较早的事务尚未提交，则需要使用UNDO构建相应的版本历史，以保证数据库的可重复读取（与MVCC相关）。

罪恶如果History列表长度如此之大，可能是历史事务发生异常而没有提交，也可能是一致快照的备份。可以通过information_schema.innodb_trx表确认对应的交易信息。经过查询，确实发现有一笔交易执行了4个小时左右，没有提交，且不是备份用户。手动杀死线程，慢检查就会消失。

3.1 说说MVCC

MySQL InnoDB支持多个版本的MVCC，并且普通SELECT时不能加锁。使用多个版本读取指定版本的行记录，减少了锁的数量，可以大大提高数据库的并发读写能力。

Innodb记录了事务中某一时刻MySQL的所有活动事务列表，并保存在读视图中。后续查询时通过com判断该记录是否可见将记录的事务ID与读取视图中的事务列表进行配对。

3.1.1 Innodb行记录

在Innodb行结构中，共有三个系统列，分别是DATA_ROW_ID、DATA_TRX_ID和DATA_ROLL_PTR。

DATA_ROW_ID：如果表没有显示定义的主键，则MySQL本身生成的ROW_ID为48位，否则代表用户定义的主键值；

DATA_TRX_ID：表示该记录的事务ID。如果是二级索引，只保存page中的trx_id；

DATA_ROLL_PTR：指向对应回滚段的指针。

3.1.2 读视图

读视图在SQL语句执行之前应用。 RC 隔离级别应用于每个 SELECT，并且应用 RR 隔离级别。该级别的读视图是事务开始后的第一个SQL请求，然后事务内的其他SQL都使用这个读视图。

rea中共有三个变量需要重点关注的d视图：

low_limit_id：表示创建读视图那一刻活动事务列表的最大事务ID；

up_limit_id：表示创建读视图时活跃事务列表的最小事务ID；

trx_ids：表示创建读视图时所有活跃事务列表被建造。

3.1.3 判断记录是否可见

当记录的DATA_TRX_ID小于read vew的up_limit_id时，表示该记录已在创建读取视图之前提交。该记录可见；

如果该记录的DATA_TRX_ID与交易创建者的TRX_ID相同，则该记录可见；

当该记录的DATA_TRX_ID大于读视图的up_limit_id，表示创建读视图后处理该记录。新创建的事务被修改并提交，记录为invisible;

在RR隔离级别下，如果事务A在事务B创建读视图之前启动，那么事务B中的SQL就看不到事务A的执行修改。因此，还有另一个规则：如果该记录对应的DATA_TRX_ID在读视图的trx_ids中，那么该记录也是不可见的。

3.1.4 DATA_ROLL_PTR

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

每行记录都有一个指针DATA_ROLL_PTR，指向最近的UNDO记录。同时，每条UNDO记录都包含一个指向前一条UNDO记录的指针，从而形成一条记录的所有UNDO历史的链表。当UNDO记录仍然存在时，重新对应历史版本可以构建绳索。

当通过DATA_TRX_ID比较发现该记录对应的版本不可见时，通过系统列DATA_ROLL_PTR找到对应的回滚段记录，并根据上述判断该记录是否为的规则继续判断可见的。如果该记录仍然不可见，则继续通过回滚段查找之前的版本，直到找到对应的可见版本。

3.2慢查问题再次出现

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

测试分为三个会话，其中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线程负责清理这些真正删除的记录和不再需要的UNDO日志。

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

SQL 是 select * fROM sbtest1 限制 1。

通过主键，扫描ID=1的记录。根据MVCC对比发现其事务ID大于该记录的DATA_TRX_ID。符合可见性规则1，记录可见；

因为ID=1已经被标记为DELETED，并且删除的记录是可见的；

因为还没有扫描完所有表数据，没有满足限制1 ，继续扫描下一条记录；

扫描到ID=2的记录，根据MVCC比对，发现其事务ID大于该记录的DATA_TRX_ID，符合可见性规则1，并且该记录可见；

由于ID=2已被标记为DELETED，所以删除的记录可见；

由于还没有扫描完所有表数据，不限制1满足，继续扫描下一条记录；

重复步骤4-6，直到找到一条记录或找到整个表。扫描完成。

既然有2000万n删除的记录，Innodb需要扫描2000万条记录，找到第一条符合条件的记录，然后返回给MySQL的Server层。