Sunday面试指南

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 的独立解析。

浏览公司面试真题 →
简历汪永久免费在线制作简历,模板直接套用、导出无水印,永久免费、下载免费,不需要付费解锁任何功能。去写简历