MYSQL下如何安全的快速刪除大表

1.1 適用場景

如果資料庫裡有一張大於1T的表需要刪除，那麼用什麼方法操作最安全呢？所謂的安全，即為刪除過程中，不影響資料庫的響應，業務連線不會卡殼，系統正常使用……

假如直接登入資料庫使用drop table tablename;會是什麼情況？在drop table的時候，innodb會維護一個全域性鎖，drop完畢鎖才能釋放。這就意味著，在白天，訪問量非常大的時候，執行刪除大表的命令，整個mysql就會掛起，在刪表期間，QPS也會嚴重下滑，後果可想而知……

1.2 最佳選擇（原理）

正確的方法為使用linux下硬連結的知識，進行大表刪除，對資料庫自身的健康度影響較小。

所謂的硬連結，就是不止一個檔名指向node Index，有好幾個檔名指向node Index。
假設，又有一個檔名指向上面的node Index，如下：

這時，如果做了刪除檔名(1)的操作，linux系統檢測到，還有一個檔名(2)指向node Index，因此並不會真正的把檔案刪了，而是把檔案(1)的引用給刪了，這步操作非常快，只是刪除引用。於是就會變成如下:

接下來，如果再刪除檔名(2)的操作，linux系統檢測到，沒有其他檔名指向該node Index，就會刪除真正的儲存檔案，這步操作，是刪真正的檔案，所以會比較慢。這時，我們寫一個for迴圈，進行小量檔案，多次刪除，直到大檔案變小，安全了，才從磁碟上RM。

1.3 環境說明

    雙主架構：192.168.157.50：30016，192.168.157.51：30016

    例項名字：xxx_test

    刪除大表：xxx.alipay_fans，大小為30GB

    檔案路徑：/data/mysql/db_xxx_test/data/xxx/Alipay_fans.ibd

1.4 操作步驟

1.4.1       建立硬連結

在50主機上：

 

在51主機上：

 

1.4.2     刪除表

 只在主庫50上操作，drop後只剩下bbk的硬連結。

 

  檢視51上的檔案情況，同主庫一樣，只剩下bbk的硬連結。

 

1.4.3     刪除檔案

 在50主機上操作：

   

 在51主機上操作：

   

1.4.4     驗證主從狀態

操作完成後，主從狀態正常，資料庫狀態正常。

 

 

1.5 指令碼說明

#filename=/data/mysql/db_xxx_test/data/xxx/alipay_fans.ibd   #filesize=30  # 定義要刪除檔案的大小，單位GB（以實際大小調整） #ln $filename $filename.bbk # for i in `seq $filesize -1 1`;do truncate -s ${i}G  $filename.bbk;ll -h $filename.bbk;done  #rm $filename.bbk

 

備註：從檔案大小30G開始，每次縮減1G，執行truncate，直到檔案剩餘1G，最後使用rm命令刪除剩餘的部分。