PHP 大量資料匯入 MySQL 效能優化：從 30 分鐘降至 10 秒的批次寫入技巧

問題情境

每日需從文字檔匯入 1.5 萬筆電話號碼至 MySQL。原邏輯逐筆 INSERT，耗時 30 分鐘以上，嚴重影響排程效率。

原始低效寫法

<?php
// $array 為 explode 後的號碼陣列，$type 為分類
for ($i = 0; $i < count($array); $i++) {
    $sql = "INSERT INTO {$table} (tel, type) VALUES ('{$array[$i]}', '{$type}')";
    mysql_query($sql); // 每筆一個 round-trip
}
// 15,000 筆 ≈ 30 分鐘

優化方案：單一 SQL 多值批次插入

<?php
$sql = "INSERT INTO {$table} (tel, type, updatetime) VALUES ";
$values = [];

foreach ($array as $tel) {
    // 使用預處理參數化更安全，此處為示範字串拼接
    $values[] = "('" . mysql_real_escape_string($tel) . "', '{$type}', NOW())";
}

$sql .= implode(',', $values);
mysql_query($sql); // 單次 round-trip
// 15,000 筆 ≈ 10 秒

進階優化：PDO 預處理 + 交易

<?php
$pdo = new PDO($dsn, $user, $pass, [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false, // 非緩衝查詢，省記憶體
]);

$batchSize = 1000; // 每批 1000 筆
$stmt = $pdo->prepare("INSERT INTO {$table} (tel, type, updatetime) VALUES (?, ?, NOW())");

$pdo->beginTransaction();
try {
    $count = 0;
    foreach ($array as $tel) {
        $stmt->execute([$tel, $type]);
        if (++$count % $batchSize === 0) {
            $pdo->commit();
            $pdo->beginTransaction(); // 重新開啟交易，避免長交易鎖表
        }
    }
    $pdo->commit();
} catch (Exception $e) {
    $pdo->rollBack();
    throw $e;
}

關鍵優化點總結

終極大招：LOAD DATA INFILE

<?php
// 將陣列寫入暫存檔
$tmpFile = sys_get_temp_dir() . '/import_' . uniqid() . '.csv';
$fp = fopen($tmpFile, 'w');
foreach ($array as $tel) {
    fputcsv($fp, [$tel, $type, date('Y-m-d H:i:s')]);
}
fclose($fp);

$sql = "LOAD DATA LOCAL INFILE '{$tmpFile}' 
        INTO TABLE {$table} 
        FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n'
        (tel, type, updatetime)";
$pdo->exec($sql);
unlink($tmpFile);
// 15,000 筆 ≈ 1-2 秒

前提：MySQL 啟用 local_infile=1，PHP PDO 設定 PDO::MYSQL_ATTR_LOCAL_INFILE => true，檔案路徑對 MySQL Server 可讀。

避坑指南

• max_allowed_packet：批次 SQL 過長會報錯，調大至 64M 或分批
• 唯一鍵衝突：使用 INSERT IGNORE、ON DUPLICATE KEY UPDATE 或 REPLACE INTO
• 主從同步延遲：大量寫入會導致 Slave 延遲，考慮寫入 Master 後延遲讀取或使用半同步複寫