刪除資料三大指令 DELETE、TRUNCATE、DROP 雖然都能「刪除」，但本質、效能、可回滾性、觸發器行為截然不同。本文從底層機制、效能對比、使用場景、避坑指南全維度解析，助你精準選型。

一、核心差異總覽表

維度	DELETE	TRUNCATE	DROP
類型	DML (資料操作語言)	DDL (資料定義語言)	DDL
刪除範圍	指定條件行（可配 WHERE）	全表所有行	表結構+資料+索引+觸發器+權限
表結構	保留	保留	刪除
自增計數器	不重置	重置歸 1	表不存在
高水位線	不變	重置	釋放空間
事務支援	✅ 可回滾	❌ 隱式提交，不可回滾	❌ 隱式提交，不可回滾
觸發器	✅ 觸發 FOR EACH ROW	❌ 不觸發	❌ 觸發器隨表刪除
鎖機制	行鎖	表鎖（元數據鎖）	表鎖（元數據鎖）
回滾段	產生 Undo Log	不產生	不產生
執行速度	慢（逐行）	極快（重建表）	最快
權限需求	DELETE	DROP	DROP

二、底層機制深度解析

DELETE：逐行標記刪除

• InnoDB：將記錄標記為「已刪除」，實際空間不釋放，後續插入可複用。
• 產生大量 Undo Log，佔用回滾段空間，大量刪除可能導致 History list length 飆升，影響 Purge 線程與查詢效能。
• 觸發 BEFORE/AFTER DELETE 觸發器，可實現軟刪除、審計日誌等業務邏輯。

TRUNCATE：重建表結構

• 本質：DROP TABLE + CREATE TABLE（保留結構定義）。
• InnoDB 5.7+：重建 .ibd 檔案，物理釋放空間給作業系統；5.6 及舊版僅邏輯重置高水位線。
• 隱式提交當前交易，無法回滾。
• 不觸發任何觸發器。
• 需 DROP 權限而非 DELETE 權限。

DROP：徹底移除

• 從資料字典移除表定義，刪除 .frm/.ibd 檔案，釋放所有空間。
• 依賴物件（視圖、外鍵、觸發器、預存程序）變為無效。
• 隱式提交，不可回滾。

三、選型決策樹

需要刪除部分資料？
  ├─ 是 → DELETE + WHERE 條件（注意交易大小、分批提交）
  └─ 否 → 需要保留表結構？
          ├─ 是 → 需要觸發觸發器/需回滾/有外鍵引用？
          │       ├─ 是 → DELETE（無 WHERE 或 WHERE 1=1）
          │       └─ 否 → TRUNCATE（極快、重置自增、釋放空間）
          └─ 否 → DROP TABLE（徹底移除，重建結構）

四、實戰最佳實踐

1. 大量資料刪除（百萬行以上）分批 DELETE

-- 避免長交易、鎖升級、Undo Log 爆炸
DELIMITER //
CREATE PROCEDURE batch_delete()
BEGIN
  DECLARE done INT DEFAULT 0;
  DECLARE batch_size INT DEFAULT 10000;
  REPEAT
    DELETE FROM big_table 
    WHERE create_time < '2023-01-01' 
    LIMIT batch_size;
    SET done = ROW_COUNT();
    -- 可選：短暫休眠減輕壓力
    -- DO SLEEP(0.1);
  UNTIL done = 0 END REPEAT;
END//
DELIMITER ;

2. 定期清理歷史資料：TRUNCATE 分區表

-- 範圍分區表，直接刪除分區（秒級，無鎖爭用）
ALTER TABLE logs DROP PARTITION p_202301;
-- 或截斷分區
ALTER TABLE logs TRUNCATE PARTITION p_202301;

3. 重建表優化碎片：TRUNCATE + REUSE STORAGE (Oracle) / OPTIMIZE TABLE (MySQL)

-- MySQL 整理碎片、回收空間
OPTIMIZE TABLE big_table;
-- 等同於：建新表→拷貝資料→重建索引→換名，需鎖表，大表慎用

五、常見陷阱與避坑

1. TRUNCATE 受外鍵阻擋： 即使子表為空，父表有被引用的外鍵也無法 TRUNCATE。必須先 ALTER TABLE child DROP FOREIGN KEY ...。
2. DELETE 不釋放磁碟空間： 執行 OPTIMIZE TABLE 或 ALTER TABLE ... ENGINE=InnoDB 重建表才能回收空間。
3. 自增 ID 耗盡： 頻繁 TRUNCATE + 插入會導致 BIGINT 自增值快速增長（雖然 2^63-1 極難耗盡），但會造成主鍵不連續。
4. 權限誤區： 給開發 DELETE 權限不代表能 TRUNCATE，需額外授予 DROP 權限。
5. 主從同步： TRUNCATE/DROP 以 Statement 格式寫入 Binlog，可能導致從庫資料不一致（如從庫有額外資料），建議 Row 格式或謹慎使用。

六、總結

DELETE：精準刪除、需回滾、觸發業務邏輯 → 首選。
TRUNCATE：全表清空、極速、重置自增、無外鍵依賴、無需回滾 → 維護首選。
DROP：表結構不再需要、徹底移除 → 下線/重構首選。

記住：**資料刪除無小事，備份先行、分批執行、驗證結果。**