## 编程知识 cdmana.com

### Preface

Delete table , The command you subconsciously think of may be direct use DROP TABLE " Table name ", It's the practice of a newborn calf , Because when you want to delete the expression space to dozens of G, Even hundreds G Watch time . Such an order goes on ,MySQL Maybe it's just rammed , The external manifestation is QPS It's falling rapidly , Customer requests slow down .

### terms of settlement

#### 1. Delete manually in low peak time

This may require DBA Take pains , I got up in the evening and deleted the table .

#### 2. Clear the data first , The last way to delete

for example 1000 Ten thousand data , Write a script every time you delete 20 ten thousand , Sleep for a while , Carry on . In this way, we can also have no perception of users .

This method makes use of linux Knowledge of hard links , To quickly delete , If you don't remember, you can go back and look for it 《 Bird brother linux Private dishes 》

ln data_center_update_log.ibd data_center_update_log.ibd.hdlk

[root@mysql01 sports_center]# ll
Total usage  19903792
-rw-r----- 1 mysql mysql       9076 10 month  17 13:15 data_center_update_log.frm
-rw-r----- 2 mysql mysql 8447328256 12 month  23 11:35 data_center_update_log.ibd
-rw-r----- 2 mysql mysql 8447328256 12 month  23 11:35 data_center_update_log.ibd.hdlk


• After executing the above order , We have one more data_center_update_log.ibd.hdlk file . This operation does not actually take up disk space , Just added a reference to the file on disk .
• When we delete any of these files , Will not affect the real files on the disk , Just subtract the number of references 1. When the number of references becomes 1 When , And then delete the file , To really do IO To delete it .
• It is by taking advantage of this characteristic that , It will be changed from mysql To delete a large file , Convert to a simple operating system level file deletion , This reduces the need for mysql Influence .

#### 4. land mysql, perform drop Table operations

 Soon ,200 Ten thousand pieces of data only use 1 Seconds to complete , This is done after the hard link is created
mysql> drop tables data_center_update_log;
Query OK, 0 rows affected (1.02 sec)

mysql> exit
Bye

Back out , Look at the data catalog again , It turns out that it's just data_center_update_log.ibd.hdlk Hard link file
[root@mysql01 sports_center]# ll
Total usage  19903792
-rw-r----- 2 mysql mysql 8447328256 12 month  23 11:35 data_center_update_log.ibd.hdlk

• although drop table after , The remaining hard link files have been linked with mysql It doesn't matter . But if the file is too large , Direct use rm Command to delete , It will also cause IO Spending soared ,CPU Overload , Which in turn affects MySQL.
• The method we use here , It can be deleted in a circular block , Slowly clean up the files , It can be done in a script
• Truncate The command is usually used to reduce or expand a file to a specified size . If the file is larger than the specified size , You lose extra data . If the file is short , It will be extended , And the extension reads zero bytes .
##### 5.1 install truncate command
[root@mysql01 ~]# cruncate
Usually the operating system will install truncate command , The command is in coreutils Inside the installation package , If not installed, you can use the following command to install

[root@mysql01 ~]# yum provides truncate
coreutils-8.22-24.el7.x86_64 : A set of basic GNU tools commonly used in shell scripts
Source     ：base
Match source ：
file name     ：/usr/bin/truncate

You can see truncate from coreutils The installation package provides , The following installation coreutils Installation package ：

[root@mysql01 ~]# yum install -y coreutils


##### 5.2 truncate Common options
-c, --no-create -->  Do not create any files
-o, --io-blocks -->  Think of the size as the number of storage blocks , Not bytes
-r, --reference=RFILE -->  Refer to the specified file size
-s, --size=SIZE -->  Set the file size according to the specified bytes

##### 5.3 truncate_bigfile.sh Script
• principle ： Use truncate -s Option to specify the file size , Specify the size of each file reduction by script , and sleep Sleep for a certain amount of time , So as to achieve controllable deletion of files
• attach ：truncate_bigfile.sh Script
#! /bin/bash
#

TRUNCATE=/usr/bin/truncate
FILE=$1 if [ x"$1" = x ];then
exit 1;
else
SIZE_M=$(du -sm "$1" | awk '{print $1}') for i in$(seq "${SIZE_M}" -100 0) do sleep 1 echo "${TRUNCATE} -s ${i}M${FILE}"
${TRUNCATE} -s "${i}"M "${FILE}" done fi if [$? -eq 0 ];then
\rm -f "\${FILE}"
else