PHP 大量資料查詢記憶體耗盡解決方案：緩衝與非緩衝查詢深度解析

問題現象

執行大量資料查詢時，PHP 拋出致命錯誤：

PHP Fatal error: Allowed memory size of 268435456 bytes exhausted

根本原因：PHP 預設使用緩衝查詢，將整個結果集一次性載入記憶體，導致記憶體用盡。

核心概念：緩衝 vs 非緩衝查詢

三大資料庫擴充套件的非緩衝查詢實作

1. mysqli（推薦）

<?php
$mysqli = new mysqli("localhost", "my_user", "my_password", "world");

// 關鍵：傳入 MYSQLI_USE_RESULT 常數啟用非緩衝模式
$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);

if ($uresult) {
    while ($row = $uresult->fetch_assoc()) {
        echo $row['Name'] . PHP_EOL; // 逐列處理
    }
    $uresult->close(); // 必須釋放結果集，否則後續查詢會失敗
}
?>

2. PDO_MySQL

<?php
$pdo = new PDO("mysql:host=localhost;dbname=world", 'my_user', 'my_pass');

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

$uresult = $pdo->query("SELECT Name FROM City");
if ($uresult) {
    while ($row = $uresult->fetch(PDO::FETCH_ASSOC)) {
        echo $row['Name'] . PHP_EOL;
    }
}
?>

3. mysql（已棄用，僅供舊專案參考）

<?php
$conn = mysql_connect("localhost", "my_user", "my_pass");
mysql_select_db("world");

// 關鍵：使用 mysql_unbuffered_query 而非 mysql_query
$uresult = mysql_unbuffered_query("SELECT Name FROM City");
if ($uresult) {
    while ($row = mysql_fetch_assoc($uresult)) {
        echo $row['Name'] . PHP_EOL;
    }
}
?>

進階應用模式

分批處理大量資料（游標式）

<?php
$pdo = new PDO("mysql:host=localhost;dbname=world", 'user', 'pass');
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

$stmt = $pdo->query("SELECT id, data FROM huge_table");
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // 處理單筆資料：寫入檔案、發送 API、計算統計...
    processRow($row);
}
// 記憶體佔用恆定，不隨資料量增長
?>

搭配 Generator 實現惰性迭代器

function fetchRows(PDO $pdo, string $sql): Generator {
    $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
    $stmt = $pdo->query($sql);
    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
        yield $row;
    }
}

foreach (fetchRows($pdo, "SELECT * FROM huge_table") as $row) {
    // 記憶體高效處理
}

效能調優建議

• 索引優化：確保查詢欄位有適當索引，減少 MySQL 掃描時間，降低伺服器端壓力。
• 限制欄位：避免 SELECT *，僅選取必要欄位，減少網路傳輸與記憶體佔用。
• 分頁 vs 游標：LIMIT offset, size 深分頁效能差；大量資料優先用非緩衝游標或 WHERE id > last_id 方式。
• 連線複用：長時間運行的非緩衝查詢佔用連線，建議使用連線池或調整 wait_timeout/max_execution_time。

常見誤區

1. 以為 PDO 預設非緩衝：PDO_MySQL 預設啟用緩衝查詢，必須顯式關閉。
2. 非緩衝查詢中執行其他查詢：同一連線在非緩衝結果集未關閉前，無法執行新查詢，會報「Commands out of sync」。
3. 混用緩衝/非緩衝：同一腳本中混用需謹慎管理連線狀態，建議統一策略。

掌握緩衝與非緩衝查詢的差異與適用場景，配合 Generator 等現代 PHP 特性，即可優雅解決大量資料查詢的記憶體耗盡問題。