MySQL 删除大量数据后,为什么磁盘空间没有变小?OPTIMIZE TABLE 能解决吗?
下面是模拟面试对话,用来讲解本题,不是真实面经。
🧑💻 面试官:删了很多MySQL数据,为什么磁盘空间没变小?
🙋♂️ 我:DELETE只删记录,执行OPTIMIZE TABLE就会释放。
🧑💻 面试官:如果长事务还要看到旧数据呢?表又在共享表空间里呢?
🙋♂️ 我:那可能需要先等清理。
🧑💻 面试官:OPTIMIZE一定能把空间还给操作系统?在线DDL是不是完全不占额外空间、也不等待锁?
数据不再可见、空间可以复用、文件变小,是三个不同结果。先看表空间,再谈重建。
面试速答(60 秒版)
InnoDB删除记录后,磁盘文件通常不会马上缩小。删除涉及记录可见性和旧版本清理;清理后出现的空闲空间,也可能只是留在表内部供后续写入复用。
因此要分清三件事:行已经删掉、内部空间可复用,以及空间真正归还给操作系统。它们不一定同时发生。
对于使用独立 .ibd 文件的表,合适的重建或 OPTIMIZE TABLE 可能整理数据并回收文件空间。但共享表空间、索引和版本配置不同,不能保证同样结果。
重建也有额外空间、I/O和锁等待成本。先确认空间是否真的急需回收、有没有长事务及足够余量,再安排备份和维护窗口,不能看到文件大就直接执行。

知识点详解:从一行被删除,到磁盘真的腾出空间
DELETE完成,不代表旧内容立刻可以丢掉
假设事务A读取了课程列表,事务B删除其中的过期课程。为了让事务A按相应隔离语义继续读取,系统可能仍需要旧版本信息。
InnoDB通过MVCC、undo与后续purge等机制处理这些关系。长时间持有快照的事务,可能让旧版本清理滞后。
因此删除很多行后,先检查有没有长事务、清理是否受阻。不能把文件大小直接当成DELETE是否生效的证明,行可见性和存储回收是不同问题。
页里出现空位,文件边界却未必改变
可以把表文件看成许多页组成的容器。删掉一些记录后,有的页出现空位,有的页仍保留其他记录。空位可以被重新利用,但并不意味着容器末尾恰好空出一整段,可以直接截短文件。
内部复用通常不是坏事。如果后续还会继续写入,保留可用空间可能避免重新申请。
所以我们需要同时看业务增长、表数据量与文件大小。单看操作系统显示的文件大小,很容易把正常可复用空间误认为泄漏。

能否归还OS,先看表空间类型
独立表空间使表有自己的 .ibd 文件,重建后有机会通过新的布局缩小文件并归还空间。共享表空间里的空位,则可能只供其中对象使用,不能直接套同一个结论。
也不能只看当前 innodb_file_per_table 变量就推断所有旧表的位置。表创建和迁移历史可能不同,需要确认目标表实际所在的表空间。
我们希望解决的是哪种空间紧张也要明确。如果想让同一数据库后续插入复用内部空间,和想给操作系统腾磁盘,做法未必相同。

OPTIMIZE是重建成本,不是免费的整理按钮
对适用的InnoDB表,OPTIMIZE会触发表与索引重建。常规表可使用在线DDL,但仍有准备和提交阶段的锁边界,还可能等待已有事务释放元数据锁。
特定配置或全文索引等情况会改变执行方式,不能保证所有表都按同样路径在线完成。
重建还会产生I/O与临时空间需求。磁盘已经逼近满容量时贸然开始,可能让问题更严重。先确认容量、备份、复制延迟和维护窗口,再选择当前版本支持的方案。
删除策略最好在文件变大前设计
假设历史数据持续增加,定期清理只是其中一环。按时间保留、分批删除,或在适合的表结构中使用分区生命周期管理,都需要结合查询与约束评估。
不能为了快速删数据直接把DELETE改成TRUNCATE。后者的范围、事务及DDL语义不同,误用会清空不该清的数据。
真正要验证的包括业务数据正确、清理完成、内部空间与文件大小变化,以及维护期间请求和副本状态。本文没有执行任何删除或重建操作。

技术依据:MySQL 8.4 OPTIMIZE TABLE、MySQL 8.4 InnoDB 磁盘优化。工程场景与解法按本文说明的条件展开。
面试官继续追问
文件不缩小,能否说删除失败了?
不能。先用查询确认可见记录,再看旧版本清理、内部复用和文件回收。
线上OPTIMIZE不用维护窗口吗?
不能保证。在线执行仍有资源和锁成本,还受表结构与配置限制,需要按实际负载评估。
面试速记卡
- 删除:记录不可见不等于所有旧版本已清理。
- 复用:表内部空闲空间不等于OS可用空间。
- 条件:先确认独立或共享表空间,不只看当前开关。
- 重建:OPTIMIZE可能回收独立表文件空间,但有I/O、空间与锁成本。
- 维护:备份、余量、长事务和复制状态需要一起检查。
公司面试真题
真题根据求职者公开面经整理,题意经过概括,非逐字原话或公司官方题库;本文为 Sunday 的独立解析。
美团 · Java开发(原帖标签) · 校招(原帖标签)
数据库表删除数据后,B+ 树为什么会留下空洞?(题意整理)
美团点评二面面经(8.19下午4点-5点) ↗
标题记录 8 月 19 日二面;原帖编辑于 2020-08-20