专栏首页数据和云详述一次大量删除导致MySQL慢查的过程

详述一次大量删除导致MySQL慢查的过程

墨墨导读:本文记录一次大量删除导致MySQL慢查的分析,大家有没有遇到过这种问题?

一、背景 监控上收到了大量慢查的告警,业务也反馈查询很慢,随即打开电脑确认慢查的原因。 二、现象描述

  • 慢查的表名都是 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 操作,慢查消失。 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 日志构建特定版本的数据。

每条行记录上面都有一个指针 DATA_ROLL_PTR,指向最近的 UNDO 记录。同时每条 UNDO 记录包含一个指向前一个 UNDO 记录的指针,这样就构成了一条记录的所有 UNDO 历史的链表。当 UNDO 的记录还存在,那么对应的记录的历史版本就能被构建出来。 当记录对应的版本通过 DATA_TRX_ID 比对发现不可见时,通过系统列 DATA_ROLL_PTR,找到对应的回滚段记录,继续通过上述判断记录可见的规则,进行判断,如果记录依旧不可见,继续通过回滚段查找之前的版本,直到找到对应可见的版本。

3.2 慢查问题复现

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

测试分为三个 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 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,就不会有本文上面出现的问题。

出处:有赞coder(youzan_coder)

编辑:尹文敏

本文分享自微信公众号 - 数据和云(OraNews)

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2019-06-25

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • 前端如何单枪匹马实现小程序页面级版本控制

    小程序冷启动时,如果发现有新版本,将会异步下载新版本的代码包,并同时用客户端本地的包进行启动,即新版本的小程序需要等下一次冷启动才会应用上。

    腾讯云serverless团队
  • count(1) 比 count(*) 效率高么?

    MySQL count(1) 真的比 count(*) 快么? 反正同事们都是这么说的,我也姑且觉得对吧,那么没有自己研究一下究竟?如果我告诉你他们一样,你信么...

    JAVA葵花宝典
  • 数据备份及恢复(mongodump/mongorestore)

    1.mongodump创建高保真的BSON文件,mongorestore可以用其恢复数据库。对于小型数据库的备份和恢复,这两个工具非常简单和高效,但对于大型数据...

    拓荒者
  • 手把手教你MySQL“误操作”回魂术

    误操作数据库的事情,估计不少开发人员都可能会遇到。毕竟常在河边走,哪有不湿鞋的呢?

    业余草
  • Mongo的备份和恢复(mongodump 和mongorestore )

    1、mongorestore恢复数据默认是追加,如打算先删除后导入,可以加上--drop参数,不过添加--drop参数后,会将数据库数据清空后再导入,如果数据库...

    拓荒者
  • MongoDB 备份与还原 mongodump、mongorestore

    ​ mongodump 是 MongoDB 官方提供的备份工具,它可以从 MongoDB 数据库读取数据,并生成 BSON 文件,mongodump 适合用于备...

    拓荒者
  • 备份(mongodump)和恢复(mongorestore)

    MongoDB提供了备份和恢复的功能,分别是MongoDB下载目录下的mongodump.exe和mongorestore.exe文件  备份数据使用下面的命令...

    拓荒者
  • MySQL的高并发处理技术MVCC

    最近五一放假,除了带小孩到处转转外,还看了几页《高性能MySQL》。另外家里还有一本《高可用MySQL》,这都是以前在 CSDN 写作时送的书。前前后后大概 4...

    业余草
  • 什么是MongoDB?简介、架构、功能和示例

    什么是MongoDB?MongoDB是一个面向文档的NoSQL数据库,用于大容量数据存储。MongoDB是2000年代中期出现的一个数据库,属于NoSQL数据库...

    MongoDB中文社区
  • linux中的 tar命令的 -C 参数,以及其它一些参数

    复制源:http://www.cnblogs.com/li-hao/archive/2011/10/03/2198480.html tar命令的-C参数  ...

    拓荒者

扫码关注云+社区

领取腾讯云代金券