如果問你怎么從MySQL
數(shù)據(jù)庫空drop一張表,很多人估計會說,很簡單啊,用drop table t_test
語句不就行了。這可真是初生牛犢不怕虎,你要真覺得這么簡單的話,去線上業(yè)務(wù)庫中drop
掉一張1TB大小的表,造成長時間的業(yè)務(wù)無法訪問數(shù)據(jù)庫,更嚴(yán)重,導(dǎo)致數(shù)據(jù)庫崩潰,宕機都是可能的。
下面就先聊聊,drop table
語句背后的事情,語句執(zhí)行之后,主要做2兩件事情
1、清除Buffer Pool緩沖
在drop table
時,innodb
引擎會清理該表在每個buffer pool
實例中中對應(yīng)的數(shù)據(jù)塊頁面,為了避免對系統(tǒng)的影響,這里的清除操作并不是真正的flush
,而是將涉及到的頁面從flush
隊列中摘除。但在摘除過程中,刪除進程會持有每個buffer pool
的全局鎖,然后搜索這個buffer pool
里對應(yīng)的頁面以便從flush list
中刪除。如果在buffer pool
中需要被搜索并刪除的頁面過多,那么遍歷時間就會增大,這就導(dǎo)致了其他事務(wù)操作被阻塞,嚴(yán)重時可導(dǎo)致數(shù)據(jù)庫鎖住。
(推薦課程:MySQL教程)
在這里還需要注意一件事情,如果數(shù)據(jù)庫的buffer pool
設(shè)置的很大,就會導(dǎo)致遍歷時間變長
清理buffer pool
時,還包含清理AHI
包含此表的數(shù)據(jù),AHI
的功能在這里就不多說了,主要是當(dāng)b+tree
的層級變高時,為避免b+tree
逐層搜索,AHI
能根據(jù)某個檢索條件,直接查詢到對應(yīng)的數(shù)據(jù)頁,跳過逐層定位的步驟。其次AHI會占用 1/16 的buffer pool
的大小,如果線上表數(shù)據(jù)不是特別大,不是超高并發(fā),不建議將開啟AHI,可以考慮關(guān)閉AHI
功能
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | ON |
+----------------------------+-------+
1 row in set (0.01 sec)
mysql> SET GLOBAL innodb_adaptive_hash_index=OFF;
Query OK, 0 rows affected (0.00 sec)
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_hash_index';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_adaptive_hash_index | OFF |
+----------------------------+-------+
1 row in set (0.01 sec)
2、刪除對應(yīng)的磁盤數(shù)據(jù)文件ibd
在刪除數(shù)據(jù)文件時,如果數(shù)據(jù)文件過大,刪除過程會產(chǎn)生大量的IO
并耗費更多的時間,造成磁盤IO
開銷飆升,CPU
負(fù)載過高,影響其他程序運行。我的一個好伙伴,就曾在線上庫刪除了一張 1TB 大小的表,結(jié)果20分鐘,數(shù)據(jù)庫無響應(yīng),最后庫崩潰,重啟了。
既然知道drop table
做了2件事情,那就針對以上 2 個事情進行優(yōu)化
在清除Buffer Pool
緩沖上,為減少當(dāng)個buffer pool
的大小,可以合理設(shè)置innodb_buffer_pool_instances
參數(shù),減少buffer pool
數(shù)據(jù)塊列表掃描時間,同時關(guān)閉AHI
功能
在步驟2上,可以巧妙的利用linux
的硬連接特性,延遲刪除真正的物理文件。
首先看看linux
系統(tǒng)的硬鏈接示意圖
當(dāng)多個文件名同時指向同一個INODE
時,這個INODE
的引用數(shù) N>1, 刪除其中任何一個文件名都會很快.因為其直接的物理文件塊沒有被刪除.只是刪除了一個指針而已;當(dāng)INODE
的引用數(shù) N=1 時, 刪除文件需要去把這個文件相關(guān)的所有數(shù)據(jù)塊清除,所以會比較耗時;
如果給數(shù)據(jù)庫表的.ibd
文件創(chuàng)建一個硬鏈接,當(dāng)刪除表時,刪除物理文件時,其實刪除的就是物理文件的一個指針,所以刪除操作響應(yīng)速度會非常快,大約不到1秒左右
下面就來演示一下具體的操作
先創(chuàng)建表文件的硬鏈接
ln t_test.ibd t_test.ibd.bak
刪除表
drop table t_test;
最后就是要真正刪除掉物理文件,釋放文件所占用的磁盤空間,那么問題來了,如果優(yōu)雅的刪除物理文件呢,在這里推薦大家coreutils
工具集中的truncate
命令
當(dāng)然需要你先安裝相關(guān)的軟件包
wget http://ftp.gnu.org/gnu/coreutils/coreutils-8.29.tar.xz
使用非root進行解壓
tar -xvJf coreutils-8.29.tar.xz
cd coreutils-8.29
./configure
make
使用root進行make install
安裝好之后,就可以寫一個腳本,非常優(yōu)雅的分布刪除大文件,${i}G
表示,每次刪除 10G
#!/bin/bash
TRUNCATE=/usr/local/bin/truncate
for i in `seq 2194 -10 10 `;
do
sleep 2
$TRUNCATE -s ${i}G /data/mysql/t_test.ibd.hdlk
done
rm -rf /data/mysql/t_test.ibd.hdlk ;
最后,給大家一個建議,不要在業(yè)務(wù)高峰期做drop table
操作,一定要在業(yè)務(wù)低峰期做。
(推薦微課:MySQL微課)
文章來源:www.toutiao.com/a6863864032139411975/
以上就是W3Cschool編程獅
關(guān)于 如何drop掉mysql庫中的1TB表單 的相關(guān)介紹了,希望對大家有所幫助。