编程知识 cdmana.com

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> exitBye退出來,再次檢視資料目錄,發現就只剩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 truncatecoreutils-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/truncateFILE=$1if [ 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}" donefiif [ $? -eq 0 ];then \rm -f "${FILE}"else echo "Please check file"fi```#### 因為有悔,所以披星戴月;因為有夢,所以奮不顧身! 個人部落格首發:`easydb.net` 微信公眾號:`easydb` 關注我,不走丟!![image](https://easydb.oss-cn-shenzhen.aliyuncs.com/%E6%A0%87%E5%87%86%E8%89%B2%E7%89

版权声明
本文为[itread01]所创,转载请带上原文链接,感谢
https://www.itread01.com/content/1608816307.html

Scroll to Top