MySQL 大數據刪除最佳實踐：分批刪除避免鎖表與主從延遲

MySQL 大數據刪除最佳實踐：分批刪除避免鎖表與主從延遲

一次性刪除海量數據會導致長時間鎖表、阻塞業務查詢、甚至引發主從復制延遲。本文介紹「分而治之」策略，將大刪除拆解為小批次，並配合間歇性暫停，平滑釋放資源。

為什麼不能一次性刪除？

定期清理三個月前的舊數據：

DELETE FROM messages WHERE dt < DATE_SUB(NOW(), INTERVAL 3 MONTH);

若涉及百萬行以上，單條 SQL 會：

• 持有大量行鎖/表鎖，阻塞並發讀寫
• 產生巨大 Undo Log 與 Redo Log，衝擊緩衝池
• 主庫執行久，從庫重放慢，導致 Seconds_Behind_Master 飆升

分批刪除腳本範例

rows_affected = 0
do {
    rows_affected = do_query("
        DELETE FROM messages 
        WHERE dt < DATE_SUB(NOW(), INTERVAL 3 MONTH) 
        LIMIT 10000
    ")
} while rows_affected > 0

每次刪 1 萬行，配合 LIMIT 控制事務大小。若再加入 SLEEP(0.5)，可將壓力分散到更長時間窗口。

關鍵參數建議

進階優化：按主鍵範圍刪除

若表有自增主鍵，可先查出需刪除的 ID 範圍，再按區間循環刪除，避免全表掃描：

-- 獲取邊界
SELECT MIN(id), MAX(id) FROM messages WHERE dt < '2024-01-01';
-- 分段刪除
DELETE FROM messages WHERE id BETWEEN 100000 AND 109999 AND dt < '2024-01-01';

總結

大數據刪除三原則：小事務、分批次、加間隔。配合 pt-archiver 等工具可實現自動化、可監控的歸檔刪除。