MySQL 刪除操作完全指南：DELETE、TRUNCATE、DROP 差異、大表清空策略與恢複原則

MySQL 刪除操作深度解析：DELETE vs TRUNCATE vs DROP、大表歸檔策略與誤刪恢複實戰

刪除操作不可逆，風險最高。本文系統對比三大刪除命令機制、效能、觸發器行為、Binlog 記錄差異，提供大表安全清空、分區歸檔、誤刪恢複標準流程。

三大刪除命令核心差異對比

DELETE 進階用法與優化

-- 1. 條件刪除 (必帶 WHERE)
DELETE FROM `emp` WHERE `emp_id` = '100001';

-- 2. 連表刪除 (多表 DELETE)
DELETE e FROM `emp` e
JOIN `dept` d ON e.`dept_id` = d.`id`
WHERE d.`name` = 'Obsolete';

-- 3. LIMIT 限制刪除行數 (防誤刪擴散、分批刪除)
DELETE FROM `emp` WHERE `emp_sal` < 300000 LIMIT 500;

-- 4. 快速清空大表但保留表結構/自增值 (技巧)
DELETE FROM `emp` WHERE 1;  -- 逐行刪除，極慢，不推薦大表
-- 推薦：TRUNCATE (若無外鍵) 或 重建表

大表安全清空/歸檔標準策略

1. 分區表輪替 (最佳)：按時間 RANGE PARTITION，ALTER TABLE ... DROP PARTITION p_202301; 秒級完成，無鎖、無 Binlog 膨脹、空間立即釋放。
2. 影子表重建 (無分區)：

CREATE TABLE `emp_new` LIKE `emp`;  -- 複製結構、索引
INSERT INTO `emp_new` SELECT * FROM `emp` WHERE `created_at` > '2024-01-01'; -- 保留近期數據
RENAME TABLE `emp` TO `emp_old`, `emp_new` TO `emp`; -- 原子切換 (MDL 鎖極短)
DROP TABLE `emp_old`; -- 異步釋放空間

3. pt-archiver (Percona Toolkit) 歸檔：

pt-archiver --source h=localhost,D=db,t=emp --purge \
  --where "created_at < '2024-01-01'" \
  --limit 1000 --commit-each --statistics \
  --dest h=localhost,D=archive_db,t=emp_archive
# 邊刪邊歸檔到歸檔庫，可控速度、可恢複

誤刪恢複實戰流程 (核心：Binlog + 備份)

場景一：DELETE 誤刪 (有事務、未提交)

ROLLBACK;  -- 立即回滾，最簡單

場景二：DELETE/UPDATE 已提交 (需 Binlog 閃回)

1. 確認 Binlog 格式為 ROW (含前後映像)。
2. 定位誤操作時間點/位點：mysqlbinlog --start-datetime="2024-01-15 10:00:00" --stop-datetime="2024-01-15 10:05:00" /var/lib/mysql/mysql-bin.000001 | grep -A 20 "DELETE FROM"。
3. 生成閃回 SQL：mysqlbinlog --flashback --start-datetime=... --stop-datetime=... binlog_file > flashback.sql (MySQL 8.0+ 支援 --flashback)。
4. 審核 flashback.sql (將 DELETE 轉為 INSERT，UPDATE 前後值互換) → 執行恢複。

場景三：TRUNCATE/DROP 誤操作 (DDL，無法閃回)

1. 物理備份恢複：PXB 全量備份 + 增量備份 → 恢複到誤操作前時間點 (PITR)。
2. 延遲從庫：維護一個 MASTER_DELAY=3600 (1 小時) 的 Slave，誤操作後停止同步，從 Slave 導出誤刪表數據導入 Master。
3. 表空間導入 (InnoDB Transportable Tablespace)：若有 .ibd 文件備份，DISCARD TABLESPACE → 複製 .ibd → IMPORT TABLESPACE (需 cfg 文件匹配)。

預防誤刪體系建設

1. 賬號分級：應用帳號 無 DROP/TRUNCATE 權限，僅 DML。DDL 僅 DBA 專用帳號通過堡壘機/工單執行。
2. SQL 審核平台：Yearning / Archery / 自研平台攔截 DDL、高危 DML，強制工單審批。
3. 延遲從庫：CHANGE MASTER TO MASTER_DELAY=3600; 最後防線。
4. 備份驗證：每日自動恢複驗證，確保備份可用、RTO/RPO 達標。
5. 命令別名保護：alias mysql='mysql --safe-updates' (啟用 SQL_SAFE_UPDATES)，alias rm='rm -i'。

結語：DELETE 可控、TRUNCATE 快但不可逆、DROP 毀滅性。大表清空用分區/影子表/pt-archiver。誤刪恢複靠 Binlog 閃回 (DML) 或 PITR/延遲從庫 (DDL)。權限最小化、審核平台、延遲從庫、備份驗證 四位一體構建誤刪免疫體系。