delete一张大表引发的一点思考

01

delete一张大表引发的一点思考

今天上班的时候接收到了一个业务方的反馈,说是一个数据库在删除表的时候报错了,我让他截给我日志看看,日志中的内容如下:

语句:delete from XXXXX_log where log_time < FROM_UNIXTIME(1563353579) limit 300000;

报错:MySqlConnection Error Lock wait timeout exceeded; try restarting transaction

从错误的日志中分析,是在删除一张表的时候出现了锁等待超时问题,系统提示"尝试重新开启事务"。

看到这个问题,第一反应是查看数据量,于是我使用explain语句查看了这个语句的执行计划,如下:

mysql> explain delete from XXXXX_log where log_time < FROM_UNIXTIME(1563353579) limit 300000;
+----+-------------+---------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
| id | select_type | table         | partitions | type | possible_keys | key  | key_len | ref  | rows     | filtered | Extra       |
+----+-------------+---------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
|  1 | DELETE      | XXXXXXXXX_log | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 47888261 |   100.00 | Using where |
+----+-------------+---------------+------------+------+---------------+------+---------+------+----------+----------+-------------+
1 row in set (0.00 sec)

可以看到type是all,而rows是4788万行,这说明这个语句走了全表扫描,没有走索引,扫面的行数大约是4788万行,所以出现了锁等待超时的问题。

于是我查看了这张表的表结构,发现它只有id字段设置了主键,而log_time字段没有设置索引,所以进行删除的时候是使用了全表扫描的方法。那么这个问题也就变得明确了,就是我们如何优化这个delete语句,让他可以快速的执行完而不影响线上的业务。

这里需要说明一下delete大表的时候带来的影响,delete一张大表的时候,如果记录数太多,则需要锁住很多数据,这个操作将占满整个事务日志,耗尽系统资源,阻塞很多小的但是很重要的查询语句。如果在主从环境下执行,则可能导致主从延迟加大。

解决这个问题方法大概有两种:

1、在delete的时候将limit后面的值设置的更小一点,每次删除一小部分内容,而且删除之后,都暂停一小会儿再做下一次删除,这样可以讲服务器上原本一次性的压力分散到一个很长的时间段中,就可以大大降低对服务器的影响,可以大大减少删除时锁的持有时间。

2、优化删除的SQL,在这个例子中,其实id是有主键的,我当时想到的是这个日志表是按照时间的顺序增长的,而id也是增长的,如果我们知道删除某一段时间的日志的SQL,可以通过查询时间和id的对应关系,将它转化为删除某一个区间内id的SQL,这样根据id进行删除,不就方便多了么。

实际操作的时候也是这么做的,我改成了先选取max(id),再计算要删到哪个id,最后删除,同时减小一次性删除的总数量,删除语句是这样的:

delete from XXXXXXX_log where id < 650919135 limit 100000;

再来查看这个SQL的执行计划,可以看到:

mysql> explain delete from XXXXXXX_log where id < 650919135 limit 100000;
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
| id | select_type | table         | partitions | type  | possible_keys | key     | key_len | ref   | rows     | filtered | Extra       |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
|  1 | DELETE      | XXXXXXXXX_log | NULL       | range | PRIMARY       | PRIMARY | 4       | const | 23394148 |   100.00 | Using where |
+----+-------------+---------------+------------+-------+---------------+---------+---------+-------+----------+----------+-------------+
1 row in set (0.00 sec)

这个时候的删除语句就已经走了主键了,这样过滤的记录数也变小了,不再是4788万行了,进行删除的时候也没有报错了。

这个问题是暂时结束了,但是可能大家对这种delete的操作没有很多概念,于是我是用线上一个已经停止使用的阿里云rds备份log库做了一个实验,查看一下这个库在一次delete操作的时候需要的时间:

mysql@rm-2zeXXXXXXXXXXi.mysql.rds.aliyuncs.com:log_bak 10:12:40>>delete from w_log where id<110000000;
Query OK, 70789994 rows affected (7 min 59.44 sec)

mysql@rm-2zeXXXXXXXXXXi.mysql.rds.aliyuncs.com:log_bak 10:23:32>>delete from w_log where id<120000000;    
Query OK, 10000000 rows affected (2 min 38.41 sec)

在一张大约一亿五千万条数据的表里面,删除七千多万条数据,阿里云rds的执行时间大约是8分钟,而删除一千万数据大约时间是2分钟40秒,这个时间只是让大家大概有个概念,如果你要一次性删除三四千万的数据,算上服务器配置,你可以预估一下操作时间,大约得三四分钟左右吧,土豪服务器请忽略,也就意味着你进行delete操作的时候,你的服务得要接受三四分钟不可用的状态。如果你接受不了,那可能得要重新评估一下这个删除操作的可用性了。

是否可以每次少量删除?

是否可以优化一下删除语句?

使用rename操作将表进行重命名,等到业务维护窗口时再进行操作?

这些问题,可能都需要考虑了。

本文分享自微信公众号 - DBA随笔(gh_acc2bbc0d447)

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

原始发表时间:2019-07-24

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

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏不想当开发的产品不是好测试

mysql 类型自动化转换问题

询问了一个朋友,他给了一个方向,mysql会自动转换类型,当你select语句的条件和数据中的type不一致的时候; 一拍大腿,是呀,之前有碰到过类似问题,如下...

14220
来自专栏ChaMd5安全团队

国际数字经济博览会安全大赛-Aliyun&Qcloud详细writeup

这次跟着Venom的师傅们一起参加了国际数字经济博览会的线下赛,谢谢师傅们带躺到第三hhh,CTF解题赛的三道云安全的题目感觉还挺经典的,拿了其中一道Aliyu...

9850
来自专栏AI科技大本营的专栏

​哪些开发问题最让程序员“头秃”?我们分析了Stack Overflow的11000个问题

自 2008 年成立以来,Stack Overflow 一直在拯救所有类型的开发人员。自那时以来,开发人员提出了数百万个关于开发领域的问题。

7220
来自专栏码客

MySql数据库表快速复制

11910
来自专栏软件测试testclass

Katalon Studio处理数据库

在日常的测试工作中需要经常要用到:数据库的处理,执行SQL语句。 Katalon Studio可以通过定制关键字的方式连接到数据库,并执行SQL语句。 基本实现...

8950
来自专栏SAS程序分享号号号

SAS- 100种数据compare的方式,你在用哪种?

小编是医药行业的,所以小编的推送涉及的知识也多为SAS在临床研究中的运行及SAS数据清洗等相关的程序,在临床试验中,很多SOP相对完善的公司,出于对数据质量的把...

9230
来自专栏Python乱炖

程序员:你见过哪些要命的奇葩代码?

有次code review,看到有个同事把一个变量命名为dayAfterYesterday,觉得莫名的喜感,就在下面批注了“哈哈哈。。。”

3910
来自专栏杨建荣的学习笔记

MyCAT让人诟病的配置文件,说说破局的思路

最近在做MySQL分布式环境的统筹管理,目前碰到的痛点是对于集群的配置管理目前是松散的,几套环境还能忍受,如果环境多了之后还是很容易凌乱,所以我们需要一套机制...

12720
来自专栏SAS程序分享号号号

SAS-一条群消息引发的思考

看上图,某群友提出将table1的结构转换成table2的结构,这个是一个很明显的转置的操作,也并不特别明显,但是还是很明显的。

5920
来自专栏MySQL技术

常用SQL语句分享

日常工作或学习过程中,我们可能会经常用到某些SQL,建议大家多多整理记录下这些常用的SQL,这样后续用到会方便很多。笔者在工作及学习过程中也整理了下个人常用的S...

7520

扫码关注云+社区

领取腾讯云代金券

年度创作总结 领取年终奖励