[PHP][MYSQL] PHP＋MySQL 批量更新百萬級資料的最佳策略

在處理數十萬甚至數百萬筆資料的更新任務時，許多開發者習慣使用迴圈執行單條 UPDATE 語句。但當資料量過大時，這種做法會導致應用程式卡死、資料庫負載飆升，效率極低。

本文將介紹一種資料庫專業人士常用的高效解決方案：使用 UPDATE JOIN 搭配臨時表（Temporary Table）。

🎯 什麼是批量更新的「效能殺手」？

當您使用 PHP 迴圈執行 10 萬次 UPDATE ... WHERE id = X 時，主要的效能瓶頸並不是資料庫處理本身，而是：

網絡傳輸延遲： 10 萬次 PHP 應用伺服器與 MySQL 伺服器之間的通訊。
SQL 解析開銷： MySQL 必須解析、驗證和優化 10 萬次 SQL 語句。
磁碟 I/O 寫入： 缺乏事務（Transaction）保護下，每一次更新都可能觸發一次昂貴的磁碟日誌寫入。

我們的目標是將這 10 萬次操作，轉換為 一次高效、單一的資料庫操作。

💡 最佳策略：UPDATE JOIN + 臨時表

這個策略的核心思路是：

收集新值： 將所有需要更新的新資料（例如 uid 和 name）組裝起來。
快速載入： 將這些新值一次性快速載入到一個輕量級的臨時表中。
單次執行： 執行一個高效的 UPDATE JOIN 語句，讓資料庫在內部利用索引完成所有幾十萬筆資料的更新。

適用情境

異質更新： 每筆資料要更新的目標值都不同（例如：客戶 A 積分變 100，客戶 B 積分變 200）。
非唯一鍵更新： 主表中的關聯鍵（如您的 uid）可能重複。

🛠️ 實戰教學：三步驟完成百萬級更新

假設我們有一個名為 performance_records 的業績表，uid 欄位會重複，我們需要根據新的清單來更新所有匹配的 name 欄位。

步驟 1：建立並填充臨時表 (使用 PHP 批量 INSERT)

我們首先建立一個臨時表 temp_name_updates，並將幾十萬筆新資料高效地寫入。

關鍵優化： 在臨時表上建立 主鍵 (PRIMARY KEY)，這能極大地加速稍後的 JOIN 關聯速度。

        
// 假設 $pdo 已經初始化
$batch_size = 5000; // 建議設置較大的批次大小
$update_data = [ /* 幾十萬筆 [uid => '...', name => '...'] 資料 */ ];

try {
    $pdo->beginTransaction();

    // 1. 建立臨時表（包含 PRIMARY KEY 進行優化）
    $pdo->exec("
        CREATE TEMPORARY TABLE IF NOT EXISTS temp_name_updates (
            uid VARCHAR(50) NOT NULL,
            name VARCHAR(255) NOT NULL,
            PRIMARY KEY (uid) 
        ) ENGINE=InnoDB;
    ");

    // 2. 批量寫入數據到臨時表
    foreach (array_chunk($update_data, $batch_size) as $batch) {
        $values = [];
        foreach ($batch as $row) {
            // 使用 quote() 確保 SQL 安全
            $uid = $pdo->quote($row['uid']);
            $name = $pdo->quote($row['name']);
            $values[] = "({$uid}, {$name})";
        }

        // 使用 INSERT IGNORE 確保即使臨時表有重複 UID，也能忽略衝突
        $insert_sql = "INSERT IGNORE INTO temp_name_updates (uid, name) VALUES " . implode(',', $values);
        $pdo->exec($insert_sql); 
    }

    $pdo->commit();
    echo "臨時表資料載入完成。\n";

} catch (PDOException $e) {
    $pdo->rollBack();
    die("資料載入失敗: " . $e->getMessage());
}

步驟 2：執行高效的 UPDATE JOIN 語句

現在，我們只需執行一條 SQL 語句，讓 MySQL 內部處理所有數據的匹配和更新。

關鍵前提： 請確保您的主表 performance_records 的 uid 欄位上已經建立了 普通索引 (INDEX)，否則 JOIN 步驟將會變成全表掃描，導致極慢。

        
try {
    $pdo->beginTransaction(); // 執行 JOIN 之前再次開始事務

    // 執行單一、高效的 UPDATE JOIN 語句
    $update_join_sql = "
        UPDATE performance_records AS T  -- 您的主表
        INNER JOIN temp_name_updates AS TEMP 
            ON T.uid = TEMP.uid  -- 透過索引高效關聯
        SET T.name = TEMP.name;  -- 將主表的 name 更新為臨時表的新 name
    ";

    $stmt = $pdo->prepare($update_join_sql);
    $stmt->execute();

    // 清理臨時表 (連線結束會自動消失，但主動清理是好習慣)
    $pdo->exec("DROP TEMPORARY TABLE temp_name_updates;");

    $pdo->commit();
    echo "成功更新 " . $stmt->rowCount() . " 筆紀錄。\n";

} catch (PDOException $e) {
    $pdo->rollBack();
    echo "更新 JOIN 失敗: " . $e->getMessage();
}

📈 為什麼這個策略速度極快？

這個方法的成功，來自於對「資料庫 I/O」和「網路延遲」的徹底優化：

效能指標	傳統迴圈 (100 萬次)	UPDATE JOIN 策略 (1 次)
網路傳輸	100 萬次 (慢)	1 次 (極快)
SQL 解析	100 萬次 (慢)	1 次 (極快)
I/O 日誌寫入	100 萬次 (若無事務)	1 次 (COMMIT 時)
資料關聯	100 萬次獨立索引查找	1 次內部高效索引 JOIN

結語

面對大數據量的批量操作，千萬不要讓程式語言的迴圈來指揮資料庫。透過 UPDATE JOIN 結合臨時表，您是讓強大的資料庫引擎在內部，以最高效的方式完成工作。這不僅能大幅縮短執行時間，還能降低資料庫的瞬時負載。

站內搜尋

謝晒的PHP網頁設計