在 MySQL 上删除了三亿条记录的故事

首先

or

首要的是

你好。我是一个从事Web工程师职业的 ningenMe。
如标题所述，关于在MySQL中删除三亿条记录的经历。
因为遇到了一些问题，所以写下来作为备忘录。

开始是警报。

起初是一个警报。
在我负责的批处理服务器上，我进行定期处理，每天一次从mysql中选择最近一个月的数据。

通常情况下，这个处理应该在大约1小时内完成，但那天已经过去了7、8个小时仍未结束，警报声不停地响起来……。

寻求的动机

暫時重試了一下，查看了日誌，但好像沒有太多問題。查詢也正確地使用了索引。於是我想，可能是因為數據庫的容量變得相當大才導致這樣。

hoge_table | 350'000'000 |

有3亿5千条记录。由于索引起作用，所以可能只是普通地慢。
所需获取的数据约为一月份的12’000’000条。这个select查询花费时间长，并导致事务失败。

数据库

每天大约有约40万条记录插入到这个表中，导致记录不断增加。
根据需求，只需要获取最近一个月的数据，所以只要保留这个时间范围的数据即可，但可惜的是没有进行轮换处理。

虽然这不是我开发的，而是继承过来的，但在那个时候没有收到任何指示，就像得到了技术债的通行证一样。

当每天的插入操作导致数据变得越来越庞大，最终达到了极限时，轮到我出场了。

如果这个大小，最好是分割一下，但很遗憾，没有做到。

进行修复

我们决定采取的方针是，在修改逻辑之前，先轻量化数据库并观察情况，这样工作量可能会少一些。考虑到删除3亿条记录可能会带来相当大的变化，于是决定执行删除操作…… 当时我还以为会顺利进行……。

Option 1: 方案一

备份完整，我充满自信地开始发送查询。
“开始发送查询啦”

DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';

「…」
「…」

感觉没什么反应，想着可能需要更多时间，一边观察着数据库的grafana，然后发现磁盘繁忙状态大幅上升。

“呀呀呀呀呀呀呀呀呀” (yā yā yā yā yā yā yā yā yā)

暫時停止查詢。

对应的第二个选择。

我意识到一次删除的数量太多了。考虑到数据库的复制时间，最好频繁进行删除。
我决定编写一个能够每次删除约 1,000,000 条记录的脚本并进行处理。

“我要开始执行了。”

我以为这样会成功。。。

第三种处理方法

第二种处理方法到底放在哪里呢……。虽然处理本身很顺利，但是发现需要花费很长时间。
如果要仔细地做而不给系统增加太大负担，大约需要两周的时间。但是这样无法满足服务要求，只好不得已中断。

接着剂的方案为第三个方案。

复制表格并重命名

删除操作的负荷太大，可能不是一个好选择。所以我决定转而使用插入操作，从零开始创建所需的数据表。

| hoge_table     | 350'000'000|
| tmp_hoge_table |  50'000'000|

只要在记录数量上达到上述状态，插入操作应该比数据处理快大约1/7。

创建表后，将其重命名为主表，并且删除当前的三亿条记录表应该就可以了。由于我知道truncate和drop与delete相比负载较低，所以选择了这种策略。

执行

“我要进行查询啦”

INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';

「…」
「…」
「哦…？」

第4种方案

虽然我认为事情会顺利进行，但在插入完成后却无限地遇到了错误。我害怕MySQL。
我已经充满了不想再进行生产工作的情绪。

嗯，仔细想想，大概也可以说这可能是插入得太多次了吧。总之，只试着插入一天的份量就成功了。

“哇！”

接下来请仔细地逐日插入，只需要一个选项。需要一个月的话，就对应35次一天的数量。

更改表格的名称

到这儿非常顺利，太好了。

警报，停止

批处理的速度也得到了恢复。
之前花了一个小时，现在在大约2分钟左右就能完成了（什么？）。

看到问题得到解决，我确认了，所以我删除了三亿条记录的表。
这是我第一次对DROP TABLE有如此清爽的感觉。

总结。

我突然意识到，因为别人忘记了批处理的轮换处理，所以我陷入了这么大的困境。
细微的设计不周之处会在运作中显现出来，夺去了我们不少工时啊……。

详细了解数据库

当您删除数据时，是否会考虑到复制负载等因素？
让我们让MySQL更友好一些。

嗯，我觉得对于了解这次内容的人来说，完全不会有困难的地方。
没有知识的话可能会觉得很难的嘛。

我曾经在Cassandra数据库中遇到过删除操作太多而导致服务器崩溃的情况，所以我在想是否有类似的情况发生。
如果我再学习一下的话，可能会将其整理成一篇文章。

最后

尽管看起来轻松，但实际上我们在团队中非常谨慎地进行双重检查来推进工作。生产数据库真的太可怕了。

拜拜了。