MySQL删除数据后空间没变小

小蓝 • 2024-11-29 22:33 • 编程

一、数据删除的机制

在MySQL中，删除数据一般是通过delete或truncate语句实现的。两者的执行依据是不同的，导致在删除之后空间占用的变化也不同。

1. delete语句

delete语句是通过行级别删除数据，删除的行从磁盘中被完全删除，但是磁盘空间会变成碎片。另外，如果一个表中有很多的删除操作，那么空间碎片会越来越多，导致查询效率变慢。空间碎片的具体表现是磁盘上的空闲空间逐渐变小。

2. truncate语句

truncate语句是通过整体表重建，删除数据之后，MySQL会将整个表的空间全部释放掉，回到空表状态。这种方式空间利用率高，速度也非常快。但是，如果表涉及到外键等约束，不能使用truncate语句，只能使用delete语句。

二、删除数据后磁盘空间没变小的原因

当我们使用delete或truncate语句删除数据之后，我们期望的是磁盘空间能够得到释放，但实际情况是磁盘空间并没有发生变化。这是由于MySQL的设计机制导致的。

1. Innodb存储引擎

Innodb存储引擎是一种高性能的存储引擎，在删除数据之后并不会立即在磁盘上删除相关的数据文件，而是将这些文件标记为可复用的空间。这样做的好处是可以提高删除数据的效率，减少磁盘I/O次数。但是，这样也是导致磁盘空间并没有实时释放的原因之一。

2. 磁盘空间再利用

MySQL认为，在将来可能还有数据需要写入到磁盘中，为了避免频繁的分配释放磁盘空间，MySQL会将删除的空间作为可再利用的空间保留下来。

三、如何释放删除后的磁盘空间

虽然MySQL不会自动释放删除后的磁盘空间，但是我们也可以手动进行释放。

1. 重建表

使用truncate语句或者一次性删除大量数据之后，可以尝试重建整个表，这样可以释放磁盘空间。重建表的操作可以通过create table as select语句实现，该语句可以复制数据，同时重构表结构。

--将表重新命名，再进行create table操作
ALTER TABLE mytable RENAME TO old_mytable;
CREATE TABLE mytable AS SELECT * FROM old_mytable;
DROP TABLE old_mytable;

2. OPTIMIZE TABLE命令

除了重建表之外，MySQL还提供了一个optimize table命令，可以通过该命令来释放删除后空间。该命令会执行以下操作：

为每个索引重新构建B-tree，使其更加紧凑
将数据文件碎片整理成连续的块
释放没有使用的空间

OPTIMIZE TABLE mytable;

3. 调整innodb_file_per_table参数

Innodb存储引擎有一个配置参数innodb_file_per_table，设置该参数为ON之后，每个Innodb表使用一个单独的表空间进行管理。这样，在删除数据之后，Innodb存储引擎会自动回收相应的表空间，释放磁盘空间。

--修改my.cnf文件
innodb_file_per_table = ON

四、小结

MySQL在删除数据后，是通过标记删除的方式来进行优化，所以，磁盘空间不会立即释放。为了释放删除后的磁盘空间，我们可以使用重建表、optimize table命令、调整innodb_file_per_table参数等方式。需要注意的是，对于有删除操作的表，我们应该尽量避免频繁的删除操作，以免导致空间碎片太多，影响查询效率。

原创文章，作者：小蓝，如若转载，请注明出处：https://www.506064.com/n/190405.html