几种常见的MySQL/PolarDB-MySQL回收表空间方法对比

DMS + ALTERTABLE 无钥匙更改最稳定。

运营前备份，并在非高峰时段进行。

面试官：使用 MySQL 时，你们是怎样做大表清理的？

上次我帮上海的一个电商客户清理了一张大桌子。
他们的 test1 表接近 5 00G 并且卡得很惨。
下面我们就以不同的方式谈谈他们遇到的坑和经历。

1 . SQL删除（DELETE语句）是最直接的方法，但如果使用不当可能会致命。
第一次运行的时候，直接写了DELETE FROM test1 WHERE id < 1>后来我了解到：
必须指定 ORDER BY ID。
否则MySQL就得扫描全表，直接消耗CPU。
使用 ORDER BY id DESC 速度更快。

删除后，OPTIMIZE TABLE将被锁定3 小时，在此期间网站菜单将无法选择。
最终我改在半夜运行它，但第二天客户服务抱怨系统出现故障。

请务必备份您的数据。
我有一个二级索引，它不添加 ON DUPLICATE KEY。
结果，在DELETE过程中，其他相关表中的数据也被删除。

2 .目前系统采用逻辑删除，del_flag=1 的记录不被数据库检索。
但这里有一个问题：
归档时，需要额外检查del_flag，这使查询计划变得复杂。
我使用触发器自动创建归档表，但是当我晚上运行归档脚本时主数据库超时。

我们有一个业务需求，要恢复误删除的数据，发现del_flag=1 的记录比正常数据多，临时表快满了。

3 分区表清理是最狠的一招，但及早规划很重要。
我们按月份划分产品，但销售人员总是在3 月2 8 日添加暂定订单，因此划分线经常会混淆。
随后改为：
分区键必须是硬的。
例如，我为订单表 YEAR (order_date) 创建了分区键。
删除旧分区 ALTER TABLE test1 DROP PARTITION p2 02 3 1 2 比删除记录快得多。

每月1 号凌晨自动删除分区，但0:00下单时出现分区不存在的Bug。
最后我手动添加了脚本。

4 要完全重置表，临时表替换方法是合适的，但风险很大。
迁移测试环境时使用此选项。

复制数据时忘记加索引，临时表直接占用了2 T磁盘。
最后使用pt-tablesync进行批量同步。

RENAME TABLE操作是原子操作，但与未找到的辅助键发生冲突，导致重命名后丢失了5 00万条数据。
然后 pt-online-schema-change使用。

5 重建表（ALTERTABLE）现在从5 .7 开始支持ONLINE DDL，但存在一些缺陷：
他们运行了在线 ALTER，CPU 上升到 3 00%。
后来我才知道原来的手表是有扳机的，更换扳机后才稳定。
官网说是在线兼容，但是没有提到触发时CPU会爆炸。

日志重放时出现网络抖动，丢失1 00条数据，最终使用binlog恢复。

6 .作为归档工具，我目前使用 Percona Toolkit 中的 pt-archiver。
它可以执行自动备份和删除，还使用gzip压缩。

一开始我把并行线程设置得太高，直接导致服务器崩溃。
切换到 4 线程后，效果非常好。

在某一时刻，存档脚本停止，备份文件包含两天的重复数据。
文件名现在带有时间戳以防止重复。

总结：
使用DELETE+ORDER BY删除少量特定数据，但不要指望空间立即被释放。

如果需要历史记录，则使用软删除，但归档时需要添加判断条件。

每月数据直接分区删除，比删除记录快1 00倍。

我使用临时表来重置表，但我需要添加索引和辅助键检查。

在线 DDL 是最好的方法，但触发器会减慢速度。

你无法轻易移动大钟。
我经历过足够多的陷阱来写操作手册……无论如何，这是可以理解的。