MySQL DELETE 后磁盘空间为什么没释放？InnoDB 空间回收完整指南

清理日志表时，经常会遇到一个看似反常的现象：明明已经 DELETE 了大量数据，查询也查不到了，但磁盘上的 .ibd 文件几乎没有变小。执行 OPTIMIZE TABLE 后，MySQL 又提示：

Table does not support optimize, doing recreate + analyze instead

更让人困惑的是，Linux 上 ls 看到文件已经缩小，information_schema.TABLES 里的 DATA_LENGTH、INDEX_LENGTH、DATA_FREE 却还停留在原来的数值。

这些现象其实都符合 InnoDB 的设计。理解它们的关键，是区分两类“释放空间”：

DELETE 通常释放的是 InnoDB 可以再次利用的空间，而不是 立即归还给操作系统的磁盘空间。

本文以 MySQL 8.0 和最常见的 InnoDB 表为背景，把删除、表空间、OPTIMIZE TABLE 和空间统计一次讲清楚。

占位符说明：下文中的 <database_name>、<table_name> 和 <deletion_condition> 都不是实际对象名。执行示例前，请分别替换为自己的数据库名、表名和删除条件。

一、DELETE 之后到底发生了什么

假设我们删除一批历史记录：

DELETE FROM <database_name>.<table_name>
WHERE <deletion_condition>;

提交事务后，这些记录对新的查询已经不可见。不过在 InnoDB 内部，空间回收并不是简单地从文件末尾剪掉一段内容。

InnoDB 使用页和 B+Tree 组织数据。表的行数据存放在聚簇索引中，二级索引又有各自的 B+Tree。删除记录后，相关位置会逐渐变成可复用空间；受 MVCC 影响，旧版本还可能要等到不再被任何事务需要时，才由 purge 机制清理。

InnoDB 独立表空间、段、区、页与 B+Tree 数据存储结构

图 1：聚簇索引的叶子页保存完整行，二级索引的叶子页保存“索引键 + 主键”；二级索引查询可能需要再回到聚簇索引取完整行。

即使 purge 已经完成，空出来的页面通常也只是留在表空间内部，供这张表后续的插入或更新复用。.ibd 文件已经向操作系统申请到的长度，一般不会因此自动缩小。

InnoDB 执行 DELETE 后从 MVCC 可见性、purge 到空间复用和 OPTIMIZE 的影响时序

图 2：COMMIT、purge 和磁盘文件缩小是三个不同时间点。查询已经看不到记录，不代表旧版本已经清理；purge 完成也不代表 .ibd 已经缩短。

可以把它想成一座仓库：货物搬走了，仓库里有了空位，但仓库建筑本身还在。空位可以重新放货，并不等于土地已经退回。

二、什么时候空间能真正归还操作系统

常见操作的效果可以这样理解：

操作	数据结果	独立 `.ibd` 是否通常缩小	适用场景
`DELETE ... WHERE ...`	删除部分数据	否	按条件清理，可使用事务
`DELETE FROM table`	删除全部数据	否	需要逐行删除语义时
`TRUNCATE TABLE`	清空整表	是	全量清空，不保留行
`DROP TABLE`	删除表及数据	是	表本身也不再需要
`OPTIMIZE TABLE`	保留剩余数据并重建	通常是	大量删除后压缩独立表空间

但这里有一个前提：表必须位于 file-per-table 独立表空间，也就是拥有自己的 .ibd 文件。

MySQL 8.0 默认开启 innodb_file_per_table，新建 InnoDB 表通常会获得独立表空间。不过，这个变量只影响表创建时的默认选择，不能证明历史表也一定是独立表空间。表也可能位于 InnoDB 系统表空间或 general 通用共享表空间。

在共享表空间中，删除或重建产生的空闲空间一般只能留给 InnoDB 内部复用，共享文件不会因为某一张表变小。MySQL 官方对 TRUNCATE TABLE 的空间回收也明确区分了这两种情况。

三、先确认表空间类型和文件路径

先检查默认设置：

SHOW VARIABLES LIKE 'innodb_file_per_table';
SHOW VARIABLES LIKE 'datadir';

再检查具体的表，而不是只看当前全局配置：

SELECT
    innodb_tables.NAME AS table_name,
    innodb_tablespaces.SPACE_TYPE AS tablespace_type,
    innodb_datafiles.PATH AS recorded_path,
    CASE
        WHEN innodb_datafiles.PATH LIKE './%'
            THEN CONCAT(@@datadir, SUBSTRING(innodb_datafiles.PATH, 3))
        ELSE innodb_datafiles.PATH
    END AS absolute_path,
    innodb_tablespaces.FILE_SIZE,
    innodb_tablespaces.ALLOCATED_SIZE
FROM information_schema.INNODB_TABLES AS innodb_tables
JOIN information_schema.INNODB_TABLESPACES AS innodb_tablespaces
    ON innodb_tablespaces.SPACE = innodb_tables.SPACE
LEFT JOIN information_schema.INNODB_DATAFILES AS innodb_datafiles
    ON innodb_datafiles.SPACE = innodb_tables.SPACE
WHERE innodb_tables.NAME = CONCAT(
    '<database_name>',
    '/',
    '<table_name>'
);

重点看 SPACE_TYPE：

Single：file-per-table 独立表空间，通常有单独的 表名.ibd。
General：通用共享表空间，多个表可能共用同一个文件。
System：InnoDB 系统表空间，数据位于 ibdata* 文件中。

独立表空间默认位于：

MySQL 数据目录/数据库名/表名.ibd

如果建表时使用了 DATA DIRECTORY，则可能位于外部目录。MySQL 运行在 Docker 中时，SQL 查到的是容器内路径，还需要通过容器挂载关系映射到宿主机。

不要在 MySQL 运行期间手动删除、移动或修改 .ibd 文件。它受 InnoDB 数据字典管理，不是可以脱离 MySQL 随意操作的普通文件。

四、InnoDB 的 OPTIMIZE 提示不是报错

对独立表空间中的大表做过大量删除后，可以考虑：

OPTIMIZE TABLE <database_name>.<table_name>;

InnoDB 常见输出是：

Table does not support optimize, doing recreate + analyze instead
status: OK

这句话不是说 InnoDB 不能执行 OPTIMIZE TABLE，而是说 InnoDB 没有实现传统意义上的独立 optimize 方法。MySQL 会把操作映射为类似：

ALTER TABLE <database_name>.<table_name> FORCE;

也就是重建表和索引，再更新统计信息。MySQL 官方的 OPTIMIZE TABLE 文档展示的标准输出正是 recreate + analyze。

真正应该关注的是最后的状态：

status: OK

如果最终为 OK，操作就成功了。对于独立表空间，重建过程只复制仍然存在的数据，重新组织聚簇索引和二级索引，再用新的 .ibd 替换旧文件，因此多余空间通常能归还给操作系统。

ALTER TABLE ... ENGINE = InnoDB 也会触发同类重建。刚执行完 OPTIMIZE TABLE 后，没有必要再重复执行一次。

五、为什么 SQL 统计和 .ibd 文件大小对不上

很多人会使用下面的查询检查空间：

SELECT
    TABLE_ROWS,
    DATA_LENGTH,
    INDEX_LENGTH,
    DATA_FREE
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<database_name>'
  AND TABLE_NAME = '<table_name>';

这组字段很有用，但不能把它当成 .ibd 文件的实时、精确拆分。

对于 InnoDB：

TABLE_ROWS 是估算行数。
DATA_LENGTH 近似表示聚簇索引分配的空间。InnoDB 的行数据就在聚簇索引中，因此主键结构也包含在这里。
INDEX_LENGTH 近似表示二级索引分配的空间，不包含聚簇索引。
DATA_FREE 主要反映表空间中完全空闲的 extent，并扣除一定安全余量；它不是所有页内空洞的总和。
.ibd 还包含表空间管理页、内部元数据、索引结构页和其他开销。

因此下面这个等式不成立：

.ibd 文件大小 = DATA_LENGTH + INDEX_LENGTH + DATA_FREE

更准确的字段别名应当是：

SELECT
    TABLE_ROWS AS estimated_rows,
    ROUND(DATA_LENGTH / 1024 / 1024, 2)
        AS clustered_index_allocated_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2)
        AS secondary_indexes_allocated_mb,
    ROUND(DATA_FREE / 1024 / 1024, 2)
        AS completely_free_extents_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<database_name>'
  AND TABLE_NAME = '<table_name>';

MySQL 官方在 INFORMATION_SCHEMA.TABLES 字段说明中也将 InnoDB 的索引空间描述为近似值。

六、还有一层原因：INFORMATION_SCHEMA 统计会缓存

MySQL 8.0 会缓存 INFORMATION_SCHEMA 中的动态表统计。缓存有效期由会话变量控制：

SELECT @@SESSION.information_schema_stats_expiry;

默认值是 86400 秒，也就是 24 小时。这意味着 OPTIMIZE TABLE 已经让 .ibd 从十几 MiB 缩到 128 KiB，当前连接查询出的 DATA_LENGTH 却仍可能是优化前的旧值。

临时绕过缓存的方法是，在 同一个连接 中执行：

SET SESSION information_schema_stats_expiry = 0;

SELECT
    TABLE_SCHEMA,
    TABLE_NAME,
    TABLE_ROWS AS estimated_rows,
    ROUND(DATA_LENGTH / 1024 / 1024, 2)
        AS clustered_index_allocated_mb,
    ROUND(INDEX_LENGTH / 1024 / 1024, 2)
        AS secondary_indexes_allocated_mb,
    ROUND(DATA_FREE / 1024 / 1024, 2)
        AS completely_free_extents_mb
FROM information_schema.TABLES
WHERE TABLE_SCHEMA = '<database_name>'
  AND TABLE_NAME = '<table_name>';

SET SESSION 只影响当前连接，不需要重启 MySQL。也可以通过 ANALYZE TABLE 更新缓存值。详见 MySQL 官方对 information_schema_stats_expiry 的说明。

即便绕过了缓存，统计仍是 InnoDB 页面分配量的近似描述，依旧不会与文件字节数完全相等。

七、怎样判断磁盘空间是否真的释放

最实用的方式，是把 InnoDB 统计和表空间文件大小放在一起看：

SET SESSION information_schema_stats_expiry = 0;

SELECT
    table_statistics.TABLE_NAME,
    ROUND(table_statistics.DATA_LENGTH / 1024 / 1024, 2)
        AS clustered_index_allocated_mb,
    ROUND(table_statistics.INDEX_LENGTH / 1024 / 1024, 2)
        AS secondary_indexes_allocated_mb,
    ROUND(table_statistics.DATA_FREE / 1024 / 1024, 2)
        AS completely_free_extents_mb,
    ROUND(innodb_tablespaces.FILE_SIZE / 1024 / 1024, 2)
        AS ibd_apparent_size_mb,
    ROUND(innodb_tablespaces.ALLOCATED_SIZE / 1024 / 1024, 2)
        AS filesystem_allocated_mb
FROM information_schema.TABLES AS table_statistics
JOIN information_schema.INNODB_TABLESPACES AS innodb_tablespaces
    ON innodb_tablespaces.NAME = CONCAT(
        table_statistics.TABLE_SCHEMA,
        '/',
        table_statistics.TABLE_NAME
    )
WHERE table_statistics.TABLE_SCHEMA = '<database_name>'
  AND table_statistics.TABLE_NAME = '<table_name>';

其中：

FILE_SIZE 是文件的表观大小，可与 ls -l 或 stat 对照。
ALLOCATED_SIZE 是文件系统实际分配的磁盘空间，可与 du 对照。

Linux 上可进一步查看精确字节数：

stat -c '%n: %s bytes' \
    /var/lib/mysql/<database_name>/<table_name>.ibd

du --block-size=1 \
    /var/lib/mysql/<database_name>/<table_name>.ibd

对于普通、未使用透明页压缩的 .ibd 文件，这两个数字往往接近；如果存在稀疏文件或页压缩，则表观长度和实际占用可能不同。对应字段定义可参考 INNODB_TABLESPACES。

如果优化前文件是 12 MiB，优化后变成 10 MiB，但 DATA_LENGTH 和 INDEX_LENGTH 几乎不变，也不一定异常。缩掉的 2 MiB 可能来自表空间尾部的空闲区域，而剩余数据与索引仍需要相近数量的 B+Tree 页面。

八、生产环境执行 OPTIMIZE 前的检查清单

OPTIMIZE TABLE 是表重建，不应当作为每天例行执行的“保养命令”。对大表操作前至少确认以下事项：

先评估收益：只有大量删除、文件明显膨胀，并且确实需要把磁盘还给操作系统时才值得执行。
准备额外空间：重建过程中需要容纳新表和临时数据，剩余磁盘不足可能直接失败。
避开高峰期：操作会带来大量磁盘 I/O，并重建所有索引。
关注元数据锁：普通 InnoDB 表通常使用 Online DDL，期间一般允许并发读写，但准备和提交阶段仍会短暂申请排他元数据锁。
处理长事务：长期未提交事务不仅可能延迟 purge，也可能让 DDL 等待更久。
关注复制链路：OPTIMIZE TABLE 默认写入 binary log，重建开销可能传递到副本。
先做好备份和回滚预案：尤其是超大表、核心业务表或磁盘水位本来就很高的实例。

如果整张表都不再需要数据，TRUNCATE TABLE 通常比 DELETE FROM table 更适合回收独立表空间。不过它是 DDL，会重置 AUTO_INCREMENT，不能带 WHERE，事务语义也不同，并且可能受到外键约束限制。

总结

判断 MySQL 删除后是否释放空间，先问清楚“释放给谁”：

DELETE：数据对查询不可见，清理后的空间主要留给 InnoDB 复用，.ibd 通常不缩小。
TRUNCATE / DROP：对于独立表空间，通常能直接让文件重建或消失。
OPTIMIZE TABLE：通过重建表和索引压缩剩余数据，适合大量删除后回收独立 .ibd 的磁盘空间。
recreate + analyze：是 InnoDB 执行 OPTIMIZE TABLE 的标准提示，不是失败；最终看 status: OK。
INFORMATION_SCHEMA.TABLES：字段是统计和近似值，还可能受到 24 小时缓存影响，不能等同于实时文件大小。
判断实际占用：优先对照 INNODB_TABLESPACES.FILE_SIZE、ALLOCATED_SIZE、stat 与 du。

掌握这几个层次后，“数据删了但磁盘没降”“OPTIMIZE 提示不支持”“SQL 统计和 .ibd 对不上”就不再是三个独立问题，而是同一套 InnoDB 表空间机制在不同层面的表现。