MySQL如何優雅的刪除大表實例詳解

前言

刪除表,大傢下意識想到的命令可能是直接使用DROP TABLE “表名”,這是初生牛犢的做法,因為當要刪除的表達空間到幾十G,甚至是幾百G的表時候。這樣一條命令下去,MySQL可能就直接夯住瞭,外在表現就是QPS急速下降,客戶請求變慢。

解決辦法

1.業務低峰時間手動執行刪除

這個可能就需要DBA不辭辛勞,大晚上爬起來刪表瞭。

2.先清除數據,最後再刪除的方式

譬如1000萬條數據,寫腳本每次刪除20萬,睡眠一段時間,繼續執行。這樣也能做到對用戶無感知。

3.對表文件(idb文件)做一個硬鏈接來加速刪除

這個方法利用瞭linux下硬鏈接的知識,來進行快速刪除,不記得話可以回去翻一下《鳥哥的linux私房菜》

ln data_center_update_log.ibd data_center_update_log.ibd.hdlk

[[email protected] sports_center]# ll
總用量 19903792
-rw-r----- 1 mysql mysql  9076 10月 17 13:15 data_center_update_log.frm
-rw-r----- 2 mysql mysql 8447328256 12月 23 11:35 data_center_update_log.ibd
-rw-r----- 2 mysql mysql 8447328256 12月 23 11:35 data_center_update_log.ibd.hdlk

執行上面命令後,我們就多瞭一個data_center_update_log.ibd.hdlk文件。此操作實際上不會占用磁盤空間,隻是增加瞭一次對磁盤上文件的引用。

當我們刪除其中任何一個文件時,都不會影響磁盤上真實的文件,隻是將其引用數目減去1。當被引用的數目變為1的時候,再去刪除文件,才會真正做IO來刪除它。

正是利用這個特點,將由原來mysql來刪除大文件的操作,轉換為一個簡單的操作系統級的文件刪除,從而減少瞭對mysql的影響。

4.登陸mysql,執行drop表操作

很快,200萬條數據隻用瞭1秒完成,此操作是在創建硬鏈接後執行的

mysql> drop tables data_center_update_log;
Query OK, 0 rows affected (1.02 sec)


mysql> exit
Bye

退出來,再次查看數據目錄,發現就隻剩data_center_update_log.ibd.hdlk硬鏈接文件瞭
[[email protected] sports_center]# ll
總用量 19903792
-rw-r----- 2 mysql mysql 8447328256 12月 23 11:35 data_center_update_log.ibd.hdlk

5.如何正確刪除ibd.hdlk硬鏈接文件呢

  • 雖然drop table之後,剩下的硬鏈接文件已經和mysql沒有關系瞭。但如果文件過大,直接用rm命令來刪除,也是會造成IO開銷飆升,CPU負載過高,進而影響到MySQL。
  • 這裡我們用到的方法,可以循環分塊刪除,慢慢地清理文件,通過一個腳本即可搞定
  • Truncate命令通常用於將文件縮小或擴展到指定的大小。如果文件大於指定的大小,則會丟失額外的數據。如果文件較短,則會對其進行擴展,並且擴展部分的讀數為零字節。

5.1 安裝truncate命令

[[email protected] ~]# cruncate
-bash: cruncate: 未找到命令
通常操作系統會安裝truncate命令,該命令在coreutils安裝包裡面,如果沒有安裝可以使用下面命令安裝

[[email protected] ~]# yum provides truncate
coreutils-8.22-24.el7.x86_64 : A set of basic GNU tools commonly used in shell scripts
源  :base
匹配來源:
文件名  :/usr/bin/truncate

可以看到truncate由coreutils安裝包提供,下面安裝coreutils安裝包:

[[email protected] ~]# yum install -y coreutils

5.2 truncate常用選項

-c, –no-create –> 不創建任何文件
-o, –io-blocks –> 將大小視為存儲塊的數量,而不是字節
-r, –reference=RFILE –> 參考指定的文件大小
-s, –size=SIZE –> 按照指定的字節設置文件大小

5.3 truncate_bigfile.sh腳本

原理:使用truncate -s選項可以指定文件大小,通過腳本指定每次文件減少的大小,並sleep睡眠一定時間,從而達到可控的刪除文件

附:truncate_bigfile.sh腳本

#! /bin/bash
#

TRUNCATE=/usr/bin/truncate
FILE=$1

if [ x"$1" = x ];then
 echo "Please input filename in"
 exit 1;
else
 SIZE_M=$(du -sm "$1" | awk '{print $1}')

 for i in $(seq "${SIZE_M}" -100 0)
 do
 sleep 1
 echo "${TRUNCATE} -s ${i}M ${FILE}"
 ${TRUNCATE} -s "${i}"M "${FILE}"
 done
fi

if [ $? -eq 0 ];then
    \rm -f "${FILE}"
else
    echo "Please check file"
fi

總結

到此這篇關於MySQL如何優雅的刪除大表的文章就介紹到這瞭,更多相關MySQL優雅刪除大表內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet!

推薦閱讀: