萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 網路程式設計 >> PHP 程式設計

PHP 非緩衝查詢大數據集:解決記憶體耗盡的三種驅動實作

問題場景

處理大量資料(如 4 萬筆以上)時,預設的緩衝查詢會一次將所有結果集載入 PHP 記憶體,導致 Allowed memory size exhausted 錯誤。非緩衝查詢(Unbuffered Query)讓 MySQL 伺服器逐行傳送資料,PHP 端只佔用單行記憶體,大幅降低記憶體峰值。

緩衝 vs 非緩衝對比

特性緩衝查詢 (預設)非緩衝查詢
記憶體佔用高(全結果集)極低(單行)
MySQL 伺服器負載低(一次傳完)高(需維持連線等待取回)
可用功能num_rows、data_seek、多次遍歷僅單向單次遍歷,不可 num_rows
適用場景小/中型結果集、需隨機存取大型結果集、匯出、ETL、串流處理

三大驅動實作方式

1. mysqli(推薦原生)

<?php
$mysqli = new mysqli('localhost', 'user', 'pass', 'db');
$mysqli->set_charset('utf8mb4');

// MYSQLI_USE_RESULT 關鍵參數啟用非緩衝模式
$result = $mysqli->query('SELECT id, name, email FROM large_table', MYSQLI_USE_RESULT);

if ($result) {
    while ($row = $result->fetch_assoc()) {
        // 處理單行資料:寫入 CSV、推送隊列、計算聚合等
        processRow($row);
    }
    $result->free(); // 釋放結果集資源
}
$mysqli->close();
?>

2. PDO_MySQL

<?php
$pdo = new PDO('mysql:host=localhost;dbname=db;charset=utf8mb4', 'user', 'pass', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false, // 使用原生預處理
]);

// 關鍵:關閉緩衝查詢
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$stmt = $pdo->query('SELECT id, name, email FROM large_table');
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    processRow($row);
}
$stmt = null; // 關閉游標
?>

3. 舊版 mysql_* 函數(已棄用/移除,僅維護舊專案參考)

<?php
// PHP 5.5 棄用,PHP 7.0 移除,僅供歷史參考
$conn = mysql_connect('localhost', 'user', 'pass');
mysql_select_db('db', $conn);

// mysql_unbuffered_query 返回資源,逐行取回
$result = mysql_unbuffered_query('SELECT id, name FROM large_table');
while ($row = mysql_fetch_assoc($result)) {
    processRow($row);
}
mysql_free_result($result);
mysql_close($conn);
?>

進階技巧:分批處理與游標

非緩衝查詢雖省記憶體,但長時間佔用資料庫連線。結合 LIMIT 分頁 或 游標 可平衡雙方:

<?php
// 分批次緩衝查詢(每次 1000 筆),兼顧記憶體與連線佔用
$batchSize = 1000;
$offset = 0;
do {
    $stmt = $pdo->prepare('SELECT id, data FROM large_table ORDER BY id LIMIT :offset, :limit');
    $stmt->bindValue(':offset', $offset, PDO::PARAM_INT);
    $stmt->bindValue(':limit', $batchSize, PDO::PARAM_INT);
    $stmt->execute();
    
    $rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
    foreach ($rows as $row) processRow($row);
    
    $offset += $batchSize;
} while (count($rows) === $batchSize);
?>

注意事項與最佳實踐

  1. 交易中不可使用非緩衝查詢:會導致「Commands out of sync」錯誤。
  2. 單一連線同一時間只能有一個非緩衝結果集:需處理完或 free() 後才能執行下一個查詢。
  3. 避免在迴圈中執行其他查詢:會干擾非緩衝結果集的讀取流。
  4. 設定 MYSQL_ATTR_USE_BUFFERED_QUERY 為 false 後,該 PDO 實例所有查詢均為非緩衝;若需混用,建議建立兩個 PDO 實例。
  5. 大量寫入/匯出:考慮 LOAD DATA INFILE、SELECT ... INTO OUTFILE 或資料庫原生匯出工具,效能遠優於 PHP 層面處理。

結語

非緩衝查詢是處理大數據集記憶體溢位的「銀彈」,但伴隨著資料庫連線佔用時間變長的代價。實務上,結合「分批緩衝查詢」或「資料庫端匯出」往往比純非緩衝更穩健。根據資料量、併發需求、業務容錯度,選擇最適合的策略。

copyright © 萬盛學電腦網 all rights reserved