核心挑戰：大事務導致鎖等待超時

直接執行 DELETE FROM big_table WHERE status = 1 涉及 600 萬行，會觸發：

• Lock wait timeout exceeded（InnoDB 行鎖升級為表鎖）
• Undo log 膨脹、Redo log 寫入風暴
• 主從複製延遲飆升（binlog 巨大）
• Buffer Pool 污染（大量脏頁刷盤）

方案一：LIMIT 分批刪除（最通用）

DELETE FROM syslogs 
WHERE status = 1 
ORDER BY status_id 
LIMIT 10000;

自動化腳本模板

#!/bin/bash
# 批量刪除腳本：每次 1 萬行，間隔 0.5 秒
while true; do
    affected=$(mysql -e "DELETE FROM syslogs WHERE status=1 ORDER BY status_id LIMIT 10000;" -ss)
    if [ "$affected" -eq 0 ]; then break; fi
    echo "Deleted $affected rows at $(date)"
    sleep 0.5
done

優化要點

• ORDER BY 主鍵/索引列：確保掃描順序穩定，避免重複掃描
• WHERE 條件命中索引：status 列建立索引
• 適度 LIMIT：5000-20000 平衡鎖持有時間與執行次數
• 間隔 Sleep：釋放鎖資源，緩解主從壓力

方案二：臨時表 + 批量生成 DELETE 語句（適合非連續 ID）

步驟

1. 導出待刪主鍵：
   mysql -e "SELECT pid FROM table1 a JOIN table2 b ON a.pid=b.pid WHERE condition;" > del_pid.txt
2. 清理首行標題：
   sed -i '1d' del_pid.txt
3. 生成刪除腳本：
   awk '{print "DELETE FROM table1 WHERE pid=" $1 ";"}' del_pid.txt > del_pid.sql
4. 分批執行：
   split -l 1000 del_pid.sql chunk_ && for f in chunk_*; do mysql < "$f"; sleep 0.2; done

方案三：新建表換名法（超大表、刪除比例>50% 最優）

-- 1. 建新表結構
CREATE TABLE syslogs_new LIKE syslogs;

-- 2. 插入保留數據（利用索引快速篩選）
INSERT INTO syslogs_new SELECT * FROM syslogs WHERE status != 1;

-- 3. 索引/約束/權限同步
-- 4. 原子切換
RENAME TABLE syslogs TO syslogs_old, syslogs_new TO syslogs;

-- 5. 異步清理舊表
DROP TABLE syslogs_old;

優勢：無鎖競爭、無碎片、速度最快（僅拷貝保留行）。
前提：有維護窗口、磁盤空間充足（需 1.5 倍表大小）。

方案四：分區表秒級清理（架構層面解決）

按時間/狀態分區：

ALTER TABLE syslogs PARTITION BY LIST COLUMNS(status) (
    PARTITION p_active VALUES IN (0),
    PARTITION p_deleted VALUES IN (1)
);

-- 秒級清理：直接丟棄分區
ALTER TABLE syslogs DROP PARTITION p_deleted;

對比總結與選型建議

方案	適用場景	優點	缺點	鎖影響
LIMIT 循環刪	刪除比例 < 30%，無維護窗口	在線業務無感、實現簡單	耗時長、產生碎片	行鎖、短時
臨時表生成 SQL	條件複雜、ID 不連續	精確控制、可審計	生成腳本耗時、文件大	行鎖、短時
新建表換名	刪除比例 > 50%，有維護窗口	最快、零碎片、索引自動重建	需雙倍空間、切換瞬間閃斷	元數據鎖、極短
分區表 DROP PARTITION	按時間/狀態定期清理	秒級、無鎖、無碎片	需提前規劃分區策略	無 DML 鎖

生產環境檢查清單

1. ✅ 確認 WHERE 條件命中索引（EXPLAIN DELETE ...）
2. ✅ 設置 innodb_lock_wait_timeout 適當值（如 50s）
3. ✅ 監控 Innodb_row_lock_waits、Seconds_Behind_Master
4. ✅ 備份前驗證：SELECT COUNT(*) FROM syslogs WHERE status=1
5. ✅ 刪除後 OPTIMIZE TABLE 或 ANALYZE TABLE 更新統計信息

總結

百萬級批量刪除**拒絕單條大事務**。常規業務首選 LIMIT 分批循環；大比例清理有窗口期用 新建表換名；周期性清理架構層面上 分區表。配合索引優化、間隔睡眠、主從監控，可在業務無感知前提下安全完成海量數據清理。