Considerations for MySQL drop table operation and drop large table

Grammar:

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 need to have drop permissions on the deleted table. Both the table definition file and the data file are removed. The user rights on the table are not automatically deleted after the table is deleted. The table name specified in the parameter does not exist, but is still deleted for the existing table. An error that is raised by specifying if exists prevents the table from being present (only one note is generated for a table that does not exist). For partitioned tables, the partition definition file (. par) associated with it is removed, in addition to the table definition, partition, and data. Parameters in MySQL5.6 [RESTRICT | CASCADE] don't do anything. The [temporary] keyword indicates that only the temporary table is deleted and the statement does not end the transaction in progress (the DDL statements in MySQL are implicitly committed) and no permission checks are performed.

Delete Library

DROP database Syntaxdrop {database | SCHEMA} [IF EXISTS] db_name ...

Deletes a library after deleting a table in the specified library. You need to have drop permissions on the library. User rights that exist on the library after the library is deleted are not automatically deleted. If exists is used to prevent errors that are caused when the library name does not exist. The default library is reset when the library is deleted. If you perform a drop Databasse link on a library that uses a symbolic link and the original database is deleted. command to return the number of tables that were removed.
This command removes the files and directories that MySQL itself produces, such as:, from the specified database directory when the general operation is removed. Bak . Dat . HSH . MRG. MYD . MYI . TRG . TRN. db. frm. ibd. NDB par,If Db.opt is present, it will also be deleted. If there are other files or directories in the database directory that are not generated by MySQL itself, the entire database directory is not removed. At this point, you need to manually clean up the remaining files and rerun the DROP DATABASE statement.
Deleting a database does not remove the temporary tables created in the library. Temporary tables are automatically cleaned or displayed by the drop temporary table at the end of the session.

Considerations for deleting large tables
For table deletions, because the INNODB engine maintains a global exclusive lock at the table cache level until the drop table is complete, other operations on the table are stuck. For larger tables, the drop table operation can take a long time, so an effective way to increase the deletion speed of large tables is required to minimize the hang time. You can do this by setting up a hard link.
For example, there is a sample table:
Example_table

When using the InnoDB engine and specifying Innodb_file_per_table=on, there are two files in the data directory that correspond to the table, respectively, for the table definition files and data files.

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

The table has a size of 100G and uses the drop table directly to complete the delete action, so this statement takes a long time to execute. You can delete this by setting a hard link on the data file that corresponds to 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 13:26 example_table.frm-rw-rw----2 mysql mysql 100G Oct 13:26 example_table . IBD-RW-RW----2 mysql mysql 100G Oct 13:26 example_table.ibd.hdlk

A example_table.ibd.hdlk file was found, and the innode of both Example_table.ibd.hdlk and EXAMPLE_TABLE.IBD were 2. That is, when more than one file name (such as a hard link) points to the same innode, the number of references to this innode is greater than 1, at which point the deletion of any one of the filenames will only delete the pointer to Innode and will not delete the physical file block directly, so it will be very fast. Until the reference count of Innode is equal to 1 o'clock, the corresponding physical file block is actually deleted, which is time-consuming to actually delete the physical file block.

Perform a drop table operation after a hard link has been established:

DROP TABLE example_table;

The discovery will be completed quickly to see the corresponding table definition and data file:

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

Only Example_table.ibd.hdlk is left, and the reference count of Innode becomes 1. That is, the drop table operation implemented the deletion of a pointer to a physical file example_table.ibd, thus very fast.

The rest of the task is to delete the real physical files, because at this time the reference count of Innode has changed to 1, delete the example_table.ibd.hdlk will actually delete the physical file. However, because of the large physical files, deleting large files can still cause higher disk IO overhead. You can therefore delete large data files using a small number of successive deletions. The Truncate tool can be used to increase or decrease the size of a specified file for this purpose:

For i in ' seq 100-1 1 ';d o sleep 2;sudo truncate-s ${i}g/data/mysql/testdb/example_table.ibd.hdlk;donesudo Rm-rf/dat A/mysql/testdb/example_table.ibd.hdlk;

Starting at 100G, each time you reduce 1G, stop for 2 seconds, continue until the file is only 1G, and then use the RM command to remove the remaining portions.

For the entire database deletion can first delete the larger table, and finally in the execution drop database to delete the entire library, the deletion of large tables can be found in the above method.

Considerations for MySQL drop table operation and drop large table