背景

在MySQL中有大表需要清理，该表超过100GB，不敢直接delete或者truncate、drop，怕影响业务。

delete、truncate、drop的区别

一般情况下（少量数据），不同的场景可以选择不同的方式来做数据删除。

1. 删除指定的部分数据，使用delete from xxx where ...
2. 删除所有数据（只是删除数据，需要保留表结构），那么建议使用truncate命令，比直接delete快.
3. 删除整个表，使用drop table命令.

delete

delete命令逐行删除数据，涉及行锁，删除多条数据时性能差.

drop

drop命令会删除表数据及结构、触发器、分区等。

drop命令会持有buffer pool的锁，还涉及ibd磁盘文件的删除。表越大持有锁的时间越长，IO资源消耗越大，会影响在线业务。

truncate

truncate命令清除整个表的数据，需要执行人有drop权限。

• truncate相当于把表drop掉然后重新创建一张新表。
• truncate成功后，不会返回具体删除的数据行数。
• truncate时数据和索引文件都会重建。

三者的区别

• truncate与drop是DDL语句，执行后无法回滚；delete是DML语句，可回滚。
• truncate只能作用于表；delete，drop可作用于表、视图等。
• truncate会清空表中的所有行，但表结构及其约束、索引等保持不变；drop会删除表的结构及其所依赖的约束、索引等。
• truncate会重置表的自增值；delete不会。
• truncate不会激活与表有关的删除触发器；delete可以。
• truncate后会使表和索引所占用的空间会恢复到初始大小；delete操作不会减少表或索引所占用的空间，drop语句将表所占用的空间全释放掉。

常见的大表删除方式

对于大表的场景，常见的做法：

1. 小批量、分批删除； 由于直接使用delete，是逐步删除，直接delete不带where条件肯定是不科学的。因此，可以通过分批delete的方式，建议where条件中最好带上主键或者是索引，加速删除的效率。但对于大表来说，这种方式性能太低。
2. 创建新表，切换旧表，drop旧表；

create table new_table like old_table ;
rename table old_table to old_table_del ;