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假设执行结果如下项目删除前data_mb20480index_mb5120free_mb256.ibd 文件大小25 GB 左右然后执行一个很常见的清理语句删除 2024 年以前的过期流水DELETE FROM order_detail WHERE created_at 2024-01-01 00:00:00;这条语句如果匹配到约 1200 万行在线下测试环境执行可能需要几分钟甚至更久。执行完成后再次查询一般会看到项目删除后data_mb8192index_mb2048free_mb10240.ibd 文件大小25 GB 左右行数已经明显下降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 STATUSSHOW 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_tableOFF那么即使执行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 GBdata_free是 10 GB那么碎片率约 83%。这说明这张表的大部分空间都已经变成空洞有必要安排重建。data_free 占比说明建议小于 5%表空间相对紧凑不需要处理5% 到 30%有一定碎片删除过数据但可复用观察后续插入是否复用空间大于 30%碎片明显文件空洞多低峰期安排ALTER TABLE ENGINEInnoDB或OPTIMIZE TABLE需要明确data_free 高不代表必须立即整理。如果接下来要删除的数据量很大先删除再重建比先重建再删除更合理。3.3 区分独立表空间和共享表空间决定回收策略MySQL 5.6 以后独立表空间默认开启参数是innodb_file_per_table。可以通过下面的命令确认当前实例的配置SHOW VARIABLES LIKE innodb_file_per_table;表空间类型文件位置DROP/TRUNCATE 后文件是否释放删除后空间回收方式独立表空间库名/表名.ibd会释放OPTIMIZE TABLE或重建表共享表空间ibdata1不释放需要导出导入或重建整个实例如果业务库完全运行在独立表空间模式下回收单表空间是相对容易的。如果是共享表空间建议评估表迁移方案把大表改成独立表空间否则后续任何清理操作都会受制于 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 5000MySQL 也可能扫描大量记录才能找到要删除的行。因此分批删除前要检查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 h127.0.0.1,P3306,Dapp_db,torder_detail,uarchiver,pxxxx \ --dest h127.0.0.1,P3306,Darchive_db,torder_detail_2023,uarchiver,pxxxx \ --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 这几种方案对磁盘空间的影响差异清理方式事务大小磁盘释放速度undo/binlog 压力适用场景一次 DELETE 千万行极大不释放碎片增多极大不推荐分批 DELETE小不释放碎片增多较小少量过期数据清理DROP PARTITION小立即释放分区文件很小分区表历史归档TRUNCATE TABLE极小立即释放旧文件很小清空全表DROP TABLE极小立即释放文件很小删除整表5. 删除后真正回收磁盘的方法与风险控制5.1 重建表ALTER TABLE ENGINEInnoDB 和 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 ENGINEInnoDB可以显式指定ALGORITHM和LOCK对执行方式和并发限制有更细的控制。执行后可以再次检查文件大小ls -lh /var/lib/mysql/app_db/order_detail.ibd正常情况下文件大小会接近information_schema.tables中的data_length index_length加上少量额外开销。5.2 8.0 在线 DDLINPLACE 与 INSTANT 带来的变化MySQL 8.0 对在线 DDL 的支持比 5.x 更完善理解三个关键词有助于选择命令。COPY最传统的方式拷贝整表数据到临时表期间只读不允许并发写入。空间和耗时都比较大。INPLACE原地重建可以在重建过程中允许并发 DML。LOCKNONE表示不阻塞读写。INSTANT只修改元数据几乎瞬间完成。适用于加列等操作但不适用于删除大量数据后的表空间收缩。对需要收缩碎片的场景一般使用ALGORITHMINPLACE, LOCKNONE。但要注意即使声明了LOCKNONE在重建过程的某些阶段仍然可能短暂加元数据锁所以业务高峰期建议不做。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 ... ALGORITHMINPLACE, LOCKNONE时仍要预留足够的磁盘空间、评估 IO 压力并在低峰期操作。不要因为看到“在线”两个字就在高峰期直接执行。5.4 什么时候应该直接 DROP 或 TRUNCATE删除数据和回收空间不一定要分开做。如果业务已经不再需要使用这张表和里面的任何数据DROP TABLE是最高效的方式。它会直接删除表定义、索引数据和 .ibd 文件磁盘空间立即释放。如果表结构还需要保留但表内数据全部都不需要那么TRUNCATE TABLE是更好的选择。InnoDB 在独立表空间下执行 TRUNCATE 时会通过重建表的方式快速清空数据并且释放原来的文件。对比一下操作行为文件释放建议DELETE FROM t逐行标记删除不释放少量数据清理DELETE FROM t 分批多事务分批删除不释放大批量数据清理TRUNCATE TABLE t重建空表释放清空全表DROP TABLE t删除整表释放表不再需要ALTER TABLE t ENGINEInnoDB重建表释放删除后整理碎片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 STATUSHistory list length 到了百万级别说明 purge 线程被卡住了。再看information_schema.innodb_trx发现一个长期运行的只读事务查询语句一直在扫描历史数据它持有的旧视图导致大批 undo log 无法清理。于是历史版本和 undo 占用的空间不断累积再加上 binlog 写入磁盘占用就上升了。处理方法是先确认这个只读事务是否还需要运行确认后终止阻塞事务等 purge 逐步清理历史链再在低峰期重建表空间。这个案例说明一个问题删除大表数据时磁盘是否释放不是只看 DELETE 本身还要考虑检测底层机制是否有阻塞。6.3 常见坑速查表常见坑现象原因处理方法一次 DELETE 千万行磁盘占用不降反升主从延迟大事务、undo/binlog 膨胀、锁竞争分批删除控制每批行数删除后没做重建表data_free 高文件不缩小DELETE 只释放页内空间不缩小文件ALTER TABLE ENGINEInnoDB或OPTIMIZE TABLE磁盘剩余空间不足就重建rebuild 失败甚至磁盘写满重建过程需要旧表加新表两份空间先清理或扩容预留空间使用DELETE清空全表耗时很久碎片仍存DELETE 逐行清理不重建文件用TRUNCATE TABLE代替在共享表空间下回收操作后 ibdata1 仍然很大共享表空间不按表释放文件迁移为独立表空间或全实例导数据删除时 purge 被长事务阻塞History list length 很高undo 很大长事务持有旧读视图等待或终止长事务观察 purge 恢复6.4 发布前检查清单和预防建议如果你的项目即将上线一个“大表清理”任务建议按下面的清单逐项检查。确认表的存储引擎是 InnoDBinnodb_file_per_table已开启。确认删除条件能够走索引使用EXPLAIN SELECT事先查看。评估待删除行数和表文件大小计算对 data_free 的影响。检查是否有长事务运行确认 undo 和 purge 不会阻塞。选择合理的删除方式分批 DELETE、归档、DROP PARTITION 或 TRUNCATE。删除完成后观察 data_free 和 .ibd 文件大小确认是否需要重建。重建表前检查磁盘剩余空间是否足够容纳新文件。记录 binlog 大小、主从延迟和实例负载操作后对比。执行前准备备份或回滚方案生产环境避免没有退路地直接操作。从长期看对于有明显时间维度的表分区是最有效的预防手段。对于必须保留大量历史数据的业务用归档表存档让主表始终保持相对紧凑比“删除后再整理碎片”的被动模式更可靠。MySQL 删除数据的空间回收机制核心就在“标记删除、后台清理、文件不自动收缩”这三点。遇到磁盘未释放的问题不要急着执行优化命令先查文件大小、data_free 和 History list length定位问题是在 purge、碎片还是文件重建层面再决定下一步操作。