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 after the table is deleted are not actively deleted by themselves.

The table name specified in the reference table does not exist, but it 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. In addition to removing the table definition, partitions and data are removed from the partition definition file (. par) associated with it.

Number of references in MySQL5.6 [RESTRICT | CASCADE] do no matter what.

[Temporary] keyword indicates that only the temporary table is deleted and the statement does not end the transaction in progress (the DDL statement in MySQL is implicitly committed). Permission checks are not 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 actively deleted by themselves. 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 run the Drop Databasse link and the original database on a library that uses symbolic links, it will be deleted. command to return the number of tables that were removed.
This command removes the files and folders that MySQL generated from the specified database folder when it removed the normal operation. Such as:. Bak  . Dat  . Hsh  . MRG. Myd  . Myi  . Trg  . Trn .db . Frm . Ibd  . ndb. Par if there is db.opt the same will be deleted. If there are other files or folders in the database folder that are not generated by MySQL itself. The entire database folder is not removed. At this point, you need to manually clean up the remaining files and execute the DROP DATABASE statement again.
Deleting a database does not remove the temporary tables created in the library. Temporary tables are cleared at the end of the session or displayed by the drop temporary table.

Remove Large table considerations
Delete the table. Since the InnoDB engine maintains a global exclusive lock at the table cache level until the drop table is complete. Such Other operations on the table will be stuck. For larger tables, the DROP table operation may require a very long time. Therefore, an effective way to improve the deletion speed of large tables is needed to minimize the time of hang. This can be achieved by setting up hard links.

For example, there is a list of examples:
Example_table

When using the InnoDB engine and specifying Innodb_file_per_table=on, there are two files that correspond to the table in the Data folder, such as the following. Define the file and data file for the table, respectively.

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 is 100G so large. Using the drop table directly to complete the delete action, the statement will run for a very long time. You can then delete it by setting a hard link on the appropriate data file for that 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 this point, any file name in the deletion will simply delete the pointer to Innode and not delete the physical file block directly. So it will be soon. The corresponding physical file block is not actually deleted until the reference count of Innode equals 1 o'clock. It is time-consuming to actually delete the physical file blocks.

Run the drop table operation after you have established a hard link:

DROP TABLE example_table;

The discovery will be done very quickly. View the corresponding table definition and data files:

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.

Also just the drop table operation implemented the removal of a pointer to the physical file example_table.ibd, and thus soon.

The rest of the task is to delete the real physical files, since the Innode reference count has changed to 1. Deleting example_table.ibd.hdlk will actually delete the physical file.

But because the physical file is large. Deleting large files can still cause high disk IO overhead.

It is therefore possible to delete large data files using a small number of successive deletions. The Truncate tool can be used to add or shrink 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;

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

The deletion of the entire database removes the larger tables first. Finally, run drop database to delete the entire library. The deletion of large tables can be seen in the above method.

Considerations for MySQL drop table operation and drop large table