MySQL’s faster drop table method for deleting large tables

Time:2022-5-16

MySQL’s faster drop table method for deleting large tables

Using hard link and truncate to reduce the impact of drop table on online environment

During drop table, all processes, whether DDL or DML, are started by Hang; Continue to execute until the end of drop; This is because InnoDB will maintain a global exclusive lock (on the table cache) and will not release it until drop table is completed. In our commonly used ext3, ext4 and NTFS file systems, it takes some time to delete a large file (tens of gigabytes, or even hundreds of gigabytes).

Next, we introduce a fast drop table method; InnoDB can return quickly no matter how large the table is, and the table deletion is completed;

[[email protected]  ~]# ll  /data/mysql/mysql-5.7.28/data/testdatabase/my_test_table* -th
-rw-r-----. 1 mysql mysql 2.0G May 29 22:39 /data/mysql/mysql-5.7.28/data/testdatabase/my_test_table.ibd
-rw-r-----. 1 mysql mysql 8.5K May 29 21:51 /data/mysql/mysql-5.7.28/data/testdatabase/my_test_table.frm
[[email protected] ~]# 

It is assumed that the MySQL installation directory is/data/mysql/mysql-5.7.28/Database istestdatabase, the large meter used for the test ismy_test_table

If you directly use drop table to delete a table, this statement will take a long time to execute. At this time, you can delete it by setting a hard link on the data file corresponding to the table.

1. If it is a master-slave architecture, please create hard links on all machines

The creation methods are simple:
Soft link (symbolic link)ln -s source target
Hard link (entity link)ln source targe

ln /data/mysql/mysql-5.7.28/data/testdatabase/my_test_table.ibd  /data/mysql/mysql-5.7.28/data/testdatabase/my_test_table.ibd.hdlk
#Enter MySQL and delete the table
> mysql -h 127.0.0.1 -uroot -p
> use testdatabase
> drop table my_test_table;

2. Then delete the real large physical files in the operating system

for i in `seq 50 -1 1 ` ;do sleep 2; truncate -s ${i}G /data/mysql/mysql-5.7.28/data/testdatabase/my_test_table.ibd.hdlk;done  
rm -rf /data/mysql/mysql-5.7.28/data/testdatabase/t_user.ibd.hdlk  

Starting from 2G, reduce 1m each time, stop for 2 seconds, continue until there is only 1g left in the file, and finally use the RM command to delete the remaining part.
(the larger the file, the more it can be reflected. Here is the test. The file is only 2G, which can be modified to m)

Attachment: