MySQL 百萬級數據更新優化：索引失效與分批提交實戰

面對 320 萬條數據的更新任務，初期腳本每秒僅處理 3~5 行，預計耗時 24 小時。通過定位索引失效、優化 WHERE 條件、引入主鍵範圍分批提交，最終將速度提升至 30 行/秒，總耗時壓縮至 3 小時。本文復盤兩大典型優化場景。

場景一：聯合索引左前綴缺失導致全表掃描

問題現場

• 表 fuckSpeed 僅有聯合索引 uin_id (uin, id)。
• 更新邏輯：先 SELECT id, url FROM fuckSpeed WHERE id BETWEEN 101 AND 200，再循環 UPDATE ... WHERE id = ?。
• 多進程並行（5 個進程分段）無效，單線程速度不變。

根因分析

-- 查詢計劃顯示：type=ALL，未命中索引
EXPLAIN SELECT id, url FROM fuckSpeed WHERE id >= 0 AND id <= 200;
-- 結果：possible_keys=NULL, key=NULL, rows=324746, Using where

聯合索引生效要求 WHERE 條件必須包含最左列。此處查詢與更新均只用 id，導致索引失效，退化為全表掃描（0.18 秒/次）。

解決方案

1. SELECT 階段補齊最左列：SELECT uin, id, url FROM fuckSpeed WHERE id BETWEEN ...（應用層需保存 uin）。
2. UPDATE 階段帶上 uin：UPDATE fuckSpeed SET type=?, typeid=? WHERE uin=? AND id=?。

-- 驗證索引生效
EXPLAIN SELECT uin, id FROM fuckSpeed WHERE uin=10023 AND id=162;
-- 結果：type=ref, key=uin_id, key_len=12, rows=4, Using index

效果：單進程速度從 3~5 行/秒 飆升至 30+ 行/秒，總耗時從 24h 降至 3h。

場景二：千萬級表無索引列更新導致主從同步延遲 40 分鐘

問題背景

• 6 張表需根據 pid 更新 brand_id，其中 2 張表達千萬級。
• 原 SQL：UPDATE table SET brand_id = ? WHERE pid = ? AND brand_id = 0。
• pid 無索引，每次更新觸發全表掃描（1000 萬行），且不同 pid 需重複執行，MySQL 主從同步延遲飆升至 40 分鐘。

專家優化建議：主鍵 ID 範圍分批

利用主鍵 id（聚簇索引，自帶有序性）將大表邏輯切片：

-- 優化後：增加主鍵範圍條件，每次只掃 1000 行
UPDATE $tableName$
SET brand_id = #newBrandId#
WHERE pid = #pid#
  AND brand_id = 0
  AND id BETWEEN #startNum# AND #endNum#;

優化效果

• 主從同步延遲 歸零；
• 10 分鐘處理 10 萬行，效率提升 6 倍；
• 資料庫負載平穩，應用健康運行。

核心心法總結

1. EXPLAIN 是第一生產力：更新前必看執行計劃，確認 type 非 ALL、key 非 NULL。
2. 聯合索引遵守最左前綴：查詢與更新條件必須包含索引最左列。
3. 大表更新必須分批：利用主鍵範圍或唯一鍵範圍，將全表掃描化為索引範圍掃描，控制單次事務行數（建議 500~2000 行）。
4. 避免在無索引列上做高頻點查更新：若業務頻繁按非索引列更新，請評估加索引收益 vs 寫入開銷。

這兩個案例揭示了 MySQL 更新性能的兩大殺手：索引設計缺陷與大事務長鎖定。針對性優化，立竿見影。