MySQL 删除数据后表文件大小不变

FreeGuideOnline 最新 2026-07-05

sql -- 查看指定数据库（如 mydb）下的表概况 SELECT TABLE_NAME, ROUND(DATA_LENGTH / 1024 / 1024, 2) AS 数据大小(MB), ROUND(INDEX_LENGTH / 1024 / 1024, 2) AS 索引大小(MB), ROUND(DATA_FREE / 1024 / 1024, 2) AS 碎片空间(MB) FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'mydb' AND TABLE_NAME = 'my_table';


- `DATA_LENGTH`：已经分配的数据空间（包含碎片）。
- `INDEX_LENGTH`：索引占用的空间。
- `DATA_FREE`：**表空间内部的空闲碎片总和**，它越接近 `DATA_LENGTH + INDEX_LENGTH`，说明表越“虚胖”。

如果 `DATA_FREE` 为 0，却依然感觉表文件很大，则可能所有页面都被标记为已使用，实际上只是删除留下的“已分配但尚未被新数据填满”的页内碎片，这种情况 `DATA_FREE` 不会体现。

---

### 三、回收空间的三种有效方案

回收空间的核心思路是**重建表**：将有效数据复制到新的表空间中，然后替换原表，从而彻底丢弃因删除而产生的碎片。

#### 1. 使用 `OPTIMIZE TABLE`（最常用）

`OPTIMIZE TABLE` 等价于执行 `ALTER TABLE ... ENGINE=InnoDB`，它会重建整个 InnoDB 表。对于**独立表空间**（`innodb_file_per_table = ON`，默认开启）的表，该操作会释放文件末尾的空闲空间，大幅缩小 `.ibd` 文件。

```sql
OPTIMIZE TABLE mydb.my_table;

注意事项（非常重要）：

  • 锁表：执行期间会对表加只读锁（对于大表可能持续数分钟甚至更久），请务必在业务低峰期操作。
  • 额外空间：操作过程中需要原表大小约等量的额外磁盘空间（临时表空间或临时文件）。确保磁盘剩余空间充足。
  • 主从延迟：在主库上执行，从库会重放 DDL 日志（MySQL 8.0 可使用原子 DDL 优化），可能需要时间同步。
  • MySQL 8.0 改进：OPTIMIZE TABLE 默认使用 ALGORITHM=INPLACE，尽量减少锁表时间，但仍需关注。

处理超大表：如果单表数据量达到几十 GB 甚至上百 GB，直接 OPTIMIZE TABLE 可能耗时太长且风险高，可考虑后面介绍的在线迁移工具。

2. 使用 ALTER TABLE ... ENGINE=InnoDB

效果与 OPTIMIZE TABLE 完全相同，但你可以显式设置算法选项：

ALTER TABLE mydb.my_table ENGINE=InnoDB, ALGORITHM=INPLACE;

同样会重建表，并释放空间。

3. 针对共享表空间（ibdata1）的特殊处理

如果你的 MySQL 版本较老或配置了 innodb_file_per_table = OFF，所有表数据都存储在共享表空间文件 ibdata1 中。此时 OPTIMIZE TABLE 会让 ibdata1 内产生更多的碎片，但文件大小只会增长，绝不会缩小。

回收 ibdata1 空间的唯一可靠方法是：

  1. 使用 mysqldump 或其他逻辑备份工具导出所有数据库。
  2. 停止 MySQL 服务，删除 ibdata1 和日志文件（按官方文档操作）。
  3. 重新初始化数据库并导入备份。

这个过程风险高、停机时间长，仅适用于极端情况。强烈建议将 innodb_file_per_table 设置为 ON，从根本上避免共享表空间膨胀问题。

4. 借助 pt-online-schema-change（免锁表方案）

对于必须7×24小时运行的生产大表，Percona Toolkit 中的 pt-online-schema-change 提供了几乎无锁的表空间回收能力。它的原理是创建一个与原表结构相同的新表，通过触发器同步增量数据，最后原子化重命名替换。

pt-online-schema-change \
  --alter "ENGINE=InnoDB" \
  D=mydb,t=my_table \
  --execute

此工具能自动处理触发器、复制延迟和性能限制，是大表回收空间的工业级方案。

5. 分区表的巧妙处理：TRUNCATE PARTITION

如果你的表使用了 RANGE 或 LIST 分区，并定期按时间删除整个分区的数据，则无需 DELETE，直接使用 TRUNCATE PARTITION 即可瞬间释放该分区所占的全部空间，且不产生碎片。

ALTER TABLE mydb.my_table TRUNCATE PARTITION p202306;