Considerations for MySQLDROPTABLE and DROP large tables

Considerations for MySQLDROPTABLE and DROP large tables

Syntax:

Delete table

DROP TABLE SyntaxDROP [TEMPORARY] TABLE [IF EXISTS]    tbl_name [, tbl_name] ...    [RESTRICT | CASCADE]

You can delete one or more tables at a time. You must have the DROP permission on the table to be deleted. Table definition files and data files are removed. After a table is deleted, its user permissions are not automatically deleted. If the table name specified in the parameter does not exist, an error is returned. However, existing tables are still deleted. You can specify if exists to prevent errors that occur when the table does not exist (in this case, only one NOTE is generated for the table that does not exist ). For partition tables, in addition to removing table definitions, partitions, and data, the associated partition definition file (. par) is also removed ). In MySQL5.6, the parameter [RESTRICT | CASCADE] does not do anything. The [TEMPORARY] keyword indicates that only TEMPORARY tables are deleted, and the statement does not end ongoing transactions (DDL statements in MySQL are implicitly committed), and no permission check is performed.

Delete Database

DROP DATABASE SyntaxDROP {DATABASE | SCHEMA} [IF EXISTS] db_name...

Delete the table in the specified database and then delete the database. You must have the DROP permission on the database. After a database is deleted, user permissions in the database are not automatically deleted. If exists is used to prevent errors caused by nonexistent library names. After the database is deleted, the default database is reset. If you execute the drop databasse link and the original database on the database that uses the symbolic link, the database will be deleted. Command to return the number of removed tables.
This command removes the files and directories generated by MySQL itself during regular operations from the specified database directory, such :. BAK. DAT. HSH. MRG. MYD. MYI. TRG. TRN. db. frm. ibd. ndb. par. If db exists. opt will also be deleted. If there are other files or directories not generated by MySQL in the database directory, the entire database directory will not be removed. In this case, you need to manually clear the remaining files and run the drop database statement again.
Deleting a database does not remove the temporary tables created in the database. The temporary table is automatically cleared at the end of the SESSION or is displayed and deleted through the drop temporary table.

Notes for deleting large tables
For table deletion, the InnoDB Engine maintains a global exclusive lock at the TABLE cache level until the DROP table is complete. In this way, other operations on the TABLE will be HANG. For large tables, the drop table operation may take a long time. Therefore, an effective method is required to increase the deletion speed of large tables and minimize the HANG retention time. You can set hard links to achieve this goal.
For example, there is a sample table:
Example_table

When InnoDB engine is used and innodb_file_per_table = ON is specified, the following two files correspond to the table in the Data Directory: Table definition files and data files.

sudo ls  -lh /data/mysql/testdb-rw-rw---- 1 mysql mysql 8.4K Oct 28 13:26 example_table.frm-rw-rw---- 1 mysql mysql 100G Oct 28 13:26 example_table.ibd

If the TABLE is as large as GB and drop table is directly used to delete the TABLE, this statement takes a long time. In this case, you can set a hard link on the data file corresponding to the table to delete the table.

sudo ln /data/mysql/testdb/example_table.ibd /data/mysql/testdb/example_table.ibd.hdlksudo ls  -lh /data/mysql/testdb-rw-rw---- 1 mysql mysql 8.4K Oct 28 13:26 example_table.frm-rw-rw---- 2 mysql mysql 100G Oct 28 13:26 example_table.ibd-rw-rw---- 2 mysql mysql 100G Oct 28 13:26 example_table.ibd.hdlk

An additional example_table.ibd.hdlk file is found, and the innode values of example_table.ibd.hdlk and example_table.ibd are both 2. That is, when multiple file names (such as hard links) point to the same innode, the number of innode references is greater than 1, deleting any file name will only delete the pointer to innode and will not directly Delete the physical file block, so it will be very fast, the corresponding physical file block is deleted only when the reference count of innode is equal to 1. It takes a long time to delete the physical file block.

After a hard link is established, execute the drop table operation:

DROP TABLE example_table；

It will be quickly completed. view the corresponding table definitions and data files:

sudo ls  -lh /data/mysql/testdb-rw-rw---- 1 mysql mysql 100G Oct 28 13:26 example_table.ibd.hdlk

Only example_table.ibd.hdlk is left, and the reference count of innode is changed to 1. That is, the drop table operation just now deletes a pointer example_table.ibd of the physical file, so it is very fast.

The remaining task is to delete the real physical file. Because the innode reference count has changed to 1, deleting example_table.ibd.hdlk will delete the physical file. However, due to the large size of physical files, deleting large files will still cause high disk IO overhead. Therefore, you can delete large data files in a few ways. The truncate tool can be used to increase or decrease the size of a specified file. It can be used for this purpose:

for i in `seq 100 -1 1 ` ;do sleep 2;sudo truncate -s ${i}G /data/mysql/testdb/example_table.ibd.hdlk;donesudo rm -rf /data/mysql/testdb/example_table.ibd.hdlk;

Start from GB, scale down 1 GB each time, stop for 2 seconds, continue until the file is 1 GB left, and finally use the rm command to delete the remaining parts.

To delete the entire DATABASE, you can delete the large tables first, and then delete the entire DATABASE in the drop database. For more information about how to delete the large tables, see the preceding method.