MySQL 删了一千万行数据结果服务器磁盘占用率一点没降甚至业务还卡了一下。这个问题做后端开发和运维的人迟早会遇到。很多人第一反应是数据都删干净了空间怎么还不给我吐出来但如果你理解 InnoDB 的存储方式就会知道这并不奇怪甚至可以说这才是默认情况。本文要讲的就是为什么 MySQL 删除大量数据后磁盘不释放该怎么查怎么真正把空间拿回来以及千万级数据删除应该怎么安全地做。先说结论DELETE 只是把数据从逻辑上标记为删除物理文件不会自动缩小。想让磁盘空间真正释放必须触发一次表重建。下面把原因、验证方式、操作方法和避坑点全部拆开讲。1. 删了千万行不代表数据库已经把空间还给了操作系统1.1 DELETE 只是把行标记成“可用”InnoDB 是 MySQL 默认的存储引擎数据最终落在表空间文件里。执行 DELETE 的时候InnoDB 做的事情并不是把文件里的字节直接抹掉而是把 B 树中对应的记录标记为删除状态把这条记录所在的页放到空闲列表里方便后续 INSERT 复用同时记录事务日志和回滚段信息保证事务可以回滚。换句话说你“删掉”的那些数据还在原来的物理文件里躺着只是从索引结构上不再对外可见。只要表空间文件没有被重建文件占用的磁盘空间就不会还给操作系统。这个逻辑很多人第一次遇到时很难接受但它是理解整篇文章的基础。你看着信息里面 row 没了但ls -lh一看.ibd文件还是老样子甚至data_free列比删除前多了不少都是正常现象。1.2 空间能不能还回去取决于表空间的物理组织方式MySQL 里 InnoDB 的表空间有两种主要组织方式共享表空间系统表空间所有表的数据都可能落在同一个ibdata1文件里独立表空间file-per-table每个表对应一个单独的.ibd文件。innodb_file_per_table参数控制这两种模式。MySQL 5.6.6 之后默认是 ON新创建的表默认走独立表空间。如果开启了独立表空间表重建之后这个表的.ibd文件会变小磁盘空间能真正释放。如果表还在共享表空间里情况会更麻烦即使你对这个表做 OPTIMIZE也只是把数据在ibdata1内部重新排列ibdata1这个文件本身不会自动收缩因为里面还存着表数据字典、回滚段、undo 等其他内容。表空间模式删除大量数据后如何释放磁盘file-per-tableON表文件大小基本不变文件内部产生碎片OPTIMIZE TABLE 或重建表file-per-tableOFF所有表共用ibdata1删除后归还更困难迁移数据到独立表空间或重建实例所以你在动手之前第一件事不是直接执行删除而是先确认自己到底处在哪种模式下。2. 先确认表空间模式、数据组成和真实碎片率2.1 第一步查 innodb_file_per_table 是否开启在 MySQL 命令行里执行SHOW VARIABLES LIKE innodb_file_per_table;输出结果是ON说明新表都是独立表空间。但已经存在的表不一定就是你想要的模式。如果之前的 DBA 曾经关闭过这个参数老表可能已经落在ibdata1里面了后面新建的表才是独立表空间。想确认某个具体表是不是独立表空间可以直接去看它的物理文件ls -lh /var/lib/mysql/your_db/your_table.ibd有对应的.ibd文件说明它是独立表空间。没有的话就要考虑它可能还留在共享表空间里。2.2 第二步用 information_schema 拿到数据量、索引量和碎片量不要凭感觉估计表有多大直接查information_schema.TABLESSELECT table_schema, 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, table_rows FROM information_schema.TABLES WHERE table_schema your_db AND table_name big_table;几个字段的含义需要先讲清data_length聚簇索引数据页占用的字节数可以近似看成表数据大小index_length二级索引占用的字节数data_free表空间里已经分配给该表、但当前没有被有效数据使用的空间table_rows估算行数InnoDB 不会每次死记精确行数这个值只能当参考。如果你的data_free相对data_length已经非常可观比如删除前表数据 100 GBdata_free到了 60 GB说明表内部碎片很严重。这种状态下查询变慢、插入性能下降、磁盘空间白白被占都是正常的。2.3 第三步对照操作系统里的 ibd 文件大小SQL 层面的信息只能说明“表内空间使用情况”最终你关心的是操作系统上的磁盘。执行下面几个命令ls -lh /var/lib/mysql/your_db/your_table.ibd du -h /var/lib/mysql/your_db/your_table.ibd df -h /var/lib/mysql注意ls看到的是文件逻辑大小du看到的是实际占用磁盘块的大小。多数情况下两者接近但文件稀疏或者刚经过某些特殊操作时会有差异。排查问题时两个都看一眼更稳。如果df -h显示分区剩余空间很小同时这个.ibd文件非常大那么你删除后磁盘没释放的问题大概率就是表文件没有重建导致的。不要一上来往磁盘坏道、磁盘被写保护这类方向猜先把数据库本身查清楚。3. 真正释放磁盘的四种常规操作3.1 OPTIMIZE TABLE最直接的整表重建重建表是释放 InnoDB 磁盘空间最常用的方式。它会把表的数据复制到一个新表空间然后替换旧文件同时重新整理索引消除碎片。OPTIMIZE TABLE your_db.big_table;执行完之后观察返回信息。通常情况下会出现optimize对应的ok状态。如果输出里带着recreate analyze这类字样说明 MySQL 实际选择了重建表的方式不用太过紧张这是正常执行路径之一。OPTIMIZE TABLE 有几个前置条件和代价需要额外磁盘空间大概和表当前大小相当执行期间会产生较高的 IO 和 CPU 压力大表重建耗时可能非常长不要在业务高峰期直接执行执行前确认主从架构下从库是否有足够空间和处理能力。我一般会先看data_free和表大小如果表只有 2 GB碎片也不多直接 OPTIMIZE 没问题。如果表已经 500 GB就要考虑下面提到的替代方案或者选择低峰期分批处理。3.2 ALTER TABLE ENGINEInnoDB重建表空间在 InnoDB 表上执行ALTER TABLE your_db.big_table ENGINE InnoDB;效果上也是重建表。MySQL 会重新组织数据和索引旧表空间文件会被替换文件大小会缩小。有些环境下OPTIMIZE TABLE会因为索引类型、版本或特殊配置出现提示用ALTER TABLE ... ENGINE InnoDB反而更直接。你也可以把它理解成“强制重建”的另一种写法。MySQL 5.6 之后 InnoDB 的 DDL 支持 Online DDL 机制不少情况下可以避免长时间锁表但重建期间仍然有额外磁盘 IO 和临时空间消耗。这里不要被“在线”这个词误导它只是说允许并发的 DML 操作不代表不占空间、不耗时间。3.3 新建表 分批搬迁可控性更高的手工重建对于超大表直接 OPTIMIZE 可能不现实因为执行时间不可控中途失败恢复也麻烦。更可控的做法是手工建新表把数据分成多批搬过去CREATE TABLE big_table_new LIKE big_table;然后分批插入INSERT INTO big_table_new SELECT * FROM big_table WHERE id BETWEEN 1 AND 100000;一批批推进每批之间可以停顿几秒避免一直占满 IO。全部搬完以后核对行数再执行重命名RENAME TABLE big_table TO big_table_old, big_table_new TO big_table; DROP TABLE big_table_old;这种方式的优点是每一步你都能控制批大小、停顿时间、完成进度都清楚。缺点是整个过程如果有新的写入需要考虑数据一致性问题。常见做法是在低峰期申请短暂停写窗口或者用工具来完成在线无感迁移。如果不想自己写脚本可以用 Percona Toolkit 里的 pt-online-schema-change 一类工具。它能在不锁表的情况下重建表并同步增量数据。工具不是银弹但处理几十 GB 到几百 GB 的表时比手工搬迁更省心。3.4 TRUNCATE 和 DROP适合清空或归档场景如果这张表已经不需要保留数据比如只是临时表、缓存表、或者数据已经导到别处直接用 TRUNCATE 或 DROP 更干净。TRUNCATE TABLE 在独立表空间下会删除原来的表文件并重新创建一个几乎为空的表文件空间会立刻释放。它和 DELETE 有本质区别TRUNCATE 是 DDL不是 DML不能按条件删除只能清空整表执行后 AUTO_INCREMENT 会重置执行过程中表会被锁住回滚成本极高在有外键引用的情况下可能无法执行。DROP TABLE 则直接把表文件删掉空间释放最快。但它连表结构都没了通常用于归档完成后的清理或者迁移后的旧表清理。注意TRUNCATE 虽然快但它的空间释放效果取决于表空间模式。如果表还在共享ibdata1里TRUNCATE 释放的也只是ibdata1内部的空闲空间文件本身不一定缩小。操作删除范围锁表情况空间释放适用场景DELETE条件删除行锁/事务控制几乎不释放文件大小业务数据删除OPTIMIZE TABLE重建整个表低峰期释放碎片空间表已删除大量数据TRUNCATE清空整表DDL 锁独立表空间下释放临时表/缓存表DROP删除整表DDL 锁全部释放归档后清理4. 千万行数据操作不能一把梭4.1 分批删除要控制圈定范围很多人删除千万行时习惯写一条大 DELETEDELETE FROM big_table WHERE created_at 2023-01-01;这条语句看起来很直接但实际执行时可能会长时间持有大量行锁产生巨大的 undo 日志binlog 写入量暴涨主从复制延迟升高占满磁盘空间或临时空间。更稳的方法是分批删。常见写法是DELETE t FROM big_table AS t JOIN ( SELECT id FROM big_table WHERE created_at 2023-01-01 ORDER BY id LIMIT 2000 ) AS b ON t.id b.id;每次只删 2000 行循环执行。批大小可以从 1000 起步观察磁盘、CPU、锁等待和主从延迟后再调整。如果业务压力大批与批之间加一个短暂停顿比如执行SELECT SLEEP(0.2);给数据库一个喘气窗口。这里有个容易踩的坑如果WHERE条件上的字段没有索引每批都要全表扫描删得越多越慢。所以删除前先确认筛选字段上有合适的索引比如created_at字段。索引要建但不要指望一条过滤条件能覆盖所有场景实际还是要看执行计划。4.2 监控锁等待和主从延迟删除千万行数据不只是“把语句跑完”的事。你要同时盯着几个指标SHOW PROCESSLIST看当前会话状态是不是updating有没有waiting for table metadata lockSHOW ENGINE INNODB STATUS看锁信息、事务状态主从架构下看从库延迟时间一般能通过SHOW SLAVE STATUS查Seconds_Behind_Master字段系统层面关注磁盘 IO 使用率、CPU 负载、剩余空间。如果发现锁等待明显或从库延迟持续上涨先停掉后续批次等数据库恢复平稳再继续。不要抱着“反正语句已经发出去跑完就好”的心态硬等大表操作最忌讳失去控制。4.3 磁盘空间紧张时的操作顺序如果磁盘已经告急了快速释放空间的正确逻辑不是立刻做 OPTIMIZE而是先找出占用最大的项。优先按这个顺序排查binlog 日志直接看数据目录下binlog.*文件大小过期的可以用PURGE BINARY LOGS BEFORE ...清理undo 表空间长期大事务可能导致 undo 文件膨胀MySQL 8.0 有自动 truncate 机制但未必能及时回收慢查询日志、错误日志日志文件被截断过吗有没有因为没开启日志轮转导致一个文件几十 GB临时文件tmpdir或innodb_temp_tablespaces_dir下是否残留大量下载到一半的临时文件大表本身的 .ibd 文件确认它到底占了多少判断是否值得重建。磁盘满了以后OPTIMIZE TABLE 基本没法执行因为它本身就需要额外空间。这时候可以先清理 binlog 和日志买出来一点空间再规划表重建。5. 执行之后怎么确认空间真的释放了5.1 三个核心指标DATA_FREE、ibd 文件大小、df 剩余空间重建完表以后不要只看“没报错”就算成功。我一般会检查三个东西重新查information_schema.TABLES看data_free是否明显下降用ls -lh /var/lib/mysql/your_db/your_table.ibd看文件大小是否变小用df -h /var/lib/mysql看整个分区剩余空间是否增加。这三个结果都正常才能说空间真正释放了。如果做的是手工搬迁重建搬迁完成后最好再执行一次ANALYZE TABLE your_db.big_table;目的是更新优化器统计信息避免执行计划因为旧统计信息选错索引。5.2 为什么有时执行完空间还是没有立刻变化空间没变化通常有几种情况。第一种是表还在共享表空间里。前面说过ibdata1不会因为你 rebuild 一张表就自动缩小。这种情况要确认所有业务表都改成独立表空间然后把数据迁移到新表或新实例最终重建整个实例才能彻底解决。第二种是执行过程中有其他文件同步变大。比如你白天做 OPTIMIZE同时 binlog 写得很猛那么df看到的剩余空间可能不升反降。这时候把 binlog 生命周期列出来看看时间点是否和你的操作重合。第三种是高水位问题。.ibd文件重新创建后InnoDB 会按一定初始大小扩容。如果你刚重建完还没写入多少数据文件可能本身就小。但如果随后立刻有大量并发写入文件又会扩展。所以判断空间释放要以“重建结束后的稳定状态”为准不要重建后半小时内就下结论。5.3 常见报错和对应的排查顺序我见过不少同学在大表删除或重建时遇到问题第一反应是“工具不行”或者“MySQL 坏了”。实际上大多数问题出在权限、依赖、输入、参数和资源这几个层面。现象优先排查方向OPTIMIZE 执行很慢确认表大小、磁盘 IO、是否和其他大查询冲突执行时报磁盘满先清理 binlog、undo、临时文件再重试锁等待超时检查业务高峰期是否同时在写这张表主从延迟暴涨暂停批任务观察从库 IO 线程和 SQL 线程状态重建后行数对不上先核对 WHERE 条件的边界再看是否有并发写报错提示找不到表或文件检查数据库实例节点、触发器、外键和路径权限核心排查顺序永远是先看现象再看输入和条件然后看环境和参数最后才怀疑工具本身。不要一上来就把生产表的 OPTIMIZE 操作挂在一台空间不足的实例上硬跑。6. 防止大表删除变成日常事故6.1 按时间范围分区淘汰数据直接删分区如果一张表的核心查询经常按时间过滤而且业务允许按时间分段分区表是很值得考虑的方向。比如按年份做 RANGE 分区ALTER TABLE big_table PARTITION BY RANGE (YEAR(created_at)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023), PARTITION p2023 VALUES LESS THAN MAXVALUE );后面想删掉 2020 年之前的数据直接ALTER TABLE big_table DROP PARTITION p2020;这比 DELETE 快得多而且分区删除后独立表空间下的文件空间释放也相对干净。注意分区表不能所有场景都无脑用分区键必须和查询条件匹配否则分区反而增加复杂度。6.2 冷热数据分离和归档表不要把一张表无限期放大。生产环境最怕的不是删除慢而是压根没有删除和归档策略。常见做法是在业务表旁边建一张归档表结构保持一致每月或每季度把超过保留期的数据搬到归档表业务表继续保持轻量归档表按季度或年份分区后续直接删分区。搬迁同样要分批可以先CREATE TABLE big_table_archive LIKE big_table;然后按主键范围或时间范围把数据搬过去搬完核对行数再从主表分批删除。这个过程不需要停机但需要脚本和监控配合。6.3 删除动作要写进运维规范个人经验里大表删除最危险的地方不是磁盘没释放而是操作前没人记录操作中没人监控操作后没人验证。建议在运维规范里固定这几条任何超过百万行的 DELETE必须提前确认条件、索引和预计影响行数执行前记录表大小、索引大小、data_free、剩余磁盘空间分批删除的脚本必须包含日志输出每批完成后打印删除行数和当前状态操作期间连接数、锁等待、主从延迟必须持续监控操作结束后对比重建前各项指标验证空间、性能和统计信息。平时把这些流程做好了遇到问题才不会手忙脚乱。真到磁盘告急再去研究 MySQL 文件怎么收缩成本要比平时高很多。最后说一句MySQL 删除大量数据后磁盘没释放不是功能缺陷而是存储引擎的物理机制决定的。你只需要理解它然后选择合适的方式触发表重建。小表直接 OPTIMIZE大表分批重建生产环境提前做好归档和分区。这套思路明确了以后再看到千万行级别的数据删除任务心里就有底了。