MySQL 大查詢切分術：分批刪除百萬舊數據，保護線上業務不阻塞

痛點：大 DELETE 鎖表、阻塞業務、主從延遲

-- 一條 SQL 刪 3 個月前數據，可能涉及百萬行
DELETE FROM messages WHERE dt < DATE_SUB(NOW(), INTERVAL 3 MONTH);

• 長事務持有鎖，阻塞寫入/讀取
• Binlog 巨大，主從延遲飆升
• 回滾段膨脹，恢復時間不可控

解決方案：切分為小批次循環刪除

#!/bin/bash
# 刪除腳本：每次 1 萬行，間隔 0.5 秒
rows_affected=1
while [ $rows_affected -gt 0 ]; do
  rows_affected=$(mysql -h localhost -u root -p'xxx' db_name -N -e \
    "DELETE FROM messages WHERE dt < DATE_SUB(NOW(), INTERVAL 3 MONTH) LIMIT 10000")
  sleep 0.5
done

關鍵參數權衡

進階優化：按主鍵範圍切分（避免全表掃描）

-- 先查出需刪除的 ID 範圍
SELECT MIN(id), MAX(id) FROM messages WHERE dt < '2024-01-01';
-- 假設 10000 ~ 5000000

-- 分批按主鍵刪除（利用聚簇索引順序讀取，極快）
DELETE FROM messages WHERE id BETWEEN 10000 AND 20000 AND dt < '2024-01-01' LIMIT 10000;
-- 循環遞增 id 範圍...

適用場景擴展

• 大批量 UPDATE：同樣切分 + 睡眠
• 大批量 INSERT：分批次提交事務
• Schema 變更：pt-online-schema-change 內部即採用分塊拷貝 + 觸發器同步

監控與告警

• 腳本輸出寫日志：時間、批次、影響行數、耗時
• 監控 Innodb_row_lock_time_avg、Seconds_Behind_Master、Threads_running
• 異常飆升自動熔停腳本

總結

**大查詢切分 = 小批次 + 間隔睡眠 + 固定範圍**。將「一次性核彈」拆解為「持續精準打擊」，線上業務零感知，主從同步平穩，運維從容不迫。建議封裝為通用工具 batch_delete.sh，納入運維標準庫。