RDS MySQL每日删旧插新后InnoDB存储空间回收问题咨询

1. 是否必须执行OPTIMIZE TABLE来释放空间？ #

InnoDB删除数据后，释放的空间会被标记为可复用的空闲空间，不会立刻归还操作系统。如果后续插入的新数据能填满这些空闲空间，完全不需要执行OPTIMIZE TABLE。

但如果你的场景中，每日删除的数据量远大于插入量，或者长期积累导致碎片过多、空间占用持续走高，OPTIMIZE TABLE确实能解决问题——它本质是重建表结构，将未使用的空间还给操作系统。不过要注意：该语句在InnoDB下等价于ALTER TABLE ... ENGINE=InnoDB，会对表产生锁（RDS虽有在线DDL优化，但仍存在业务影响），因此不建议每日执行，应放在低峰期，或按周/月的周期操作。

2. 有没有自动释放存储空间的配置？ #

InnoDB没有直接自动归还空间给操作系统的配置，但可以通过以下方式优化空间复用，减少手动执行OPTIMIZE TABLE的需求：

• 确保innodb_file_per_table开启：该参数默认已启用（RDS MySQL通常默认开启），它让每个表拥有独立的ibd文件，而非存入共享的ibdata1。这种情况下，OPTIMIZE TABLE能直接释放单个表的空间给系统；若用共享表空间，释放的空间只能在共享池内复用，无法归还操作系统。
• 调整innodb_purge_threads：该参数控制清理旧事务数据的线程数，MySQL 5.7及以上默认值为4。适当调大该值可加快删除后的数据清理速度，让空闲空间更快被标记为可复用状态。
• 改用分区表：既然是按日删除数据，建议将表按日期做RANGE分区，每日直接删除前一天的分区（执行ALTER TABLE ... DROP PARTITION）。这种方式会直接删除分区对应的物理文件，空间立刻归还操作系统，效率远高于逐行删除，是这类场景的最优解。

OPTIMIZE TABLE补充说明（翻译自MySQL 5.7官方文档） #

OPTIMIZE TABLE 主要用于整理表碎片、回收未使用空间，并优化表的存储结构。针对InnoDB表，该语句会重建表、更新索引统计信息，并在innodb_file_per_table开启时将未使用空间归还操作系统。执行该语句需要表的ALTER权限。
注意：如果表不存在碎片，OPTIMIZE TABLE会快速结束，不会执行实际的重建操作。此外，执行过程中表会被锁定（除非使用在线DDL，RDS可能支持），因此需避开业务高峰时段。

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