MySQL空表.ibd文件体积过大的原因及收缩方案咨询

你的使用模式确实是空表状态下.ibd文件体积过大的直接原因。
开启innodb_file_per_table后，每张InnoDB表独立使用自己的表空间文件存储数据、索引。当你用DELETE语句删除表内记录时，InnoDB不会将释放出的磁盘空间返还给操作系统，只会将对应的数据页标记为「可复用」状态，留给后续新写入的数据使用。你每日批量插入数十万条记录的过程中，表空间会持续扩容到数GB大小来存这些数据，后续删光所有记录后，这些已经申请到的磁盘空间依然会保留在.ibd文件中，就会出现表内无数据但文件体积高达数GB的情况。

不需要盲目定期执行，要结合你的业务场景判断：

• 如果你每日删完数据后，第二天依然会向同一张表插入差不多量级的新数据，完全没必要跑OPTIMIZE TABLE。那些标记为可复用的空间会直接被第二天的新写入使用，不会重复申请磁盘空间；反而你把文件收缩到很小之后，第二天写入时又要重新触发表空间扩容，带来额外的性能开销。
• 如果你后续很长时间都不会再向这张表写入大量数据，想要回收磁盘空间，可以执行OPTIMIZE TABLE。注意InnoDB下OPTIMIZE TABLE的本质是重建整表，执行过程中会占用较高的IO、CPU资源，大表场景下建议在业务低峰期操作。

• 全量删除数据时用TRUNCATE TABLE代替DELETE：你是处理完所有记录后清空全表，TRUNCATE TABLE属于DDL操作，执行时会直接删除原有的大体积表空间文件，重新生成初始大小的新表空间文件，执行速度极快，会直接把磁盘空间返还给操作系统，不需要额外做收缩操作。注意TRUNCATE操作无法回滚，确认数据全部迁移完成后再执行即可。
• 用分区表替代普通表：如果你的数据是按日期批次写入，可以将表按处理时间设置为范围分区，每一批次的数据对应一个独立分区，数据处理完成后直接执行DROP PARTITION删除对应分区，操作秒级完成，会直接释放对应磁盘空间，不会产生碎片。
• 纯临时处理场景用临时表：如果这张表只用来做单次数据处理的中转，不需要长期保留表结构，可以用CREATE TEMPORARY TABLE创建会话级临时表，数据处理完成后会话断开临时表会自动删除，临时表默认存储在共享临时表空间中，不会在业务库目录下生成独立的.ibd文件，也不会产生长期的空间占用。
• 执行ALTER TABLE 表名 ENGINE=InnoDB：这个操作和OPTIMIZE TABLE的效果完全一致，都是通过重建表的方式整理碎片、释放未使用的表空间，没有本质区别。

注意：如果后续马上要向表中写入同量级的数据，不建议做表空间收缩操作，保留已申请的空间供复用比反复收缩、扩容的效率高很多。

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