INSERT ... SELECT 導致 InnoDB 全表鎖定風險與安全替代方案

問題現象

INSERT ... SELECT ... 當 SELECT 來源表為 InnoDB 時，無論目標表引擎，MySQL 會對來源表掃描到的所有記錄加鎖（Next-Key Lock）。無索引導致全表掃描 → 全表鎖定 → 極嚴重阻塞。

Oracle 遷移陷阱

• Oracle 無此機制，INSERT ... SELECT 極常見（統計中間表等）。
• 遷移若不加索引或不改寫，上線即引發災難性鎖爭用。

根因：Binlog 複製一致性

MySQL 為保證主從復制時「從庫重放順序與主庫一致」，必須鎖定讀取集合，防止幻讀導致主從數據不一致。

錯誤繞過：innodb_locks_unsafe_for_binlog

• 設 ON 可不鎖定讀取集合。
• 極度危險：主從復制、時間點恢復、災難恢復均可能導致數據不一致。
• 嚴禁生產環境開啟。

安全替代方案

-- 方案 1：SELECT ... INTO OUTFILE + LOAD DATA INFILE
SELECT * FROM src_table INTO OUTFILE '/tmp/data.txt';
LOAD DATA INFILE '/tmp/data.txt' INTO TABLE dst_table;

-- 方案 2：應用層分批讀取、分批寫入（配合 LIMIT、游標）
-- 方案 3：使用臨時表中轉
CREATE TEMPORARY TABLE tmp AS SELECT ... FROM src_table;
INSERT INTO dst_table SELECT * FROM tmp;

上述方案不鎖定來源表記錄，適用於大量資料搬遷、統計中間表生成等場景。