問題現場

200 萬行用戶日誌表 tmp_open_ip 需根據 44 萬行 IP 庫表 ip 更新地址欄位。直接 UPDATE ... JOIN 執行 1.5 小時未完成，預估需數天。

慢速 UPDATE 分析

UPDATE tmp_open_ip AS u
INNER JOIN ip ON u.ip BETWEEN ip.s AND ip.e
SET u.address = ip.a;

瓶頸：

1. 逐行掃描外表，對每行做範圍查詢（BETWEEN 無法有效用索引）
2. 大量隨機 I/O 更新聚簇索引
3. 產生大量 Undo Log、Redo Log、Binlog
4. 鎖競爭嚴重，併發讀寫被阻塞

解決方案：INSERT INTO ... SELECT（換寫法，不改邏輯）

步驟 1：建立目標表（同結構，無索引加速寫入）

CREATE TABLE tmp_open_address (
    email VARCHAR(60) NOT NULL DEFAULT '',
    address VARCHAR(50) NOT NULL DEFAULT '',
    ip INT(10) UNSIGNED NOT NULL DEFAULT 0
) ENGINE=MyISAM DEFAULT CHARSET=utf8;  -- MyISAM 批次寫入更快，或 InnoDB 關閉雙重寫

步驟 2：單條 INSERT SELECT 完成資料轉換

INSERT INTO tmp_open_address (email, address, ip)
SELECT l.email, ip.a, l.ip
FROM tmp_open_log AS l
INNER JOIN ip ON l.ip BETWEEN ip.s AND ip.e;

結果： 254 萬行資料，**16.9 秒** 完成！較 UPDATE 提速 300 倍。

步驟 3：原子切換（重命名表）

RENAME TABLE tmp_open_ip TO tmp_open_ip_old, tmp_open_address TO tmp_open_ip;
-- 或建立索引後再切換
ALTER TABLE tmp_open_address ADD KEY idx_email (email), ADD KEY idx_ip (ip);
RENAME TABLE ...

為什麼 INSERT 比 UPDATE 快？

維度	UPDATE	INSERT SELECT
寫入模式	隨機更新現有頁面	順序追加新頁面
索引維護	即時維護所有二級索引	批次建立/延遲維護
鎖粒度	行鎖 + 間隙鎖	表級鎖（MyISAM）或意向鎖
日誌量	Undo + Redo + Binlog（舊值+新值）	Redo + Binlog（僅新值）
碎片產生	嚴重	無

適用場景與限制

• ✅ 適用：全表/大批次更新、欄位衍生計算、資料遷移、ETL
• ❌ 不適用：小批次點更新、需保持主鍵不變且被外鍵引用、高併發線上業務（需配合原子切換）

進階優化技巧

1. 分批次處理：大表分塊 WHERE id BETWEEN ? AND ? 循環 INSERT，避免長事務
2. 禁用索引/外鍵：ALTER TABLE ... DISABLE KEYS（MyISAM）或 SET FOREIGN_KEY_CHECKS=0
3. 調大批次緩衝：SET SESSION bulk_insert_buffer_size = 256M;
4. 使用 LOAD DATA INFILE：導出 CSV 再導入，速度更快（需檔案系統存取權）
5. ClickHouse/StarRocks：超大規模 OLAP 場景考慮列式引擎

總結

大表大批次更新，**改寫為「建新表 INSERT SELECT → 切換表名」**，是關係型資料庫通用的高效能模式。配合分批、禁用索引、調整參數，可將小時級操作壓縮至秒級。