删除MySQL表数百万行后如何高效回收磁盘空间？

一、分区表改造（长期最优方案） #

audits是典型的时间序列数据，按时间分区是最彻底的解决方案，完全规避后续的空间回收和跨表查询问题：

• 操作流程：
  1. 新建与原表结构（含索引）一致的分区表audits_partitioned，按审计记录的时间字段（比如created_at）做RANGE分区，建议按季度或月划分（例如每个月一个分区）。
  2. 将原表中需保留的近一年数据导入新分区表。
  3. 短时间锁表后，把原表重命名为audits_old，新分区表重命名为audits，随即解锁（锁表时间极短，仅需完成表名切换）。
  4. 后续清理旧数据直接执行ALTER TABLE audits DROP PARTITION p_2023;（替换为对应年份/月份的分区名），该操作瞬间完成，且直接释放磁盘空间给操作系统，无需OPTIMIZE。
  5. 日常维护：提前创建未来1-2个周期的分区，避免写入失败。
• 核心优势：
  • 无需修改应用代码，MySQL会自动扫描对应分区执行查询，完全隐藏底层表结构变化。
  • 清理数据高效无锁，彻底解决空间回收难题。
  • 适配高吞吐量写入场景，分区表的写入、查询性能优于大表。

二、在线DDL分批空间回收（临时救急+轻量长期维护） #

如果暂时不想改分区表，可以用InnoDB的在线DDL替代全表OPTIMIZE，降低业务影响：

• 操作流程：
  1. 确保innodb_online_alter_log_max_size参数设置足够大，支持在线DDL的增量日志存储。
  2. 执行ALTER TABLE audits ENGINE=InnoDB;——该命令等价于OPTIMIZE，但支持在线操作，仅在最后切换表的瞬间会短时间锁表，其余时间表保持可读可写。
  3. 若单次执行仍耗时过长，可以改为定期执行（比如每月一次），配合每日分批DELETE（每次删10万行左右），减少表碎片积累，缩短每次DDL的执行时间。
• 核心优势：无需修改应用代码，对业务影响极小，适合快速回收空间的临时场景。

三、外部归档+原表轻量化（需保留历史数据的场景） #

如果需要保留一年以上的审计数据，可将旧数据归档到低成本外部存储，同时轻量化原表：

• 操作流程：
  1. 按时间范围（比如每周一次）将一年前的数据导出到S3、ClickHouse等外部存储（可用mysqldump或binlog同步工具实现）。
  2. 原表中分批删除已归档的数据，每月执行一次ALTER TABLE audits ENGINE=InnoDB;回收空间。
  3. 应用层查询历史数据时直接访问外部存储，无需合并原表数据。
• 核心优势：保留完整历史数据，原表保持较小规模，写入和查询性能更稳定，清理操作可分批执行无长时间停机。

四、使用pt-online-schema-change工具（大表在线重建） #

借助Percona的pt-online-schema-change工具，可更灵活地在线重建表，进一步降低锁表风险：

• 操作流程：
  1. 安装Percona Toolkit后，执行pt-online-schema-change D=你的数据库名,t=audits --alter="ENGINE=InnoDB" --execute。
  2. 工具会自动创建临时表，逐步复制原表数据，同时通过触发器同步增量写入，完成后自动切换表名、删除旧表。
• 核心优势：几乎不影响业务读写，自动处理增量数据，适合超大规模表的空间回收操作。

内容的提问来源于stack exchange，提问作者Sidharth Samant