首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >delete大表时发生的一些有趣的现象

delete大表时发生的一些有趣的现象

作者头像
AsiaYe
发布2019-11-22 10:58:30
9140
发布2019-11-22 10:58:30
举报
文章被收录于专栏:DBA随笔DBA随笔
20

Nov,2019

今天周三,这两天回家比较早,早点休息了,感觉身体还是最重要的。人上年龄了感觉记忆力衰退特别明显,经常干某个事情干到一般就忘了,希望早点休息能够让我返老还童,今天早点睡了,晚安同志们。。。

delete大表时发生的一些有趣的现象

最近,由于某个业务需求,要删除某个表中30天以前的数据,该表的数据已经保存了三四年了,整个表的大小也已经有110G了,说实话,这种的我之前都没有处理过。

首先这个问题可以有一些解决方法,比如通过rename语句,将表重命名,然后创建新表,再把最近一个月的数据导入到新表中去,这样,操作时间上会更短。但是需要业务方接受rename操作和创建新表操作的短暂延迟,预估在2s以内。

当然,还有第二种办法,就是笨一点,写个存储过程或者写个脚本去删除,花费的时间长一些,但是这种方法并不会真正的释放表的空间,而只是清除表中的记录,要想释放空间,可能还需要做optimize table或者alter table 的操作,这个优化的操作相当耗时,所以要在维护窗口做。

由于这样那样的原因吧,暂时选择了先delete的方法,每次删除表中的1000条数据,然后sleep 1s,接着删除,在删除到3000w条左右的时候,删除的操作就比较耗时了,于是我手动删除了一下,看看删除的效果,在删除表的过程中,看到了几个有意思的现象:

1、where条件不一样,花费的时间有显著差距。

mysql 09:42:41>>delete from user_XXXX where id < 35199000;

Query OK, 1000 rows affected (3.22 sec)

mysql 09:42:59>>select min(id) from user_XXXX;

+----------+

| min(id) |

+----------+

| 35199000 |

+----------+

1 row in set (2.29 sec)

mysql 09:44:54>>delete from user_XXXX where id >=35199000 and id < 35200000;

Query OK, 1000 rows affected (0.54 sec)

可以看到,表中的数据大概是到3000w左右了,接下来进行两个操作:

a、先进行一个delete的操作,删除了1000条记录,其中where条件是id<35199000,删除总共花费的时间是3.22s

b、查看当前的最小id值,发现是35199000,然后再删除1000条数据,这个时候,where条件里写上一个范围,指定最小和最大值,从结果中不难看出,也删除了1000条数据,但是花费的时间是0.54s,这个时间就很短了,比上面的少了6倍。

2、不同记录处,删除同样条数的记录,响应的时间长短不一致。例如在刚开始删除的时候,id为0~1000的记录删除,可能只需要10ms不到的时间,越往后时间越长,看看我截取的结果:

mysql 09:44:54>>delete from user_XXXX where id >=35199000 and id < 35200000;

Query OK, 1000 rows affected (0.54 sec)

mysql 09:45:54>>delete from user_XXXX where id >=35200000 and id < 35201000;

Query OK, 1000 rows affected (0.68 sec)

mysql 13:09:07>>delete from user_XXXX where id >=51031000 and id < 51032000;

Query OK, 1000 rows affected (5.52 sec)

mysql 13:10:35>>delete from user_XXXX where id >=51031000 and id < 51033000;

Query OK, 1000 rows affected (5.70 sec)

也就是说,类似的语句,在主键id是3000w左右的时候运行时长和id是5000w左右的时候运行时长是不一样的,而且差距有10倍之多。而且这个时间,多次查询,基本保持一致。

3、select min(id) 语法执行的时候,执行时间的差距非常大,及时id本身是主键。

mysql 13:45:14>>select max(id) from user_XXXX;

+-----------+

| max(id) |

+-----------+

| 322415101 |

+-----------+

1 row in set (0.00 sec)

mysql 13:45:21>>select min(id) from user_XXXX;

+----------+

| min(id) |

+----------+

| 51033000 |

+----------+

1 row in set (2 min 9.08 sec)

这几个现象有些能够解释,有些我也没有想清楚。这里我抛出几个问题,心中大概有个答案,但是还有一些细节没有想清楚,等日后想清楚了再来回答。

求min(id)的时候,如果id本身是主键,不应该直接从聚集索引叶子节点的最左侧拿到相关记录么?为什么这个操作也能这么慢?innodb中查询优化器对于这种查询究竟会怎么处理?关于大表的数据删除,有没有更好的处理办法?(上百G的表),如果大家有好的办法,还请后台留言,不吝赐教。。。

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2019-11-20,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 DBA随笔 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档