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

這篇文章給大家分享的是有關(guān)Innodb中MySQL如何刪除2T的大表的內(nèi)容。小編覺得挺實(shí)用的，因此分享給大家做個(gè)參考，一起跟隨小編過(guò)來(lái)看看吧。

成都創(chuàng)新互聯(lián)公司是一家集網(wǎng)站建設(shè),渾源企業(yè)網(wǎng)站建設(shè),渾源品牌網(wǎng)站建設(shè),網(wǎng)站定制,渾源網(wǎng)站建設(shè)報(bào)價(jià),網(wǎng)絡(luò)營(yíng)銷,網(wǎng)絡(luò)優(yōu)化,渾源網(wǎng)站推廣為一體的創(chuàng)新建站企業(yè)，幫助傳統(tǒng)企業(yè)提升企業(yè)形象加強(qiáng)企業(yè)競(jìng)爭(zhēng)力?？沙浞譂M足這一群體相比中小企業(yè)更為豐富、高端、多元的互聯(lián)網(wǎng)需求。同時(shí)我們時(shí)刻保持專業(yè)、時(shí)尚、前沿，時(shí)刻以成就客戶成長(zhǎng)自我，堅(jiān)持不斷學(xué)習(xí)、思考、沉淀、凈化自己，讓我們?yōu)楦嗟钠髽I(yè)打造出實(shí)用型網(wǎng)站。

假設(shè)，你有一個(gè)表erp,如果你直接進(jìn)行下面的命令

drop table erp

這個(gè)時(shí)候所有的mysql的相關(guān)進(jìn)程都會(huì)停止，直到drop結(jié)束，mysql才會(huì)恢復(fù)執(zhí)行。出現(xiàn)這個(gè)情況的原因就是因?yàn)?，在drop table的時(shí)候，innodb維護(hù)了一個(gè)全局鎖，drop完畢鎖就釋放了。

這意味著，如果在白天，訪問(wèn)量非常大的時(shí)候，如果你在不做任何處理措施的情況下，執(zhí)行了刪大表的命令，整個(gè)mysql就掛在那了，在刪表期間，QPS會(huì)嚴(yán)重下滑，然后產(chǎn)品經(jīng)理就來(lái)找你喝茶了。所以才有了漫畫中的一幕，你可以在晚上十二點(diǎn)，夜深人靜的時(shí)候再刪。

當(dāng)然，有的人不服，可能會(huì)說(shuō)："你可以寫一個(gè)刪除表的存儲(chǔ)過(guò)程，在晚上沒啥訪問(wèn)量的時(shí)候運(yùn)行一次就行。"
我內(nèi)心一驚，細(xì)想一下，只能說(shuō)："大家還是別抬杠了，還是聽我說(shuō)一下業(yè)內(nèi)通用做法。"

一個(gè)假設(shè)

先說(shuō)明一下，在這里有一個(gè)前提，mysql開啟了獨(dú)立表空間，MySQL5.6.7之后默認(rèn)開啟。

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

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 ，代表采用的是獨(dú)立表空間。

于是，大家要問(wèn)我，獨(dú)立表空間和共享表空間的區(qū)別？

共享表空間：某一個(gè)數(shù)據(jù)庫(kù)的所有的表數(shù)據(jù)，索引文件全部放在一個(gè)文件中，默認(rèn)這個(gè)共享表空間的文件路徑在data目錄下。 默認(rèn)的文件名為:ibdata1(此文件，可以擴(kuò)展成多個(gè))。注意，在這種方式下，運(yùn)維超級(jí)不方便。你看，所有數(shù)據(jù)都在一個(gè)文件里，要對(duì)單表維護(hù)，十分不方便。另外，你在做delete操作的時(shí)候，文件內(nèi)會(huì)留下很多間隙，ibdata1文件不會(huì)自動(dòng)收縮。換句話說(shuō)，使用共享表空間來(lái)存儲(chǔ)數(shù)據(jù)，會(huì)遭遇drop table之后，空間無(wú)法釋放的問(wèn)題。

獨(dú)立表空間:每一個(gè)表都以獨(dú)立方式來(lái)部署，每個(gè)表都有一個(gè).frm表描述文件，還有一個(gè).ibd文件。

.frm文件：保存了每個(gè)表的元數(shù)據(jù)，包括表結(jié)構(gòu)的定義等，該文件與數(shù)據(jù)庫(kù)引擎無(wú)關(guān)。

.ibd文件：保存了每個(gè)表的數(shù)據(jù)和索引的文件。

注意，在這種方式下，每個(gè)表都有自已獨(dú)立的表空間，這樣運(yùn)維起來(lái)方便，可以實(shí)現(xiàn)單表在不同數(shù)據(jù)庫(kù)之間的移動(dòng)。另外，在執(zhí)行drop table操作的時(shí)候，是可以自動(dòng)回收表空間。在執(zhí)行delete操作后，可以通過(guò)alter table TableName engine=innodb可以整理碎片，回收部分表空間。

ps：my.cnf中的datadir就是用來(lái)設(shè)置數(shù)據(jù)存儲(chǔ)目錄

好了，上面巴拉巴拉了一大堆，我只想說(shuō)一個(gè)事情:

在絕大部分情況下，運(yùn)維一定會(huì)為mysql選擇獨(dú)立表空間的存儲(chǔ)方式，因?yàn)椴捎锚?dú)立表空間的方式，從性能優(yōu)化和運(yùn)維難易角度來(lái)說(shuō)，實(shí)在強(qiáng)太多。

所以，我在一開始所提到的前提，mysql需要開啟獨(dú)立表空間。這個(gè)假設(shè)，百分九十的情況下是成立的。如果真的遇到了，你們公司的mysql采用的是共享表空間的情況，請(qǐng)你和你們家的運(yùn)維談?wù)勑?，?wèn)問(wèn)為啥用共享表空間。

正確姿勢(shì)

假設(shè)，我們有datadir = /data/mysql/,另外，我們有有一個(gè)database,名為mytest。在數(shù)據(jù)庫(kù)mytest中，有一個(gè)表，名為erp，執(zhí)行下列命令

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

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

-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的作用，上面介紹過(guò)了?，F(xiàn)在就是erp.ibd文件太大，所以刪除卡住了。

如何解決這個(gè)問(wèn)題呢？

這里需要利用了linux中硬鏈接的知識(shí)，來(lái)進(jìn)行快速刪除。下面容我上《鳥哥的私房菜》中的一些內(nèi)容，

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

至于這個(gè)硬鏈接，我簡(jiǎn)單說(shuō)一下，不想貼一大堆話過(guò)來(lái)，看起來(lái)太累。

就是對(duì)于真正存儲(chǔ)的文件來(lái)說(shuō)，有一個(gè)

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

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

假設(shè)，這會(huì)又有一個(gè)文件名指向上面的node Index，即

這個(gè)時(shí)候，你做了刪除文件名(1)的操作，linux系統(tǒng)檢測(cè)到，還有一個(gè)文件名(2)指向node Index，因此并不會(huì)真正的把文件刪了，而是把步驟(2)的引用給刪了，這步操作非?？欤吘怪皇莿h除引用。于是圖就變成了這樣

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

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

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

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

此時(shí)，文件目錄如下所示

-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

你會(huì)發(fā)現(xiàn)，多了一個(gè)erp.ibd.hdlk文件，且erp.ibd和erp.ibd.hdlk的inode均為2。

此時(shí)，你執(zhí)行drop table操作

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

你會(huì)發(fā)現(xiàn)，不到1秒就刪除了。因?yàn)椋藭r(shí)有兩個(gè)文件名稱(erp.ibd和erp.ibd.hdlk),同時(shí)指向一個(gè)inode.這個(gè)時(shí)候，執(zhí)行刪除操作，只是把引用給刪了，所以非?？?。

那么，這時(shí)的刪除，已經(jīng)把table從mysql中刪除。但是磁盤空間，還沒釋放，因?yàn)檫€剩一個(gè)文件erp.ibd.hdlk。

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

如果你沒啥經(jīng)驗(yàn)，一定會(huì)回答我，用rm命令來(lái)刪。這里需要說(shuō)明的是，在生產(chǎn)環(huán)境，直接用rm命令來(lái)刪大文件，會(huì)造成磁盤IO開銷飆升,CPU負(fù)載過(guò)高，是會(huì)影響其他程序運(yùn)行的。

那么，這種時(shí)候，就是應(yīng)該用truncate命令來(lái)刪，truncate命令在coreutils工具集中。

詳情，大家可以去百度一下，有人對(duì)rm和truncate命令，專程測(cè)試過(guò)，truncate命令對(duì)磁盤IO，CPU負(fù)載幾乎無(wú)影響。

刪除腳本如下

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ù)庫(kù)是部署在windows上怎么辦。這個(gè)問(wèn)題，我來(lái)回答，其實(shí)不夠?qū)I(yè)。因?yàn)槲页龅酪詠?lái)，還沒碰到過(guò)，生產(chǎn)環(huán)境上，mysql是部在windows上的。假設(shè)真的碰到了，windows下有一個(gè)工具叫mklink，是在windows下創(chuàng)建硬鏈接鎖用，應(yīng)該能完成類似功能

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

網(wǎng)站欄目：Innodb中mysql如何刪除2T的大表
轉(zhuǎn)載注明：http://muchs.cn/article42/jiodhc.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供云服務(wù)器、全網(wǎng)營(yíng)銷推廣、網(wǎng)站收錄、外貿(mào)網(wǎng)站建設(shè)、網(wǎng)站設(shè)計(jì)、

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請(qǐng)盡快告知，我們將會(huì)在第一時(shí)間刪除。文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng)，如需處理請(qǐng)聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時(shí)需注明來(lái)源： 創(chuàng)新互聯(lián)