前言
本文主要給大家介紹了關(guān)于Innodb中mysql快速刪除2T的大表的相關(guān)內(nèi)容,分享出來供大家參考學習,下面話不多說了,來一起看看詳細的介紹吧
來,先來看小漫畫陶冶一下情操
OK,這里就說了。假設,你有一個表erp,如果你直接進行下面的命令
這個時候所有的mysql的相關(guān)進程都會停止,直到drop結(jié)束,mysql才會恢復執(zhí)行。出現(xiàn)這個情況的原因就是因為,在drop table的時候,innodb維護了一個全局鎖,drop完畢鎖就釋放了。
這意味著,如果在白天,訪問量非常大的時候,如果你在不做任何處理措施的情況下,執(zhí)行了刪大表的命令,整個mysql就掛在那了,在刪表期間,QPS會嚴重下滑,然后產(chǎn)品經(jīng)理就來找你喝茶了。所以才有了漫畫中的一幕,你可以在晚上十二點,夜深人靜的時候再刪。
當然,有的人不服,可能會說:"你可以寫一個刪除表的存儲過程,在晚上沒啥訪問量的時候運行一次就行。"
我內(nèi)心一驚,細想一下,只能說:"大家還是別抬杠了,還是聽我說一下業(yè)內(nèi)通用做法。"
一個假設
先說明一下,在這里有一個前提,mysql開啟了獨立表空間,MySQL5.6.7之后默認開啟。
也就是在my.cnf中,有這么一條配置(這些是屬于mysql優(yōu)化的知識,后期給大家介紹)
innodb_file_per_table = 1
查看表空間狀態(tài),用下面的命令
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 ,代表采用的是獨立表空間。
于是,大家要問我,獨立表空間和共享表空間的區(qū)別?
共享表空間:某一個數(shù)據(jù)庫的所有的表數(shù)據(jù),索引文件全部放在一個文件中,默認這個共享表空間的文件路徑在data目錄下。 默認的文件名為:ibdata1(此文件,可以擴展成多個)。注意,在這種方式下,運維超級不方便。你看,所有數(shù)據(jù)都在一個文件里,要對單表維護,十分不方便。另外,你在做delete操作的時候,文件內(nèi)會留下很多間隙,ibdata1文件不會自動收縮。換句話說,使用共享表空間來存儲數(shù)據(jù),會遭遇drop table之后,空間無法釋放的問題。
獨立表空間:每一個表都以獨立方式來部署,每個表都有一個.frm表描述文件,還有一個.ibd文件。
.frm文件:保存了每個表的元數(shù)據(jù),包括表結(jié)構(gòu)的定義等,該文件與數(shù)據(jù)庫引擎無關(guān)。
.ibd文件:保存了每個表的數(shù)據(jù)和索引的文件。
注意,在這種方式下,每個表都有自已獨立的表空間,這樣運維起來方便,可以實現(xiàn)單表在不同數(shù)據(jù)庫之間的移動。另外,在執(zhí)行drop table操作的時候,是可以自動回收表空間。在執(zhí)行delete操作后,可以通過alter table TableName engine=innodb可以整理碎片,回收部分表空間。
ps:my.cnf中的datadir就是用來設置數(shù)據(jù)存儲目錄
好了,上面巴拉巴拉了一大堆,我只想說一個事情:
在絕大部分情況下,運維一定會為mysql選擇獨立表空間的存儲方式,因為采用獨立表空間的方式,從性能優(yōu)化和運維難易角度來說,實在強太多。
所以,我在一開始所提到的前提,mysql需要開啟獨立表空間。這個假設,百分九十的情況下是成立的。如果真的遇到了,你們公司的mysql采用的是共享表空間的情況,請你和你們家的運維談談心,問問為啥用共享表空間。
正確姿勢
假設,我們有datadir = /data/mysql/,另外,我們有有一個database,名為mytest。在數(shù)據(jù)庫mytest中,有一個表,名為erp,執(zhí)行下列命令
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的作用,上面介紹過了?,F(xiàn)在就是erp.ibd文件太大,所以刪除卡住了。
如何解決這個問題呢?
這里需要利用了linux中硬鏈接的知識,來進行快速刪除。下面容我上《鳥哥的私房菜》中的一些內(nèi)容,
軟鏈接其實大家可以類比理解為windows中的快捷方式,就不多介紹了,主要介紹一下硬鏈接。
至于這個硬鏈接,我簡單說一下,不想貼一大堆話過來,看起來太累。
就是對于真正存儲的文件來說,有一個
然后呢有一個文件名指向上面的node Index
那么,所謂的硬鏈接,就是不止一個文件名指向node Index,有好幾個文件名指向node Index。
假設,這會又有一個文件名指向上面的node Index,即
這個時候,你做了刪除文件名(1)的操作,linux系統(tǒng)檢測到,還有一個文件名(2)指向node Index,因此并不會真正的把文件刪了,而是把步驟(2)的引用給刪了,這步操作非???,畢竟只是刪除引用。于是圖就變成了這樣
接下來,你再做刪除文件名(2)的操作,linux系統(tǒng)檢測到,沒有其他文件名指向該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
你會發(fā)現(xiàn),多了一個erp.ibd.hdlk文件,且erp.ibd和erp.ibd.hdlk的inode均為2。
此時,你執(zhí)行drop table操作
mysql> drop table erp;
Query OK, 0 rows affected (0.99 sec)
你會發(fā)現(xiàn),不到1秒就刪除了。因為,此時有兩個文件名稱(erp.ibd和erp.ibd.hdlk),同時指向一個inode.這個時候,執(zhí)行刪除操作,只是把引用給刪了,所以非常快。
那么,這時的刪除,已經(jīng)把table從mysql中刪除。但是磁盤空間,還沒釋放,因為還剩一個文件erp.ibd.hdlk。
如何正確的刪除erp.ibd.hdlk呢?
如果你沒啥經(jīng)驗,一定會回答我,用rm命令來刪。這里需要說明的是,在生產(chǎn)環(huán)境,直接用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秒,繼續(xù),直到文件只剩10G,最后使用rm命令刪除剩余的部分。
其他情況
這里指的是,如果數(shù)據(jù)庫是部署在windows上怎么辦。這個問題,我來回答,其實不夠?qū)I(yè)。因為我出道以來,還沒碰到過,生產(chǎn)環(huán)境上,mysql是部在windows上的。假設真的碰到了,windows下有一個工具叫mklink,是在windows下創(chuàng)建硬鏈接鎖用,應該能完成類似功能
總結(jié)
本文所講的內(nèi)容,中小型公司的研發(fā)比較容易遇到。因為中小型公司沒有專業(yè)的DBA,研發(fā)童鞋啥都得干。希望大家有所收獲吧。
好了,以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學習或者工作具有一定的參考學習價值,如果有疑問大家可以留言交流,謝謝大家對腳本之家的支持。
您可能感興趣的文章:- mysql 大表批量刪除大量數(shù)據(jù)的實現(xiàn)方法
- MySQL如何優(yōu)雅的刪除大表實例詳解
- MySQL 刪除大表的性能問題解決方案
- 淺談MySQL如何優(yōu)雅的做大表刪除