架构师丢了一句话过来“单表delete数据之后占用空间不会释放。” 当时组里几个刚接触MySQL的同事第一反应都是不至于吧我delete了这么多行data目录怎么也得小一点。结果跑到线上执行完删除再用ls -lh看一眼 ibd 文件人直接愣住了——文件大小纹丝不动磁盘告警该亮还是亮。这个现象在 MySQL 的 InnoDB 引擎下太典型了尤其是那些按日/按月做数据清理、只删不重整的业务表。写这篇东西就是想把这个“看起来不合理、实际上有完整机制”的问题彻底讲透为什么 delete 之后空间不释放、怎么判断表到底有没有碎片、真正回收空间有哪些手段以及大表清理时怎么避免把业务拖死。这篇内容适合 DBA、后端开发、运维同学看也适合那些被线上磁盘告警逼着做清理动作的人。我尽量用实操说话少讲虚的。1. 现象实录DELETE 之后表文件为什么纹丝不动1.1 一次最简单的删除实验先在自己的测试库还原一遍不然很多人不信邪。我建了一张测试表结构很简单就两个字段CREATE TABLE t_space_test ( id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(200), content VARCHAR(500), create_time DATETIME ) ENGINEInnoDB;用存储过程灌进去 1000 万行数据每行大概 200 字节左右最终t_space_test.ibd文件大小差不多 2.3GB。接着执行DELETE FROM t_space_test WHERE id 9900000;删完 990 万行之后来三部曲检查查表统计信息SELECT table_rows, data_length, data_free FROM information_schema.tables WHERE table_namet_space_test;看 OS 层文件大小ls -lh /var/lib/mysql/testdb/t_space_test.ibd执行SHOW TABLE STATUS LIKE t_space_test;结果呢表统计里table_rows变成了 10 万左右但 ibd 文件还是 2.3GBdata_free却涨到了几百 MB。这说明了一个非常关键的事实InnoDB 把腾出来的空间标记为“可复用”但是并没有把这些空间交还给操作系统。这不是 bug。这是 InnoDB 的默认行为背后有它的道理。1.2 为什么查询变快了文件却没变小很多同学在这个地方会产生第二个疑惑我删完数据之后再跑SELECT COUNT(*)或者范围查询速度很快甚至比删除前还快这不是说明空间“释放了吗”这里得区两个概念逻辑空间释放和物理空间释放。delete 之后那些数据页确实“空”出来了页内的空闲记录槽可以继续接纳新插入的行。下一次INSERT新数据时InnoDB 优先复用页内碎片空间这个过程不需要额外申请新的磁盘页所以写入和扫描的数据量变小了查询自然变快。但对操作系统来说文件还是那么大只是文件内部多了很多“空洞”。打个比方一栋 30 层的酒店所有客人退房之后房间空出来了前台说房间可以重新预定但你要问楼长“这栋楼能不能拆掉 10 层”楼长会说不行——因为楼的结构和地基还在那里。表空间文件就是这栋楼空洞就是退掉的房间文件尺寸就是楼层数。只要楼没拆楼层数永远不变。1.3 什么时候文件会真正变小唯一让 ibd 文件真正缩小的办法就是把表重建一遍。常见操作无非OPTIMIZE TABLE、ALTER TABLE ... ENGINEInnoDB或者用工具做在线表重建。重建的过程中InnoDB 会把现有数据按物理顺序重新写入到一个全新的表空间文件里旧文件在切换成功后直接废弃这时候操作系统层面的文件大小才会变小。所以“delete 之后空间会释放”这句话本身没有错但它释放的是“可以被复用的逻辑空间”不是“还给操作系统的物理空间”。很多线上磁盘告警场景你以为删了数据就能腾出磁盘结果删完告警还在就是这个道理。2. 根因分析InnoDB 的“温柔”删除机制2.1 标记删除与 MVCC 的历史包袱要理解 InnoDB 为什么不直接物理删除得先回到它的事务模型。InnoDB 支持 MVCC多版本并发控制核心机制是一行数据在事务中被修改或删除时旧版本不能立刻物理消失因为其他并发事务可能还要读到旧版本的数据。具体到 delete 操作InnoDB 做了这样几件事在聚簇索引记录上打一个删除标记delete mark记录被标记为“已删除”但物理记录还留在原页面里。把删除前的旧值写入 undo log方便回滚或者供老版本读取。后台的 purge 线程负责在合适的时机把带删除标记的记录真正清理掉清理完之后页面内会留下空洞。这个“合适的时机”要满足条件当前没有其他事务需要看到被删除行的旧版本。说白了要等所有活跃事务的最小快照点read view越过这条记录之后purge 线程才动手。所以你执行完一条DELETE它只是标记不是物理清除。哪怕 purge 线程后续把记录物理清理掉了页面的空洞依然属于 InnoDB 的“自留地”不会主动归还操作系统。2.2 页内部结构空洞是怎么产生的InnoDB 的聚簇索引以 B 树组织默认一个数据页 16KB。当页面里的记录被标记删除再被 purge 之后这个页并不会立即从表空间中移除。InnoDB 允许新插入的行使用这些空闲空间但如果表从此之后没有新的写入页就一直是“半空”状态。更棘手的是索引页。一张表往往还有二级索引DELETE 数据时二级索引记录也要同步维护。二级索引页同样会留下空洞并且索引的 B 树不会因为数据量减少就自动收缩、自动合并叶子节点。只有持续的大量删除触发页合并阈值才有可能让相邻页合并释放一个整页出来。但释放出来的整页进的是 InnoDB 内部的 free list不是操作系统的空闲空间。大量随机删除还会导致另一个问题页内的空洞碎片化。例如一个页原来有 100 条记录删掉 80 条之后理论上剩余空间能放下 80 条新记录但如果新插入的行宽度不同碎片空间利用率就会下降。极端情况下表里明明有大量空闲页却还要不断扩展文件这就是碎片导致的“表空间虚胖”。2.3 表空间高水位线原理可以这样理解 InnoDB 表空间管理表文件内部维护着一条“高水位线”这条线记录着这个表空间文件曾经扩展到的大小。InnoDB 分配新页时只要高水位线以内还有空闲页就优先在文件内部复用只有水位线以内的空间全部用完才往文件末尾追加新页也就是文件扩容。删除数据只会在水位线以内制造空洞不会降低水位线。所以表现出来就是文件大小纹丝不动内部却布满了可复用的空洞。什么时候水位线会下降只有重建表空间OPTIMIZE/ALTER或者 TRUNCATE 重新开始才会把高水位线压下去。TRUNCATE 是个特例它是直接 drop 掉旧表再建一张全新的空表所以空间能立刻释放。但 TRUNCATE 不可回滚操作前得想清楚。3. 判断表是否碎片化把账算清楚再动手3.1 用 information_schema 算碎片率先说结论不是所有表都需要做空间整理。碎片太多才值得操作。怎么量化最常用的方式是从 information_schema.tables 里读取 data_free 字段。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/1024/1024/(data_length/1024/1024 index_length/1024/1024)*100, 2) AS fragment_pct FROM information_schema.tables WHERE table_schema testdb ORDER BY free_mb DESC;data_free单位是字节含义是表空间中空闲但已分配的总字节数这基本可以反映碎片程度。碎片率 data_free / (data_length index_length)。一般来说低于 5% 意味着碎片很少超过 20% 就该考虑整理了超过 30% 基本属于重度碎片。需要注意一点information_schema.tables 里的 data_length、data_free 是优化器统计信息不一定实时。做判断之前先执行一次ANALYZE TABLE t_space_test;刷新统计信息不然数字可能是滞后的。3.2 从 OS 层面确认真实文件大小统计信息只能作为参考最真实的是看物理文件。ls -lh /var/lib/mysql/testdb/InnoDB 在开启innodb_file_per_table之后每个表一个 .ibd 文件。这个文件大小就是表空间的真实物理占用。如果这个文件大小和 data_length index_length data_free 之间存在明显差异说明碎片空间确实很多。还有一种情况需要警惕表和索引的数据量很大但data_free显示为 0。这通常意味着空闲空间都被占用了文件已经扩展到了新的高度接下来要进一步扩张此时如果磁盘剩余空间不足写操作会直接报“Table is full”。这种情况下更需要提前做扩容或者数据清理而不是等着磁盘告警。3.3 顺带看一眼 History List Length执行SHOW ENGINE INNODB STATUS\G;在 TRANSACTIONS 段落能找到History list length这个指标。这个数字反映的是 undo 日志中没有被 purge 清理的事务数量。如果这个数字持续很高比如几万甚至几十万说明 purge 线程跟不上删除速度大量被删除的旧版本还残留在 undo 表空间和表数据页里。这种情况下去做 OPTIMIZE TABLE可能出现文件缩不下去、或者 OPTIMIZE 很慢的现象。第一步应该是先观察活跃事务和长事务等长事务结束、purge 追上来之后再做整理。否则你整理出来的临时文件可能瞬间又膨胀到和原来一样大。SHOW FULL PROCESSLIST;看有没有长时间挂起的查询尤其是SELECT大事务它们的存在会阻塞 purge 进程。优先处理这些会话再评估空间整理。4. 空间怎么回收从 OPTIMIZE 到在线重建4.1 OPTIMIZE TABLE最直接的整理手段OPTIMIZE TABLE 的逻辑本质上是重建表把现有数据复制到新表空间按主键顺序重新排列然后原子替换旧文件。执行完成后空洞被消除文件尺寸变小高水位线被重置。OPTIMIZE TABLE t_space_test;它有两个前置条件要注意。第一必须开启innodb_file_per_table独立表空间模式下 OPTIMIZE 才可能收缩文件如果是共享表空间ibdata1OPTIMIZE 并不能让 ibdata1 变小。第二执行期间会消耗额外的磁盘空间因为要建一份新的表数据文件建议剩余磁盘空间保持在新表预估大小的 1.1 倍以上。MySQL 5.6 之后 InnoDB 的 OPTIMIZE 默认走 Online DDL 的 INPLACE 算法不会全程锁表但在准备阶段和提交阶段仍有短暂元数据锁。线上低峰期操作问题不大超大表则要评估耗时因为拷贝数据期间会有大量 IO 和 CPU 消耗。4.2 ALTER TABLE ENGINEInnoDB 与 Online DDL你可能会在别人家的工单里看到这种写法ALTER TABLE t_space_test ENGINEInnoDB, ALGORITHMINPLACE;这本质和 OPTIMIZE 一样都是重建表。之所以有人用 ALTER 而不是 OPTIMIZE是因为 Online DDL 提供了更细的控制选项比如ALGORITHMINPLACE明确要求原地重建ALGORITHMCOPY则退化为老式拷贝方案会全程锁表一般不建议。但 INPLACE 不是免费的。重建期间对表的并发 DML 数据会先记录到 online loginnodb_online_alter_log_max_size参数限制了这块空间的最大值默认 128MB。如果重建期间有大量写流量online log 满了之后 DML 会被阻塞。所以必须在业务低峰期做并且提前评估写频率。另外MySQL 5.7 之后 Online DDL 支持 INPLACE 重建主表但还是不支持含有全文索引的表。如果你表上建了 FULLTEXT 索引老老实实用的办法只有pt-online-schema-change或者先删全文索引、重建完再加回来。4.3 大表在线上怎么安全重建pt-online-schema-change 的取舍当表已经大到连续几个小时无法接受任何锁表/锁写风险时就需要 pt-osc 这类工具出马。核心思路是建一张影子表复制原表结构然后全量拷贝数据到影子表通过触发器把增量数据同步到影子表最后RENAME TABLE原子切换。pt-online-schema-change --alter ENGINEInnoDB \ Dtestdb,tt_space_test \ --host127.0.0.1 --userdba --passwordxxx \ --max-load Threads_running30 --critical-load Threads_running50 \ --chunk-size1000 --sleep0.5 --execute几个关键参数--alter指定重建方式也可以写DROP PRIMARY KEY之类复杂操作但这里重点是用它来重建表。--chunk-size控制每个批次拷贝多少行防止大事务。--max-load和--critical-load让工具根据当前负载自动暂停或中止避免压垮主库。--sleep每批之间的间隔单位秒帮助控制主从延迟。pt-osc 有三点硬性要求表必须有主键或唯一键否则无法做增量同步表上不能有触发器因为工具自身要创建触发器目标表不能有外键关联否则切换会有麻烦。还有一点容易踩坑pt-osc 执行期间 binlog 和 relay log 会产生大量增量事件磁盘空间也要提前预留不然工具跑一半磁盘先满了。主从复制场景中pt-osc 导致的从库延迟必须持续观察。如果 MySQL 主从延迟已经很大建议暂停工具或者调大--sleep时间让从库先追上来。4.4 归档类数据清理DELETE 不如图“分区分治”如果数据清理发生在明确的归档场景比如订单表只保留近 6 个月数据、日志表只保留近 30 天数据那按时间字段做 RANGE 分区是最优解。思路是建表时按天/按月建分区清理时直接DROP PARTITION。ALTER TABLE t_order DROP PARTITION p202501, p202502;DROP PARTITION 是物理删除分区文件空间能真正释放而且速度非常快不会像 DELETE 一样产生大量 undo log、不会拖垮复制、不会导致碎片泛滥。如果表已经存在、没建过分区那也可以考虑用pt-archiver做分批删除归档。直接跑一条大 DELETE 可能会把 undo 撑爆还会长时间持有行锁造成主从延迟放大。分批删除配合SLEEP是更稳妥的选择DELETE FROM t_space_test WHERE id BETWEEN 1 AND 10000 LIMIT 1000; SELECT SLEEP(0.1);类似思路可以在存储过程里循环执行每一批控制在一个事务内并定期COMMIT。这样既不会让 undo log 爆炸也不会长时间锁住区间主从延迟也会平滑很多。5. 实战问题与避坑指南5.1 先别急着 OPTIMIZE算清磁盘里都是谁很多线上事故都是这么来的磁盘满了某位同学一看最大的表就是它直接跑 OPTIMIZE跑到一半磁盘彻底写满数据库 hang 住。所以在做空间整理之前先系统看一遍磁盘占用账本数据文件du -sh /var/lib/mysql/*/binlogls -lh /var/lib/mysql/binlog.*undo logSHOW GLOBAL STATUS LIKE Innodb_undo_tablespaces;relay log、slow log、error logbinlog 没清理的情况下即便 OPTIMIZE 完成磁盘空间可能依然不够。我就是建议先把binlog_expire_logs_seconds或expire_logs_days调短执行PURGE BINARY LOGS BEFORE NOW() - INTERVAL 1 DAY;后再评估表的整理计划。很多人对数据库里的空间占用和普通磁盘文件清理的逻辑是混淆的——这就像系统临时文件删了但进程没退出导致空间仍被占用一样物理上文件并没有被释放。数据库表空间同理你删了行但表文件还被进程“攥在手里”要让它物理释放就必须做表重建。undo 表空间的膨胀也经常被忽略。一个长时间不提交的事务会让 undo log 疯狂增长如果 undo 是独立表空间它会占磁盘空间且很长一段时间不收缩。遇到这种场景先杀长事务比直接 OPTIMIZE 表更紧急。5.2 操作时机、备份与回滚方案表重建类操作哪怕再安全也有风险尤其是大表。我的执行前检查清单大致如下确认库/表在备份策略内重要业务表建议先做mysqldump --single-transaction或物理备份快照到其他机器。测试环境先跑一遍确认耗时线上有个预期值。选低峰期执行业务告警阈值确认过。确认主从状态SHOW SLAVE STATUS\G;或 Performance Schema 复制指标Seconds_Behind_Master/SQL 线程延迟明显偏大时不要操作。磁盘剩余空间至少为新表预估大小的 1.1 倍新表预估大小 当前 data_length index_length - 可回收空间。准备好回滚手段注意 OPTIMIZE 和 ALTER 都是重建后立即替换基本无法回滚所以必须在事前备份上做文章。5.3 常见问题速查表delete 后空间不释放的完整应对现象根本原因解决方案DELETE 后 ibd 文件大小不变标记删除 页内空洞 高水位线未降OPTIMIZE TABLE / ALTER TABLE ENGINEInnoDBOPTIMIZE 后文件也没变小表内仍有过大 data_free统计信息滞后purge 线程未追平ANALYZE TABLE 刷新统计观察 History list length等待长事务结束后再重建表很小但是 data_free 百分比极高频繁 DELETE/UPDATE 导致碎片多低峰期 OPTIMIZE 一次pt-osc 跑一半 binlog/relay log 暴涨工具产生的 DML 事件全部写进日志预留 binlog 空间调大 chunk 时间间隔必要时换 gh-ost主从延迟持续升高大事务在从库回放耗时大分批 DELETE sleep使用 pt-archiver或者改用分区 DROPDROP 大分区时内存/IO 抖动分区页过多分批执行多个 DROP PARTITION避免一次删太多分区TRUNCATE 之后空间释放了但误删数据TRUNCATE 是 DDL无法通过 binlog 恢复操作前必须物理备份/快照5.4 一个容易忽略的“假释放”场景有时候你以为 OPTIMIZE 成功了因为 information_schema 里的 data_free 归零了结果ls -lh一看文件大小根本没降。这种情况我也踩过。原因是 OPTIMIZE 只在你操作的那个时刻把空洞重新编排了但紧随其后的另一个大事务又删除了大量数据于是文件很快又出现大量空洞。或者是因为表上有定时任务在跑 DELETE优化完的空间立即被新一轮标记删除占据。所以整理空间不是一劳永逸的事关键是给药方如果业务表的数据清理是常态化的就应该在表设计阶段就把清理路径规划好。要么按时间分区定期 DROP要么控制 DELETE 的批量节奏要么建独立的归档库把旧数据搬到归档表。我在实际运维里还有一个体会碎片空间往往不是最占磁盘的元凶binlog 和 undo log 才是。机器磁盘告警时先看这些容易被忽略的部分而不是一上来就锁定某个大表跑优化。数据库占用空间的管理真正的功夫在平时的监控和设计上。建议把“表碎片率”“History list length”“binlog 保留周期”“undo 表空间大小”都放进监控面板设置好阈值碎片率超过 20% 自动提醒这样就不会在磁盘红线面前被动救火了。