MySQL百萬級批量插入效能大比拼：三種方案實測解析

向空表插入200萬條資料，三種方案耗時對比：

• 方案一：單條INSERT循環 —— 2小時+
• 方案二：事務批次提交（每10萬提交） —— 8分13秒
• 方案三：單條SQL多值插入（拼接VALUES） —— 1分鐘

效能差距超120倍，本文深度剖析原理與最佳實踐。

測試表結構

CREATE TABLE example (
  example_id INT NOT NULL,
  name VARCHAR(50) NOT NULL,
  value VARCHAR(50) NOT NULL,
  other_value VARCHAR(50) NOT NULL
);

方案一：單條INSERT循環（最差）

for ($i=0; $i<2000000; $i++) {
  $connect_mysql->insert(['value' => '50']);
}

缺陷：每條SQL獨立解析、優化、執行、提交；網路往返+事務開銷巨大；PHP端循環開銷額外增加。

方案二：事務批次提交（工程常用）

$connect_mysql->query('BEGIN');
for ($i=0; $i<2000000; $i++) {
  $connect_mysql->insert(['value' => '50']);
  if ($i % 100000 == 0) {
    $connect_mysql->query('COMMIT');
    $connect_mysql->query('BEGIN');
  }
}
$connect_mysql->query('COMMIT');

優勢：減少事務提交次數（20次 vs 200萬次），利用InnoDB redo log批次刷盤。
劣勢：仍發送200萬條獨立SQL，網路協議解析開銷大。

方案三：單SQL多值插入（極致效能）

$sql = "INSERT INTO twenty_million (value) VALUES ";
for ($i=0; $i<2000000; $i++) {
  $sql .= "('50'),";
}
$sql = substr($sql, 0, -1);  // 移除尾部逗號
$connect_mysql->query($sql);

關鍵配置：需調大max_allowed_packet（如SET GLOBAL max_allowed_packet = 20M），否則大SQL會被截斷報錯。

原理：單次網路往返、單次解析優化、單次事務提交、InnoDB批次寫入change buffer+刷盤，效能極致。

實戰建議與避坑指南

進階優化技巧

• 主鍵順序插入：InnoDB聚簇索引按主鍵順序儲存，順序寫入避免頁分裂
• 關閉唯一檢查：SET UNIQUE_CHECKS=0（導入後重開）
• 關閉外鍵檢查：SET FOREIGN_KEY_CHECKS=0
• 調大innodb_buffer_pool_size：減少磁碟I/O
• 使用PreparedStatement：方案二中複用執行計畫，減少解析開銷

選對方案，百萬級插入從「小時級」降為「分鐘級」，甚至「秒級」。