今天聊一个数据库运维里特别常见的“诡异”现象你在 MySQL 里DELETE删掉了上千万行数据结果点开服务器一看磁盘空间几乎没变。不是删除没生效不是统计错了而是 MySQL 的 InnoDB 存储引擎在“默认情况下”不会把删除数据占用的物理文件缩小。这个现象困扰过很多刚接触数据库维护的人也经常在面试里被拿来当考点。这篇文章不讲虚的直接回答三件事为什么磁盘不释放、怎么判断空间去哪了、有哪些可行方案能把空间真正腾出来。同时会给出实际可执行的 SQL、命令和操作步骤以及每种方案的代价和坑点。先给一个结论性的速览后面逐项展开。1. 核心能力速览能力项说明问题对象MySQL 5.7 / 8.0InnoDB 引擎核心原因删数据只标记为“可复用空间”不会主动交还操作系统快速查看方式information_schema.tables查表大小、系统盘df查水位释放表空间方案OPTIMIZE TABLE、建新表替换、重建实例/迁移数据共享表空间问题ibdata1只增不减需改独立表空间或重建实例binlog 占用单独占用磁盘需按保留策略清理主要风险重建表需要额外磁盘空间、可能长时间锁表、生产环境需低峰操作适用读者数据库运维、后端开发、DBA、准备面试的工程师合规提醒删除数据和清理日志前必须确认备份、保留策略与业务授权在这个问题里最需要先理解的一点是删除数据和释放磁盘在 MySQL 里是两件不同的事。下面从 InnoDB 的存储结构开始拆解。2. MySQL 删除数据后磁盘不释放的根本原因2.1 InnoDB 表空间文件的“只增不减”特性InnoDB 的默认表空间模式是每张表一个独立表空间文件也就是表名对应的.ibd文件。当你执行DELETE FROM t WHERE ...时InnoDB 做的事情是在 B 树索引结构中把这些行标记为“已删除”并把它们所在的页面放到空闲链表中供后续INSERT和UPDATE复用。这个过程不涉及文件系统的ftruncate或fallocate收缩操作所以从操作系统角度看.ibd文件的字节数没有变化。只要文件大小不变磁盘被占用的空间就不会释放。这就是最核心的原因InnoDB 优先复用已删除行的空间而不是把物理文件缩小。2.2 删除操作在 B 树中的真实行为InnoDB 的数据存储在聚簇索引的 B 树叶子节点中每个数据页默认 16KB。删除一行数据时页面上会留出一个“洞”或者进行页内行压缩但页面本身不会被立即回收。大量删除后一个数据页可能只放了少量有效行但整个页仍然占用 16KB 空间。结果是逻辑上行数变少了物理上文件还是那么大。如果删除的数据量非常大比如一个千万行大表删掉 80% 的行那么这张表会变得“空心化”——有效数据密度很低但.ibd文件体积没有变化。此时执行查询虽然也能走索引但扫描的物理页可能比实际需要多很多。2.3 磁盘空间到底去哪了从磁盘占用的角度删完千万数据后磁盘没释放通常有三个去向表数据文件.ibd大量空洞数据页留在文件里等待复用。共享表空间ibdata1系统表空间包含数据字典、变更缓冲区、UNDO 段等历史信息默认情况下非常难收缩。binlog、错误日志、慢查询日志等其他文件删除业务数据不会清理历史 binlog如果binlog保留天数很长磁盘占用会持续增长。很多人只盯着表数据文件忽略了ibdata1和 binlog这也是排查时容易被遗漏的部分。3. 适用场景与操作边界3.1 哪些场景必须处理磁盘不释放磁盘水位已经达到 80% 以上继续写入存在风险。业务表属于日志、流水、审计等持续累积型数据删除后需要真正回收空间。数据库实例需要迁移、扩容或调整表空间布局。历史归档任务执行后发现文件系统空间没有按预期下降需要人工干预。3.2 哪些场景可以先不动删除的数据量不大表文件空洞比例很低。磁盘空间充足且后续会有大量写入来复用这些空洞。业务表是热表频繁更新删除空间很快会被循环使用。没有低峰维护窗口无法承受重建表的锁表时间。3.3 操作边界与合规提醒生产环境做任何表重建、数据清理、日志清理操作都要遵守以下边界操作前必须做备份至少要有最近的物理备份或逻辑备份。数据删除要符合业务合规和隐私要求不能随意清理未确认可删除的数据。大表 DDL 尽量在业务低峰执行并评估锁表影响。清理 binlog 前要确认从库同步进度避免主从延迟导致从库追不上。4. 环境准备与前置检查4.1 确认 MySQL 版本和 InnoDB 参数不同版本的OPTIMIZE TABLE行为和ALTER TABLE算法不一样先确认版本SELECT VERSION();再看 InnoDB 关键参数SHOW VARIABLES LIKE innodb_file_per_table; SHOW VARIABLES LIKE innodb_undo_tablespaces; SHOW VARIABLES LIKE innodb_purge_threads;innodb_file_per_tableON时表数据和索引存放在独立.ibd文件中便于单表重建释放空间。如果是 OFF数据可能堆积在ibdata1中处理起来更麻烦。4.2 确认磁盘空间和文件大小在操作系统层面先看磁盘水位df -h再看目标库的目录占用du -sh /var/lib/mysql/*登录 MySQL 后用下面 SQL 查询目标表当前占用空间SELECT 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, 2) AS total_mb FROM information_schema.tables WHERE table_schema 你的库名 AND table_name 你的表名;这个查询能快速确认表逻辑占用但要注意data_length反映的是逻辑分配大小不等于磁盘真实文件大小。磁盘真实大小以ls -lh 表名.ibd或du -h为准。4.3 预留额外磁盘空间几乎所有释放表空间的方案本质都是“重建表”而重建表的时候会生成新的数据文件。也就是说操作期间磁盘上可能同时存在旧表文件和新表文件磁盘空间占用会先上涨再下降。如果是大表建议预留至少等于该表当前大小 1 倍的额外空间。如果磁盘已经接近满先扩容或清理其他非关键文件再执行重建操作。5. 方案一使用 OPTIMIZE TABLE 重建表释放空间5.1 原理OPTIMIZE TABLE在 InnoDB 引擎上会触发一次表重建把包含大量空洞的数据复制到新表中重建索引然后删除旧文件。执行完成后.ibd文件会按照当前数据量重新分配磁盘空间自然释放。5.2 执行方式先在测试环境或从库上试运行确认执行时间和锁表情况USE 你的库名; OPTIMIZE TABLE 你的表名;在 MySQL 5.7 中OPTIMIZE TABLE会锁表在线 DDL 支持有限。MySQL 8.0 对 InnoDB 的OPTIMIZE TABLE默认使用ALGORITHMINPLACE服务可用性会好一些但依然不建议在业务高峰期执行。5.3 注意事项执行时间取决于数据量、IO 和 CPU大表可能跑几十分钟甚至数小时。操作期间需要额外的磁盘空间。如果表非常大重建过程中发生中断MySQL 回滚也可能需要较长时间。建议在从库上先执行一遍观察执行时间和磁盘波动。5.4 判断是否成功执行完成后再查询表和文件大小ls -lh /var/lib/mysql/你的库名/你的表名.ibd如果文件体积明显缩小说明空间释放成功。同时可以再次运行df -h确认磁盘水位变化。6. 方案二建新表替换旧表6.1 适用场景OPTIMIZE TABLE适合表结构不变、数据需要整体压缩重组的场景。但如果原表数据量太大或者字段结构本身需要调整更稳妥的方式是“新建一张空表分批迁移数据再替换旧表”。这个方案在数据库运维中也叫“重建迁移法”优点是可控性强可以随时监控进度缺点是操作步骤多。6.2 操作流程首先建一张新表表结构可以和原表一致也可以按需调整CREATE TABLE 你的表名_new LIKE 你的表名;然后分批插入数据。这里强烈不建议一次INSERT INTO ... SELECT * FROM ...因为大表会占用大量事务资源和磁盘 IO。推荐按主键范围分批迁移-- 第一次插入前 100 万行按主键排序取范围 INSERT IGNORE INTO 你的表名_new SELECT * FROM 你的表名 WHERE id BETWEEN 1 AND 1000000; -- 每批执行完根据实际情况调整范围比如 100 万 ~ 200 万 INSERT IGNORE INTO 你的表名_new SELECT * FROM 你的表名 WHERE id BETWEEN 1000001 AND 2000000;迁完后确认新旧表行数一致SELECT COUNT(*) FROM 你的表名; SELECT COUNT(*) FROM 你的表名_new;行数确认无误后在业务低峰把新表切换为正式表RENAME TABLE 你的表名 TO 你的表名_old, 你的表名_new TO 你的表名;最后确认业务无异常再删除旧表并释放磁盘空间DROP TABLE 你的表名_old;6.3 这个方案的优点和风险优点是可以把迁移和切换分开处理任何一步发现问题都可以回滚。风险在于整个过程中存在两张表磁盘占用会先增加切换表名的时候如果业务连接没有重连可能短暂报错。另外INSERT IGNORE会忽略主键或唯一键冲突如果你的数据本身有重复记录需要谨慎使用更稳妥的做法是先做好数据校验。7. 方案三处理 ibdata1 和 binlog 造成的空间占用7.1 ibdata1 为什么只增不减ibdata1是 InnoDB 的系统表空间文件存放数据字典、双写缓冲区、Undo 日志等。在 MySQL 5.6 之前甚至很多 5.7 的默认配置下所有表的数据都可能放进这个文件。后来默认开启innodb_file_per_table普通表有了独立.ibd但ibdata1仍然会存储一些系统级数据。最麻烦的是ibdata1内部的历史 Undo 信息和数据碎片不会被自动截断文件只会越来越大哪怕删光了所有业务数据ibdata1也可能不缩水。处理方式比较有限要么把innodb_undo_tablespaces改为独立 Undo 表空间要么直接重做整个实例数据目录。7.2 检查共享表空间占用ls -lh /var/lib/mysql/ibdata1在 MySQL 内可以确认当前是否还有表使用共享表空间SHOW VARIABLES LIKE innodb_file_per_table;如果innodb_file_per_tableOFF需要修改配置并迁移表到独立表空间。如果为 ONibdata1仍然很大主要原因通常是历史 Undo 信息和数据字典碎片普通运维阶段很难在线释放。7.3 迁移到独立表空间或重建实例从实践角度看ibdata1超过 10GB 甚至几十GB、且空间紧张时比较可靠的处理流程是使用mysqldump或mydumper先逻辑备份所有需要保留的业务数据。停止 MySQL 服务。备份或删除旧的ibdata1和日志文件。修改my.cnf确保innodb_file_per_tableON并配置独立的 Undo 表空间。初始化 MySQL 数据目录重启实例。导入备份恢复数据。这个方案比较重但能彻底解决ibdata1持续膨胀的问题。执行前一定把备份留存足够长时间并做好回滚预案。7.4 binlog 的清理binlog 占用的磁盘空间经常被忽略。先确认保留策略SHOW VARIABLES LIKE binlog_expire_logs_seconds; SHOW VARIABLES LIKE expire_logs_days;MySQL 8.0 使用binlog_expire_logs_seconds控制自动清理时间。如果磁盘告急可手动清理指定时间之前的 binlogPURGE BINARY LOGS BEFORE NOW() - INTERVAL 3 DAY;执行前先确认从库状态避免主从断档SHOW REPLICA STATUS\G SHOW SLAVE STATUS\G只有从库已经同步到对应位点才能安全清理。8. 方案四分区表与定期归档设计8.1 为什么说分区表能避免“删除不释放”如果你经常需要删除“某个时间段之前”的数据分区表是比反复DELETE更合理的方案。分区表把数据按规则切分到不同物理分区删除某一时间段的数据时只需DROP PARTITION相当于直接删除整个分区文件空间会立刻释放而不是像DELETE一样只留空洞。典型场景是日志流水表按月分区CREATE TABLE operation_log ( id BIGINT NOT NULL, log_time DATETIME NOT NULL, content TEXT, PRIMARY KEY (id, log_time) ) PARTITION BY RANGE (TO_DAYS(log_time)) ( PARTITION p202501 VALUES LESS THAN (TO_DAYS(2025-02-01)), PARTITION p202502 VALUES LESS THAN (TO_DAYS(2025-03-01)), PARTITION p202503 VALUES LESS THAN (TO_DAYS(2025-04-01)) );删除今年 1 月的数据ALTER TABLE operation_log DROP PARTITION p202501;8.2 什么时候适合引入分区表数据量持续增长且需要定期清理历史时间段数据。查询条件固定包含分区键。删除量很大且希望删除操作能快速释放磁盘。表主键里能包含分区字段或可以接受调整主键设计。分区表也有一些限制比如分区键必须包含在主键或唯一键中分区数量过多时元数据管理成本上升某些 SQL 如果无法裁剪分区反而更慢。所以分区不是万能的而是要配合查询模式设计。8.3 归档表设计如果不想改分区也可以把历史数据先搬运到归档库或归档表再从原表分批删除。归档表单独存储删除原表数据后不释放磁盘也没关系因为归档表本身可以设置更长的保留周期。常见做法是每天定时任务把 90 天前的数据复制到xxx_archive表确认无误后从原表分批删除这样原表一直保持比较小的体积归档表按月份再处理。9. 大表批量删除的通用注意事项这段内容对应标题里“删千万数据”这个动作。如果删除操作本身设计不当不仅磁盘不释放还会引发主从延迟、锁竞争、Undo 暴涨等问题。9.1 避免一次性大事务删除一次性执行DELETE FROM t WHERE create_time 2024-01-01删除上千万行会有一个巨大的事务产生海量 Undo 日志同时长时间的锁持有会让复制延迟加剧甚至撑爆 Undo 表空间。更稳妥的是分批删除每次删除固定行数事务提交后停留一下再继续DELETE FROM 你的表名 WHERE create_time 2024-01-01 LIMIT 10000;配合应用层循环调用每次删除 1 万行。也可以用存储过程或脚本来做循环执行。9.2 按主键范围分片LIMIT删除在大表上可能触发全表扫描如果配合应用层循环性能不一定好。更稳定的方式是记主键断点-- 先查最大主键 SELECT MAX(id) FROM 你的表名 WHERE create_time 2024-01-01; -- 每次删除 id 落在某个范围内例如小于断点值的 100 万行 DELETE FROM 你的表名 WHERE id BETWEEN 1 AND 1000000 AND create_time 2024-01-01;执行完一批COMMIT更新断点继续下一批。这样做的好处是每次扫描范围明确事务大小可控。9.3 观察 Undo 和磁盘变化删除过程中要监控临时表空间和 Undo 表空间增长情况。磁盘剩余空间变化。从库延迟时间。锁等待数量。如果磁盘空间在删除过程中反而上涨通常就是 Undo 表空间在膨胀。不要慌先确认innodb_undo_tablespaces配置如果 Undo 独立且配置了自动截断事务提交后空间会逐步收回如果不独立可能还是要走重建实例的方案。10. 常见问题与排查方法问题现象可能原因排查方式解决方案删除千万数据后磁盘没变化InnoDB 数据页留空洞不缩文件ls -lh *.ibd、df -h、information_schema.tables对比按数据量评估空洞率决定是否重建表OPTIMIZE TABLE执行后文件反而变大重建过程中需要额外空间或存在大量索引碎片也可能表中有大量 text/blob 字段比较执行前后的文件大小和表行数等待执行完成确认是否真正释放必要时用迁移法重建ibdata1占用巨大且不缩小共享表空间存储 Undo、数据字典碎片ls -lh ibdata1、查看innodb_file_per_table重建实例或迁移到独立表空间binlog 占满磁盘binlog 保留时间过长或从库延迟SHOW MASTER STATUS、查看 binlog 文件列表确认从库位点后PURGE BINARY LOGS BEFORE ...DELETE大事务执行缓慢单事务删除行数太多Undo 膨胀查看SHOW ENGINE INNODB STATUS的 History list length改为分批删除控制每批事务大小执行OPTIMIZE TABLE时业务卡顿表重建期间锁冲突或 IO 过高查看SHOW PROCESSLIST、监控 IO放在低峰期执行必要时用pt-online-schema-change或gh-ost控制删除后查询仍然很慢表内空洞过多扫描大量空页执行EXPLAIN检查扫描行数对比表行数和数据文件大小重建表或定期归档磁盘显示已满无法执行任何建表操作可用 inode 不足或磁盘空间不足df -h、df -i先清理 binlog、临时文件再处理表空间11. 最佳实践与日常运维建议11.1 先测后上从库先行大表重建和删除操作先在从库或测试环境执行一遍。记录执行时间、磁盘占用峰值、锁等待情况再决定生产环境操作窗口。如果团队已经有pt-online-schema-change或gh-ost这类在线改表工具经验可以优先使用它们来减少锁表影响。但工具本身也要评估不要在没验证过的环境上直接使用。11.2 建立磁盘水位和表膨胀监控每天检查磁盘使用率建议超过 75% 就触发预警。定期扫描information_schema.tables观察大表的逻辑大小变化和增长趋势。对大表单独记录.ibd文件大小防止逻辑大小和物理文件差异过大。11.3 归档清理要提前设计与其等磁盘告警再想办法不如在业务设计阶段就规划好哪些表需要保留 30 天、哪些需要保留 90 天、哪些数据需要永久归档。用定时任务 分区表或归档表的方式把清理动作自动化避免人工删除量过大。11.4 数据文件、日志文件、备份文件分目录管理把datadir、binlog、备份文件放在不同磁盘或目录可以避免某个文件增长把整个磁盘撑爆。同时也有利于单独扩容和灾备恢复。11.5 合规与安全提醒删除数据前务必确认业务授权尤其是涉及用户隐私、交易流水、日志审计的数据要有明确的保留策略和删除审批流程。清理 binlog 和日志文件时确认不影响审计需求和从库同步。测试环境的清理操作不应影响生产数据也建议不要在生产库做无备份的删除实验。12. 总结与下一步回到最初的问题MySQL 删千万数据磁盘为什么没释放答案不复杂——InnoDB 把删除空间留在表文件和 Undo 文件里等复用不会主动收缩文件真正要释放必须重建表或重建实例。所以下次遇到“删除后磁盘没变”的情况先按顺序做三件事用df -h和ls -lh *.ibd确认文件物理大小变化。查information_schema.tables估算表空洞比例。根据磁盘剩余空间和业务低峰窗口选OPTIMIZE TABLE、迁移法或分区方案。第一次验证时建议先从一张小表或测试表开始走一遍“查看大小 → 删除数据 → 重建表 → 再查看大小”的完整流程直观感受空间释放前后的差异再迁移到大表上操作。最容易踩的坑是磁盘已经快满了还直接执行OPTIMIZE TABLE结果重建过程需要两倍空间反而把磁盘写满导致实例不可用。因此重建前先确认剩余空间必要时先清 binlog 或扩容。后续如果你想进一步规范这件事可以按分区表、归档表、在线 DDL 工具、磁盘监控告警这几个方向逐步落地。数据库的空间管理和数据生命周期设计不该等磁盘红了才想起来处理。