您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 資料庫相關知識

MySQL 批量插入優化：多值語句、事務與有序寫入三大法寶

編輯：MySQL 資料庫相關知識

從百萬到千萬級資料導入的效能實測

大量資料導入 (ETL、報表) 耗時常達數小時。本文實測三種優化手段對 `INSERT` 效能的影響：合併多值、事務包裹、主鍵有序寫入。

優化策略對比

策略	原理	適用場景	注意事項
多值單語句 `INSERT ... VALUES (...), (...), ...`	減少 Binlog/Redo Log 刷盤頻率、SQL 解析次數、網路往返	中小批次導入	受 `max_allowed_packet` 限制 (預設 1MB，建議調大至 8MB+)
事務包裹 `START TRANSACTION; ... COMMIT;`	將多次 Redo Log 合併一次提交，減少 `fsync`	配合多值語句使用	事務過大超過 `innodb_log_buffer_size` 會觸發刷盤，建議分批提交 (如每 1000~5000 行)
主鍵有序寫入	避免 B+Tree 頁分裂與合併，順寫極大提升索引維護效率	自增主鍵或有序業務鍵導入	無序導入隨機 IO 激增，千萬級數據性能崩塌

綜合實測結論 (InnoDB)

小數據量 (<1萬)：多值 + 事務提升顯著 (數倍~十倍)。
大數據量 (>100萬)：**有序寫入是關鍵**。無序寫入隨 buffer pool 滿載導致磁碟亂讀寫，性能指數級下降；有序寫入配合多值+事務可維持高穩定吞吐。
最佳組合：`有序數據` + `多值語句` (每句 500~1000 行) + `分批事務提交` (每批 5000~10000 行)。

配置參數建議

max_allowed_packet = 64M
innodb_log_buffer_size = 64M
innodb_flush_log_at_trx_commit = 2  # 導入期間可暫設 2，事後改回 1
innodb_buffer_pool_size = 物理記憶體 70%~80%

程式碼範例

$batch = []; $batchSize = 1000; $commitSize = 5000;
$pdo->beginTransaction();
foreach ($rows as $i => $row) {
    $batch[] = "('{$row['a']}', '{$row['b']}')";
    if (count($batch) >= $batchSize) {
        $sql = "INSERT INTO tbl (a,b) VALUES " . implode(',', $batch);
        $pdo->exec($sql);
        $batch = [];
    }
    if ($i % $commitSize === 0) { $pdo->commit(); $pdo->beginTransaction(); }
}
if ($batch) { $pdo->exec("INSERT INTO tbl (a,b) VALUES " . implode(',', $batch)); }
$pdo->commit();