如何drop掉mysql庫中的1TB表單

如果問你怎么從MySQL數(shù)據(jù)庫空drop一張表，很多人估計(jì)會(huì)說，很簡(jiǎn)單啊，用drop table t_test語句不就行了。這可真是初生牛犢不怕虎，你要真覺得這么簡(jiǎn)單的話，去線上業(yè)務(wù)庫中drop掉一張1TB大小的表，造成長(zhǎng)時(shí)間的業(yè)務(wù)無法訪問數(shù)據(jù)庫，更嚴(yán)重，導(dǎo)致數(shù)據(jù)庫崩潰，宕機(jī)都是可能的。

下面就先聊聊，drop table語句背后的事情，語句執(zhí)行之后，主要做2兩件事情

1、清除Buffer Pool緩沖

在drop table時(shí)，innodb引擎會(huì)清理該表在每個(gè)buffer pool實(shí)例中中對(duì)應(yīng)的數(shù)據(jù)塊頁面，為了避免對(duì)系統(tǒng)的影響，這里的清除操作并不是真正的flush，而是將涉及到的頁面從flush隊(duì)列中摘除。但在摘除過程中，刪除進(jìn)程會(huì)持有每個(gè)buffer pool的全局鎖，然后搜索這個(gè)buffer pool里對(duì)應(yīng)的頁面以便從flush list中刪除。如果在buffer pool中需要被搜索并刪除的頁面過多，那么遍歷時(shí)間就會(huì)增大，這就導(dǎo)致了其他事務(wù)操作被阻塞，嚴(yán)重時(shí)可導(dǎo)致數(shù)據(jù)庫鎖住。

(推薦課程：MySQL教程)

在這里還需要注意一件事情，如果數(shù)據(jù)庫的buffer pool設(shè)置的很大，就會(huì)導(dǎo)致遍歷時(shí)間變長(zhǎng) 清理buffer pool時(shí)，還包含清理AHI包含此表的數(shù)據(jù)，AHI的功能在這里就不多說了，主要是當(dāng)b+tree的層級(jí)變高時(shí)，為避免b+tree逐層搜索，AHI能根據(jù)某個(gè)檢索條件，直接查詢到對(duì)應(yīng)的數(shù)據(jù)頁，跳過逐層定位的步驟。其次AHI會(huì)占用 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、刪除對(duì)應(yīng)的磁盤數(shù)據(jù)文件ibd

在刪除數(shù)據(jù)文件時(shí)，如果數(shù)據(jù)文件過大，刪除過程會(huì)產(chǎn)生大量的IO并耗費(fèi)更多的時(shí)間，造成磁盤IO開銷飆升,CPU負(fù)載過高，影響其他程序運(yùn)行。我的一個(gè)好伙伴，就曾在線上庫刪除了一張 1TB 大小的表，結(jié)果20分鐘，數(shù)據(jù)庫無響應(yīng)，最后庫崩潰，重啟了。

既然知道drop table做了2件事情，那就針對(duì)以上 2 個(gè)事情進(jìn)行優(yōu)化

在清除Buffer Pool緩沖上，為減少當(dāng)個(gè)buffer pool的大小，可以合理設(shè)置innodb_buffer_pool_instances參數(shù)，減少buffer pool數(shù)據(jù)塊列表掃描時(shí)間，同時(shí)關(guān)閉AHI功能

在步驟2上，可以巧妙的利用linux的硬連接特性，延遲刪除真正的物理文件。

首先看看linux系統(tǒng)的硬鏈接示意圖

當(dāng)多個(gè)文件名同時(shí)指向同一個(gè)INODE時(shí),這個(gè)INODE的引用數(shù) N>1, 刪除其中任何一個(gè)文件名都會(huì)很快.因?yàn)槠渲苯拥奈锢砦募K沒有被刪除.只是刪除了一個(gè)指針而已;當(dāng)INODE的引用數(shù) N=1 時(shí), 刪除文件需要去把這個(gè)文件相關(guān)的所有數(shù)據(jù)塊清除,所以會(huì)比較耗時(shí);

如果給數(shù)據(jù)庫表的.ibd文件創(chuàng)建一個(gè)硬鏈接，當(dāng)刪除表時(shí)，刪除物理文件時(shí)，其實(shí)刪除的就是物理文件的一個(gè)指針，所以刪除操作響應(yīng)速度會(huì)非?？?，大約不到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進(jìn)行解壓
tar -xvJf coreutils-8.29.tar.xz
cd coreutils-8.29
./configure
make
使用root進(jìn)行make install

安裝好之后，就可以寫一個(gè)腳本，非常優(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 ;

最后，給大家一個(gè)建議，不要在業(yè)務(wù)高峰期做drop table操作，一定要在業(yè)務(wù)低峰期做。

(推薦微課：MySQL微課)

文章來源：www.toutiao.com/a6863864032139411975/

以上就是W3Cschool編程獅關(guān)于 如何drop掉mysql庫中的1TB表單 的相關(guān)介紹了，希望對(duì)大家有所幫助。