一、数据删除的机制

在 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 参数等方式。需要注意的是，对于有删除操作的表，我们应该尽量避免频繁的删除操作，以免导致空间碎片太多，影响查询效率。