2000GB MySQL InnoDB大表旧数据删除及近1个月数据留存方案咨询

现有方案存在的问题 #

• 触发器同步的性能与一致性风险：原表是140亿条数据的大表，触发器会给每一次增删改操作额外增加一次写新表的开销，高并发场景下会直接拖慢原表的写入速度，引发锁等待、事务超时等问题。此外，触发器的原子性依赖于数据库事务，如果原表操作成功但新表写入失败，会直接导致数据不一致，后续排查和修复的成本极高。
• RENAME+DROP大表的系统负载问题：RENAME操作虽然是原子的，但后续DROP 2000GB的旧表时，InnoDB需要清理大量的缓存、undo日志及磁盘数据，这个过程会占用大量IO和CPU资源，可能导致数据库服务卡顿，甚至影响其他业务的正常运行。
• 每日删除+每周OPTIMIZE的不可持续性：
  • 每日DELETE操作如果涉及大量数据，依然会生成大量undo日志，且行级锁会影响业务读写；
  • OPTIMIZE TABLE对InnoDB大表来说，本质是锁表重建，2000GB的表执行该操作会导致长时间的业务不可用，完全不符合生产环境的要求；
  • 即使执行OPTIMIZE，部分文件系统（如ext3）无法立即回收释放的空间，磁盘占用问题依然无法解决。

可行的优化建议 #

一次性清理历史数据的方案 #

1. 分区表改造（最优解）：
   • 将原表改造为按eventtime的分区表（按天或按月分区均可），如果原表未分区，可使用pt-online-schema-change或gh-ost工具在线执行分区改造，全程不锁表，不影响业务。
   • 改造完成后，直接DROP 30天前的分区即可，该操作是元数据级别的，几乎无性能开销，且能立即释放磁盘空间。
2. 批量导出导入切换：
   • 使用mydumper或mysqldump在业务低峰期导出近1个月的数据，导入到同结构的新表中，确保新表的索引、统计信息与原表一致。
   • 执行RENAME TABLE完成切换，之后DROP旧表，这种方式比触发器同步更可靠，不会影响原表的日常写入性能。

长期保留近1个月数据的方案 #

1. 基于分区表的自动清理：
   • 采用按天分区的策略，每天定时执行DROP 30天前的分区，完全替代DELETE操作，无需担心锁表、undo日志及碎片问题。
2. 批量删除替代单次DELETE：
   • 如果无法使用分区表，改为分批批量删除：每次删除固定数量的旧数据（如每次删10万条），通过LIMIT控制，分多次在业务低峰期执行，减少对业务的影响。
3. 在线表重建替代OPTIMIZE TABLE：
   • 放弃每周OPTIMIZE，改用ALTER TABLE your_table ENGINE=InnoDB ALGORITHM=INPLACE, LOCK=NONE（MySQL 8.0+支持）在线重建表，整理碎片并回收空间，该操作无需锁表，可每月执行一次，避免频繁操作带来的开销。
4. 冷热数据分离归档：
   • 将超过30天的冷数据迁移到归档存储（如ClickHouse、HDFS或专门的归档数据库），仅在MySQL中保留近1个月的热数据，从根源上减轻MySQL的存储和性能压力。

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