核心差異一覽
| 維度 | DELETE | TRUNCATE |
|---|
| 語法類型 | DML | DDL |
| 執行機制 | 逐行刪除，記錄 Binlog（ROW 格式每行一條） | 釋放數據頁，重建表結構，最少 Binlog |
| 速度 | 慢（大表極慢） | 極快（元數據操作） |
| 事務安全 | 支持回滾 | 隱式提交，不可回滾 |
| 觸發器 | 觸發 FOR EACH ROW | 不觸發 |
| 自增 ID | 保持不變 | 重置為 1（或初始值） |
| 外鍵約束 | 檢查並報錯/級聯 | 若被引用則報錯（需先刪 FK） |
| 空間回收 | 不釋放磁盤空間（標記刪除） | 釋放數據頁歸還表空間 |
| WHERE 支持 | 支持 | 不支持 |
| 權限需求 | DELETE 權限 | DROP 權限 |
典型場景選型
-- 1. 清空全表、重置自增 ID、釋放空間 → TRUNCATE
TRUNCATE TABLE logs;
-- 2. 保留自增 ID、需回滾、有觸發器/外鍵 → DELETE
DELETE FROM orders WHERE status = 'cancelled';
-- 3. 大表分批刪除（避免長事務、鎖升級、主從延遲）
-- 建議：每批 5000~10000 行，間隔 SLEEP
WHILE (SELECT COUNT(*) FROM big_tbl WHERE dt < '2023-01-01') > 0 DO
DELETE FROM big_tbl WHERE dt < '2023-01-01' LIMIT 5000;
DO SLEEP(0.5);
END WHILE;
-- 4. 分區表快速清理歷史分區
ALTER TABLE big_tbl DROP PARTITION p_202201;
InnoDB 大表 DELETE 性能優化
- 按主鍵順序刪除：減少頁分裂、Undo Log 體積
- 使用索引覆蓋：
WHERE indexed_col = val 避免全掃
- 調大
innodb_buffer_pool_size：減少磁盤 IO
- 臨時設
innodb_flush_log_at_trx_commit=2：批量刪除期間犧牲 1s 數據安全換吞吐
TRUNCATE 陷阱
- MySQL 5.7+
TRUNCATE 會獲取 MDL 排他鎖，阻塞並發 DDL/DML
- 若表被
FOREIGN KEY 引用，必須先 ALTER TABLE child DROP FOREIGN KEY fk_name
- 復制環境下
TRUNCATE 以 STATEMENT 格式寫 Binlog，從庫同步極快
總結口訣
全表清空要快用 TRUNCATE，帶條件刪改用 DELETE；
自增歸零釋放空間 TRUNCATE，事務回滾觸發器 DELETE；
大表分批刪除要有度，主鍵順序索引覆蓋最優秀。
