MySQL delete 删数据后磁盘空间未释放

问题描述

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

解决方案

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

1、执行 optimize table ${table_name}。
2、执行 alter table ${table_name} engine = innodb。

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

问题分析
当在 MySQL 中使用 DELETE 命令删除数据时，磁盘空间通常不会立即释放。这是因为 MySQL 的工作方式以及数据库的存储引擎的特性。

具体来说，MySQL 中的 DELETE 命令并不会直接从磁盘上删除数据行，而是通过将被删除的数据行标记为已删除来进行操作。这个操作称为"软删除"，被标记为已删除的数据行实际上仍然存在于数据文件中，只是在逻辑上被视为不可见。

MySQL 使用一种称为 MVCC（多版本并发控制）的机制来处理数据的可见性。这意味着即使删除了数据行，其他事务在操作该表时仍然可以看到旧版本的数据，直到这些事务也被提交或回滚。这就是为什么删除数据后磁盘空间不会立即释放的原因之一。

另一个原因是为了保持数据库的一致性和性能。在大多数情况下，实时地释放磁盘空间可能会导致性能下降，因为系统需要频繁地进行磁盘空间的分配和释放操作。

以下命令可以查看磁盘碎片问题：

select table_schema,table_name,concat( round(data_free/ 1024 / 1024 / 1024, 2 ), 'GB' ) AS datafree,data_length,index_length,table_rows,CREATE_TIME FROM  information_schema.TABLES where TABLE_NAME='vrtc_server_users';
SELECT * from information_schema.`TABLES` where table_name ='vrtc_server_users';
show  table status;

数据库技术交流群：