mysql数据库的数据删除及表空间释放之二三事

引言

最近，系统的mysql数据库空间占用比又比较高了。一开始，没搞明白，数据库里面的记录都定时通过delete命令清理了，咋数据库空间没释放出来呢。

三种删除数据的方法

翻阅了相关资料，才发现mysql 清空数据提供了三种方法。
1.删除/清空表数据：delete
mysql语法格式如下：

#删除部分数据
delete from tb_name where clause;

#清空表，仅删除数据、保留表结构，同时也不释放表空间
delete from tb_name;在这里插入代码片

delete 语句特点：

• 删除表中数据而不删除表结构，也不释放空间
• delete可以删除一行、多行、乃至整张表
• 每次删除一行，都在事务日志中为所删除的每行记录一项，可回滚
• 如果不加where条件，表示删除表中所有数据，仅删除数据、保留表结构，同时也不释放表空间

2.删除表：drop 数据表
mysql语法格式如下：

drop table table_name;
drop table if exists table_name;

drop 语句特点：

• drop会删除整个表，包括表结构和所有数据，释放空间
• 立即执行，执行速度最快
• 不可回滚

3.清空表中的所有数据：truncate
mysql语法格式如下：

#清空多张表、库中所有表的数据
truncate table table_name1,table_name2,...;

#清空单张表的数据
truncate table table_name;

truncate语句特点：

• truncate会删除表中的所有数据、释放空间，但是保留表结构
• 只能操作表，不能与where一起使用
• truncate删除操作立即生效，原数据不放到rollback segment中，不能rollback，操作不触发trigger
• truncate删除数据后会释放表空间、重置Identity（标识列、自增字段），相当于自增列会被置为初始值，又重新从1开始记录、而非接着原来的id数
• truncate删除数据后不写服务器log，整体删除速度快

那我们在delete了数据库中的大表的部分数据后，怎么释放空间呢？

使用delete语句删除数据时，delete语句只是将记录的位置或数据页标记为了“可复用”，但是磁盘文件的大小不会改变，即表空间不会直接回收。
此时可以通过optimize table语句释放表空间

使用“optimize table”命令释放MySQL实例的表空间

mysql 语法格式如下：

	OPTIMIZE TABLE table_name；

需要注意的是
optimize table语句在RDS MySQL 5.7、RDS MySQL 8.0上采用Online DDL方式执行，允许并发执行DML。对大表进行optimize table操作会带来突发的IO和Buffer使用量，可能导致锁表和抢占资源，业务高峰期可能会导致实例不可用以及监控断点。建议在业务低峰期操作。