怎麼drop掉mysql函式庫中的1TB表單

1、清除Buffer Pool緩衝

在drop table時，innodb引擎會清理該表在每個buffer pool實例中對應的資料塊頁面，為了避免對系統的影響，這裡的清除操作並不是真正的flush，而是將涉及的頁面從flush佇列中摘除。但在摘除過程中，刪除程序會持有每個buffer pool的全域鎖定，然後搜尋這個buffer pool裡對應的頁面以便從flush list中刪除。如果在buffer pool中需要被搜尋並刪除的頁面過多，那麼遍歷時間就會增大，這就導致了其他事務操作被阻塞，嚴重時可導致資料庫鎖定。

(推薦課程：MySQL教學)

在這裡還需要注意一件事情，如果資料庫的buffer pool設定的很大，就會導致遍歷時間變長 清理buffer pool時，也包含清理AHI包含此表的數據，AHI的功能在這裡就不多說了，主要是當 b tree的層級變高時，為避免b tree逐層搜索，AHI能根據某個檢索條件，直接查詢到對應的資料頁，跳過逐層定位的步驟。其次AHI會佔用1/16 的buffer pool的大小，如果線上表格資料不是特別大，不是超高並發，不建議將開啟AHI，可以考慮關閉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、刪除對應的磁碟資料檔案ibd

#在刪除資料檔案時，如果資料檔案過大，刪除過程會產生大量的IO 並耗費更多的時間，造成磁碟IO開銷飆升,CPU負載過高，影響其他程式運作。我的一個好夥伴，就曾在線上庫刪除了一張 1TB 大小的表，結果20分鐘，資料庫無回應，最後庫崩潰，重啟了。

既然知道drop table做了2件事情，那就針對以上2 件事進行最佳化

在清除Buffer Pool緩衝上，要減少當個buffer pool的大小，可以合理地設定innodb_buffer_pool_instances參數，減少buffer pool資料區塊清單掃描時間，同時關閉AHI功能

在步驟2上，可以巧妙的利用linux的硬連結特性，延遲刪除真正的實體檔案。

當多個檔名同時指向同一個INODE時,這個INODE的引用數N>1, 刪除其中任何一個檔名都會很快.因為其直接的實體檔案區塊沒有被刪除.只是刪除了一個指標而已;當INODE的引用數N=1 時, 刪除檔案需要去把這個檔案相關的所有資料區塊清除,所以會比較耗時;

如果給資料庫表的.ibd檔案建立一個硬鏈接，當刪除表時，刪除物理檔案時，其實刪除的就是物理檔案的一個指針，所以刪除操作響應速度會非常快，大約不到1秒左右

下面就來演示一下具體的操作

先创建表文件的硬链接
ln t_test.ibd t_test.ibd.bak
删除表
drop table t_test;

最後就是要真正刪除掉物理文件，釋放文件所佔用的磁碟空間，那麼問題來了，如果優雅的刪除實體檔案呢，在這裡推薦大家coreutils工具集中的truncate命令

當然需要你先安裝相關的軟體套件

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

安裝好之後，就可以寫一個腳本，非常優雅的分佈刪除大文件，${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 ;

以上是怎麼drop掉mysql函式庫中的1TB表單的詳細內容。更多資訊請關注PHP中文網其他相關文章！