MySQL DELETE LIMIT 用法詳解：分批刪除、排序刪除與多表限制繞過

DELETE ... LIMIT 核心用途

• 限制單次刪除行數，避免長鎖表、大事務、Replication 延遲
• 搭配 ORDER BY 實現「刪除最舊/最新 N 筆」
• 分批清理大量歷史資料的標準模式

單表語法

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

基本範例

-- 刪除所有行（保留表結構）
DELETE FROM table_name;
-- 或
DELETE * FROM table_name;

-- 僅刪除前 30 筆（無 ORDER BY 時順序不確定）
DELETE FROM db LIMIT 30;

-- 刪除最舊 1 筆（需 ORDER BY）
DELETE FROM somelog WHERE user = 'jcole' ORDER BY timestamp_column LIMIT 1;

-- 實務：分批刪除符合條件的資料，每批 100 筆
DELETE FROM tag_list WHERE aid = '6666' LIMIT 100;

LIMIT 語法細節

• LIMIT N 等同 LIMIT 0, N：刪除前 N 筆
• 不支援 LIMIT offset, count（與 SELECT 不同），即無法直接「跳過前 100 筆刪除後 50 筆」
• 需配合 ORDER BY 確保確定性順序

多表 DELETE 不支援 LIMIT 的解決方案

直接使用會報錯：

DELETE t1 FROM t1 JOIN t2 ON t1.id=t2.id LIMIT 10;
-- ERROR 1064: You have an error in your SQL syntax near 'limit 10'

繞過技巧：子查詢先選主鍵再刪除

DELETE A FROM test1 A
INNER JOIN (SELECT id FROM test2 LIMIT 10) B
ON A.id = B.id;

原理：子查詢先以 LIMIT 取得目標主鍵集合，外層按主鍵精確刪除，避開多表 LIMIT 限制。

進階模式：分批處理大量關聯刪除

-- 1. 先查出需刪除的主鍵批次
SELECT id FROM t1
JOIN t2 ON t1.id = t2.id
WHERE t2.status = 'deleted'
ORDER BY t1.id
LIMIT 1000;

-- 2. 應用層迴圈分批執行
DELETE FROM t1 WHERE id IN (批次ID列表);
DELETE FROM t2 WHERE id IN (批次ID列表);

效能與最佳實踐

• 索引支援：WHERE 條件與 ORDER BY 欄位需有索引，否則全表掃描 + filesort 極慢
• 批次大小：100-1000 行/批次平衡鎖持有時間與總耗時
• 間隔休眠：批次間 usleep(100000) (0.1s) 降低主從延遲與鎖衝突
• 監控進度：ROW_COUNT() 判斷是否結束
• 替代方案：極大量刪除考慮 pt-archiver (Percona Toolkit) 或重建表法

小結

DELETE ... LIMIT 是線上安全刪除大量資料的利器。單表直接用，多表用子查詢繞過。核心要訣：有索引、小批次、加間隔、看進度。