大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
刚转行做DBA那会儿，我干过一件现在想起来还脸红的事。
业务方说订单表数据太多，让清理历史数据。我吭哧吭哧写了个DELETE FROM orders WHERE create_time < '2024-01-01'，删了三百多万行。删完还挺得意，跑去跟业务方邀功：“删完了，查询快了不少吧？”
业务方看了一眼磁盘监控，说：“快是快了，但磁盘空间怎么一点没少？还涨了？”
我打开df -h一看，表文件纹丝不动。再跑du -sh，果然还是那么大。当时脑子里只有一个念头：删掉的数据去哪了？
后来才搞明白，InnoDB的DELETE，从来就不是“真删除”。
先把这件事里绕不开的几个概念说清楚：
标记删除——InnoDB执行DELETE时，不会立即从磁盘上抹掉这行数据。它只是在行的头部做一个标记，表示“这行已删除，空间可以被复用”。真正的物理清理，是purge线程在后台异步做的。
purge线程——InnoDB的后台清理线程。它负责清理标记删除的记录和MVCC的旧版本数据。如果它清理不过来，标记删除的记录会持续堆积。
页合并——当相邻两个数据页的空闲空间加起来足够装下一整页时，InnoDB会尝试把两个页合并成一个，释放出一页的空间。触发阈值由MERGE_THRESHOLD控制，默认50%。
DATA_FREE——information_schema.TABLES里的一个字段，表示表空间里已经分配但还没被使用的空间（字节）。它的大小，直接反映“删除空洞”有多严重。
一、DELETE之后到底发生了什么
我那条DELETE执行的时候，InnoDB做的是这样一件事：
在聚簇索引和每一个二级索引里，把这三百万行标记为删除。数据页上的记录其实还在，只是被贴上了“可复用”的标签。
purge线程随后开始异步清理。清理完成后，页上的空间变成“空闲”，可以被后来的INSERT复用。但关键在于——页本身不会被释放回操作系统。除非这一页变空、且满足合并条件，否则它仍然属于这张表。
这就是“删除空洞”：空间在表内部被标记为可用，但表文件不缩小。
我后来查了一下，那三百多万行删除后，DATA_FREE涨了将近20GB。表文件大小？一点没变。
二、页合并为什么没生效
页合并是InnoDB回收空间的唯一内部机制。但它的触发条件，比大多数人想象的苛刻得多。
条件一：相邻页的空闲空间总和 ≥ 一页的容量。
一个16KB的数据页，空闲空间得超过50%（超过8KB），它才“有资格”被考虑合并。但有资格不等于会合并——必须和相邻页的空闲空间加起来能装下一整页的内容，InnoDB才会动手。
条件二：MERGE_THRESHOLD的阈值限制。
默认MERGE_THRESHOLD=50。大量页的空闲空间卡在40%-49%之间——不够触发合并，但已经浪费了将近一半空间。这些页永远不会被合并，空间也永远不会释放。
条件三：purge线程必须先完成清理。
页合并的前提是页上的标记删除记录已经被purge清理掉。如果purge滞后（长事务阻塞、innodb_purge_threads不够），页上还有大量“标记删除但未清理”的记录，空闲空间的计算就不准，合并也无法正确触发。
三条凑在一起的结果：删了三百万行，DATA_FREE涨了20GB，页合并一次都没触发。表文件原地不动，甚至因为新数据插入继续增长。
三、怎么确认空洞有多严重
那次教训之后，我把这个查询加进了日常巡检：
SELECT TABLE_NAME, DATA_LENGTH, DATA_FREE,
ROUND(DATA_FREE / (DATA_LENGTH + DATA_FREE) * 100, 2) AS free_pct
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'orders';
free_pct超过30%说明空洞严重，超过50%需要立即处理。
再看purge是不是跟上了：
SHOW GLOBAL STATUS LIKE 'Innodb_history_list_length';
这个值持续增长，说明purge清理不过来，旧版本在堆积。如果有长事务在跑，purge会被直接卡住。
四、解决方案：OPTIMIZE的代价与替代
方案一：OPTIMIZE TABLE
OPTIMIZE TABLE orders;
原理是重建表：建一个新表空间，把数据逐行复制过去，删旧表，重命名新表。重建后的表空间紧凑，空洞全部消除。
代价是磁盘空间短暂翻倍（新旧表同时存在），执行期间大量I/O，大表可能锁表或阻塞写入。MySQL 8.0的OPTIMIZE TABLE对InnoDB走Online DDL，支持并发DML，但大表执行时仍可能引发I/O峰值。
方案二：ALTER TABLE ... ENGINE=InnoDB
ALTER TABLE orders ENGINE=InnoDB, ALGORITHM=INPLACE, LOCK=NONE;
效果类似，但语法更明确地指定Online DDL。适用于MySQL 5.6+，对写入的阻塞更小。
方案三：pt-online-schema-change
Percona的工具，创建影子表、触发器同步增量、原子rename。整个过程对业务透明，但需要额外磁盘空间和更长的执行时间。适合几十GB以上的超大表，且不能接受任何锁表。
方案四：预防——别一次性大DELETE
最根本的预防：不要用一次性大DELETE。用游标分批删除，每批几千行，提交后再删下一批。这样purge有机会跟上，页合并也更可能触发。配合innodb_purge_threads=4（默认值通常够用），减少长事务。
五、小结
DELETE删了数据空间不释放，根因在InnoDB的标记删除机制和页合并的苛刻条件。DATA_FREE是判断空洞严重程度的核心指标。OPTIMIZE TABLE能解决但代价大，ALTER TABLE ... ENGINE=InnoDB是更安全的在线替代。最根本的方案是避免大删除——分批删除，让purge和页合并有机会工作。
那次教训之后，我再也没写过一次性大DELETE。
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
