MySQL DELETE 語法全解析：單表、多表、修飾符、性能與陷阱

語法速查

單表刪除

DELETE [LOW_PRIORITY] [QUICK] [IGNORE] FROM tbl_name
[WHERE where_condition]
[ORDER BY ...]
[LIMIT row_count]

多表刪除（兩種等價語法）

-- 语法 1：刪除 FROM 前列出的表
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
tbl_name[.*] [, tbl_name[.*] ...]
FROM table_references
[WHERE where_condition]

-- 语法 2：刪除 FROM...USING 中列出的表
DELETE [LOW_PRIORITY] [QUICK] [IGNORE]
FROM tbl_name[.*] [, tbl_name[.*] ...]
USING table_references
[WHERE where_condition]

修飾符詳解

QUICK 修飾符深度解析

原理：MyISAM B-Tree 刪除葉子節點後，默認嘗試合併相鄰頁。QUICK 跳過合併。

適用場景

• 刪除「舊數據區間」，隨後插入「新數據區間」（如按時間分區歸檔），新值會復用舊頁空間 → QUICK 有效。

不適用場景

• 刪除導致索引頁「欠滿」（underfilled），但後續插入值**不落入該區間** → 頁長期欠滿，形成「洞」 → 需 OPTIMIZE TABLE 重建索引。

最佳組合

DELETE QUICK FROM huge_table WHERE log_date < '2023-01-01';
OPTIMIZE TABLE huge_table;  -- 重建索引，回收空間

LIMIT 與 ORDER BY 聯用：精準刪除 Top-N

DELETE FROM somelog
WHERE user = 'jcole'
ORDER BY timestamp_column
LIMIT 1;  -- 刪除最舊一條

• 僅單表支持；多表 DELETE 禁用 ORDER BY/LIMIT。
• 常用於日志輪轉、隊列消費確認。

多表刪除實戰

-- 刪除 t1、t2 中符合關聯條件的行，t3 僅作關聯過濾
DELETE t1, t2 FROM t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;
-- 等價
DELETE FROM t1, t2 USING t1, t2, t3 WHERE t1.id=t2.id AND t2.id=t3.id;

• 支持所有 JOIN 類型（INNER/LEFT/RIGHT）。
• 別名規則：若用別名，刪除列表必須用別名（DELETE a FROM t AS a ...）。
• 跨庫刪除：DELETE db1.t1, db2.t2 FROM db1.t1, db2.t2 ...（不可用別名）。

外鍵約束下的多表刪除陷阱

涉及 InnoDB 外鍵時，優化器可能重排表順序導致外鍵檢查失敗。解決：單表刪除父表，依賴 ON DELETE CASCADE/SET NULL 自動級聯；或暫時 SET FOREIGN_KEY_CHECKS=0（極度謹慎）。

子查詢限制

-- 禁止：同表子查詢
DELETE FROM t WHERE id IN (SELECT id FROM t WHERE ...);
-- 繞過：派生表
DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id FROM t WHERE ...) AS x);

性能優化清單

1. 大批量刪除：分塊刪除（LIMIT 1000 循環）避免長事務、鎖升級、Binlog 膨脹。
2. 刪除後碎片回收：MyISAM OPTIMIZE TABLE，InnoDB ALTER TABLE ... ENGINE=InnoDB（重建表）。 3. 歸檔優於刪除：歷史數據 INSERT INTO archive SELECT ... FROM src WHERE ...; DELETE ... 或分區表 DROP PARTITION（秒級、無碎片）。 4. 統計信息更新：大量刪除後 ANALYZE TABLE 更新 Cardinality，防止優化器誤判。

總結

DELETE 看似簡單，實則暗藏 QUICK 碎片陷阱、多表別名規則、外鍵級聯順序、子查詢限制等深坑。掌握「分塊刪、歸檔替刪、分區 Drop Partition」三板斧，才是大數據量下的正解。