上个月帮客户收拾一个订单库1.4亿行的订单表接近110GB业务方说历史订单留着没用让我清掉一半。DBA按时间分批跑DELETE每批1万行循环删到凌晨4点总算把45%的数据抹掉了。第二天早上业务方一查磁盘瞬间炸了表文件大小明明还是110GB你们到底删了个寂寞这个场景我见过太多次了几乎每个做MySQL运维的人都会在某一天被问同样的问题为什么delete删了那么多数据表文件的大小就是纹丝不动答案和网上很多文章说的碎片两个字其实不太一样真正的原因是InnoDB的空间回收机制。这篇文章我就把这个机制从头到尾拆开讲一遍顺便把表空间回收的几条实操路线、以及我在生产环境里踩过的坑都写出来。如果你是后端开发或者负责线上数据库这篇应该能帮你少走不少弯路。1. 问题现场delete后表文件为何纹丝不动1.1 一个真实的生产事故场景事情发生得很典型。那张订单表建在MySQL 5.7上engineinnodb default row_formatdynamic日积月累跑到了110GB其中60%都是三年前的老数据。业务方的需求非常明确保留最近两年的订单再早的数据全部清掉。我们评估之后决定分批DELETE脚本大概是这样的DELETE FROM order_info WHERE create_time 2022-01-01 ORDER BY id LIMIT 10000;每批删1万行批与批之间sleep 2秒避免产生过大的事务和主从延迟。跑了不到一个小时ROW_COUNT()告诉我已经删掉了接近50GB的数据。但是当我执行ls -lh /data/mysql/data/3306/trade/order_info.ibd看到的仍然是110GB一个字节都没少。业务方当场质疑是不是DELETE没用上索引或者数据没删干净其实都不是表里行数已经实实在在少了一半。这个现象的核心原因是InnoDB把DELETE设计成了标记删除而不是物理删除。之前我也见过有人用ANALYZE TABLE去刷新统计信息再去看文件大小当然还是没用因为问题根本不在统计信息这一层。1.2 你以为的没变可能只是选错了指标很多人排查这种问题时习惯只看操作系统文件大小这是最直观但也最容易误导人的一个指标。实际上要分三层来看操作系统层面du -sh、ls -lh看的是.ibd文件的物理大小这张表在没有做重建操作之前文件大小不会因为DELETE而收缩。逻辑层面information_schema.tables里的data_length和index_length代表InnoDB认为这张表当前占用了多少页。这部分在DELETE之后通常会有一定下降但如果你不走OPTIMIZE它下降的速度也远没有你想象中那么快。实际可复用空间看data_free字段它表示表空间内已经空闲但还没归还给操作系统的页集合大小。很多情况下DELETE完一半数据之后data_free会从几百MB涨到几十GB但文件大小依然不变。我后来在客户现场执行了一条SQLSELECT table_name, ROUND(data_length/1024/1024, 2) AS data_mb, ROUND(index_length/1024/1024, 2) AS idx_mb, ROUND(data_free/1024/1024, 2) AS free_mb FROM information_schema.tables WHERE table_schema trade AND table_name order_info;结果非常直观data_length确实减少了data_free涨到了30多GB但ls看到的物理文件还是原样。所以判断删除是否生效不要只看文件大小你得先搞清楚InnoDB内部把空间藏到了哪里。2. InnoDB底层原理delete到底做了什么2.1 标记删除数据页里的墓碑要理解空间为什么不被释放得先看DELETE在InnoDB内部到底做了哪几步操作。第一步把旧版本行写入undo log。这很好理解因为InnoDB有MVCC机制万一有其他事务在删除前已经读过这笔数据需要用undo log构造旧版本返回给一致性读。第二步在聚簇索引对应的数据页中把目标行的记录头部标记为已删除也就是设置一个delete flag位。这一步只是打个标记记录本身还完整地躺在数据页里。关键就在这里标记删除的记录占据的位置没有立刻清空它对于当前事务和后续事务来说已经不可见但物理上仍然占用着页内空间。我用一个生活化的类比就像合租室友说要搬走他在房间门口贴了一张已搬走的纸条但人还坐在屋里你的房租也没有因此减少一分钱。InnoDB为什么非得这样设计因为记录一旦被物理删除其他并发事务的一致性读就没法通过undo log快速定位旧版本了而且频繁的物理删除会导致B树页分裂、合并和索引重建性能开销非常大。所以标记删除后台清理是公认更稳妥的路径。2.2 页空间复用与数据文件收缩是两码事InnoDB以页为最小存储单位默认每页16KB。DELETE标记删除之后页内会留下一个空洞。当新的INSERT到来时InnoDB会优先尝试把新记录插入到这些空洞中这种机制叫空间复用。这很好能提高页的利用率减少文件增长。但问题是空间复用和数据文件收缩是两回事。一个数据页里哪怕只剩下1条有效记录另外15KB全是空洞这个页依然不会被释放。只有当一个页内所有记录都被标记删除整个页才可能从B树索引中摘除归还给表空间的空闲页列表。而且就算页归还了也不代表文件会变小。InnoDB对独立表空间文件的处理原则是只增不减文件增长靠扩展区extent通常1MB页释放后只是进入空闲列表后续插入优先分配这些空闲页。只有当文件末尾出现连续的完全空闲区时理论上才能把尾部空间裁掉但InnoDB不会自动做这种收缩操作它宁愿保持文件大小稳定也不愿意频繁扩大缩小带来性能抖动。所以你会看到DELETE之后表文件尺寸没变但表内部其实多了大量可用空位。这就是逻辑删了物理没回收的本质。2.3 聚簇索引与二级索引的连锁反应很多人只盯着主键索引忽略了二级索引。DELETE时InnoDB不仅会在聚簇索引中标记记录删除还会同步在二级索引页中标记对应的索引记录。二级索引的结构是索引列 主键值删除时同样打上delete flag。这些被打上删除标记的记录需要后台purge线程来真正清理。purge线程会根据undo log中的信息去把标记删除的记录从索引页里物理抹掉并释放对应页内的空间。如果日常DML量不大purge线程通常能跟上但如果你一口气DELETE了几千万行undo log会急剧膨胀purge线程就可能处理不过来。出现这种情况时最直观的现象是SHOW ENGINE INNODB STATUS\G里的History list length持续走高。这个值代表尚未purge掉的旧版本链长度。如果它持续在百万级别甚至更高说明purge已经严重滞后被标记删除的记录会堆积在数据页中表空间碎片率自然居高不下。所以DELETE大量数据之后空间不回收不只是文件没缩小更准确地说是标记删除的记录还没来得及被purge线程清完或者虽然有部分页被清空复用但InnoDB没有把空闲页交还给OS。2.4 为什么文件大小不会自动归还操作系统网上很多说法把问题简单归结为碎片其实碎片只是表象。真正的原因有三个第一InnoDB的独立表空间文件是按需增长的在运行期间几乎不会把空闲页返还给OS。如果每次DELETE都让文件缩小下一次的大批量INSERT又会让文件瞬间膨胀这对文件系统的连续空间分配和InnoDB的页分配策略都是灾难。第二标记删除的记录需要purge线程异步清理清理完成后页内空间进入复用状态但这些页不一定会变成文件末尾的空闲页。只要不是文件末尾连续释放操作系统层面的文件长度就无法缩减。第三InnoDB为了应对事务回滚和崩溃恢复必须保留足够的undo信息删除操作本身会产生大量redo和undo日志。这些日志空间如果没释放也会影响你对数据到底删没删干净的判断。一句话总结InnoDB牺牲了物理空间立即归还这个特性换来了写入性能的稳定和页分配的连续性。这么设计不是缺陷而是取舍。真正需要回收物理空间时你必须显式告诉InnoDB把表重建一遍。3. 表空间回收的实操方案3.1 方案一OPTIMIZE TABLE 打头阵最直接的办法就是OPTIMIZE TABLE order_info;这个操作的本质是重新组织表的数据和索引把标记删除的空洞清掉、把散落的页重新排布最后生成一个全新的表文件。执行完毕之后再去看.ibd文件大小绝大多数情况下会明显缩小。在MySQL 5.6及以上版本OPTIMIZE TABLE走的是在线DDL流程ALGORITHMINPLACE理论上允许并发DML。但我要提醒一句所谓在线不是全程无锁。在执行过程中它会在开始和结束阶段短暂获取表的MDL元数据锁如果业务高峰期正好有长事务卡着照样可能出现Waiting for table metadata lock的情况。另外一个必须重视的问题是磁盘空间。OPTIMIZE TABLE重建表时需要在数据目录生成一份临时表副本所以磁盘可用空间至少要是当前表大小的1倍以上。我用110GB的表举例你至少要保证数据目录有110GB以上的空闲否则跑到一半就可能报No space left on device那时候表还可能处于中间状态处理起来非常狼狈。还有一点执行时间。10GB以下的表几分钟能搞定上百GB的表可能要跑几小时。这期间主库的IO会明显吃紧建议安排在业务低峰期并且提前跟业务方确认可维护窗口。3.2 方案二ALTER TABLE 重建表的隐藏用法很多人不知道ALTER TABLE t ENGINEInnoDB;也能达到整理碎片、重建表的效果原理和OPTIMIZE TABLE基本一致。如果你想顺便改字符集、行格式或者某个字段类型可以一次性完成ALTER TABLE order_info ENGINEInnoDB, DEFAULT CHARSETutf8mb4, ROW_FORMATDYNAMIC;这种方式适合反正我要改结构不如顺带整理一下碎片的场景。如果只是单纯想回收空间我更推荐直接OPTIMIZE TABLE语义更清晰。另外在MySQL 8.0里ALTER TABLE ... ENGINEInnoDB和OPTIMIZE TABLE的执行计划几乎一样你不需要重复执行。但8.0有一个额外的优势DDL支持原子性失败后会自动回滚不像5.7那么让人提心吊胆。3.3 方案三在线工具 pt-osc / gh-ost 实战如果表已经大到无法承受直接重建带来的压力比如核心业务表100GB以上且7×24小时不能停写那我建议上在线工具。目前社区主流就是两个Percona Toolkit的pt-online-schema-change以及GitHub开源的gh-ost。pt-osc的做法是先创建一张影子表在原表上创建触发器捕获增量DML然后分批把原表数据拷贝到影子表最后用影子表替换原表。它的优点是成熟稳定支持MySQL 5.5/5.6/5.7/8.0但触发器会在原表上带来额外负载对有大量写入的表的性能影响比较明显。gh-ost的做法完全不同它不建触发器而是伪装成从库读取主库的binlog把增量变更在影子表上重放。这种方式对主库的侵入更小也避免了大写入场景下触发器造成的死锁问题。我在多个项目里对比过如果业务写入量很高gh-ost明显比pt-osc稳。两者都需要额外磁盘空间也都只能在低峰期做但相比直接OPTIMIZE它们能把不可用窗口压缩到秒级切换这是最大的价值。对比项pt-oscgh-ost同步方式触发器捕获DML模拟从库读binlog对主库影响触发器开销较大相对较小支持版本老版本兼容性好需要binlog_formatROW切换方式原子替换表原子替换表适用场景中小并发写高并发写、大表3.4 大表重建的磁盘空间边界计算我在生产环境见过太多人开开心心执行OPTIMIZE TABLE半小时后收到磁盘告警直接懵了。这里整理一个简单的空间预案公式当前表文件大小S需要预留空闲空间至少S × 1.2若使用pt-osc/gh-ost还要额外预留S × 0.5的binlog空间和临时文件空间举个例子表文件100GB直接OPTIMIZE需要大约120GB空闲用gh-ost的话建议空闲空间达到150GB再动。空间不够宁可不回收先用分区方案解决也不要硬着头皮去跑重建。如果数据目录在同一个磁盘上还要小心tmpdir的容量。MySQL的在线DDL在排序阶段会把临时文件放到tmpdir或innodb_tmpdir指定的目录如果那个目录空间不足也会导致重建失败。我一般习惯在建表前先执行SHOW VARIABLES LIKE innodb_tmpdir;然后确认对应目录有足够的空间再动手。4. 常见问题与排查技巧实录4.1 回收后查询变慢了先别慌现实中有一个特别讽刺的现象你费尽心思把表重建完空间回收了结果第二天业务反馈说某些查询变慢了。我一开始也遇到过研究之后发现原因主要有这么几个。第一重建表会重新计算索引统计信息优化器可能选择了一条和之前不一样的执行计划。原本走索引的查询重建后可能因为统计信息变化而走了全表扫描。解决办法是先跑一次ANALYZE TABLE order_info;让统计信息更准确再观察执行计划。第二重建表会让数据页重新排布原本在内存里缓存得很热的数据页全部失效缓存冷启动阶段查询延迟自然升高。这种情况不需要特别处理等业务跑一阵子Buffer Pool缓存热了性能就会恢复。第三极少数情况下OPTIMIZE会让索引的区分度变得和优化器预期不一致导致优化器误判。这时候可以手动用FORCE INDEX或者USE INDEX临时兜底但我个人不建议长期依赖索引提示还是应该从统计信息和索引设计上找根因。4.2 用SQL定位碎片率别再用肉眼看文件大小与其凭感觉判断该不该整理表不如用数据说话。下面的SQL可以直观看出每张表的逻辑大小和空闲空间SELECT table_schema, table_name, ROUND(data_length/1024/1024, 2) AS data_mb, ROUND(index_length/1024/1024, 2) AS idx_mb, ROUND(data_free/1024/1024, 2) AS free_mb, ROUND(data_free/(data_lengthindex_length)*100, 2) AS frag_ratio FROM information_schema.tables WHERE table_schema NOT IN (mysql,information_schema,performance_schema,sys) ORDER BY free_mb DESC;frag_ratio就是碎片率。我个人的经验阈值是碎片率超过20%且表文件超过10GB就有必要安排一次重建如果碎片率低于10%一般不用特别处理。但要强调一点data_free并不能精确等于文件末尾可收缩的空间它只是InnoDB当前可复用空闲页的一个估计值。你拿它做横向对比、判断哪些表需要优化完全够用但别指望它告诉你OPTIMIZE之后文件一定会缩小多少。4.3 删除重建的坑磁盘不足、主从延迟、事务爆炸这个组合拳我踩过的坑至少有三次每次都能写进事故报告。第一次是磁盘不足。当时一张65GB的表我满打满算预留了70GB空闲结果跑重建到一半磁盘就满了。原因是我忽略了binlog和undo也在同一块磁盘上DDL期间的日志文件迅速膨胀。自那以后我的空间预案改成了至少1.5倍。第二次是主从延迟。OPTIMIZE TABLE在主库执行主库生成的DDL会同步到从库从库也要做一样的重建。如果从库性能不如主库延迟会非常明显。100GB的表重建主库跑两个半小时从库落后了接近四个小时业务读操作全部受影响。后来我都是先确认从库的延迟容忍度或者直接在从库上操作完再切主。第三次是delete事务太大。有人为了快一次性执行DELETE FROM order_info WHERE create_time 2022-01-01;不带LIMIT结果undo log直接爆炸而且整个表被锁了几十分钟。正确的做法是分批删每批控制在一万行以内配合SLEEP()减少主从压力同时监控History list length不要持续上涨。4.4 一个冷门但实用的选项页合并阈值除了前面说的大招InnoDB还有一个被很多人忽略的参数叫MERGE_THRESHOLD它控制的是数据页使用率低于多少时InnoDB会尝试把相邻页的数据合并从而释放空闲页。默认值是50表示当页内数据量低于50%时就会考虑跟相邻页合并。如果你计划删除大量数据可以在创建表的时候就设置一个更激进的阈值比如CREATE TABLE order_info ( id BIGINT NOT NULL, create_time DATETIME NOT NULL, ... ) ENGINEInnoDB DEFAULT ROW_FORMATDYNAMIC COMMENT订单表 MERGE_THRESHOLD30;MERGE_THRESHOLD30表示页使用率低于30%才触发合并。这样可以减少合并操作的频率但代价是更早产生空间碎片。如果你的场景是大量删除后再大量插入这个参数值得玩。但是请注意页合并只影响InnoDB内部页的数量不会直接让表文件变小。所以它不能替代OPTIMIZE只能作为降低碎片率的一种辅助手段。5. 预防与长期策略别再踩删除的坑5.1 能分区就分区TRUNCATE PARTITION 才是秒回收如果让我给一条最重要的建议那就是在设计表结构的时候把数据生命周期管理考虑进去能用分区表就用分区表。按日期做RANGE分区的订单表清理历史数据根本不需要DELETE。直接ALTER TABLE order_info DROP PARTITION p2021, p2020;执行完这些分区对应的数据文件会被直接删除物理空间立刻归还操作系统不会产生几百GB的binlog不会拖垮purge线程也不用再跑一次OPTIMIZE。整个过程就像删掉一个文件而不是在一张大表里打洞。当然分区表也有代价分区键的选择需要非常谨慎如果业务查询没有带上分区键反而可能因为要扫描多个分区而变慢。所以这个方案更适合在业务模型明确、需要定期清理历史数据的场景下使用。5.2 归档代替删除很多业务之所以要删除数据不是因为数据没用而是因为访问得越来越少。这种情况下我更推荐做归档把过期数据从热表迁移到冷表或者数据仓库而不是直接DELETE。归档工具我常用pt-archiver它能一条条地把数据从源表搬到归档表同时控制批量大小和速度对在线业务影响小。比如pt-archiver \ --source hlocalhost,Dtrade,torder_info \ --dest hlocalhost,Darchive,torder_info_2021 \ --where create_time 2022-01-01 \ --limit 1000 --commit-each --sleep 0.1归档完成之后源表虽然还有大量空洞但业务数据已经安全后续再决定是OPTIMIZE还是直接DROP掉旧分区都从容得多。5.3 日常碎片监控与巡检最后一条建议把碎片率和表文件增长纳入日常巡检。不需要特别复杂的工具定时跑一遍上一节那条SQL把frag_ratio超过20%的表输出到一个监控看板就行。如果用的是Prometheus加mysqld_exporter直接在mysql_info_schema_tables相关指标里看一下data_free也可以。我自己的习惯是每周巡检一次把排名前10的高碎片表列出来和业务方确认这些表是否还存在高频写入。如果只是历史遗留的冷表就直接在周末维护窗口执行OPTIMIZE如果是核心业务表再结合在线工具分阶段处理。这样既不会让碎片问题积重难返也不会贸然在高峰期做危险操作。最后再分享一个我踩过几次坑之后才养成的习惯任何DELETE操作之前先看一眼这张表是不是分区表如果是普通表先在测试环境跑一遍同样量级的DELETE和OPTIMIZE记录文件变化和耗时再上生产。InnoDB的空间回收机制说穿了并不复杂但真正能把这套机制用明白的人都是在无数个半夜三点被业务电话叫醒之后才学会敬畏删除这两个字的。