MySQL DELETE 删除数据后释放空间

首先来看一下MySQL删除数据的几种方式

MySQL 删除数据的几种方式有 DELETE、TRUNCATE、DROP

DELETE:

要注意，delete是DML语言，删除数据后，并不会真正的删除数据，每次从表中删除一行，都会将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

还有，InnoDB 数据库在使用 delete 进行删除操作的时候，只会将已经删除的数据标记为删除，并没有把数据文件删除，因此并不会彻底的释放空间。这些被删除的数据会被保存在一个链接清单中，当有新数据写入的时候，MySQL 会重新利用这些已删除的空间进行再写入。

DELETE操作不会减少数据或索引所占用的空间。

语法：

DELETE FROM [表名称] WHERE ......;

TRUNCATE

truncate是DDL，会隐式提交，所以，不能回滚。

对于外键（foreignkey ）约束引用的表，不能使用 TRUNCATE，而应使用不带 where 子句的 delete 语句。

TRUNCATE 不能用于参与了索引视图的表。

当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小。

语法：

TRUNCATE TABLE [表名称];

DROP

DROP 是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

DROP 语句删除表结构及所有数据，并将表所占用的空间全部释放。

DROP 语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

语法：

DROP TABLE [表名称];

一般而言，DROP > TRUNCATE > DELETE

了解了这些后，就会发现 TRUNCATE 和 DROP 虽然删除快，删除完空间也会释放，但是最好还是使用 DELETE 。

那么如何释放 DELETE 删除后的空间和索引空间呢？

这里可以使用另一个SQL命令，OPTIMIZE TABLE

语法：

OPTIMIZE TABLE [表名称];

OPTIMIZE TABLE 将重新组织表数据和相关索引数据的物理存储空间，减少存储空间并提高I/O访问效率。对每个表所做的影响取决于该表所使用的存储引擎。该命令对视图无效。

使用后就可以删除 DELETE 后所占用的空间和索引空间。

有几点需要注意一下：

1. 对于 InnoDB 存储引擎 MySQL，OPTIMIZE 命令，将会被映射为 ALTER TABLE … FORCE，并将重建表，更新索引统计信息，释放未使用的索引空间，这就意味着在一定程度上 OPTIMIZE 操作会造成一定的表阻塞（具体可以参考官网）。

2. OPTIMIZE 操作会锁表，所以最好不要在高峰期使用。

3. OPTIMIZE 操作相当于物理删除，一旦删除，恢复就很麻烦，所以最好使用逻辑删除，也不要经常使用，每月一次就够了

另外

还有一点，如果表是自增主键的话，删除完数据后再次添加数据，主键还是从之前删除完的数据自增，而不是从剩下的数据自增。

可以使用如下方法，进行修改

ALTER TABLE [表名称] AUTO_INCREMENT  = [21];

现在再去插入一条数据试试。

当然还有一种方案就是使用 TRUNCATE 命令，但是这样的话，数据库表数据就全部清空了，如果需要自增id从1开始的话可以试试