MySQL 批量插入優化實戰：多值 INSERT、事務包裝、有序寫入三大核心技巧

MySQL 批量插入優化實戰：多值 INSERT、事務包裝、有序寫入三大核心技巧

大數據量導入（報表、日誌、同步）時，單條 INSERT 效能極低。三大技巧疊加可提升 10-100 倍吞吐。

技巧 1：多值 INSERT 合併 SQL

-- 慢：N 條單獨 INSERT
INSERT INTO t(a,b) VALUES(1,2);
INSERT INTO t(a,b) VALUES(3,4);

-- 快：單條多值
INSERT INTO t(a,b) VALUES(1,2),(3,4),(5,6)...;

原理：減少 SQL 解析、網路 RTT、Binlog/Redo Log 刷盤頻率（合併寫入）。

技巧 2：事務包裝

START TRANSACTION;
INSERT INTO t(a,b) VALUES ...;  -- 批次 1
INSERT INTO t(a,b) VALUES ...;  -- 批次 2
...
COMMIT;

原理：InnoDB 事務提交時才刷 Redo Log（innodb_flush_log_at_trx_commit=1），批次提交大幅減少 fsync。注意：事務過大超過 innodb_log_buffer_size 會觸發提前刷盤，建議每 1000-5000 行提交一次。

技巧 3：主鍵有序寫入

-- 亂序：主鍵隨機，B+ 樹頻繁分裂合併
INSERT INTO t(id,...) VALUES (100,...), (1,...), (50,...);

-- 有序：主鍵遞增，追加寫入，零分裂
INSERT INTO t(id,...) VALUES (1,...), (2,...), (3,...);

原理：InnoDB 聚簇索引按主鍵順序儲存。有序寫入 = 追加頁面，無頁面分裂、無隨機 IO。大表導入前建議按主鍵排序。

效能對比實測（百萬級）

生產級導入腳本模板

#!/bin/bash
# load_data.sh
BATCH_SIZE=2000
COMMIT_EVERY=5000

mysql -h$HOST -u$USER -p$PASS $DB <

關鍵配置參數
max_allowed_packet = 256M      # 支援大批次 SQL
innodb_log_buffer_size = 64M   # 容納大事務 Redo
innodb_flush_log_at_trx_commit = 1  # 數據安全；導入階段可暫設 2 提速
bulk_insert_buffer_size = 256M # MyISAM 專用，InnoDB 無效

總結
批量插入鐵三角：**多值合併 + 事務分批 + 主鍵有序**。配合 LOAD DATA INFILE（最快，繞過 SQL 層）與關閉二級索引/外鍵檢查，可達到理論極限吞吐。