Innodb中mysql如何快速刪除2T的大表

標籤：adf   通過   經理   val   相關   database   作用   d3d   永久連結   

小漫畫

來，先來看小漫畫陶冶一下情操

OK，這裡就說了。假設，你有一個表erp,如果你直接進行下面的命令
這個時候所有的mysql的相關進程都會停止，直到drop結束，mysql才會恢複執行。出現這個情況的原因就是因為，在drop table的時候，innodb維護了一個全域鎖，drop完畢鎖就釋放了。

這意味著，如果在白天，訪問量非常大的時候，如果你在不做任何處理措施的情況下，執行了刪大表的命令，整個mysql就掛在那了，在刪表期間，QPS會嚴重下滑，然後產品經理就來找你喝茶了。所以才有了漫畫中的一幕，你可以在晚上十二點，夜深人靜的時候再刪。

當然，有的人不服，可能會說："你可以寫一個刪除表的預存程序，在晚上沒啥訪問量的時候運行一次就行。"

我內心一驚，細想一下，只能說："大家還是別抬杠了，還是聽我說一下業內通用做法。"

一個假設

先說明一下，在這裡有一個前提，mysql開啟了獨立資料表空間，MySQL5.6.7之後預設開啟。

也就是在my.cnf中，有這麼一條配置(這些是屬於mysql最佳化的知識，後期給大家介紹)

查看錶空間狀態，用下面的命令

如果innodb_file_per_table的value值為OFF，代表採用的是共用資料表空間。

如果innodb_file_per_table的value值為ON ，代表採用的是獨立資料表空間。

於是，大家要問我，獨立資料表空間和共用資料表空間的區別？

共用資料表空間：某一個資料庫的所有的表資料，索引檔案全部放在一個檔案中，預設這個共用資料表空間的檔案路徑在data目錄下。 預設的檔案名稱為:ibdata1(此檔案，可以擴充成多個)。注意，在這種方式下，營運超級不方便。你看，所有資料都在一個檔案裡，要對單表維護，十分不方便。另外，你在做delete操作的時候，檔案內會留下很多間隙，ibdata1檔案不會自動收縮。換句話說，使用共用資料表空間來儲存資料，會遭遇drop table之後，空間無法釋放的問題。

獨立資料表空間:每一個表都以獨立方式來部署，每個表都有一個.frm表描述檔案，還有一個.ibd檔案。

.frm檔案：儲存了每個表的中繼資料，包括表結構的定義等，該檔案與資料庫引擎無關。

.ibd檔案：儲存了每個表的資料和索引的檔案。

注意，在這種方式下，每個表都有自已獨立的資料表空間，這樣營運起來方便，可以實現單表在不同資料庫之間的移動。另外，在執行drop table操作的時候，是可以自動回收資料表空間。在執行delete操作後，可以通過執行alter table TableName engine=innodb語句來整理片段，回收部分資料表空間。

ps：my.cnf中的datadir就是用來設定資料存放區目錄

好了，上面巴拉巴拉了一大堆，我只想說一個事情:

Innodb中mysql如何快速刪除2T的大表
所以，我在一開始所提到的前提，mysql需要開啟獨立資料表空間。這個假設，百分九十的情況下是成立的。如果真的遇到了，你們公司的mysql採用的是共用資料表空間的情況，請你和你們家的營運談談心，問問為啥用共用資料表空間。

正確姿勢

假設，我們有datadir = /data/mysql/,另外，我們有一個database,名為mytest。在資料庫mytest中，有一個表，名為erp，執行下列命令

得到下面的輸出(我過濾了一下)

frm和ibd的作用，上面介紹過了。現在就是erp.ibd檔案太大，所以刪除卡住了。

如何解決這個問題呢？

這裡需要利用了linux中永久連結的知識，來進行快速刪除。下面容我上《鳥哥的私房菜》中的一些內容，

軟連結其實大家可以類比理解為windows中的捷徑，就不多介紹了，主要介紹一下永久連結。

至於這個永久連結，我簡單說一下，不想貼一大堆話過來，看起來太累。

就是對於真正儲存的檔案來說，有一個Inode Index指向隱藏檔

然後呢有一個檔案名稱指向的Inode Index

那麼，所謂的永久連結，就是不止一個檔案名稱指向Inode Index，有好幾個檔案名稱指向Inode Index。

假設，這會又有一個檔案名稱指向上面的Inode Index，即

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

接下來，你再做刪除檔案名稱(2)的操作，linux系統檢測到，沒有其他檔案名稱指向該Inode Index，就會刪除真正的隱藏檔，這步操作，是刪真正的檔案，所以比較慢。

OK，我們用的就是上面的原理。

先給erp.ibd建立一個永久連結，利用ln命令

此時，檔案目錄如下所示

你會發現，多了一個erp.ibd.hdlk檔案，且erp.ibd和erp.ibd.hdlk的innode均為2。

此時，你執行drop table操作

你會發現，不到1秒就刪除了。因為，此時有兩個檔案名稱(erp.ibd和erp.ibd.hdlk),同時指向一個innode.這個時候，執行刪除操作，只是把引用給刪了，所以非常快。

那麼，這時的刪除，已經把table從mysql中刪除。但是磁碟空間，還沒釋放，因為還剩一個檔案erp.ibd.hdlk。

如何正確的刪除erp.ibd.hdlk呢？

如果你沒啥經驗，一定會回答我，用rm命令來刪。這裡需要說明的是，在生產環境，直接用rm命令來刪大檔案，會造成磁碟IO開銷飆升,CPU負載過高，是會影響其他程式啟動並執行。

那麼，這種時候，就是應該用truncate命令來進行刪除。需要說明的是，truncate命令在coreutils工具集中，需要另外安裝。

詳情，大家可以去百度一下安裝教程。另外，網上有流傳一些文章，這些文章對rm和truncate命令專程測試過，truncate命令對磁碟IO，CPU負載幾乎無影響。

刪除指令碼如下

從2194G開始，每次縮減10G，停2秒，繼續，直到檔案只剩10G，最後使用rm命令刪除剩餘的部分。

其他情況

這裡指的是，如果資料庫是部署在windows上怎麼辦。這個問題，我來回答，其實不夠專業。因為我出道以來，還沒碰到過，生產環境上，mysql是部在windows上的。假設真的碰到了，windows下有一個工具叫mklink，是可以在windows下建立永久連結，應該能完成類似功能。

總結

第一篇mysql的文章，試試水，本文有點偏營運，所講的內容，中小型公司的研發比較容易遇到。因為中小型公司沒有專業的DBA，研發童鞋啥都得幹。希望大家有所收穫吧。

Innodb中mysql如何快速刪除2T的大表