磁盘空间不足时：关于挑战 MySQL 数据库清理的教训

Source: Dev.to

When Disk Space Is Not Enough: A Lesson in Challenging MySQL Database Cleansing

数据库维护很少是轻松的事，尤其是面对高并发事务数据时。最近，我们接到任务，需要清理一个已经膨胀到 220 GB 的 MySQL 数据库。任务很简单：在四个表中删除 2018‑2023 年的数据，以回收空间并提升性能。

然而，正如每个开发者都知道的，“简单”任务往往隐藏着复杂的陷阱。下面我们将分享在存储限制和技术难题中如何一步步完成这项工作的经验。

挑战：房间里的 200 GB “大象”

在总计 220 GB 的大小中，单个表占用了 200 GB。该表充斥着事务记录——以及真正的罪魁祸首——BLOB 附件。我们的目标是仅保留 2024 年及之后的数据。

第1阶段：“克隆并交换”策略（理想方案）

1. 克隆 – 创建现有表的副本并使用新名称。
2. 过滤 – 仅将2024年至今的数据插入新表。
3. 交换 – 重命名原始表并用新表替换它们。
4. 清理 – 验证完成后删除旧表。

优点： 安全；原始数据保持不变，作为检查点。

在开发环境中的结果： 完美。尽管备份和恢复需要时间，但效果完全符合预期。

生产现实检查

当我们迁移到生产环境时，遇到了阻碍。生产服务器的 500 GB 总容量 中，只剩下 100 GB 可用空间。将一个 200 GB 的表克隆到只有 100 GB 的空间中在数学上是不可能的。我们需要一个 Plan B。

第 2 阶段：“分块删除”策略（安全的赌注？）

我们转向了更细粒度的方法：直接从现有表中删除数据。为防止查询运行时间过长或锁表，我们遵循了以下步骤：

• 按年份范围逐年删除数据（2018、2019 等）。
• 使用 Bash 脚本 通过 LIMIT 子句自动化分块删除，保持系统响应，避免数据库“卡死”。

陷阱：OPTIMIZE TABLE 悖论

删除操作已成功执行，但 MySQL 在执行 DELETE 后不会自动缩小文件大小（因为“高水位标记”和数据碎片化）。要真正回收 100 GB 以上的空间，需要运行 OPTIMIZE TABLE。

关键失误： OPTIMIZE TABLE 会创建表的临时副本。即使只删除了一半的数据，该操作仍然需要足够的空闲空间来重新写入整张表。我们原本的 100 GB 空闲空间再次成为瓶颈。我们陷入了僵局。

第 3阶段：“离线手术”（最终方案）

1. 导出 – 导出巨大的 200 GB 表并将其移动到拥有充足存储的备用服务器。
2. 清理与压缩 – 在备用服务器上，删除不需要的行并运行 OPTIMIZE TABLE。
3. 结果 – 优化后的表大小显著降至 100 GB 以下。
4. 导入 – 将已压缩的表以新名称传回生产环境。
5. 最终交换 – 由于新表已能适应 100 GB 的空闲空间，我们将其与原表交换，并删除了旧的 200 GB 巨表。

对我们而言的关键要点

• 空闲空间是你的最佳伙伴 – 在规划迁移前务必检查 df -h。大多数 MySQL 维护操作（如 OPTIMIZE 或 ALTER）至少需要相当于最大表大小 2× 的空间。
• 谨慎使用优化 – 记住 OPTIMIZE TABLE 并非“免费”操作；它需要等同于表当前大小的临时存储。
• 分块操作救急 – 小批量删除数据可以避免长时间锁定，保持数据库响应。
• 异地处理可救命 – 当本地存储不足时，将繁重的转换任务转移到更大的环境中，可在不中断的情况下解除瓶颈。

作者：Taufiq Abdullah。

An “in‑place” fix; it is a rebuild.

**Think Outside the Box:**  
If your local disk is full, use a secondary staging server to process the data before bringing it back home.

Database cleansing is as much about managing infrastructure as it is about writing SQL. Have you ever faced a storage “deadlock” like this? I’d love to hear how you handled it in the comments!