歡迎光臨
每天分享高質量文章

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

來自:打雜的ZRJ(微信號:zrj_guduyan)

小漫畫

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

OK,這裡就說了。假設,你有一個表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_tablevalue值為OFF,代表採用的是共享表空間
如果innodb_file_per_tablevalue值為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

frmibd的作用,上面介紹過了。現在就是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命令

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.ibderp.ibd.hdlk的innode均為2。
此時,你執行drop table操作

mysqldrop table erp;
Query OK, 0 rows affected (0.99 sec)

你會發現,不到1秒就刪除了。因為,此時有兩個檔案名稱(erp.ibderp.ibd.hdlk),同時指向一個innode.這個時候,執行刪除操作,只是把取用給刪了,所以非常快。
那麼,這時的刪除,已經把table從mysql中刪除。但是磁盤空間,還沒釋放,因為還剩一個檔案erp.ibd.hdlk
如何正確的刪除erp.ibd.hdlk呢?
如果你沒啥經驗,一定會回答我,用rm命令來刪。這裡需要說明的是,在生產環境,直接用rm命令來刪大檔案,會造成磁盤IO開銷飆升,CPU負載過高,是會影響其他程式運行的。
那麼,這種時候,就是應該用truncate命令來進行刪除。需要說明的是,truncate命令在coreutils工具集中,需要另外安裝。

詳情,大家可以去百度一下安裝教程。另外,網上有流傳一些文章,這些文章對rmtruncate命令專程測試過,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下創建硬鏈接,應該能完成類似功能。

總結

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


●編號397,輸入編號直達本文

●輸入m獲取文章目錄

推薦↓↓↓

 

Web開發

更多推薦18個技術類微信公眾號

涵蓋:程式人生、演算法與資料結構、黑客技術與網絡安全、大資料技術、前端開發、Java、Python、Web開發、安卓開發、iOS開發、C/C++、.NET、Linux、資料庫、運維等。

赞(0)

分享創造快樂