優化 MySQL 大批量插入的五大妙招：從 40 分鐘降到 0.04 秒

背景

20 萬條資料插入，單條 INSERT 循環執行預估 40-60 分鐘。經優化後單次批量插入 999 條僅需 0.03-0.04 秒。

INSERT 耗時分解

表大小隨 log N (B-Tree) 影響索引插入速度。

五大加速妙招

1. 多值單條 INSERT（核心）

-- 慢：循環 1000 次 INSERT
INSERT INTO test (col) VALUES ('a');
-- 快：單條多值
INSERT INTO test (col) VALUES ('a'),('b'),('c') ...;  -- 999 個值

實測：0.03 秒 vs 9-10 秒，提升 300 倍。

2. LOAD DATA INFILE（極速載入）

• 比多條 INSERT 快 20 倍。
• 配合 myisamchk --keys-used=0 禁用索引 → 載入 → myisamchk -rq 重建索引，避免逐行維護 B-Tree。
• 或用 ALTER TABLE ... DISABLE/ENABLE KEYS 替代 myisamchk，省去 FLUSH TABLES。

3. 鎖表 / 事務批量提交

-- MyISAM
LOCK TABLES t WRITE;
INSERT INTO t VALUES ...;  -- 批次 1
INSERT INTO t VALUES ...;  -- 批次 2
UNLOCK TABLES;

-- InnoDB
START TRANSACTION;
INSERT ...; INSERT ...;
COMMIT;

索引緩衝區僅在結束時刷盤一次，大幅減少 I/O。長事務/鎖表需控制批次大小（如每 1000 行 COMMIT/UNLOCK）平衡併發。

4. INSERT DELAYED（非同步寫入）

• 客戶端立即返回，Server 排隊寫入。
• 適用：日誌、統計、允許延遲可見。
• 限制：無法獲取 LAST_INSERT_ID()、從庫忽略、崩潰丟失佇列資料。

5. IGNORE / ON DUPLICATE KEY UPDATE

• INSERT IGNORE：重複鍵靜默跳過，無需先查再插。
• ON DUPLICATE KEY UPDATE：衝突時更新，VALUES(col) 引用插入值。
  範例：INSERT INTO t(a,b,c) VALUES(1,2,3) ON DUPLICATE KEY UPDATE c=VALUES(a)+VALUES(b);

系統參數調優

• bulk_insert_buffer_size：MyISAM 批量插入緩衝（預設 8M）。
• key_buffer_size：擴大索引緩衝，加速 LOAD DATA / INSERT。