MySQL 超大表快速刪除方式

2022-08-24 18:01:49

MySQL 超大表快速刪除

MySQL裡面直接對大表執行drop table刪除有可能導致MySQL Hang住，對業務造成影響。刪除超大表的前提是該表是獨立表空間，這樣刪除才有效。

表建立一個硬連結

# du -sh pay_bills.ibd 
175G        pay_bills.ibd
# 建立硬連結
# ln pay_bills.ibd pay_bills.ibd_hdlk

執行表刪除

在Linux中，每個儲存檔案都會有指向該檔案的Inode Index，多個檔名可以通過相同Inode Index指向相同一個儲存檔案。

如果該檔名參照的Inode Index上還被其他檔名參照，則只會刪除該檔名和Inode Index之間的參照

如果該檔名參照的Inode Index上沒有被其他檔名參照，則刪除該檔名和Inode Index之間的參照並刪除Inode Index指向的儲存檔案。

實際上只是刪除了對 pay_bills.ibd 的一個檔案參照，我們 pay_bills.ibd_hdlk 對物理檔案的參照還是存在的，就不會執行OS級別的刪除操作，IO波動不大，降低對MySQL的影響。

mysql> drop table pay_bills;
Query OK, 0 rows affected (3.24 sec)

執行檔案刪除

安裝 truncate 工具

# yum install coreutils -y

執行刪除指令碼

#!/bin/bash
TRUNCATE=/usr/bin/truncate
# 從175G開始每次刪除2G，最後如果指令碼truncate後還剩下部分檔案，使用rm刪除
for i in `seq 175 -2 1`; do 
  $TRUNCATE -s ${i}G  pay_bills.ibd_hdlk
  sleep 1
done
rm -f pay_bills.ibd_hdlk

MySQL快速清空大表資料

專案初次上線，進行效能測試造的資料量巨大，都是些不可用資料，但又有一些是必須保留的，很多時候需要進行系統性的清理資料或者是，將有用的資料篩選出來之後再插入到表中！保留表結構或者重新建表(匯出資料庫中的表結構)，重新執行SQL語句。

MySQL刪除或清空表資料

清空表資料的五種方式

1、truncate–刪除所有資料，保留表結構，不能復原還原

2、delete–是逐行刪除速度極慢，不適合大量資料刪除

3、drop–刪除表，資料和表結構一起刪除

4、匯出表結構，再次執行一遍;

5、匯出資料庫所有表結構，再次執行一遍，清空所有表(與4一致)

清空表資料語法

truncate table 表名;（僅保留表結構，速度很快）

delete from 表名;

delete from 表名 where 列名="value ";

DROP `t_product_events` IF EXIST;
CREATE TABLE `t_product_events` (
`id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '事件ID',
`level` int(11) NULL DEFAULT NULL,
`product_id` bigint(20) NOT NULL COMMENT '產品型別ID',
`identifier` varchar(64) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
`name` varchar(64) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '事件名稱',
`description` varchar(256) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '事件描述',
`type` int(11) NOT NULL COMMENT '事件型別，0：info（資訊）、1：alert（告警）、2：error（故障）',
`ref_id` int(11) UNSIGNED NULL DEFAULT 0 COMMENT '引入模板時有意義',
`original_required` tinyint(2) NOT NULL,
`update_required` tinyint(2) NOT NULL DEFAULT 0 COMMENT '是否是標準功能的必選事件，0：可選，1：必選',
`custom` tinyint(2) NOT NULL COMMENT '0：模板匯入，1：自定義',
`method` varchar(128) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '事件對應的方法名稱（根據identifier生成）',
`create_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` datetime(0) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP(0),
`ref` bigint(20) NULL DEFAULT NULL,
`related` int(11) NULL DEFAULT 0 COMMENT '被預釋出或者已釋出關聯個數',
`copyright` tinyint(2) NULL DEFAULT 0 COMMENT '是否釋出過， 1 釋出過 0未釋出',
`prerelease` tinyint(2) NULL DEFAULT 0 COMMENT '是否預釋出過， 1 預釋出過 0未預釋出',
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_product_id`(`product_id`) USING BTREE COMMENT '查詢優化'
) ENGINE = InnoDB AUTO_INCREMENT = 497560 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '產品型別事件表' ROW_FORMAT = Compact;

以上為個人經驗，希望能給大家一個參考，也希望大家多多支援it145.com。

MySQL 超大表快速刪除方式

目錄