處理大量資料(如 4 萬筆以上)時,預設的緩衝查詢會一次將所有結果集載入 PHP 記憶體,導致 Allowed memory size exhausted 錯誤。非緩衝查詢(Unbuffered Query)讓 MySQL 伺服器逐行傳送資料,PHP 端只佔用單行記憶體,大幅降低記憶體峰值。
| 特性 | 緩衝查詢 (預設) | 非緩衝查詢 |
|---|---|---|
| 記憶體佔用 | 高(全結果集) | 極低(單行) |
| MySQL 伺服器負載 | 低(一次傳完) | 高(需維持連線等待取回) |
| 可用功能 | num_rows、data_seek、多次遍歷 | 僅單向單次遍歷,不可 num_rows |
| 適用場景 | 小/中型結果集、需隨機存取 | 大型結果集、匯出、ETL、串流處理 |
<?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();
?>
<?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; // 關閉游標
?>
<?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);
?>
free() 後才能執行下一個查詢。MYSQL_ATTR_USE_BUFFERED_QUERY 為 false 後,該 PDO 實例所有查詢均為非緩衝;若需混用,建議建立兩個 PDO 實例。LOAD DATA INFILE、SELECT ... INTO OUTFILE 或資料庫原生匯出工具,效能遠優於 PHP 層面處理。非緩衝查詢是處理大數據集記憶體溢位的「銀彈」,但伴隨著資料庫連線佔用時間變長的代價。實務上,結合「分批緩衝查詢」或「資料庫端匯出」往往比純非緩衝更穩健。根據資料量、併發需求、業務容錯度,選擇最適合的策略。