Shulou(Shulou.com)06/01 Report--

The correct method for emptying and deleting large tables in MySQL

1 clear the big table 1.1 truncate

The way to delete data in a table is delete,truncate, where TRUNCATE TABLE is used to delete all rows in the table without recording a single row delete operation. TRUNCATE TABLE is similar to a DELETE statement without a WHERE clause; however, TRUNCATE TABLE is faster and uses fewer system and transaction log resources.

Truncate clears the table data. It takes more than 20 seconds for a 50-gigabyte table

Mysql > truncate table old_table;1.2 rename1.2.1 format: mysql > rename table original table name to new table name

When you execute RENAME, you cannot have any locked tables or active transactions. You must also have ALTER and DROP permissions on the original table, as well as CREATE and INSERT permissions on the new table.

1.2.2 instance # create an empty table (test); mysql > create table old_table (id int); # new_table clone old_table table structure; mysql > create table new_table like old_table;# old_table renamed to backup_table,new_table > RENAME TABLE old_table TO backup_table,new_table TO old_table;# delete old tables and data; mysql > DROP TABLE old_table; 2 delete 2.1 background

In the production environment, there may be a need to delete an unimportant large table, because the large table takes up a lot of disk space. If we drop this table directly, it usually takes more than 20 seconds. We will always feel that the master MySQL will be stuck. Now we will give you a correct way to delete it.

2.2 first let's look at this large table # shell [root@ras221 db] # du-shc old_table*12K old_table.frm49G old_ table.ibd [root @ ras221 db] # ls-l old_table*-rw-r- 1 mysql mysql 9075 November 8 11:39 old_table.frm-rw-r- 1 mysql mysql 52273610752 January 10 14:12 old_ table.ibd [root @ ras221 db] # ln old_table.ibd old_ Table.ibd.bak # hard links [root@ras221 db] # ls-l old_table*-rw-r- 1 mysql mysql 9075 November 8 11:39 old_table.frm-rw-r- 1 mysql mysql 52273610752 January 10 14:12 old_table.ibd-rw-r- 1 mysql mysql 52273610752 January 10 14:12 old_table.ibd.bak [root@ras221 db] # mysql mysql > drop table old_table;Query Ok, 0 rows affacted (0.92sec)

By doing this, you can reduce the time it takes for mysql drop big hang to live, and then delete the real .bak piece during the business trough.

As for the principle:

Is to use the principle of OS HARD LINK.

When multiple filenames point to the same INODE at the same time, the number of references to the INODE is N > 1, and deleting any one filename will be quick.

Because its direct physical file block has not been deleted. Just deleted a pointer.

When the reference number of INODE is Number1, deleting a file needs to clear all data blocks related to the file, so it will be time-consuming

For large table operations, there are actually several Tips:

You can replace drop table with rename table first.

Mysql > rename table old_table to old_table_bak

This action is also quickly equivalent to changing a file name.

You can also consider using the XFS file system, which is faster for Drop tables.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.