三分钟清空数据库大表数据

作者: | 更新日期:

上周有一个数据库一直在告警,今天看了下。

本文首发于公众号:天空的代码世界,微信号:tiankonguse
原文地址:https://mp.weixin.qq.com/s/vc-QsIJ7rcst_Ch1EG5R2A

一、背景

上周有一个数据库磁盘快满了,一直在告警。

当时比较忙,我一看磁盘使用率,还能撑一个月,便没处理。
今天想起来了,便花了几分钟来解决这个问题。

二、找到大容量表

我们都知道,一个数据库里面有很多表。
那就需要找到是哪个表把数据库弄满了。

我敲了这样一个命令,就可以清晰的找到目标了。

select TABLE_NAME, concat(round(DATA_LENGTH/1024/1024/1024, 2),'GB') as c_all_data
from information_schema.TABLES
where table_schema='my_db'
order by DATA_LENGTH desc;

原来第一个表太大了,足足有1.4T的大小。

为什么会这么大呢?
这需要从这张表的作用说起。

这张表是我做的一个辅助工具表。
我负责的一个系统有数据变更时,这个辅助工具会拉去所有机器的数据来对比数据一致性,然后将对比的结果储存在这个表里。
考虑到时流水,我也写了一个定时清理数据的工具,只保留最近一段时间的对比结果。

这个已经上线三年了,记得刚上线的一年里,看监控容量都是比较平稳的。

最近异常了,我首先猜测清理数据工具可能异常没有按照预期清理数据。
登陆相关机器一看日志,在正常允许,但是每次清理时设置了最大数量。
于是我便知道原因了。

原因系统的数据变更量比较小,这个清理工具能够把历史的数据都清理掉。
大概一年前,我们开始做短视频业务,数据更新量翻了好几倍,这就导致清理的数据没有新增的数据多了。
所以我就修改清理工具,把最大数量设置的大一点(与现在相比再翻几倍),这样清理数据的速度就会大于新增数据的速度了。

接下来就是清理历史数据了。
毕竟这么大的容量,靠那个工具慢慢删,要删半年了。
所以就面临一个问题:如何快速清空这个大表的数据。

三、快速清空大表数据

删除大表的数据有三个方法。

第一个方法是慢慢delete, 数据量少是可行,大了一般不采用这个方法。
第二个方法是drop删除表,这个看起来不错,但是由于存在事务,真正执行起来,发现也会很慢。
第三个方法是truncate清空表,这个没有事务,执行的很快。

我便是使用第三个方法来操作的,这么大的表,十几秒就删完数据了。

当然,由于操作前我不知道需要多少时间, 我并不是直接对原表truncate的,而是使用另一个投机取巧的方法。

第一步先创建一个相同表结构的临时表table_new
第二步将原始表table快速renametable_old表。
第三步将临时表table_new快速renametable表。

这样下来行云如水,完全是瞬间完成。
之后我就可以慢慢的truncate清空table_old表了。
当然也很快,只用了十几秒。

四、最后

到这里就出现一个面试题了:怎么快速删除清空一个表的数据?

第一个方法虽然很笨,但是可以解决问题;比如慢慢delete或者直接删除表。

第二个方法不错,但是不是最优的,比如truncate清空,但是时间不可控。

第三个方法是最优的不好想,经过引导最终也能答上来。
比如先rename走,这样就不影响服务了,之后慢慢清理。

一道好的面试题就应该像这道题,有多种方法,可以逐渐引导,从而找到最优答案。

-EOF-

本文公众号:天空的代码世界
个人微信号:tiankonguse
QQ算法群:165531769(不止算法)
知识星球:不止算法

本文首发于公众号:天空的代码世界,微信号:tiankonguse
如果你想留言,可以在微信里面打开下面的地址进行留言。如果下面没有地址,可以关注公众号留言。
原文地址:https://mp.weixin.qq.com/s/vc-QsIJ7rcst_Ch1EG5R2A

点击查看评论

关注公众号,接收最新消息

关注小密圈,学习各种算法

tiankonguse +
穿越