MySQL Delete 删数据后磁盘空间未释放的原因

目录

• 问题描述

• 解决方案

• 问题分析

问题描述

在 MySQL 中使用 delete 语句删除数据之后，监控视图中可用的磁盘空间没有增加，磁盘使用率没有下降等等。

解决方案

delete 不释放空间是 MySQL 自身机制的原因，需要重建表才可以释放磁盘空间，可以参考的操作：

1. 执行 optimize table ${table_name}。

2. 如果是 InnoDB 的表，执行 alter table ${table_name} engine = innodb。
   

需要注意以下两个问题：

这两个命令都会重建表，尽量不要在磁盘空间紧张（>90％）的时候进行操作，先扩容磁盘，操作完之后再缩容。
这两个命令在开始和结束的时候都会尝试获取 metadata lock，所以尽量不要在业务高峰期执行。

问题分析

在 MySQL 的机制中，delete 删除的行只是被标记为删除状态，如果删除的行很多，整个数据页（innodb_page）的行都会被删除的时候，数据页也只会标记为删除，都不会真正的物理删除，而是一直占用，等待被复用。

例如：

可以看到 delete 前后，data_length 并没有发生变化，但是 data_free 增加了很多。这说明数据虽然删了，但是并没有被释放，仍旧被 test1 表占用，只是显示处于 free 状态，以后再写入新数据的时候就可以直接复用，而不需要在申请新的磁盘空间了。

这个时候使用alter table test1 engine = innodb 看看效果：

可以看到 data_length 和 data_free 都变成了空表的状态，仅有一个 innodb_page (默认 16k)。

PS：data_free 本身也可以用来评估表的空间碎片，当这个数字非常高的时候，可以考虑用同样的方法重建表，回收一部分磁盘空间。

来源：https://cloud.tencent.com/developer/inventory/2101/article/1688879