PHP 大量資料導入 MySQL 效能優化：單次多值 INSERT 實現 10 倍加速

問題場景

每日需從文字檔匯入 15,000 筆電話號碼至 MySQL。原邏輯：逐行讀取 → 查重 → 不存在則 INSERT。單筆 INSERT 導致 15,000 次往返，耗時 5 分鐘+。

優化核心：批次 INSERT

<?php
// 假設 $numbers 為去重後的陣列，已排除資料庫中既有號碼
$batchSize = 500; // 每批 500 筆，避免單條 SQL 過長超過 max_allowed_packet
$table = 'phones';
$type = 'unverified';

$placeholders = [];
$params = [];
$types = '';

foreach ($numbers as $i => $phone) {
    $placeholders[] = '(?, ?)';
    $params[] = $phone;
    $params[] = $type;
    $types .= 'ss';

    // 湊滿一批或最後一筆
    if (count($placeholders) >= $batchSize || $i === count($numbers) - 1) {
        $sql = "INSERT INTO `$table` (`phone`, `type`) VALUES " . implode(', ', $placeholders);
        $stmt = $mysqli->prepare($sql);
        $stmt->bind_param($types, ...$params);
        $stmt->execute();
        // 重置
        $placeholders = [];
        $params = [];
        $types = '';
    }
}

效能對比

進階方案：LOAD DATA INFILE（極速）

<?php
// 1. 產生 CSV 檔（欄位順序對應資料表）
$tmpFile = sys_get_temp_dir() . '/import_' . uniqid() . '.csv';
$fp = fopen($tmpFile, 'w');
foreach ($numbers as $phone) {
    fputcsv($fp, [$phone, $type]);
}
fclose($fp);

// 2. 執行導入
$sql = "LOAD DATA LOCAL INFILE '$tmpFile' 
        INTO TABLE `$table` 
        FIELDS TERMINATED BY ',' ENCLOSED BY '\"' 
        LINES TERMINATED BY '\n' 
        (`phone`, `type`)";
$mysqli->query($sql);

// 3. 清理
unlink($tmpFile);

• 需 MySQL 開啟 local_infile=1，PHP mysqli 需 MYSQLI_OPT_LOCAL_INFILE。
• CSV 格式需與資料表欄位嚴格對應。
• 速度最快，但錯誤處理較難（如主鍵衝突會中止整批）。

查重優化：避免逐筆 SELECT

<?php
// 1. 先將待匯入號碼載入臨時表
$mysqli->query("CREATE TEMPORARY TABLE tmp_import (phone VARCHAR(20) PRIMARY KEY) ENGINE=MEMORY");
$stmt = $mysqli->prepare("INSERT IGNORE INTO tmp_import (phone) VALUES (?)");
foreach ($numbers as $phone) {
    $stmt->bind_param('s', $phone);
    $stmt->execute();
}

// 2. 找出資料庫中不存在的號碼（LEFT JOIN）
$sql = "SELECT i.phone FROM tmp_import i 
        LEFT JOIN `$table` t ON t.phone = i.phone 
        WHERE t.phone IS NULL";
$newNumbers = $mysqli->query($sql)->fetch_all(MYSQLI_ASSOC);

// 3. 批次 INSERT 新號碼
// ... 同批次 INSERT 邏輯

• 臨時表用 ENGINE=MEMORY 極快，連線結束自動銷毀。
• 單次 JOIN 替代 N 次 SELECT，查重從 O(N) 降為 O(1) 交互。

其他加速技巧

• 關閉索引：ALTER TABLE $table DISABLE KEYS（MyISAM）或 SET SESSION sql_log_bin=0（InnoDB 避免 binlog）。導入後重建。
• 自動提交關閉：$mysqli->autocommit(false)，批次提交，最後 commit()。
• 使用預處理語句：防注入、解析一次多次執行。
• 調大 max_allowed_packet：批次 SQL 長度不得超過此值（預設 4MB/64MB）。

總結

大量導入 MySQL 三板斧：批次 INSERT（通用、易實現）、LOAD DATA INFILE（極速、格式固定）、臨時表 JOIN 查重（替換逐筆 SELECT）。配合關閉自動提交、調整參數，可將 15,000 筆從 5 分鐘壓縮到 10 秒內，提升 30 倍以上。