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

這篇文章給大家分享的是有關Innodb中mysql如何刪除2T的大表的內容。小編覺得挺實用的，因此分享給大家做個參考，一起跟隨小編過來看看吧。

假設，你有一個表erp,如果你直接進行下面的命令

drop table erp

這個時候所有的mysql的相關進程都會停止，直到drop結束，mysql才會恢復執行。出現這個情況的原因就是因為，在drop table的時候，innodb維護了一個全局鎖，drop完畢鎖就釋放了。

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

當然，有的人不服，可能會說："你可以寫一個刪除表的存儲過程，在晚上沒啥訪問量的時候運行一次就行。"
我內心一驚，細想一下，只能說："大家還是別抬杠了，還是聽我說一下業內通用做法。"

一個假設

先說明一下，在這里有一個前提，mysql開啟了獨立表空間，MySQL5.6.7之后默認開啟。

也就是在my.cnf中，有這么一條配置(這些是屬于mysql優化的知識，后期給大家介紹)

innodb_file_per_table = 1

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

mysql> show variables like '%per_table'; 
+-----------------------+-------+ 
| Variable_name  | Value | 
+-----------------------+-------+ 
| innodb_file_per_table | OFF | 
+-----------------------+-------+

如果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就是用來設置數據存儲目錄

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

在絕大部分情況下，運維一定會為mysql選擇獨立表空間的存儲方式，因為采用獨立表空間的方式，從性能優化和運維難易角度來說，實在強太多。

所以，我在一開始所提到的前提，mysql需要開啟獨立表空間。這個假設，百分九十的情況下是成立的。如果真的遇到了，你們公司的mysql采用的是共享表空間的情況，請你和你們家的運維談談心，問問為啥用共享表空間。

正確姿勢

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

mysql> system ls -l /data/mysql/mytest/

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

-rw-r----- 1 mysql mysql  9023 8 18 05:21 erp.frm
-rw-r----- 1 mysql mysql 2356792000512 8 18 05:21 erp.ibd

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

如何解決這個問題呢？

這里需要利用了linux中硬鏈接的知識，來進行快速刪除。下面容我上《鳥哥的私房菜》中的一些內容，

軟鏈接其實大家可以類比理解為windows中的快捷方式，就不多介紹了，主要介紹一下硬鏈接。

至于這個硬鏈接，我簡單說一下，不想貼一大堆話過來，看起來太累。

就是對于真正存儲的文件來說，有一個

然后呢有一個文件名指向上面的node Index

那么，所謂的硬鏈接，就是不止一個文件名指向node Index，有好幾個文件名指向node Index。

假設，這會又有一個文件名指向上面的node Index，即

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

接下來，你再做刪除文件名(2)的操作，linux系統檢測到，沒有其他文件名指向該node Index，就會刪除真正的存儲文件，這步操作，是刪真正的文件，所以比較慢。

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

先給erp.ibd建立一個硬鏈接，利用ln命令

mysql> system ln /data/mysql/mytest/erp.ibd /data/mysql/mytest/erp.ibd.hdlk

此時，文件目錄如下所示

-rw-r----- 1 mysql mysql          9023  8 18 05:21 erp.frm
-rw-r----- 2 mysql mysql 2356792000512  8 18 05:21 erp.ibd
-rw-r----- 2 mysql mysql 2356792000512  8 18 05:21 erp.ibd.hdlk

你會發現，多了一個erp.ibd.hdlk文件，且erp.ibd和erp.ibd.hdlk的inode均為2。

此時，你執行drop table操作

mysql> drop table erp;
Query OK, 0 rows affected (0.99 sec)

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

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

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

如果你沒啥經驗，一定會回答我，用rm命令來刪。這里需要說明的是，在生產環境，直接用rm命令來刪大文件，會造成磁盤IO開銷飆升,CPU負載過高，是會影響其他程序運行的。

那么，這種時候，就是應該用truncate命令來刪，truncate命令在coreutils工具集中。

詳情，大家可以去百度一下，有人對rm和truncate命令，專程測試過，truncate命令對磁盤IO，CPU負載幾乎無影響。

刪除腳本如下

TRUNCATE=/usr/local/bin/truncate
for i in `seq 2194 -10 10 `; 
do 
 sleep 2
 $TRUNCATE -s ${i}G /data/mysql/mytest/erp.ibd.hdlk 
done
rm -rf /data/mysql/mytest/erp.ibd.hdlk ;

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

其他情況

這里指的是，如果數據庫是部署在windows上怎么辦。這個問題，我來回答，其實不夠專業。因為我出道以來，還沒碰到過，生產環境上，mysql是部在windows上的。假設真的碰到了，windows下有一個工具叫mklink，是在windows下創建硬鏈接鎖用，應該能完成類似功能

感謝各位的閱讀！關于“Innodb中mysql如何刪除2T的大表”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，讓大家可以學到更多知識，如果覺得文章不錯，可以把它分享出去讓更多的人看到吧！