mysql deallocate MySQL 大表清理避坑指南：DELETE 越删越满，TRUNCATE 秒级释放空间

mysql deallocate MySQL 大表清理避坑指南：DELETE 越删越满，TRUNCATE 秒级释放空间

问题描述

磁盘还有10G可用空间， 的 表 占用 380G，通过 命令删除该表数据，结果磁盘空间很快就没有了mysql deallocate，怎么回事？？？

问题分析

先说结论：这种情况mysql deallocate，既不能直接 （越删越大）mysql deallocate，也不能在线 ALTER（峰值再占 380 G 会直接把盘打满宕机）。

为什么不能？

看起来是“删数据”，但 的物理机制是：

1、不真正抹去数据页

只是把记录打上 并写入 undo 页（MVCC 需要保留旧版本）。

→ 原数据仍在 .ibd 文件里mysql deallocate MySQL 大表清理避坑指南：DELETE 越删越满，TRUNCATE 秒级释放空间，文件大小一分不减。

2、写 undo

每删一行都要生成 ，undo 表空间还要扩张；

大 会一次性产生几十 GB 甚至上百 GB undo，10 G 剩余空间瞬间被吃光。

3、写 redo

所有 undo 和数据页的修改都要进 redo log（*），redo 同样膨胀。

4、purge 后才会回收

只有当事务提交 且 没有更早的读视图引用这些旧版本时，后台 purge 线程才把 undo 和数据页标记为“可重用”；这个回收是“内部空洞”，.ibd 文件尺寸不会缩小（除非重建表）。

为什么不能ALTER或？

ALTER（alter table =;）和 （ table ）都是 DDL，而且都是走 COPY 算法（如下图），需要新建一个和原表一样大的临时表，很快就把磁盘占满。

注：对 表，TABLE 基本等价于 ALTER TABLE … =——都会重建聚簇索引mysql deallocate MySQL 大表清理避坑指南：DELETE 越删越满，TRUNCATE 秒级释放空间，释放空洞、收缩文件。

解决方案

1、用 的 — + –quick 一行行流式抽，不生成中间大文件。

# 本机执行，stdout 直接走管道，不占磁盘
mysqldump -uroot -p --single-transaction --quick --skip-add-drop-table 
          --no-create-db --complete-insert --extended-insert=FALSE 
          db_name t_trace 
| gzip -1 > /other_mount/t_trace.sql.gz

2、用 table ; 快速清理表。

是一个 DDL，只记录 DDL Log（不记录undo/redo），立即释放磁盘（删除 .ibd 文件然后建个新的），秒级完成（与表大小无关），不可回滚。