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

-Note: when MySQL deletes a table in a production environment, if the table is too large, tens of gigabytes or even hundreds of gigabytes, you should be careful when deleting it, otherwise it will lead to MySQLhang residence and affect the business.

1. When deleting a table, MySQL is divided into two steps:

①: the process of cleaning up buffer pool pages

When deleting a table, innodb clears the corresponding page of the file in buffer pool. For the page that deletes the table, you only need to delete the page from the flush queue without flush operation, so that the impact on the system can be reduced.

②: the process of deleting ibd disk files (this process is resource-intensive and slow and affects the performance of the database)

2. Delete the pose of the big table correctly:

①: make a hard link to the system files of the table to speed up deletion

For example: ln / mysql/data/test/emp.ibd / mysql/data/test/emp.ibd.hdlk

[mysql@db2 test] $ln / mysql/data/test/emp.ibd / mysql/data/test/emp.ibd.hdlk

[mysql@db2 test] $ls-l

Total 208

-rw-r--r-- 1 mysql mysql 65 Mar 3 10:35 db.opt

-rw-rw---- 1 mysql mysql 8556 May 12 04:40 emp.frm

-rw-rw---- 2 mysql mysql 98304 May 12 04:40 emp.ibd

-rw-rw---- 2 mysql mysql 98304 May 12 04:40 emp.ibd.hdlk

[mysql@db2 test] $

Then execute: drop table emp;-in the database

②: the second method is to delete data in batches and set a conditional orderly deletion. For example, you can delete data in batches according to daily data deletion or batch deletion according to a range.

-Note: when deleting tables in the production database, if the amount of table data is too large, either delete it in batches or do hard links.

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.