一、 前言：凌晨接到的“刪表”需求

“運(yùn)維同學(xué)，把那張 1000 萬行的歷史日志表刪了吧，磁盤快滿了，而且查詢太慢了。”

如果你在凌晨接到這個需求，你會怎么做？

• 小白做法：直接 DROP TABLE big_log; 然后去睡覺。
  • 后果：如果表正在被寫入或讀取，這個命令會卡住，持有 MDL（元數(shù)據(jù)鎖），導(dǎo)致業(yè)務(wù)阻塞；即使沒阻塞，InnoDB 清理 1000 萬行數(shù)據(jù)的 undo log 和 redo log 也會把 IO 打滿，拖垮整個數(shù)據(jù)庫。
• 資深 DBA 做法：使用“影子表”策略，神不知鬼不覺地完成刪除。

今天，我們就來聊聊生產(chǎn)環(huán)境大表刪除的正確姿勢。

二、 為什么直接 DROP 很危險(xiǎn)？

在 InnoDB 存儲引擎下，DROP TABLE 并不是一個原子操作，它大致分為以下幾個階段：

1. 等待 MDL 鎖：必須等待所有正在訪問該表的事務(wù)提交。如果有長事務(wù)，你就一直等著吧。
2. 標(biāo)記刪除：將表標(biāo)記為“已刪除”，此時(shí)新的查詢無法訪問。
3. 后臺 Purge：InnoDB 后臺線程開始逐行刪除數(shù)據(jù)，并將空間標(biāo)記為“可重用”。
   • 痛點(diǎn)：這個過程會產(chǎn)生大量的 I/O 壓力和 CPU 消耗（特別是維護(hù)索引 B+ 樹）。
   • 痛點(diǎn)：對于 1000 萬行的表，這個過程可能持續(xù)幾分鐘甚至幾十分鐘。
4. 釋放文件句柄：最后刪除 .ibd 文件。

核心風(fēng)險(xiǎn)：在第 3 階段，雖然表已經(jīng)不能查詢了，但如果你的應(yīng)用沒有做好重試機(jī)制，大量的請求會瞬間報(bào)錯“Table doesn’t exist”。更糟糕的是，如果這張表有外鍵關(guān)聯(lián)，DROP 會直接失敗或引發(fā)級聯(lián)鎖。

三、 核心方案：“金蟬脫殼”法（RENAME + DROP）

這是互聯(lián)網(wǎng)大廠最通用的方案。核心思想是：把“刪表”這個重型操作，拆分為“重命名（快）”+“重建（快）”+“后臺清理（慢）”三個步驟。

操作 SOP（標(biāo)準(zhǔn)作業(yè)程序）

假設(shè)我們要刪除的表是 my_db.big_table。

第一步：瞬間“移走”大表（關(guān)鍵?。?/h4>

-- 在業(yè)務(wù)低峰期執(zhí)行（如凌晨 2 點(diǎn)）
RENAME TABLE my_db.big_table TO my_db.big_table_to_drop;

• 原理：RENAME TABLE 是一個 DDL 操作，它只修改數(shù)據(jù)字典（Data Dictionary），不涉及數(shù)據(jù)搬遷。
• 耗時(shí)：毫秒級，幾乎瞬間完成。
• 影響：
  • 業(yè)務(wù)端對 big_table 的請求會立即報(bào)錯（表不存在）。
  • 對策：配合應(yīng)用端的“失敗重試”機(jī)制，或者在維護(hù)窗口操作。此時(shí) big_table_to_drop 已經(jīng)與業(yè)務(wù)隔離，后續(xù)的刪除操作再慢也不會影響線上。

第二步：快速重建“空殼”表（可選但推薦）

如果業(yè)務(wù)不能沒有這張表（哪怕是空的），立即重建結(jié)構(gòu)：

CREATE TABLE my_db.big_table LIKE my_db.big_table_to_drop;

• 原理：LIKE 關(guān)鍵字會復(fù)制原表的所有索引、字段屬性、分區(qū)規(guī)則，但不復(fù)制數(shù)據(jù)。
• 耗時(shí)：極快，只涉及元數(shù)據(jù)拷貝。
• 效果：業(yè)務(wù)端現(xiàn)在可以訪問 big_table 了，雖然是空的，但服務(wù)恢復(fù)了。

第三步：后臺“慢慢”刪除

現(xiàn)在，big_table_to_drop 就像一個被隔離的“垃圾場”，你可以隨時(shí)處理它，而不用擔(dān)心影響用戶：

-- 可以在當(dāng)前會話執(zhí)行，也可以開個新會話在后臺執(zhí)行
-- 甚至可以等到第二天早上再執(zhí)行
DROP TABLE my_db.big_table_to_drop;

• 注意：此時(shí)的 DROP 依然需要清理 1000 萬行數(shù)據(jù)，依然會慢，但因?yàn)樗呀?jīng)不在業(yè)務(wù)鏈路上了，慢一點(diǎn)又何妨？

四、 進(jìn)階技巧與避坑指南

1. 遇到外鍵約束怎么辦？

如果大表被外鍵引用，直接 DROP 會報(bào)錯。
方案：臨時(shí)關(guān)閉外鍵檢查（僅限 Session 級別，不要全局關(guān)閉?。?。

SET FOREIGN_KEY_CHECKS = 0;
DROP TABLE my_db.big_table_to_drop;
SET FOREIGN_KEY_CHECKS = 1;

2. 如何釋放磁盤空間？

刪除表后，你會發(fā)現(xiàn)磁盤空間并沒有立刻釋放。

• 原因：InnoDB 的空間是復(fù)用的。如果開啟了 innodb_file_per_table=ON（默認(rèn)開啟），刪除表后 .ibd 文件會被 操作系統(tǒng)回收。
• 特殊情況：如果是系統(tǒng)表空間（ibdata1），空間無法釋放給操作系統(tǒng)，只會標(biāo)記為 InnoDB 內(nèi)部可用。
• 急救方案：如果必須立刻釋放空間，且不怕鎖表，可以考慮 OPTIMIZE TABLE（針對剩余表）或者導(dǎo)出數(shù)據(jù)再重建整個實(shí)例（極端情況）。但對于“刪表”這個動作本身，通常不需要額外操作，空間會被后續(xù)新數(shù)據(jù)覆蓋重用。

3. 絕對不要忘了備份！

在執(zhí)行 RENAME 之前，哪怕你有 99% 的把握，也要做一次快照：

# 僅備份結(jié)構(gòu)
mysqldump -u root -p --no-data my_db big_table > big_table_structure.sql

# 備份結(jié)構(gòu)+數(shù)據(jù)（如果磁盤夠大）
mysqldump -u root -p my_db big_table > big_table_backup.sql

4. 監(jiān)控與觀察

在執(zhí)行 DROP TABLE my_db.big_table_to_drop; 時(shí)，如何知道進(jìn)度？

• 查看進(jìn)程：SHOW PROCESSLIST; 查看狀態(tài)是否為 cleaning up。
• 查看 IO：使用 iotop 或 iostat 觀察磁盤寫入是否在進(jìn)行。
• InnoDB 狀態(tài)：SHOW ENGINE INNODB STATUS\G 查看 Purge 線程的工作情況。

五、 替代方案：如果不想用 DROP？

有些場景下，你可能只是想清空數(shù)據(jù)，而不是刪表（保留表結(jié)構(gòu)給后續(xù)使用）。

方案 A：TRUNCATE TABLE

TRUNCATE TABLE big_table;

• 優(yōu)點(diǎn)：速度極快，直接丟棄表空間重建。
• 缺點(diǎn)：屬于 DDL，會隱式提交事務(wù)，且無法回滾。如果有外鍵，可能會報(bào)錯。

方案 B：pt-archiver（Percona 工具集）
如果連 TRUNCATE 都怕鎖表（雖然很快），可以使用 pt-archiver 工具逐批刪除數(shù)據(jù)，并將數(shù)據(jù)歸檔到文件中，實(shí)現(xiàn)“無損刪除”。

六、 總結(jié)

刪除 1000 萬行的大表，拼的不是手速，而是策略。

最后的一句話建議：
永遠(yuǎn)在從庫（Slave）上先演練一遍，確認(rèn)時(shí)間和影響后，再在主庫（Master）操作。操作前請默念三遍：我有備份，我有備份，我有備份。

到此這篇關(guān)于MySQL安全快速的刪除一張大表的正確方式的文章就介紹到這了,更多相關(guān)MySQL刪除一張大表內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論