介绍
你好。 我是ningenMe，网络开发人员。
正如标题所说，我的故事是物理删除MySQL中300亿条记录的故事。
我对此产生了兴趣，所以我决定做一个提醒（说明）。
主页 - 警报
我使用和维护的批处理服务器有一个定期进程，每天从 MySQL 收集上个月的数据。
通常这个过程会在1小时左右完成，但这次足足有7、8个小时才完成，而且警报还没有停止弹出……
寻找原因
我尝试重新启动该进程并查看日志，但没有发现任何问题。
查询已正确索引。 但当我思考问题出在哪里时，我意识到数据库的大小相当大。
hoge_table | 350'000'000 |350 亿条记录。 索引似乎工作正常，只是非常慢。
每月所需的数据收集量约为 12 条记录。 看起来select命令花了很长时间，事务很长时间没有执行。
DB
它本质上是一个每天增加约 400 个条目的表。 该数据库原本应该只收集上个月的数据，因此预计它能够承受这个数据量，但不幸的是，不包括旋转操作。
这个数据库不是我开发的。 我从另一位开发人员那里接手了它，所以它仍然感觉像是技术债务。
有一天，每天插入的数据量变得越来越大，最终达到了极限。 假设在处理如此大量的数据时，有必要将它们分开，但不幸的是，这并没有做到。
然后我就开始行动了。
更正
减小数据库本身的大小并减少处理它的时间比改变逻辑本身更为合理。
如果你删除300亿条记录，情况应该会发生很大的变化，所以我决定这样做……呃，我认为这一定可以。
动作1
准备好可靠的备份后，我终于开始发送请求。
「发送请求」
DELETE FROM hoge_table WHERE create_time <= 'YYYY-MM-DD HH:MM:SS';“ ...”
“ ...”
“嗯……没有回答。 也许这个过程需要很长时间？” — 我想，但为了以防万一，我查看了 grafana，发现磁盘负载增长得非常快。
“危险。”我又想了想，立刻停止了这个请求。
动作2
分析完所有内容后，我意识到数据量太大，无法一次性删除所有内容。
我决定编写一个可以删除大约 1 条记录的脚本并启动它。
「我执行剧本」
“现在这肯定会起作用，”我想。
动作3
第二种方法有效，但结果非常耗费劳动力。
如果要小心翼翼地做好每件事，没有不必要的紧张，大约需要两周的时间。 但这个场景仍然不能满足业务需求，所以我们不得不放弃它。
所以我决定这样做：
复制表并重命名
从上一步中，我意识到删除如此大量的数据会产生同样大的负载。 因此，我决定使用插入从头开始创建一个新表，并将要删除的数据移动到其中。
| hoge_table | 350'000'000|
| tmp_hoge_table | 50'000'000|如果将新表设为与上面相同的大小，数据处理速度也应该会快 1/7。
创建表并重命名后，我开始将其用作主表。 现在，如果我删除包含 300 亿条记录的表，一切都应该没问题。
我发现截断或删除产生的开销比删除更少，并决定使用此方法。
执行
「发送请求」
INSERT INTO tmp_hoge_table SELECT FROM hoge_table create_time > 'YYYY-MM-DD HH:MM:SS';
“ ...”
“ ...”
“嗯……？”
动作4
本以为之前的想法可行，但是发送插入请求后，出现了多个错误。 MySQL 并不宽容。
我已经很累了，我开始想我不想再这样做了。
我坐下来想了想，意识到也许一次插入查询太多了......
我尝试发送一个插入请求，获取数据库应在 1 天内处理的数据量。 发生了！
好吧，之后我们继续发送相同数量数据的请求。 由于我们需要删除一个月的数据，因此我们重复此操作大约 35 次。
重命名表
幸运之神眷顾了我：一切都很顺利。
警报失踪
批处理速度有所提高。
以前这个过程需要大约一个小时，现在大约需要两分钟。
当我确定所有问题都解决后，我删除了300亿条记录。 我删除了桌子，感觉重获新生。
概括
我意识到批处理中缺少旋转处理，这是主要问题。 这种架构错误会导致时间的浪费。
从数据库中删除记录时，您是否考虑过数据复制期间的负载？ 我们不要让 MySQL 超载。
精通数据库的人肯定不会遇到这样的问题。 对于你们其他人，我希望这篇文章有用。
谢谢阅读！
如果您告诉我们您是否喜欢这篇文章，翻译是否清晰，对您是否有用，我们将非常高兴？
来源： habr.com