測試環境

• 表結構：example_id INT, name VARCHAR(50), value VARCHAR(50), other_value VARCHAR(50)
• 目標：向空表插入 200 萬筆 測試資料
• 語言：PHP + PDO（模擬真實應用）

三種寫法效能對比

方案	耗時	相對效能	關鍵點
1. 迴圈單條 INSERT	2 小時 7 分	1×（基準）	每筆獨立事務、網路往返、解析優化
2. 事務批量（每 10 萬提交）	8 分 13 秒	~15×	減少事務提交開銷，仍逐條送 SQL
3. 多值 INSERT ... VALUES (),(),()...（單次 1 萬行）	0.45 秒	~1800×	單次解析、單次網路、批次寫入 redo log

多值 INSERT 語法

INSERT INTO example (example_id, name, value, other_value) VALUES
(100, 'Name 1', 'Value 1', 'Other 1'),
(101, 'Name 2', 'Value 2', 'Other 2'),
...
(10099, 'Name 10000', 'Value 10000', 'Other 10000');

• 若欄位順序與建表一致，可省略欄位清單：INSERT INTO example VALUES (...),(...)...
• 單條 SQL 長度受 max_allowed_packet 限制（預設 4/64 MB），大批次需分批。

PHP 實作範本

function batchInsert(PDO $pdo, array $rows, int $batchSize = 10000): void {
    $cols = ['example_id', 'name', 'value', 'other_value'];
    $placeholders = '(' . implode(',', array_fill(0, count($cols), '?')) . ')';
    $sql = "INSERT INTO example (" . implode(',', $cols) . ") VALUES ";
    
    $stmt = $pdo->prepare($sql . $placeholders); // 單條預備
    $pdo->beginTransaction();
    
    $count = 0;
    foreach ($rows as $row) {
        $stmt->execute(array_values($row));
        if (++$count % $batchSize === 0) {
            $pdo->commit();
            $pdo->beginTransaction();
        }
    }
    $pdo->commit();
}

進階：用 implode 組裝超大 VALUES 字串，配合 PDO::exec 直執行，省去預備語句開銷，更快。

為何多值 INSERT 如此快？

1. 解析/優化一次：200 萬條單獨 INSERT 需 200 萬次解析，多值僅 1 次。
2. 網路往返一次：Client→Server 單次傳輸所有資料。
3. Redo Log 群組提交：InnoDB 將多筆變更合併寫入 redo log，極大減少 fsync。
4. Change Buffer 合併：二級索引變更在記憶體合併再刷盤。

生產環境檢查清單

• 調大 max_allowed_packet（如 256M）
• 批次大小 1000–5000 行，避免單條 SQL 過大導致 OOM 或超時
• 配合 LOAD DATA INFILE 更快（繞過 SQL 層，直接寫頁）
• 關閉二進制日誌或用 SET sql_log_bin=0 進一步加速（非複寫環境）

總結

批量寫入鐵律：少發 SQL、大批次、用事務。多值 INSERT 是應用層最易落地、效能提升最大的手段；極致場景改用 LOAD DATA。實測 200 萬筆從 2.1 小時 → 0.45 秒，效能提升 1800 倍。