上个月处理过一起典型的磁盘告警某业务库的流水表有 40GB开发同学执行了一条DELETE清理掉近 2000 万行“历史数据”跑完之后回来告诉我“删完了”结果我登上去一看df -h磁盘占用几乎纹丝不动。开发当场就有点慌怀疑是不是删错了表或者 MySQL 出了什么诡异的 bug。这不是个例很多人在DELETE大批量数据后都遇到过“空间没释放”的问题。这篇文章就把我这次的完整排查链路、InnoDB 底层原理、实测过的空间回收方案以及哪些场景根本不需要回收一次性说清楚。1. 先还原现场确认“没释放”到底是不是错觉遇到这种问题第一步不是急着查原理而是先确认两件事DELETE 是否真的删掉了目标数据以及我们判断“磁盘没释放”的观测方式是否准确。很多时候“没释放”不是错觉但也确实有可能是被其他因素干扰了判断。1.1 删除是否真的生效了先看 DELETE 的执行结果。以我那次为例执行的是DELETE FROM t_order_flow WHERE create_time 2024-01-01;MySQL 会返回一个ROW_COUNT()显示删除了多少行。紧接着再查一下这张表的行数分布确认目标范围的数据确实没了SELECT COUNT(*) FROM t_order_flow WHERE create_time 2024-01-01; SELECT COUNT(*) FROM t_order_flow;正常情况下前一个查询应该返回 0后一个表总行数应该有明显下降。这一步看着基础但我确实见过有人在测试环境误删了别的分区数据还以为是“MySQL 没释放空间”硬排查了半天。1.2 观测层面的三条线索确认数据删除没问题之后就要用多个维度去验证“空间没释放”这个结论是否站得住。我一般同时看三处观测维度验证方法我这次的实际结果操作系统层面df -h查看所属磁盘分区删除前后几乎没有变化表物理文件大小ls -lh /var/lib/mysql/yourdb/t_order_flow.ibd删除前约 40GB删除后仍约 39.xGBInnoDB 内部统计information_schema.tables的data_free从几百 MB 涨到了 1.5GB 左右data_free这个字段很关键它表示 InnoDB 表空间内部的碎片空间后面我会专门讲怎么读它。如果前两处都没变化而data_free涨了基本可以锁定问题方向DELETE 确实删了逻辑数据但这些数据所在的物理空间InnoDB 没有交还给操作系统。1.3 容易被误判的干扰项还有一种情况也要排除删除过程中产生的临时文件、undo 日志、binlog 可能在你观测的瞬间“抵掉”了本应释放的空间。比如你删除 2000 万行binlog 可能写了几个 GBundo 表空间文件也在增长。如果只看整个磁盘可能感觉“删了那么多数据空间一点没少”其实是 binlog 和 undo 把空间“吃”回去了。所以排查时要分清你要找的是业务表的.ibd文件是否有变化而不是整块磁盘。把业务表文件单独拿出来看结论才干净。2. InnoDB 的页与删除标记被划掉的只是逻辑数据确认现象真实存在后就进入核心问题了为什么 DELETE 删掉了记录表文件却不肯瘦身这要从 InnoDB 组织数据的最小结构——“页”说起。2.1 记录头里的 delete mark并没有真正擦除数据InnoDB 默认每页 16KB行数据按主键顺序存在页里。当执行 DELETE 时InnoDB 不会像文件系统那样把这条记录的字节全部抹掉它只是在记录头里打了一个delete mark标记。可以理解成你用铅笔在笔记本上划了一条删除线字还留在纸面上。只有后续插入的新数据需要复用这块位置时才会真正覆盖它。这意味着什么删除 2000 万行等于在几十万个页里各划了几条删除线。页还是那些页文件还是那个文件只是页内部多了一批“可覆盖的空位”。2.2 MVCC 机制决定了记录不能立刻被物理清除为什么 InnoDB 不直接物理删除非要留个标记慢慢覆盖核心原因是事务隔离。MySQL 默认的REPEATABLE READ隔离级别下一个事务开始后它能看到的一致性视图由read view决定。如果事务 A 在 DELETE 之前开启了事务那么它后面执行的查询理论上还需要看到这些“已被删除”的旧版本数据。旧版本数据放在哪一部分在 undo log 里一部分就依赖原来的记录在页面上保留原样。如果在 DELETE 事务提交的瞬间把磁盘上的记录全部擦掉那些正在跑的长事务就找不到历史版本了结果就是读数据全乱套。所以 InnoDB 采取的策略是先把删除动作记录到 undo log把记录标记为删除后续由后台purge线程在确认没有任何事务还需要旧版本之后再逐步清理。这里容易有个误区purge 线程即使把记录彻底清理了也只是让页内的空间变成真正的 free space并不会主动收缩表空间文件。文件大小这个层面上InnoDB 是“只进不出”的。2.3 purge 线程干了活但文件还是那么大复盘我这次实际看到的数据执行完 DELETE 后再查information_schema.innodb_metrics或系统状态确实能看到 purge 线程在工作。等了一段时间后页级别上的空闲空间其实变多了data_free也从几百 MB 涨到了 1.5GB。但.ibd文件的大小变化基本可以忽略不计。打个比方帮助理解InnoDB 的表空间文件就像一个大型仓库仓库里有很多货架DELETE 只是把货架上的商品清走了货架本身还在。purge 线程相当于把货架擦了擦让它更干净但仓库没有拆掉仓库占地面积不会因为货架空了几个而变小。2.4 空出来的货架会被后续 INSERT 优先使用这也是 DELETE 不留空闲空间给文件系统的一个“补偿”被标记删除的页内空间会进入 InnoDB 的空闲空间管理。后续再往这张表里 INSERT 新数据时InnoDB 会优先填充这些已经空闲的页而不是立刻从文件系统申请新空间。所以“DELETE 后不回收空间”对于高频写入的表来说并不全是坏事它是在为以后的写入预留空间。3. 空间到底去哪儿了从页内碎片到表空间碎片理解了删除标记机制接下来的问题是我们眼里看到的“磁盘空间”和 MySQL 眼里的“表空间”到底差在哪。这一层不搞清楚后面做任何操作都容易踩坑。3.1 三个层级对应三种空间我把这个过程拆成三个层级来看排查时更清楚操作系统文件层即磁盘上实际的.ibd文件大小。它由 InnoDB 负责扩容但基本不会自行收缩。InnoDB 表空间管理层表空间有“段segment→ 区extent→ 页page”的结构。一个区包含连续的 64 个页约 1MB。如果一批页内的数据全删光了理论上这个区可以标记为空闲放回表空间的空闲区列表。页内部空间层每页内部记录删除后的碎片空间由页内的PAGE_FREE链表管理新插入的数据会优先来这里“找位置”。DELETE删除的数据通常分散在很多页里很少能做到整整个区都清空。因此即便 InnoDB 内部把一批区释放到空闲列表文件长度也不会变因为这些空闲区仍然在表空间文件内部属于“内部可复用资源”操作系统并不知道这些区域已经“空”了。3.2 information_schema 里的 data_free 应该怎么看直观观察碎片程度的字段就是data_freeSELECT table_schema, table_name, data_length, index_length, data_free, (data_free / (data_length index_length data_free) * 100) AS fragment_ratio FROM information_schema.tables WHERE table_schema yourdb AND table_name t_order_flow;这里的data_free单位是字节表示表空间中 B-tree 页内碎片空间和未使用区域的估算值。我这次测出来DELETE 之前data_free在 300MB 左右DELETE 之后涨到 1.5GB碎片率约 3.7%。看着不算特别夸张因为表基数大但空间确实没有回到操作系统的掌控中。要注意这个字段是一个估算值不是精确统计。真正决定磁盘占用大小的仍然要以.ibd文件的实际大小为准。3.3 独立表空间和共享表空间的差别上面讲的情况默认是innodb_file_per_tableON也就是每张表的数据独立存放到库名/表名.ibd文件里这也是 MySQL 5.6 之后的默认配置。但如果你的实例里有些老表存在系统表空间ibdata1里情况会更复杂ibdata1本身是共享文件里面可能有数据字典、undo 等混合内容它更是“只涨不缩”即使删光了一张表的数据ibdata1文件也不会变小只能靠重建实例或迁移数据来瘦身。所以排查之前先用下面的 SQL 确认表在哪个表空间SELECT table_schema, table_name, engine, tablespace_name FROM information_schema.tables WHERE table_name t_order_flow;任何tablespace_name不是自己库名的表就要格外留意它的空间回收复杂度会高很多。3.4 为什么 TRUNCATE 可以释放DELETE 却不行很多人会把 DELETE 和 TRUNCATE 混在一起比较但这两者的底层路径完全不一样。TRUNCATE 在 InnoDB 里的本质是“重建表”直接删除原来的表空间文件和索引结构然后新建一个空表。文件都换了空间自然就释放了。DELETE 是逐行标记删除文件还是一整块里面留了无数空位文件系统当然拿不到空间。这也是为什么日常运维里对一张“要全清空”的表我会直接建议用 TRUNCATE而不是 DELETE FROM 全表。前者秒级完成且空间立刻释放后者可能要跑很久跑完空间文件还是那么大。4. 实测过的空间回收方案四种路径的代价与适用条件如果确实需要把磁盘空间还给操作系统那就要主动触发 InnoDB 进行表重建。我实际测过并且敢在生产环境用的主要是下面这几种方式。每一种都有明确的代价选之前要先想清楚。4.1 OPTIMIZE TABLE常规首选但需要额外磁盘空间OPTIMIZE TABLE t_order_flow;执行OPTIMIZE TABLE时InnoDB 会重建表并压缩碎片完成后原来的 40GB 文件会明显缩小我这次测下来缩到了 6GB 左右几乎等于活跃数据的大小。这个结果非常直观。但这里有个必须强调的坑重建过程会在表空间所在目录生成一个临时文件大小接近原表大小。如果你的磁盘可用空间小于当前表文件大小OPTIMIZE TABLE可能会中途失败甚至导致磁盘直接写满。所以执行前务必确认df -h /var/lib/mysql可用空间至少要比目标表当前.ibd文件大才比较安全。我在测试环境模拟过磁盘余量不足时重建任务会卡在复制数据的阶段吃满磁盘后报No space left on device。另外OPTIMIZE TABLE在 5.7 和 8.0 里都支持 Online DDL理论上允许并发读写但实际执行时对 IO、CPU 的消耗都不低建议放在业务低峰期。如果表非常大几百 GB整个过程可能持续数小时期间主从复制延迟会明显拉大也要有心理准备。4.2 ALTER TABLE ... ENGINEInnoDB和 OPTIMIZE 等价的另一种姿势ALTER TABLE t_order_flow ENGINE InnoDB;这条语句的执行计划和OPTIMIZE TABLE基本一致都是强制重建表。你还可以加上ALGORITHMINPLACE避免COPY算法带来的额外开销ALTER TABLE t_order_flow ENGINE InnoDB, ALGORITHMINPLACE;实测下来这两者在碎片整理效果上没有本质差别。我通常是根据当时操作习惯随机选如果团队更熟悉OPTIMIZE就用前者如果希望顺带调整表选项就用ALTER TABLE。要注意的是MySQL 8.0 里若表使用了INSTANT算法做过加列等 DDL后续再执行某些重建操作可能会有额外的限制或者提示升级到 8.0 后做这类操作前先跑一次SHOW CREATE TABLE确认一下表定义更稳妥。4.3 分区表的快速释放路径DROP PARTITION如果目标表是分区表并且你清理的数据刚好能按分区边界切割那根本不需要重建全表。直接删掉对应分区速度极快空间释放也彻底ALTER TABLE t_order_flow DROP PARTITION p2023_q4;分区文件在独立表空间模式下是单独存放的DROP PARTITION会直接删除对应的分区文件磁盘空间立刻回来。这个方案我在线上尝试过很多次即使分区数据有十几 GB秒级就能完成。前提是分区设计从一开始就做好了并且清理规则和分区边界对齐。没有分区的表想借助分区来做清理是可以的但代价不低需要对大表做分区迁移本身也是一次大操作。这里不展开只是想提醒一点如果你的表有清晰的时间维度和定期清理需求从建表阶段就考虑分区会给你后面省掉很多麻烦。4.4 磁盘快满了连 OPTIMIZE 都跑不动怎么办这是最棘手的情况表占了几百 GB磁盘可用空间只剩几个 GB你根本不可能执行OPTIMIZE TABLE因为临时文件都没地方放。这时候我一般按下面几个方向处理如果临时无法停机用pt-online-schema-change或gh-ost这类在线表重建工具。它们会创建一张影子表逐步从原表拷贝数据到影子表最后通过原子性改名完成切换。拷贝过程中需要的额外空间同样取决于表大小但可以通过分批处理、同时在另一块磁盘上放影子表等方式缓解空间压力。如果能接受短时间写锁定手动“建新表 导数据 rename”也是一个思路。前提是业务能接受一段只读窗口。具体步骤如下-- 1. 新建一张结构一致的空表 CREATE TABLE t_order_flow_new LIKE t_order_flow; -- 2. 分批把需要保留的数据导入新表 INSERT INTO t_order_flow_new SELECT * FROM t_order_flow WHERE create_time 2024-01-01; -- 3. 表名切换 RENAME TABLE t_order_flow TO t_order_flow_old, t_order_flow_new TO t_order_flow;如果数据删了后还要继续长期写入且查询性能可以接受那不妨先不折腾。把“空间未释放”当作一个已知状态毕竟后续 INSERT 会逐步复用。真正需要处理的时候往往是业务已经明显变慢、或者磁盘告警已经威胁到写入时。4.5 四种方案的横向对比方案空间释放效果是否需要额外磁盘是否阻塞业务适用场景OPTIMIZE TABLE彻底文件收缩明显需要约等于原表大小的空间在线低峰期更稳妥常规整理磁盘有余量ALTER TABLE ENGINEInnoDB彻底与 OPTIMIZE 相同需要额外空间在线低峰期更稳妥与 OPTIMIZE 等价DROP PARTITION彻底秒级释放不需要只在分区元数据上短暂加锁分区表按时间清理pt-osc / gh-ost / 手工重建彻底需要但可控在线 / 短时只读大表、磁盘紧张、无法停机5. 先别急着整理这些场景其实可以“不管”最后说点更实际的。我接手过不少系统开发同学看完这类文章第一反应是“那我每次 DELETE 完之后是不是都该 OPTIMIZE 一下” 不一定。判断要不要整理不是看 DELETE 删了多少行而是看这张表接下来的命运。5.1 高频清理又高频写入的表碎片会被自然复用如果一张表每天都有 DELETE 清理历史数据同时又有大量 INSERT 写入新数据那么之前 DELETE 留下的页内空间很快就会被新数据填回去。这类表即使不执行 OPTIMIZE空间利用率也会趋于稳定。强行整理反而会在重写过程中产生大量 binlog放大主从延迟增加不必要的运维风险。我遇到过一张告警记录表每周清理一次清理完空间也是不降一开始也想 OPTIMIZE。后来观察一段时间发现清理后第二天新写入的数据就开始大量复用之前的空闲页。整理一次带来的收益只有几百 MB代价却是几分钟的 IO 高峰完全不划算。5.2 判断要不要整理的两个硬指标我自己的判断标准比较朴素一般看两个指标第一data_free占总空间的比率。如果碎片率高于 20%并且表写入频率不高那值得整理。如果碎片率不到 5%基本不用管。第二全表扫描或二级索引扫描的耗时是否明显劣化。DELETE 造成的页内空洞过多会让范围查询扫描到大量“已删除”的记录头增加了 IO 和判断开销。如果你发现一个本来几十毫秒的查询数据删完后变慢到几百毫秒那说明碎片对查询路径已经产生了实质影响这时候才需要整理。5.3 整理前的检查清单少做一步都可能出事故无论最终选择哪种方案动手前我建议过一遍这个清单确认当前磁盘剩余空间至少大于目标表文件大小否则不要启动任何重建类操作。确认没有长时间未提交的读写事务。有长事务在跑时重建过程可能迟迟无法完成甚至导致 undo 膨胀。确认主从架构下有充足的低峰期窗口并预估大表的重建时间。几百 GB 的表一次 OPTIMIZE 跑三四个小时很正常要提前评估 binlog 增长量。确认表上是否有外键约束。有外键引用的表重建过程会涉及关联检查复杂度会翻倍。如果表本身有分区优先考虑分区级清理别拿着 OPTIMIZE 去动全表。最后分享一点个人体会MySQL 的 DELETE 不释放空间往深说是存储引擎对一致性、性能和空间复用的综合取舍不是 bug。理解了这个取舍你就不会在每次清理完数据后急着做重建也不会在真正需要回收空间时走错方向。运维和开发的差别往往就在这一步判断上面。
