接手一套业务库的时候我遇到过这么个情况order_pay_record订单流水表占了 1.2TB业务侧说历史数据已经删了三个月明明去掉了几百GB的旧记录可磁盘空间不但没释放还在缓慢上涨。ls -lh看那张表的.ibd文件大小几乎没变。如果你也遇到过同样的场景说明你正在踩 InnoDB 表空间只增不减的经典坑。这篇文章不打算讲官方文档里那些定义而是把我在生产环境整理大表空间时真正踩过的坑、反复确认过的原理、以及最终跑通的几个方案一次性讲清楚。适合正在为磁盘告警头疼的 DBA也适合后端同学在遇到删了数据空间不降时知道该往哪个方向排查。1. 空间只涨不跌背后是 InnoDB 的页面复用机制1.1 delete 只是打标记页和文件并不归还很多人第一次发现DELETE FROM 大表 WHERE ...执行完、磁盘空间没变化时第一反应是命令没生效或者MySQL 有 bug。其实都不是这是 InnoDB 的存储机制决定的。InnoDB 的数据是按 B 树组织的树的每个节点就是一个数据页默认 16KB。页里存放一行一行的记录。当你执行 DELETE 时InnoDB 并不会真的把那一页上的记录物理抹掉而是在记录头打一个deleted_flag标记表示这条记录已删除。这个位置后续如果有新记录插入而且键值范围落在同一页InnoDB 会优先复用这块空间。问题在于如果删除的是一个很大的历史区间比如订单流水按时间删除三个月前的数据而这些数据在索引层面是连续分布的大片区间那么在相当长一段时间里这个区间内不会再有新数据插入。结果就是页被清空了一部分但文件系统看这个.ibd文件那部分空间依然被 MySQL 占着没有归还给操作系统。这里要区分两件事页内空间是否能复用和表文件是否收缩。前者是 InnoDB 内部空间复用机制后者是文件系统层面的空间释放。DELETE 只影响前者而且只影响未来的写入对文件大小没有任何作用。你删得再多.ibd文件只会原地不动甚至因为碎片和后续写入继续变大。1.2 两个最容易忽视的空间黑洞共享表空间与 undo 日志单表空间不释放已经很头疼更隐蔽的是另外两个地方。第一个是共享表空间ibdata1。如果你在初始化 MySQL 时没有开启innodb_file_per_tableON那么所有 InnoDB 表的数据都会放在这个共享文件里。这种情况下你哪怕重建这一张表也只能让它在共享表空间里的那部分数据重写一遍ibdata1文件本身大概率不会变小。很多老库是从 MySQL 5.5/5.6 时代一路升级来的当时默认就是共享表空间导致这个文件动辄几十GB甚至上百GB。要回收它最彻底的办法是把所有 InnoDB 表逻辑导出、重新初始化实例、再导入工作量和风险都非常大。第二个是 undo 日志。大量 DELETE 会产生大量 undo log记录数据被删除之前的样子供事务回滚和 MVCC 多版本读取使用。如果有一个长事务一直不提交或者某个会话的 read view 还停留在很早的时间点那些被标记删除的旧版本就一直无法 purgeundo 表空间MySQL 5.7 独立出来或ibdata1中的 undo 区域就会持续膨胀。你明明只整理一张表结果发现实例整体磁盘空间还在涨往往就是这个原因。1.3 你要整理的是表还是实例所以在动手之前先问自己一个问题我现在要做的是单表空间整理还是实例空间整理如果是单表文件收缩前提一定是innodb_file_per_tableON且现网基本都是这个默认值。这种情况下你需要的是让表重建一次。如果是实例整体空间不足还要去看ibdata1、undo 表空间、binlog、临时表空间ibtmp1各自占了多少。很多人在 8.0 里发现磁盘快满了跑了一堆OPTIMIZE TABLE也没用其实是因为空间被 binlog 或 undo 吃掉了方向和手段完全不对。我习惯用一句话给业务解释这个机制DELETE 只是给房间贴了售罄标签并不是退租MySQL 要等未来的客人住进去才发现这房间还能用。你想让房东把空间退回来只有一个办法——把整栋楼拆了重新盖。 拆楼重盖就是下面要讲的各种重建表方案。2. 动手前先把空间账和风险账算清楚2.1 用 information_schema 找出最值得整理的表整理空间不是拿起OPTIMIZE TABLE就干。先查清楚哪些表真正占空间、哪些表只是碎片率高。我一般用下面这条 SQL 做第一轮扫描SELECT table_schema, table_name, ROUND(data_length / 1024 / 1024, 2) AS data_mb, ROUND(index_length / 1024 / 1024, 2) AS index_mb, ROUND((data_length index_length) / 1024 / 1024 / 1024, 2) AS total_gb, table_rows, data_free FROM information_schema.tables WHERE table_schema NOT IN (mysql, information_schema, performance_schema, sys) ORDER BY (data_length index_length) DESC LIMIT 30;这张表里data_length是数据部分占用index_length是二级索引占用data_free是分配了但空闲的空间也就是碎片。注意table_rows只是估算值千万别拿它当精确行数用。如果一张表data_free特别大比如占到了data_length的 20% 以上说明这个表被频繁增删改过重建后空间收缩的效果会非常明显。反过来如果表数据量不大但索引很多空间主要被index_length吃掉了那要优化的是索引策略而不是单纯整理空间。2.2 物理文件核对与碎片率计算information_schema里的统计信息不一定实时它的数据来自后台统计线程可能滞后。所以做重要操作前我会再核对物理文件ls -lh /var/lib/mysql/your_db/order_pay_record.ibd du -sh /var/lib/mysql/your_db/再看表状态SHOW TABLE STATUS FROM your_db LIKE order_pay_record\G重点看Data_free和Data_length。Data_free / Data_length就是碎片率。如果碎片率高重建表后文件能明显缩小碎片率低重建表更多是压缩页空洞收益可能不大。这里有个实操细节SHOW TABLE STATUS里的行数、Data_free 也不是每次都精确建议先执行一次ANALYZE TABLE再观察得到的结果相对靠谱。2.3 预判风险清单在触发任何整理动作之前我会把下面这张表过一遍每项都对号入座。这是多年被坑出来的习惯。检查项怎么查不查的后果磁盘剩余空间df -h重建到一半磁盘写满生产直接故障表是否有外键依赖information_schema.key_column_usage切换表名后外键关系断裂应用报错表上是否有触发器SHOW TRIGGERS LIKE order_pay_record搬运数据时触发器重复执行数据翻倍是否分区表SHOW CREATE TABLE里看PARTITION关键字对分区表全表重建浪费时间还没意义业务可中断窗口和业务确认在线 DDL 再快也有锁窗口长事务冲突主从复制状态SHOW SLAVE STATUS\G看Seconds_Behind_Master从库延迟越拖越大查询出现明显滞后binlog 保留时长SHOW VARIABLES LIKE binlog_expire_logs_seconds重建产生海量 binlog把磁盘再次打爆这张表过完基本就能判断能不能停服能停多久是单表重建还是只能在线工具后面所有方案选择都建立在这些答案之上。3. 选对重建姿势从 OPTIMIZE 到 gh-ost 的适用边界3.1 OPTIMIZE/ALTER 重建的极限最直接的整理空间命令就是OPTIMIZE TABLE order_pay_record;MySQL 5.7 之后的 InnoDB 对OPTIMIZE TABLE已经支持在线执行底层其实是执行了一次ALTER TABLE ... ENGINEInnoDB重建表。你也可以显式写ALTER TABLE order_pay_record ENGINEInnoDB, ALGORITHMINPLACE, LOCKNONE;这种方式会把整张表的数据重新写到新的表空间文件里B 树重新排列页内空洞消失然后删除旧文件。所以空间才会真正释放。但它有很明显的适用边界表文件越大重建时间越长期间需要的额外磁盘空间越大。我的经验是单表在 50GB 以下、业务允许在低峰期接受一定 IO 压力和短暂锁等待的情况下直接用OPTIMIZE TABLE最简单。超过 100GB就要仔细权衡了。而且要注意即使LOCKNONEDDL 在执行前需要拿元数据锁MDL在开始和结束阶段也会有短暂的锁表窗口。如果表上正好跑着一个大查询DDL 可能一直等在那边看起来像卡死。MySQL 8.0 还引入了LOCK_WAIT_TIMEOUT但实际场景里我很少依赖它更习惯在低峰期操作。3.2 新表 分批拷贝 RENAME 切换如果业务可以停写一小段时间但表又非常大、不想让OPTIMIZE TABLE在线上跑几个小时我经常用新建表 分批迁移 原子切换的方式。基本思路CREATE TABLE order_pay_record_new LIKE order_pay_record;按主键范围或时间范围分批把需要保留的数据从旧表 insert 到新表。校验新表数据完整性。停写窗口内执行RENAME TABLE order_pay_record TO order_pay_record_bak, order_pay_record_new TO order_pay_record;确认无异常后DROP TABLE order_pay_record_bak;这个方案的好处是完全可控每一批迁移多少数据、批与批之间 sleep 多久、压力大时随时暂停全凭自己掌握。坏处是如果表在迁移期间还在持续写入新表就永远追不上最新数据切换会丢数据。所以要么选一个业务低峰甚至停写窗口要么还是用专业在线工具。3.3 pt-osc 与 gh-ost在线工具的取舍如果表是几百GB甚至上TB业务 7×24 小时写入不能停那就要用在线表结构变更工具。最主流的是 Percona Toolkit 里的pt-online-schema-change以及 GitHub 开源的gh-ost。pt-osc的原理是在原表上创建一个影子表同时创建三个触发器INSERT/UPDATE/DELETE把原表的增量变更同步到影子表等数据搬完通过原子RENAME切换。它依赖触发器原表写入并发高时触发器开销会被放大。gh-ost的原理不同不创建触发器而是解析 binlog 中的行级变更把增量同步到影子表。优点是对原库侵入小也支持暂停、限速、流量控制。代价是要求 binlog 必须开启且格式为ROW复制链路上也要保证log_slave_updates开启。我用gh-ost跑过一张 800GB 的表核心参数大约是gh-ost \ --host127.0.0.1 \ --useradmin \ --passwordxxx \ --databaseyour_db \ --tableorder_pay_record \ --alterENGINEInnoDB \ --chunk-size1000 \ --max-lag-millis3000 \ --max-loadThreads_running50 \ --initially-drop-ghost-table \ --ok-drop-ghost-table \ --execute--chunk-size控制每批处理的行数--max-lag-millis控制主从延迟阈值--max-load控制压力。整理空间时--alterENGINEInnoDB就够了它会重建表、收缩文件效果等同于优化表。3.4 分区表可以直接切分区如果你的表已经按时间做了 RANGE 分区整理空间根本不需要重建整表。直接删掉历史分区即可ALTER TABLE order_pay_record DROP PARTITION p2024_01, p2024_02;每个分区对应独立的表空间文件开启innodb_file_per_table后DROP PARTITION会直接删除对应文件空间立即释放速度极快而且不会产生大量 undo 和碎片。这也是我为什么一直强调大表设计阶段就要考虑分区的原因。很多空间整理问题在设计层面加一个分区键就能从源头上避免。4. 一次真实的大表重建从方案到切换的操作链路4.1 案例背景之前有一张pay_flow表大小 280GB保留全部流水 12 个月。业务提了个需求只保留最近 3 个月其余 9 个月历史数据删掉。按数据量估算删除后逻辑数据大约剩 60GB但直接 DELETE 的话表文件不会缩小还容易造成大事务、主从延迟、undo 膨胀。我当时的方案不是 DELETE而是重建保留表把需要保留的 3 个月数据搬到新表然后切换。这样既删除了历史数据又实现了表空间收缩一步到位。4.2 准备阶段先确认磁盘df -h看数据盘剩余空间。因为重建过程旧表 280GB 还在、新表最终 60GB 也要占空间至少需要额外 80-100GB 可用空间。我习惯预留目标表大小的 1.5 倍避免意外。然后确认表结构看有没有外键和触发器SHOW CREATE TABLE pay_flow\G确认没有外键引用但有 1 个 INSERT 触发器后面搬运时要注意避免它被触发可以先在会话里关闭sql_log_bin不行触发器还是要处理最稳妥是让 DBA 和业务确认触发器的作用必要时临时禁用或重建到新表。接着创建新表CREATE TABLE pay_flow_new LIKE pay_flow;LIKE会复制表结构包括索引、自增属性但不会复制数据也不会复制外键如果有外键LIKE不会带过来所以要在切换前手动加回来。4.3 分批搬数据迁移命令大概长这样。先按主键找到最大最小值然后每隔一段区间搬一批for (( start0; start200000000; start50000 )) do mysql -h127.0.0.1 -uxxx -pxxx your_db -e INSERT INTO pay_flow_new SELECT * FROM pay_flow WHERE id ${start} AND id $((start50000)) AND create_time 2025-04-01; sleep 2 done注意这里有三个关键点一是必须用主键范围做分片不要用LIMIT一条条分页搬。LIMIT offset越到后面越慢因为要扫描前面所有行。用主键范围每批都是索引定位速度稳定。二是每批行数不要贪多。我一般控制在 1 万到 5 万行看单行平均长度。pay_flow单行不超过 1KB5 万行也就是 50MB 的事务大小binlog 和 undo 都还能接受。如果事务太大从库回放压力会剧增。三是批与批之间加sleep给主库 IO 和从库复制一个喘息窗口。对 280GB 的大表整个迁移大概需要几个小时这个时间成本必须有心理准备。4.4 收尾切换与空间释放迁移结束前先做几个校验SELECT COUNT(*) FROM pay_flow; SELECT COUNT(*) FROM pay_flow_new; SELECT MAX(id), MAX(create_time) FROM pay_flow_new; SELECT SUM(amount) FROM pay_flow WHERE create_time 2025-04-01; SELECT SUM(amount) FROM pay_flow_new;加一个ANALYZE TABLE pay_flow_new;让统计信息就位避免切换后优化器拿错误的行数估算执行计划。然后进入停写窗口。业务方确认暂停写入后一条命令完成切换RENAME TABLE pay_flow TO pay_flow_bak, pay_flow_new TO pay_flow;RENAME TABLE是原子操作执行瞬间完成对业务影响极小。切换后不要急着删旧表先让业务跑几分钟确认查询正常、写入正常再DROP TABLE pay_flow_bak;这一步执行完280GB 的旧文件才真正从磁盘上消失。此时再看df -h空间会非常直观地降下来。4.5 重建后必做的事切换完成不等于结束。我每次还会做三件事重新执行ANALYZE TABLE pay_flow;新表虽然是LIKE建的但统计信息还是空的。检查慢查询日志看有没有原本依赖旧表统计信息的 SQL 突然走错索引。检查从库延迟和磁盘空间确认大事务已经回放完成。这三件事做完整个整理流程才算真正闭环。5. 在线整理最容易翻车的四个软问题5.1 元数据锁长事务会卡死整个 DDL即使 InnoDB 支持在线 DDLMySQL 在做任何 DDL 前仍然要拿元数据锁MDL。如果表上有一个跑了几小时的大查询或者一个一直没提交的事务DDL 就会卡在Waiting for table metadata lock状态。这个坑我见过太多次明明用的是没有锁表的在线工具结果 DDL 一执行后续所有对这个表的读写全部阻塞应用连接池被打满。所以无论是pt-osc还是gh-ost执行前我都会先查SHOW PROCESSLIST;重点看有没有Sleep状态很久的会话、有没有Waiting for table metadata lock以及有没有长时间运行的 SELECT。把这些清理掉再启动工具。另外可以设置一个锁等待超时兜底SET SESSION lock_wait_timeout 30;5.2 磁盘空间整理动作可能比你想象更吃空间重建表的本质是把数据再写一份。无论哪种方案在旧表没删除之前新表的数据文件、重建产生的临时文件都同时占用磁盘。OPTIMIZE TABLE和ALTER TABLE在重建过程中也需要额外的临时空间。很多事故发生在磁盘使用率已经 85% 的时候DBA 想当然整理一下空间就能降下来结果整理过程需要额外空间直接把磁盘撑到 100%数据库直接 Hang 住。我的建议是磁盘使用率超过 75% 时不要贸然触发大表整理。先扩容或者先清理 binlog、慢查询日志、无用备份给整理动作留出空间。MySQL 8.0 里还可以把临时文件目录放到独立磁盘SET GLOBAL innodb_tmpdir /data/mysql_tmp;数据库进程对/data/mysql_tmp要有写权限否则重建会直接报错。5.3 日志与主从延迟大表重建会产生海量 binlog。新表数据每 insert 一批就会生成一批 binlog从库要拉取这些 binlog 并回放如果从库配置不如主库延迟很容易飙到几千秒。处理手段有三个控制工具参数--chunk-size调小一点--max-lag-millis设成 2000-3000ms提前看从库能力如果从库 IO 本身就很差建议先升级从库配置再执行操作时间选在业务低峰期比如凌晨 2-5 点给自己留足够的延迟追赶时间。而且要注意binlog 不是主库生成完就删它要等从库消费完、且达到保留策略才会清理。如果从库延迟太严重主库 binlog 积压磁盘可能再次报警。这是一根绳上的蚂蚱整理表之前一定要把从库状态看好。5.4 外键和触发器这些影子对象手动做新表 RENAME 切换最容易被坑的就是外键和触发器。如果一张表被其他表外键引用直接 RENAME 回原表名MySQL 在打开外键检查时不会自动帮你把引用关系指到新表。轻则应用报错重则后续写入因为外键约束失败。所以操作前要查SELECT TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_TABLE_NAME, REFERENCED_COLUMN_NAME FROM information_schema.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_NAME pay_flow;有外键的情况下要么切换后手动重建外键要么考虑用能自动处理外键的在线工具但即便工具我也建议先确认不要全信自动化。触发器更隐蔽。如果你用INSERT INTO new_table SELECT ... FROM old_table这种方式搬数据原表上的INSERT触发器并不会在新表上生效。但如果新表结构里有自增、有默认值或者业务依赖原表触发器产生的行为切换后行为就变了。所以CREATE TABLE ... LIKE建完新表后要记得SHOW TRIGGERS核对并在新表上重建需要保留的触发器。6. 空间整理的终局思路归档策略与根因预防6.1 能分区就别裸奔时间分区表的正确打开方式整理空间的最高境界是让整理这个动作变成一条 SQL 就能完成的日常操作。如果订单表从一开始就按月份做 RANGE 分区保留 3 个月数据就是ALTER TABLE order_pay_record DROP PARTITION p2025_01, p2025_02;分区文件直接删除空间立即释放不需要重建不会产生碎片从库延迟也低。相比在普通表上 DELETE 几亿行这个操作的成本几乎可以忽略。建分区表的姿势大概是这样CREATE TABLE order_pay_record ( id BIGINT NOT NULL AUTO_INCREMENT, create_time DATETIME NOT NULL, ... PRIMARY KEY (id, create_time) ) PARTITION BY RANGE (TO_DAYS(create_time)) ( PARTITION p2025_04 VALUES LESS THAN (TO_DAYS(2025-05-01)), PARTITION p2025_05 VALUES LESS THAN (TO_DAYS(2025-06-01)), PARTITION p2025_06 VALUES LESS THAN (TO_DAYS(2025-07-01)), PARTITION pmax VALUES LESS THAN MAXVALUE );注意分区表要求分区键必须包含在主键或唯一键里所以上面主键要带上create_time。这也是很多老表想改成分区表时最头疼的问题往往需要重建表来调整主键结构又是一番折腾。6.2 大批量清理用小步慢跑如果表因为各种历史原因已经没法分区且只能 DELETE 清理旧数据那就别一条 DELETE 删几亿行。原因很简单一个超大的 DELETE 事务产生的 undo log 可能把磁盘写满事务回滚段无限膨胀主从复制也会被拖垮。正确做法是分批删除while true; do mysql -h127.0.0.1 -uxxx -pxxx your_db -e DELETE FROM pay_flow WHERE create_time 2025-04-01 ORDER BY id LIMIT 5000; sleep 1 done每批删 5000 行左右事务短小锁范围小undo 能及时回收binlog 也不会产生单个巨大的事务。有需要的话也可以用 Percona 的pt-archiver工具它可以按主键范围小批量删除还能控制延迟和速率。但这种清理方式只能让逻辑数据减少.ibd文件依然是不会缩小的。它只是把碎片空间留给未来复用。如果业务上已经确认那些历史区间再也不会写入那么 DELETE 后最终还是要搭配一次表重建才能真正释放文件空间。6.3 监控与巡检让空间问题提前暴露空间整理最怕的就是等磁盘告警了才动手。我现在的习惯是每周自动跑一次空间巡检把这个查询写进 crontabSELECT table_schema, table_name, ROUND((data_length index_length) / 1024 / 1024 / 1024, 2) AS total_gb, ROUND(data_free / 1024 / 1024 / 1024, 2) AS frag_gb, ROUND(data_free / (data_length 1) * 100, 2) AS frag_ratio FROM information_schema.tables WHERE table_schema NOT IN (mysql, information_schema, performance_schema, sys) ORDER BY data_free DESC LIMIT 20;碎片率如果连续两周上涨或者某个表文件大小逼近磁盘容量阈值就提前规划整理窗口。不要等盘满了再处理那时候往往已经没有重建表所需要的空间只能先删别的东西腾地方非常被动。另外监控脚本里一定要加上对 binlog 目录、undo 表空间、临时表空间的检查。很多时候MySQL 数据目录越来越大根本不是业务表涨了而是ibdata1或者ibtmp1在偷偷膨胀。我见过一个实例业务表总共不到 200GBibtmp1却吃掉了 300GB 磁盘起因是频繁的大排序和大临时表查询。这个问题靠重建业务表永远解决不了。6.4 终极预防冷热分离表空间整理的频率往往和系统的数据架构设计成反比。表设计得好根本不需要频繁整理设计得不好DBA 就得反复给业务擦屁股。冷热分离是我给所有流水型大表业务的第一建议。在线库只保留热数据最近 3-6 个月历史数据定期归档到另一套只读库、分析库或者对象存储通过一个归档任务每天/每月自动搬运。这样在线表永远维持在一个可控的大小空间整理从数月的头疼事变成每月例行小事。冷热分离不一定要迁移到别的数据库。哪怕是同一套 MySQL 实例把历史数据挪到单独的历史库也会让主表的 B 树层级降下来查询性能提升空间维护成本大幅下降。回到最开始那张 1.2TB 的订单流水表。最后我们把在线库调整为保留 3 个月历史数据按月归档到独立的历史库原表重建后收缩到 60GB 左右。之后每个月只需对历史库做一次分区维护没有再出现过磁盘报警。我个人现在的习惯是接到任何MySQL 空间不足的问题第一件事不是去 OPTIMIZE而是先问自己三个问题——这个表还在持续写入吗业务能不能接受一个短暂的停写窗口表能不能改成按时间分区这三个问题想清楚了方案基本就出来了。整理空间本身不复杂复杂的是在整理过程中不伤害线上业务。这也是为什么我强烈建议不管用什么方案都要先在测试环境用同结构、同量级数据跑一遍全流程等自己心里有底了再碰生产库。