PHP MySQL 緩衝查詢 vs 非緩衝查詢：深度對比與選型指南

PHP MySQL 緩衝查詢 vs 非緩衝查詢：深度對比與選型指南

PHP 連接 MySQL 時，預設使用緩衝查詢：一次性將結果集從 MySQL 傳輸至 PHP 進程記憶體。對於大結果集，這會導致 PHP 記憶體飆升甚至 OOM。非緩衝查詢則逐行從 MySQL 服務器拉取，記憶體占用極低，但限制操作靈活性。本文深度解析兩者機制、API 對應、適用場景與實戰選型建議。

一、核心機制對比

二、mysqli / PDO API 對應表

<?php
/* ---------- mysqli ---------- */
// 緩衝（預設）
$mysqli->query("SELECT * FROM big_table");              // MYSQLI_STORE_RESULT
$mysqli->query("SELECT * FROM big_table", MYSQLI_STORE_RESULT);
$stmt = $mysqli->prepare("SELECT * FROM big_table");
$stmt->execute();
$stmt->store_result(); // 顯式緩衝

// 非緩衝
$mysqli->query("SELECT * FROM big_table", MYSQLI_USE_RESULT);
$mysqli->use_result();
$stmt = $mysqli->prepare("SELECT * FROM big_table");
$stmt->execute(); // 預處理語句預設非緩衝

/* ---------- PDO ---------- */
// 緩衝（預設）
$pdo->query("SELECT * FROM big_table");
// 非緩衝
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$pdo->query("SELECT * FROM big_table");
?>

三、記憶體統計陷阱

• libmysqlclient（舊驅動）：結果集佔用的 C 層記憶體 不計入 PHP memory_limit，直到賦值給 PHP 變量
• mysqlnd（PHP 5.4+ 預設）：原生驅動，結果集記憶體 計入 memory_limit，監控更準確

四、預處理語句的特殊行為

<?php
$stmt = $mysqli->prepare("SELECT * FROM logs WHERE date > ?");
$stmt->bind_param('s', $date);
$stmt->execute();

// 預處理語句預設「非緩衝」，結果留在 MySQL 服務器
// 若需 num_rows 或隨機訪問，必須顯式緩衝：
$stmt->store_result(); // 將全部結果傳回 PHP
echo $stmt->num_rows;  // 現在可用

// 用完必須釋放
$stmt->free_result();

// mysqlnd 環境下，更推薦 get_result + fetch_all
$result = $stmt->get_result();           // 取得 mysqli_result
$rows = $result->fetch_all(MYSQLI_ASSOC); // 一次性取陣列
?>

五、選型決策樹

結果集 < 10 萬行 且 需隨機訪問/統計行數
    → 緩衝查詢（預設，最省事）

結果集 > 10 萬行 或 記憶體 < 128MB
    → 非緩衝查詢（mysqli_use_result / PDO::MYSQL_ATTR_USE_BUFFERED_QUERY=false）

預處理語句 + 需 num_rows
    → $stmt->store_result() 後再操作，用完 $stmt->free_result()

串流導出 CSV / JSON Lines
    → 非緩衝 + 迴圈 fetch_row 逐行輸出，記憶體恆定

六、常見誤區

1. 以為 LIMIT 就能解決大結果集記憶體問題 —— 錯，緩衝模式下 MySQL 仍會先傳輸全量結果再截取
2. 非緩衝查詢中調用 num_rows 直接報錯 —— 需先遍歷或 store_result
3. 同一連接上混用緩衝/非緩衝查詢 —— 非緩衝未讀完時發送新查詢會報 Commands out of sync