1. 项目概述与设计思路1.1 核心需求解析前阵子我们的支付日志表已经涨到了 200 多 GB占了整个实例磁盘空间的一半。业务方做完月度归档后在备库上执行了 DELETE 清理了一个月前的历史数据删掉了将近 8000 万行。结果运维同事发现磁盘使用率几乎没变化于是问架构师怎么回事架构师只回了一句单表 delete 数据之后占用空间不会释放。这句话在群里传开之后好几个刚接触 MySQL 的同事都懵了。这个现象确实容易让人困惑因为我们平时对文件系统的认知是把文件内容删了文件占用的空间就应该腾出来。但数据库底层并不是按“文件内容”来组织数据的InnoDB 存储引擎管理数据的基本单位是数据页Page默认大小为 16KB数据页内部的数据是按主键顺序排列的。删除操作的本质是把数据页里面的记录标记为“已删除”而不是把整个数据页从磁盘上抹掉。简单理解删除操作修改的是数据页内部的状态磁盘上的文件空间还在只是里面多了很多“空洞”。这篇文章面向的是被这个问题困扰的 DBA、后端开发、运维同学。我会先讲清楚 delete 之后空间为什么不释放的底层机制再分享生产环境中如何验证、如何真正把空间收缩回来以及什么时候不建议收缩最后穿插一些我们自己踩过的坑。1.2 为什么架构师那句话是对的从存储引擎层面看DELETE FROM 执行完之后InnoDB 需要保证事务的隔离性和 MVCC 语义。被删除的行不能立刻从物理文件中抹掉因为如果有其他事务还在读取这些行它们必须能读到删除前的版本。所以 InnoDB 的做法是在聚簇索引主键索引的记录上打一个 delete-mark 标记然后把这个动作写入 undo log。真正从索引中物理移除这条记录要等 purge 线程在合适的时机异步完成而且 purge 之后只是让数据页内出现可复用空间文件大小依然不会变化。这背后还有个索引层面的问题。InnoDB 的主键索引是 B 树结构数据页之间通过双向链表连接。如果删除操作导致某个数据页中所有记录都被标记删除理论上这个数据页是可以从 B 树中摘除的但实际上 InnoDB 不会立即做“页合并”。因为合并页需要申请锁、修改兄弟节点的指针、更新父节点索引项开销很大而且如果马上又要插入新数据页分裂的成本更高。所以 InnoDB 的策略是保留这些空页优先复用它们来存放后续插入的数据。数据文件不收缩本质上是 InnoDB 为了减少随机 IO、提升写入性能而做的设计取舍。它把空间复用的粒度控制在数据页内部和数据页层面而不是文件系统层面。理解了这一点才能明白 delete 之后空间不释放不是 bug而是存储引擎的预期行为。2. 核心细节解析与影响面分析2.1 数据页内部的删除语义要彻底理解删除行为需要从数据页的内部结构入手。每个 16KB 的数据页包含页头、页尾、记录区、空闲区等部分。记录在页内按主键顺序链式存储每条记录有记录头信息其中有一个 delete flag 位。执行 DELETE 时InnoDB 会先根据主键定位到目标记录然后做两件事把记录的 delete flag 置为 1将记录从链表中断开逻辑上移除。这个页内操作非常快代价与记录数无关。但这只是正常删除的第一步。接着 InnoDB 会为这条删除操作生成 undo 记录用于事务回滚和 MVCC 快照读。undo 记录里保存了被删除行的完整镜像包括所有列的值以及对应的主键信息。只有当系统中没有任何活跃事务的 read view 还需要访问这条删除前的版本时purge 线程才能安全地清理 undo 记录同时真正释放数据页中的物理空间。这里的“物理”释放指的也是把这个位置标记为可复用而不是还给操作系统。数据页内有了空洞之后后续 INSERT 的新记录会根据主键顺序优先填充到相邻数据页中可复用的位置。如果空洞分布在不同的页里插入了大量新数据后页内碎片还会导致索引结构膨胀查询需要扫描更多的数据页这是另一个层面的性能问题。2.2 主键索引与二级索引的空间差异删除操作对主键索引和二级索引的影响不一样。主键索引直接承载数据行删除一条记录后该记录占据的槽位立即成为可复用空间。如果某个数据页的全部记录都被删除那么这个页会变成“全空页”InnoDB 会把它标记为空闲页并放入表空间的 free 链表中后续任何需要分配新页的插入操作都可以复用这个页。二级索引的删除逻辑更复杂一些。二级索引的叶子节点存储的是索引键值和主键值的组合。删除一行数据时InnoDB 需要删除该行在每一个二级索引中对应的索引条目。注意二级索引条目上的删除也是标记删除但这些空间不会优先被复用。原因是二级索引的物理顺序由索引键决定新插入的数据可能分布在完全不同的位置很难正好填充到已删除索引条目留下的空洞中。这就解释了为什么一张表有多个二级索引时DELETE 大量数据后二级索引文件会比主键索引文件保留更多空洞。你在生产环境里观察 .ibd 文件大小时会发现删完数据后索引文件几乎大小不变甚至有些场景下还会变大——那是 purge 进程尚未完成清理、同时又有新的索引页被分配的阶段。2.3 MVCC 与多版本链的拖累效应在事务隔离级别为可重复读REPEATABLE READMySQL 默认时MVCC 的影响尤其明显。长事务、长查询、备份任务、慢查询都会导致 purge 进程滞后。曾经遇到过一个问题一张大表删除了一半的数据隔了一个星期文件大小都没变化以为是删除没生效后来查 information_schema.innodb_trx 发现有一个跑了三天的分析查询把整个 purge 堵住了。原理是这样的被删除记录的旧版本仍然通过 undo log 在数据页中保留痕迹。如果有事务的 read view 还引用这个版本的记录purge 线程就不能清理对应 undo 记录。当所有旧事务结束后purge 会一批批清理但每次清理都会产生 IO 开销如果删除数据量太大purge 的积压可能需要数小时甚至数天才能消化完。这种状态下空间不释放是很正常的而且不建议强制干预。正确的做法是先等待 purge 完成再评估是否需要收缩表空间。如果你想观察 purge 的进度可以通过 SHOW ENGINE INNODB STATUS 查看 history list length这个值表示 undo 日志中未清理的事务版本数。它持续下降说明 purge 在正常工作。2.4 对查询性能和写入性能的实际影响delete 留下的空间空洞不只是“占着磁盘不用”这么简单。B 树的叶子节点如果大量空洞范围查询就需要读取更多的数据页因为页内的有效记录密度降低了。同样是扫 1GB 的有效数据碎片化的表可能需要读 2GB 甚至更多的物理页这会直接拉高 IO 延迟和 CPU 使用率。写入性能也会受影响。数据页出现空洞后插入新数据时的页内定位更复杂页分裂的触发条件也可能提前。更麻烦的是如果后续写入的数据仍然保持高增长趋势表空间会继续“长胖”让磁盘使用率看起来一直很高监控告警很难区分到底是删了没删还是删了没释放。我自己在压测环境里验证过一张 5000 万行的表随机删除 60% 的行之后未做表重建前同样的 SELECT 聚合查询耗时增加了 40% 左右。做完表重建后查询耗时回落到了删除前的水平。这个代价在生产环境中会被放大尤其是核心业务表被大量清理后整体性能可能明显下滑。3. 实操过程与核心环节实现3.1 验证 delete 后空间未释放的具体方法先不要急着分析原理上手实测一遍最有说服力。我自己写了一个验证流程供参考。-- 1. 创建测试表插入100万行数据 CREATE TABLE test_delete_space ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(64), created_at DATETIME ) ENGINEInnoDB; -- 通过存储过程或工具批量插入100万行插入完成后在文件系统层面观察表数据文件大小。ls -lh /var/lib/mysql/testdb/test_delete_space.ibd紧接着执行大范围删除删掉 90% 的行然后再次查看文件大小。# 删除前 -rw-r----- 1 mysql mysql 377M /var/lib/mysql/testdb/test_delete_space.ibd # 删除90%行后立即查看 -rw-r----- 1 mysql mysql 377M /var/lib/mysql/testdb/test_delete_space.ibd文件大小完全一样。因为 100 万行的表数据文件本来也就 300 多 MB这个实验可以快速做。要注意的是delete 后文件大小是否变化还取决于删除的行是否跨页、数据页是否全部为空。如果删除的是零星分布的数据所有页都有存活记录文件大小必然不变。如果删除的数据恰好使得大量页变成全空InnoDB 会把这些页放入空闲链表文件大小也可能短暂不变但后续的写入会优先使用这些空页。3.2 计算表碎片量的辅助 SQL除了看文件大小我们还可以通过 information_schema 来评估表的碎片率。SELECT table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb, ROUND(data_free / 1024 / 1024, 2) AS free_mb, ROUND(data_free / (data_length index_length) * 100, 2) AS fragment_pct FROM information_schema.tables WHERE table_schema testdb AND table_name test_delete_space;data_free 表示该表空间中被标记为空闲但尚未归还操作系统的空间大小。这个值越大说明内部碎片越多。碎片率超过 20% 的时候表重建的收益通常会比较明显。不过要提醒一点data_free 的值是一个估算值反映的是表空间级空闲页的数量不区分页的位置也不能定位到具体是哪个索引产生的碎片。对于大表我更推荐用 MySQL 8.0 之后的性能字典表或者直接看 ibd 文件大小与 information_schema 中 data_length 的对比。# 方法一看表空间文件实际占用 stat -c %s /var/lib/mysql/testdb/test_delete_space.ibd # 方法二对比逻辑数据量与文件大小的差距为什么要把逻辑大小和物理大小对比 # 因为物理大小持续不变而逻辑大小代表有效数据量这两者差距越大说明表的碎片化程度越严重也越值得做压缩回收。3.3 让空间真正释放的几个实操方案3.3.1 最直接的手段OPTIMIZE TABLE-- InnoDB 表支持在线重建 OPTIMIZE TABLE test_delete_space;这个命令的底层行为其实是重建表。它会新建一份临时表文件将原表数据按主键顺序重新插入新表然后原子切换文件完成后删除旧文件。执行过程中InnoDB 会以 ONLINE 模式运行允许并发 DML但要注意即使支持在线大表执行时负载依然很高因为重建的过程本质上是把整张表复制一遍会产生大量 IO 和 CPU 消耗。执行完后看文件大小-rw-r----- 1 mysql mysql 47M /var/lib/mysql/testdb/test_delete_space.ibd从 377M 降到 47M。在 MySQL 5.7 和 8.0 中OPTIMIZE TABLE 对于 InnoDB 表是真正的表重建不是旧版的“只整理碎片不压缩文件”。对于 MySQL 5.7 之前的老版本行为可能有所不同需要先确认版本行为。3.3.2 手工重建表ALTER TABLE ... ENGINEInnoDB不写 OPTIMIZE TABLE 而直接用 ALTER TABLE 重建很多人觉得没区别其实两者底层机制类似都是做 Online DDL。区别在于 ALTER TABLE 可以配合 ALGORITHM 和 LOCK 选项明确指定执行策略控制更细腻。ALTER TABLE test_delete_space ENGINE InnoDB, ALGORITHM INPLACE, LOCK NONE;ALGORITHMINPLACE 表示在 InnoDB 内部完成重建不需要拷贝到临时文件但要原地修改表空间。LOCKNONE 表示允许并发的读写操作。极端情况下如果表上有长时间未提交的事务LOCKNONE 也可能锁升级。如果表很小业务压力又不大用默认选项执行也是安全的。3.3.3 用 pt-online-schema-change 处理超大表如果表大于 500GB 或者业务不能接受任何微小的锁阻塞原生 OPTIMIZE TABLE 即使支持 ONLINE重启复制、主从切换、异常中断恢复等极端场景下的风险仍然存在。Percona Toolkit 的 pt-online-schema-change 是这类场景下的成熟解。pt-online-schema-change \ --alter ENGINEInnoDB \ Dtestdb,ttest_delete_space \ --host127.0.0.1 \ --userdba \ --password*** \ --chunk-size2000 \ --max-lag2 \ --critical-loadThreads_running100 \ --executept-online-schema-change 的实现思路是创建一张与原表结构相同的空表然后在原表上创建三个触发器分别捕获 INSERT、UPDATE、DELETE把增量变更同步到新表同时按照主键范围分批把原表数据拷贝到新表。拷完后在短暂锁表窗口内做表名切换。整个过程业务基本无感。用 pt-online-schema-change 要注意的一点是触发器会降低写入性能高峰时段执行需要结合 chunk-size 和 max-lag 调参。如果备库延迟严重拷贝速度会自动降下来。3.3.4 从业务层面预防分区表和归档策略再补充一个从源头解决问题的思路。对于日志表、流水表这类只关心近期数据的热数据建议在建表时直接设计成分区表按月或者按周分区。到了归档期直接 DROP PARTITION 而不是 DELETE 大量行。ALTER TABLE payment_log DROP PARTITION p202409;DROP PARTITION 是 DDL 操作直接删除整个分区对应的数据文件段空间是真正释放的不需要重建表也不存在碎片问题。代价是分区表的分区键必须满足所有查询都能通过分区裁剪定位到分区否则查询性能反而变差。而且 MySQL 的分区表在某些场景下比如包含唯一索引时分区键受限使用起来有额外约束需要在表结构设计阶段就规划好。3.4 重建表的完整执行窗口参考下面这张表是我们在生产环境多次操作后积累的参考值可以用于预估你所在环境的执行时间和影响表大小行数引擎版本模式预计耗时风险说明50GB2 亿MySQL 8.0OPTIMIZE TABLE30-60 分钟IO 负载高建议低峰执行200GB8 亿MySQL 8.0pt-online-schema-change3-5 小时期间触发 DML 有额外开销需关注复制延迟1TB40 亿MySQL 8.0分区表 分区维护分钟级单分区设计期就要规划后期改造成本高耗时与服务器磁盘类型SSD 还是机械盘、innodb_buffer_pool_size、主从复制拓扑都强相关。上面数据是 SSD 环境下 buffer pool 给到 80GB 的参考值如果硬件配置差耗时还要再翻倍。4. 常见问题与避坑实战4.1 问题速查表现象原因解决方案delete 后空间一点没变InnoDB 只标记删除不归还空间等待 purge 完成后重建表delete 后空间变小了一点点整页被清空并释放回文件系统但大部分页还有存活记录仍然需要表重建才能彻底回收OPTIMIZE TABLE 执行完后空间没变可能 purge 没完成或表本身无碎片查看 history list length确认无积压OPTIMIZE 执行中复制延迟暴涨重建表产生的大量写 IO 拖慢从库用 pt-online-schema-change 限速或低峰执行delete 大量数据后查询反而变慢碎片化导致有效记录密度降低扫描页数增加重建表或考虑分区ALTER TABLE 卡住不动有长事务持有元数据锁查 information_schema.innodb_trx 和 processlist等待或 kill 会话4.2 锁与复制延迟的排查技巧重建大表最怕的就是锁竞争。虽然 ONLINE DDL 允许并发 DML但在执行开始和结束阶段仍然需要短暂的元数据锁MDL。如果业务侧有长事务始终不提交DDL 就会卡在“Waiting for table metadata lock”状态。我处理过的一个真实案例一张 300GB 的表做 OPTIMIZE持续了两小时一直在等待元数据锁最终排查发现是一个应用连接池的连接泄漏开启了一个事务却从未提交。通过 information_schema.innodb_trx 看到 trx_started 是 20 小时前直接 kill 掉那个线程之后OPTIMIZE 才开始真正执行。复制延迟的问题更多发生在主从架构中。主库上做表重建会产生大量的写日志从库的 SQL 线程要全部重放一遍。如果用原生 OPTIMIZE TABLE这个操作在从库上也是完整重建整体耗时并不比主库短。如果你用 pt-online-schema-change需要注意 trigger 产生的 DML 会增量复制到从库对从库的压力是持续性的而不是一次性大事务。观察 replication lag 并动态调低 chunk-size 是最实用的控速方式。另一个容易忽略的点pt-online-schema-change 中途失败后清理残留表。它会创建类似_test_delete_space_new的临时表如果操作异常中断需要手动确认残留的表和触发器避免它们长期占用空间。清理之后可以重新执行。4.3 什么时候不建议收缩表空间不是所有 delete 之后都要立刻做表收缩有一些场景做收缩反而是得不偿失。比如业务上只是周期性清理数据清理完马上又会大量写入那么表里的空洞很快就会被重新填充重建表的收益维持不了几天不如等到数据量稳定后再统一收缩一次。如果是日志表本身已有归档机制直接归档后清理通常也意味着马上又会写入新数据这种场景做 OPTIMIZE 的价值就不大。空间紧张时不如扩容磁盘把精力留给真正需要重建的表。还有一种情况是数据量不大但碎片率不高比如只删了几万行在几十 GB 的表里占比可以忽略。这时候 OPTIMIZE TABLE 消耗的 IO 和空间成本比回收的碎片空间还大性价比不划算。另外特别提醒一点OPTIMIZE TABLE 或 ALTER TABLE 重建时临时表需要额外的磁盘空间来存数据副本。确保磁盘剩余空间大于原表大小否则重建到一半磁盘写满操作会回滚甚至可能触发实例磁盘告警。这算是基础设施层面最容易踩的坑我在测试环境里翻过一次车原表 180GB磁盘只剩 150GBOPTIMIZE 跑了一半直接报错幸好 InnoDB 能回滚到原状态否则就是一地鸡毛。4.4 几个独家避坑技巧第一个建议在做任何表重建操作之前先跑一遍 SHOW ENGINE INNODB STATUS看 history list length。如果这个值很高说明 purge 还没追上来此时重建表的效果不理想。可以先等它降下来或者先手动触发一次 purge方法是在业务低峰执行一个小的随机更新或 FLUSH 操作让 purge 线程唤醒。第二个建议如果目标是压缩文件系统空间请关注操作前后的 .ibd 文件大小变化而不是只盯着 information_schema 的 data_free。因为 MySQL 8.0 中 data_free 的统计有时不够实时文件大小才是二进制真值。第三个建议重建超大表时先估算需要多少临时空间再检查一下磁盘剩余空间留出的余量至少是原表大小的 50%。如果空间不足优先选择追加磁盘、迁移表空间文件或者改用分区方案不要硬扛。第四个建议监控表空间变化时我习惯在重建前记录一个基线数据包括表大小、行数、碎片率、平均行长度。重建后对比这些指标能更准确地判断本次操作是否达到预期。平均行长度可以从data_length / rows计算碎片率公式是data_free / (data_length index_length) * 100。这些数字比单纯看文件大小更能说明问题。5. 如何选择适合自己的空间回收方案先说结论如果表小于 100GB、业务允许短时间写入性能波动直接用 OPTIMIZE TABLE 或 ALTER TABLE 重建就行如果表超过 200GB 且在线业务要求不能有感知优先考虑 pt-online-schema-change 结合低峰期执行如果这类大表本来就是日志流水类最理想的选择其实是分区表从数据生命周期上让删除工作变成 DROP PARTITION。很多团队一开始建表时不会考虑数据增长和清理节奏等到表膨胀到几百 GB 才开始研究怎么回收空间。这个阶段任何方案都伴随着高昂的运维成本。对于日志型数据分区表设计带来的收益远比想象中的大不仅仅是空间回收还包括查询性能提升和归档流程简化值得在表设计阶段多花半小时规划。还有一类方案容易被忽略归档后直接停掉旧表把数据迁移到冷存储。比如把半年之前的流水从 MySQL 迁到 ClickHouse、OSS 或 Hive源表如果业务不再访问直接 DROP TABLE空间释放最彻底。这个思路的关键是数据访问模式要清晰只保留热数据在 MySQL 中冷数据走外部存储整个 MySQL 实例的体积会健康很多。如果业务模式是“保留全量数据但允许陈旧数据离线”可以考虑把大表拆成两张表活跃表只保存近期数据历史表保存全部数据。查询时通过视图或业务层路由区分。这样活跃表永远可以保持较少的体积定期重建也很快。不过这要求业务代码同步调整属于一个不小的工程改造。我个人倾向的决策流程是确认删除后碎片率是否高再判断这张表的数据增长模式是否适合重建然后结合磁盘余量、维护窗口、复制拓扑选择具体方案。盲目执行 OPTIMIZE 并不可取但因为担心锁和延迟而放任碎片膨胀同样不可取。最后再说一个我们实际用过的做法先在从库上执行 OPTIMIZE TABLE观察从库的负载和延迟正常后再在主库执行。这样如果重建过程中出现异常至少不影响主库稳定。另一个技巧是把大表的重建拆成多个批次做——用 pt-archiver 按时间范围分批 DELETE 数据每删完一个批次就记录表碎片率和文件大小等到整体删除完毕再统一做一次 OPTIMIZE这样对大事务和大锁的需求都更低。实测下来这种方式对核心库的冲击比一次性 DELETE 几千万行小得多也更容易控制节奏。