MySQL异步删除大表的方法

背景

在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 ;
rename table new_table to old_table;
drop table old_table_del;

rename命令支持多个表，上面2条语句也可以合并成一条：

rename table old_table to old_table_del, new_table to old_table;

最后一步drop表时，采用建立硬链接的方式删除：

1. 建立idb文件的硬链接：

ln test.idb test.idb.link

关于硬链接：

具有相同inode节点号的多个文件互为硬链接文件，硬链接文件可以理解成是文件的另一个入口；
删除硬链接文件或者删除源文件时，文件实体并未被删除；
删除源文件和所有硬链接文件后，文件实体才会被删除；
因此，我们可以通过给文件设置硬链接文件的方式，来防止重要文件被误删除；
创建硬链接可以用命令：ln source_file hardlink

1. 执行drop操作（快速返回）：

drop table test;

1. 删除数据文件，使用限速删除工具操作：

bt-rm

TDSQL异步删除大表功能

如果使用的是TDSQL，基于腾讯自研TXSQL内核支持异步删除大表：

https://cloud.tencent.com/document/product/236/48904

实现原理如下：

1. 将ibd文件名重命名为临时文件名，临时文件存放在 innodb_async_drop_tmp_dir 指定的目录下
2. 在后台线程中逐步truncate .ibd文件，每次 truncate 的文件大小由 innodb_async_truncate_size 控制
3. 当文件size足够小的时候，终将.ibd文件删除
4. drop大表异步化相关参数已支持动态设置, 无须重启实例

该功能无需用户操作，由内核自动完成，其原理是在删除表时，为表的数据文件在另外一个目录中创建一个硬连接。当执行 drop table 后，删除的只是该文件的一个硬连接。之后后台线程扫描到硬连接目录中有需要删除的文件，自动在后台 truncate 前面 drop 掉表数据文件。

建议

1. 数据量小的时候，清空表数据，使用truncate命令，删除表可直接drop
2. 数据量大的时候，使用创建硬链接的方式，drop table后再逐步删除文件；
3. 使用TDSQL的话，打开异步删除配置参数，直接drop table即可。