MySQL删除数据后磁盘不释放？InnoDB表空间回收原理与实战

在实际 MySQL 运维中，删除数据之后发现磁盘空间并没有释放，是一个出现频率很高、又容易让人误判的问题。尤其当业务表里堆积了上千万行数据，你想通过 DELETE 清理过期数据，删除完成后查询 COUNT 发现行数已经明显下降，但到服务器上看数据目录，原来的 .ibd 文件大小几乎没变化。更极端的情况是，删除过程中磁盘占用反而上升。问题并不在 DELETE 语句本身，而在 InnoDB 的存储结构、事务机制和表空间的回收策略。

这篇文章会从一次典型的“删千万数据、磁盘未释放”现象出发，拆解 DELETE 在 InnoDB 中的真实执行链路，解释为什么删除会被推迟、为什么表空间文件不会自动收缩、什么情况下数据占据的空间能被复用，以及如何确认和分析磁盘占用。最后给出分批删除、归档、分区清理和在线重建表空间的方法，并提供一套可直接用于线上排查的清单。

1. 一个典型现象：千万行删除后磁盘占用几乎没变

1.1 先复现一次“删除数据、观察磁盘”的完整过程

假设有一张订单流水表order_detail，已经有超过两千万行数据。它的主键为id，同时有created_at字段来表示业务时间。在开始删除之前，先查看表当前的空间占用情况。

使用 information_schema 查询表和索引的逻辑占用：

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_length + index_length + data_free) / 1024 / 1024, 2) AS total_mb FROM information_schema.tables WHERE table_schema = 'app_db' AND table_name = 'order_detail';

同时看操作系统层面实际的表文件大小：

ls -lh /var/lib/mysql/app_db/order_detail.ibd

假设执行结果如下：

然后执行一个很常见的清理语句，删除 2024 年以前的过期流水：

DELETE FROM order_detail WHERE created_at < '2024-01-01 00:00:00';

这条语句如果匹配到约 1200 万行，在线下测试环境执行可能需要几分钟甚至更久。执行完成后再次查询，一般会看到：

行数已经明显下降，data_length和index_length也显著减小，但free_mb从 256 MB 涨到了 10 GB 左右，操作系统上的.ibd文件大小依然是 25 GB。也就是说，这 10 GB 空间并没有还给操作系统，它只是从“有效数据”变成了“表空间内部的空闲区域”。

1.2 从现象看本质：删除后被占用的空间到底去了哪里

出现这种现象，本质上有两种可能。

第一种，空间被标记为可复用。在 InnoDB 存储引擎内部，删除的数据经过一系列处理后，它们原先占用的页和记录位置会变成空闲状态。之后如果继续向这张表插入数据，InnoDB 会优先复用这些空闲页。但前提是插入的数据行能够匹配这些页的组织方式。如果新数据的写入顺序和旧数据不一致，或者旧数据分散在大量页中，这些空闲空间就不会被快速消耗掉。

第二种，删除过程中产生了额外的临时数据。比如 undo log、binlog、临时排序文件，这些文件也会占用磁盘。尤其是删除千万行这种量级的数据，undo log 会记录被删除行的旧版本，binlog 会记录完整的事务变更，磁盘占用在短时间内不降反升非常常见。

很多人在这个阶段会直接执行OPTIMIZE TABLE，却在业务高峰期把实例锁住或者把磁盘空间打满。因此，先理解 InnoDB 的删除链路，比急着回收空间更重要。

2. InnoDB 的删除链路：标记删除、后台清理与页复用

2.1 DELETE 不是物理擦除，而是两段式处理

InnoDB 使用 B+ 树组织聚簇索引，数据行最终存储在默认 16 KB 的页中。执行一条 DELETE 语句时，它不会像文件系统删除文件一样立刻把磁盘上的字节擦掉。相反，删除要经过两个阶段。

第一阶段，事务提交前，把满足条件的记录标记为删除状态。这个标记在 InnoDB 内部称为 delete-mark。被标记的记录仍然占用页面空间，仍然存在于 B+ 树中，只是对其他事务不可见。

第二阶段，事务提交后，由后台 purge 线程真正清理这些被标记的记录。purge 会遍历 undo log 中记录的历史版本，把不再被任何事务需要的行记录从页中移除，并调整 B+ 树节点。

第一阶段的“标记删除”保证了 MVCC 中其他事务仍能读到删除前的快照，第二阶段则负责把物理空间释放出来。

2.2 undo log 和 purge 线程在删除过程中扮演的角色

理解 purg 线程之前，先要理解 undo log。删除一条记录时，InnoDB 需要把这条记录删除前的信息写入 undo log。如果事务最终回滚，可以通过 undo log 把记录恢复回来；如果事务提交，undo log 中记录的旧版本还需要等待所有可能读取它的旧事务结束。

purge 线程的作用就是异步清理“已经提交且不再被引用”的旧版本数据。在 MySQL 5.7 和 8.0 中，purge 线程数量可以由参数innodb_purge_threads控制，默认值通常是 1 到 4 之间。

大量删除时，如果存在一个长期未提交的只读事务，或者存在一个非常长的历史事务，那么某个时间点之前的 undo log 无法被清理，purge 线程只能暂停在这条“历史链”上。这会造成两个结果：

• 被 delete-mark 的记录无法完成物理删除，页内空间无法释放；
• undo log 文件持续膨胀，磁盘占用进一步上升。

检查 purge 是否滞后，最常用的命令是SHOW ENGINE INNODB STATUS：

SHOW ENGINE INNODB STATUS\G

输出中重点看这一行：

History list length 26893417

当 History list length 达到百万级别时，说明 purge 已经严重落后，删除操作产生的历史版本堆积了大量空间。

2.3 为什么行删光了，.ibd 文件却不会自动变小

即使 purge 已经把记录物理删除了，.ibd 文件的大小依然不会自动缩小。原因是 InnoDB 管理表空间文件时，默认只会维护文件内部的页分配状态，不会频繁修改文件末端位置。

可以这样理解：一张表的 .ibd 文件相当于一个大的容器。DELETE 只是在容器里清出了一些空间，容器本身的边界并没有缩小。只要数据页还属于这个表空间，文件系统就认为这些空间仍然被 MySQL 占用。

真正能让 .ibd 文件缩小的操作只有几种：

• DROP TABLE，删除整张表并移除 .ibd 文件；
• TRUNCATE TABLE，在独立表空间模式下重建新的空表文件；
• 重建表，例如ALTER TABLE ... ENGINE = InnoDB、OPTIMIZE TABLE，让 InnoDB 创建新文件并复制有效数据，完成后删除旧文件。

这也是很多人删完数据发现磁盘没释放的根本原因：他们只执行了 DELETE，却没有执行任何触发“文件重建”的操作。

注意：如果你的表使用了共享表空间，也就是innodb_file_per_table=OFF，那么即使执行OPTIMIZE TABLE，操作系统上的 ibdata1 文件也可能不会收缩。数据会重新组织，但文件边界不会改变。

3. 动手前先盘清楚：当前表空间、碎片和文件大小

3.1 用 information_schema 拿到表和索引的空间占用

在删除数据或回收空间之前，先做一次空间盘点。除了第一节的简单查询，还可以把查询粒度放大到整库，快速找到占用最大、碎片最明显的表：

SELECT table_schema, table_name, engine, ROUND((data_length + index_length) / 1024 / 1024, 2) AS total_mb, 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 FROM information_schema.tables WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema', 'sys') ORDER BY total_mb DESC LIMIT 20;

这里有几个字段需要解释：

• data_length：聚簇索引占用的大小，也就是表数据主体。
• index_length：二级索引占用的大小。
• data_free：已经分配给表空间但当前未被使用，或者已经被标记为可复用的空间。

需要特别说明，table_rows是估算值，不能当作精确行数。判断行数应该使用COUNT(*)，但大表执行COUNT(*)也可能很慢，所以空间盘点阶段主要看文件大小和碎片率。

3.2 判断碎片率：data_free 与文件大小的关系

一张表是否需要整理空间，不能只看 data_free 的绝对值，而要计算它在整个表空间中的占比。常用的简化公式是：

碎片率 = data_free / (data_length + index_length + data_free)

例如data_length + index_length是 2 GB，data_free是 10 GB，那么碎片率约 83%。这说明这张表的大部分空间都已经变成空洞，有必要安排重建。

需要明确，data_free 高不代表必须立即整理。如果接下来要删除的数据量很大，先删除再重建，比先重建再删除更合理。

3.3 区分独立表空间和共享表空间，决定回收策略

MySQL 5.6 以后，独立表空间默认开启，参数是innodb_file_per_table。可以通过下面的命令确认当前实例的配置：

SHOW VARIABLES LIKE 'innodb_file_per_table';

如果业务库完全运行在独立表空间模式下，回收单表空间是相对容易的。如果是共享表空间，建议评估表迁移方案，把大表改成独立表空间，否则后续任何清理操作都会受制于 ibdata1 文件的整体状态。

还有一个容易忽略的点：分区表。独立表空间模式下，每个 InnoDB 分区通常对应一个独立的分区文件。删除整个分区的空间释放效率，远高于 DELETE 后重建整个表。

4. 删除大量数据的正确姿势：从分批删除到分区清理

4.1 一次 DELETE 千万行的问题：事务、undo 和锁

很多开发人员写删除语句时，只关心条件是否准确，却不关心删除量级。对于千万行级别的删除，一条 SQL 直接在线上执行，通常会带来四个问题。

第一，事务过大。DELETE 默认在一个事务中执行，所有行锁和 undo log 都会累积，直到事务提交才释放。

第二，undo log 膨胀。删除范围越大，需要记录的旧版本越多，undo 表空间占用越高。

第三，binlog 写入放大。主从复制环境中，binlog 会记录 DELETE 的完整影响行或者变更前镜像，千万行删除会产生非常大的 binlog 文件，还可能加重主从延迟。

第四，锁竞争。大批量删除会持有大量行锁，并且在高隔离级别下可能产生间隙锁，阻塞其他业务请求。

学习环境里一次删除几百万行可能没感觉，生产环境一次删除千万行，很容易把实例拖垮。

4.2 分批删除：按主键范围切分事务

正确做法是把一次大事务拆成多个小事务。常见思路是根据主键范围或时间范围，每次只删除几千行，提交后短暂停顿，再继续下一批。

使用存储过程实现分批删除：

DELIMITER $$ CREATE PROCEDURE batch_delete_order_detail() BEGIN DECLARE v_rows INT DEFAULT 1; DECLARE v_deadline DATETIME; SET v_deadline = '2024-01-01 00:00:00'; WHILE v_rows > 0 DO DELETE FROM order_detail WHERE created_at < v_deadline ORDER BY id LIMIT 5000; SET v_rows = ROW_COUNT(); COMMIT; DO SLEEP(0.2); END WHILE; END$$ DELIMITER ;

调用：

CALL batch_delete_order_detail();

这段逻辑的关键在于LIMIT 5000。每次事务只删除 5000 行，事务提交后锁释放、undo 可以逐步清理，主从延迟也更容易控制。

但要注意两个问题：

• ORDER BY id配合LIMIT时需要走主键排序，如果表很大，排序成本可能比较高。
• 如果删除条件不能利用索引，比如created_at上没有索引，那么即使 LIMIT 5000，MySQL 也可能扫描大量记录才能找到要删除的行。

因此，分批删除前要检查EXPLAIN SELECT的执行计划，确保条件能够命中索引。

注意：不要使用DELETE FROM t WHERE id IN (SELECT id FROM t WHERE ... LIMIT 5000)这种直接写法。MySQL 不允许在同一个表上对子查询进行修改操作，需要再包一层临时表。上面的存储过程直接使用带 LIMIT 的 DELETE 会更稳定。

4.3 归档场景：pt-archiver 和分区表方案

如果数据需要从大表搬到历史库，而不是直接物理删除，推荐使用 Percona Toolkit 中的pt-archiver。它既能分批查询、分批删除，也能把数据写入文件或另一张表。

pt-archiver \ --source h=127.0.0.1,P=3306,D=app_db,t=order_detail,u=archiver,p=xxxx \ --dest h=127.0.0.1,P=3306,D=archive_db,t=order_detail_2023,u=archiver,p=xxxx \ --where "created_at < '2024-01-01 00:00:00'" \ --limit 1000 \ --commit-each \ --bulk-delete \ --bulk-insert

各参数含义：

• --source：源表连接信息。
• --dest：目标表连接信息，可以省略，省略时只删除不归档。
• --where：筛选条件。
• --limit：每批处理行数。
• --commit-each：每批提交一次，避免长事务。
• --bulk-delete和--bulk-insert：批量删除和批量插入，提高吞吐。

如果业务表天然按时间维度访问，分区表是更彻底的长久方案。建表时按月份分区：

CREATE TABLE order_detail_part ( id BIGINT NOT NULL, created_at DATETIME NOT NULL, amount DECIMAL(10,2), PRIMARY KEY (id, created_at) ) PARTITION BY RANGE (TO_DAYS(created_at)) ( PARTITION p202301 VALUES LESS THAN (TO_DAYS('2023-02-01')), PARTITION p202302 VALUES LESS THAN (TO_DAYS('2023-03-01')), PARTITION p202303 VALUES LESS THAN (TO_DAYS('2023-04-01')) );

清理旧分区时，一条语句直接删除整个分区文件：

ALTER TABLE order_detail_part DROP PARTITION p202301;

在独立表空间模式下，这个操作会直接释放对应的分区文件，磁盘空间立刻可见。相比 DELETE 再 OPTIMIZE，效率高出很多。

4.4 这几种方案对磁盘空间的影响差异

5. 删除后真正回收磁盘的方法与风险控制

5.1 重建表：ALTER TABLE ENGINE=InnoDB 和 OPTIMIZE TABLE 的关系

DELETE 之后，data_free 增大，但文件大小不变。要让文件真正收缩，核心动作是重建表。

重建表的基本逻辑是：创建一张新表，按顺序读取旧表有效数据，写入新表，让聚簇索引重新排列，最后用新文件替换旧文件。这样，旧文件中的空洞和碎片全部消失，data_free 会明显下降。

最常用的命令：

ALTER TABLE order_detail ENGINE = InnoDB, ALGORITHM = INPLACE, LOCK = NONE;

在 MySQL 5.7 和 8.0 中，OPTIMIZE TABLE对 InnoDB 表来说本质上也是重建表，同时伴随一次索引统计更新：

OPTIMIZE TABLE order_detail;

两者的共同点是都要复制或移动数据，都需要额外磁盘空间。不同点在于ALTER TABLE ENGINE=InnoDB可以显式指定ALGORITHM和LOCK，对执行方式和并发限制有更细的控制。

执行后，可以再次检查文件大小：

ls -lh /var/lib/mysql/app_db/order_detail.ibd

正常情况下，文件大小会接近information_schema.tables中的data_length + index_length加上少量额外开销。

5.2 8.0 在线 DDL：INPLACE 与 INSTANT 带来的变化

MySQL 8.0 对在线 DDL 的支持比 5.x 更完善，理解三个关键词有助于选择命令。

• COPY：最传统的方式，拷贝整表数据到临时表，期间只读，不允许并发写入。空间和耗时都比较大。
• INPLACE：原地重建，可以在重建过程中允许并发 DML。LOCK=NONE表示不阻塞读写。
• INSTANT：只修改元数据，几乎瞬间完成。适用于加列等操作，但不适用于删除大量数据后的表空间收缩。

对需要收缩碎片的场景，一般使用ALGORITHM=INPLACE, LOCK=NONE。但要注意，即使声明了LOCK=NONE，在重建过程的某些阶段仍然可能短暂加元数据锁，所以业务高峰期建议不做。

MySQL 8.0 中还有一个参数值得关注：

SHOW VARIABLES LIKE 'innodb_online_alter_log_max_size';

默认通常是 128 MB。在线 DDL 过程中，如果并发写入过多，超过这个大小，DDL 可能会失败或者退化为更保守的模式。大量删除后的重建表操作，如果业务还在持续写入，建议提前调大这个值，比如 512 MB，并在完成后评估是否改回。

5.3 回收过程中需要的额外空间和时间

重建表不是零成本操作。在独立表空间模式下，InnoDB 重建表时，旧文件通常不会立即删除，而是在新文件完成后才替换。因此，磁盘上需要同时容纳旧表空间和新表空间的临时文件。

如果你现有文件 25 GB，重建过程可能还需要额外 25 GB 左右的空间。这个空间不够，会直接导致操作失败，甚至因为磁盘写满影响整个实例。

因此，执行重建表前，务必检查：

df -h /var/lib/mysql

剩余空间至少要大于需要重建表的当前文件大小。如果剩余空间不足，先清理备份、binlog 或扩容，再执行。

同时，重建表期间会产生大量 IO，对主从环境要观察从库延迟。如果是超大表，建议使用pt-online-schema-change或者把操作放到维护窗口。

注意：生产环境执行ALTER TABLE ... ALGORITHM=INPLACE, LOCK=NONE时，仍要预留足够的磁盘空间、评估 IO 压力，并在低峰期操作。不要因为看到“在线”两个字就在高峰期直接执行。

5.4 什么时候应该直接 DROP 或 TRUNCATE

删除数据和回收空间不一定要分开做。如果业务已经不再需要使用这张表和里面的任何数据，DROP TABLE是最高效的方式。它会直接删除表定义、索引数据和 .ibd 文件，磁盘空间立即释放。

如果表结构还需要保留，但表内数据全部都不需要，那么TRUNCATE TABLE是更好的选择。InnoDB 在独立表空间下执行 TRUNCATE 时，会通过重建表的方式快速清空数据，并且释放原来的文件。

对比一下：

6. 如何排查磁盘未释放问题：从检查命令到根因确认

6.1 逐层检查：文件大小、表空间统计、历史链表、undo

遇到“删完数据磁盘没释放”的现象，建议按照下面顺序排查。

第一层，确认行数和文件大小。先确认数据真的删掉了，再看文件大小有没有变化。

SELECT COUNT(*) FROM order_detail WHERE created_at < '2024-01-01 00:00:00';

ls -lh /var/lib/mysql/app_db/order_detail.ibd

第二层，查表空间统计，看 data_free 是否增加。

SHOW TABLE STATUS LIKE 'order_detail';

第三层，查 history list length，确认 purge 是否滞后。

SHOW ENGINE INNODB STATUS\G

第四层，查是否有长事务持有旧读视图，阻塞 purge。

SELECT trx_id, trx_started, trx_state, trx_rows_modified, trx_query FROM information_schema.innodb_trx ORDER BY trx_started;

第五层，查 undo 表空间大小和 binlog 目录：

ls -lh /var/lib/mysql/undo_001 ls -lh /var/lib/mysql/binlog.*

6.2 一个排查案例：删除了两千万行，空间反而多了

有一次，某个业务表执行完一次两千万行的 DELETE 后，开发反馈磁盘可用空间不降反升。接到问题后，先执行df -h，发现磁盘确实比删除前少了几个 GB。

接下来看表空间统计，发现.ibd文件大小没有变化，但data_free从几百 MB 涨到了接近 10 GB。这说明删除本身已经产生了大量空洞，文件内容被“腾空”但没缩小。

然后看SHOW ENGINE INNODB STATUS，History list length 到了百万级别，说明 purge 线程被卡住了。再看information_schema.innodb_trx，发现一个长期运行的只读事务，查询语句一直在扫描历史数据，它持有的旧视图导致大批 undo log 无法清理。

于是历史版本和 undo 占用的空间不断累积，再加上 binlog 写入，磁盘占用就上升了。处理方法是先确认这个只读事务是否还需要运行，确认后终止阻塞事务，等 purge 逐步清理历史链，再在低峰期重建表空间。

这个案例说明一个问题：删除大表数据时，磁盘是否释放不是只看 DELETE 本身，还要考虑检测底层机制是否有阻塞。

6.3 常见坑速查表

6.4 发布前检查清单和预防建议

如果你的项目即将上线一个“大表清理”任务，建议按下面的清单逐项检查。

• 确认表的存储引擎是 InnoDB，innodb_file_per_table已开启。
• 确认删除条件能够走索引，使用EXPLAIN SELECT事先查看。
• 评估待删除行数和表文件大小，计算对 data_free 的影响。
• 检查是否有长事务运行，确认 undo 和 purge 不会阻塞。
• 选择合理的删除方式：分批 DELETE、归档、DROP PARTITION 或 TRUNCATE。
• 删除完成后，观察 data_free 和 .ibd 文件大小，确认是否需要重建。
• 重建表前，检查磁盘剩余空间是否足够容纳新文件。
• 记录 binlog 大小、主从延迟和实例负载，操作后对比。
• 执行前准备备份或回滚方案，生产环境避免没有退路地直接操作。

从长期看，对于有明显时间维度的表，分区是最有效的预防手段。对于必须保留大量历史数据的业务，用归档表存档，让主表始终保持相对紧凑，比“删除后再整理碎片”的被动模式更可靠。

MySQL 删除数据的空间回收机制，核心就在“标记删除、后台清理、文件不自动收缩”这三点。遇到磁盘未释放的问题，不要急着执行优化命令，先查文件大小、data_free 和 History list length，定位问题是在 purge、碎片还是文件重建层面，再决定下一步操作。