Toàn tập về PDO::MYSQL_ATTR_USE_BUFFERED_QUERY — Buffered vs Unbuffered Query

1️⃣ Giới thiệu

Khi bạn query dữ liệu MySQL bằng PDO, mặc định PHP sẽ buffer toàn bộ kết quả vào RAM.
Cờ điều khiển hành vi này là:

PDO::MYSQL_ATTR_USE_BUFFERED_QUERY

Cờ này quyết định cách MySQL và PHP trao đổi dữ liệu:

2️⃣ Cách hoạt động

🔹 Khi PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = true

• MySQL trả toàn bộ kết quả về PHP ngay lập tức.
• PHP lưu toàn bộ dữ liệu (cả BLOB, TEXT, JSON, …) vào RAM.
• Sau đó bạn có thể fetch() hoặc foreach() bao nhiêu lần tùy thích.

👉 Ưu điểm:

• Truy cập nhanh, có thể chạy nhiều query cùng lúc trên cùng connection.
• Không phải lo thứ tự fetch().

👉 Nhược điểm:

• Rất tốn RAM nếu kết quả lớn.
• Dữ liệu BLOB (ảnh, nhị phân) cũng bị cache toàn bộ trong RAM, dù bạn không dùng.

🔹 Khi PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = false

• MySQL stream từng dòng (row) sang PHP khi bạn fetch().
• PHP không lưu toàn bộ dữ liệu trong RAM, mà đọc từng phần một.

👉 Ưu điểm:

• Siêu tiết kiệm RAM, lý tưởng cho dữ liệu lớn (LONGTEXT, LONGBLOB).
• Giảm nguy cơ PHP “ngốn” hàng trăm MB khi SELECT *.

👉 Nhược điểm:

• Không thể chạy query mới trên cùng connection cho đến khi fetch() xong.
• Không thể rowCount() trước khi đọc hết dữ liệu.

3️⃣ Ví dụ trong code PHP thuần

✅ Mặc định (buffered)

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => true, // mặc định
]);

$stmt = $pdo->query("SELECT * FROM big_table");
foreach ($stmt as $row) {
    // Mọi dữ liệu đã nằm trong RAM
}

🧩 Toàn bộ kết quả sẽ nạp vào RAM ngay sau khi query() chạy.
Nếu bảng có 500MB dữ liệu → PHP RAM tăng đột ngột.

✅ Unbuffered (stream)

$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false,
]);

$stmt = $pdo->query("SELECT * FROM big_table");

while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    // Dòng nào đọc, dòng đó đến
    process($row);
}

// Đảm bảo giải phóng bộ nhớ và cho phép query khác
$stmt->closeCursor();

💡 Mẹo:

• closeCursor() bắt buộc nếu bạn muốn chạy query tiếp theo.
• Dùng khi bạn xử lý file lớn, blob, hay stream dữ liệu sang client.

4️⃣ So sánh hiệu năng (minh họa thực tế)

5️⃣ Ứng dụng trong CodeIgniter 3 (CI3)

CI3 mặc định khi dùng:

'dbdriver' => 'mysqli'

→ luôn bật buffered query (và không thể tắt).

Muốn kiểm soát PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, bạn phải dùng:

'dbdriver' => 'pdo'

🔧 Cấu hình CI3 bật/tắt buffered query

File: application/config/database.php

$db['default'] = array(
    'dsn'      => 'mysql:host=localhost;dbname=test;charset=utf8mb4',
    'hostname' => '',
    'username' => 'root',
    'password' => '',
    'database' => 'test',
    'dbdriver' => 'pdo',
    'pconnect' => FALSE,
    'db_debug' => TRUE,
    'cache_on' => FALSE,
    'char_set' => 'utf8mb4',
    'dbcollat' => 'utf8mb4_unicode_ci',
    'swap_pre' => '',
    'encrypt' => FALSE,
    'compress' => FALSE,
    'stricton' => FALSE,
    'failover' => array(),
    'save_queries' => TRUE,

    // ⚙️ Cấu hình tùy chọn PDO
    'options' => array(
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false // Tắt buffered query
    )
);

⚠️ Nếu bạn dùng 'mysqli', trường 'options' sẽ bị bỏ qua.

🔍 Kiểm tra trạng thái trong CI3

Bạn có thể kiểm tra trực tiếp:

var_dump($this->db->conn_id->getAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY));

Kết quả:

bool(false)

hoặc

bool(true)

📊 Minh họa thực tế trong CI3

Buffered (mặc định):

$query = $this->db->query("SELECT * FROM tbl_files");
$rows = $query->result_array(); // toàn bộ BLOB nạp vào RAM

Unbuffered (stream):

$query = $this->db->query("SELECT * FROM tbl_files");
while ($row = $query->unbuffered_row('array')) {
    process($row);
}

Lưu ý: CI3 có hỗ trợ unbuffered_row(), nhưng vẫn cần PDO mode unbuffered để thực sự tiết kiệm RAM.

6️⃣ Khi nào nên tắt buffered query?

7️⃣ Kết luận

💬 Gợi ý thực tế cho dev

• Với website, API hoặc CMS bình thường → giữ mặc định (buffered).
• Với các hệ thống xử lý dữ liệu lớn (log, ảnh nhị phân, file, đồng bộ DB) → chuyển qua pdo và tắt buffered.
• Khi export hàng loạt (CSV, JSON, backup) → PDO::MYSQL_ATTR_USE_BUFFERED_QUERY = false là “cứu tinh”.

🧾 Mẫu code CI3 đầy đủ (đã tối ưu)

$db['default'] = [
    'dsn'      => 'mysql:host=localhost;dbname=bigdata;charset=utf8mb4',
    'username' => 'root',
    'password' => '',
    'dbdriver' => 'pdo',
    'pconnect' => FALSE,
    'db_debug' => TRUE,
    'char_set' => 'utf8mb4',
    'dbcollat' => 'utf8mb4_unicode_ci',
    'options'  => [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
        PDO::MYSQL_ATTR_USE_BUFFERED_QUERY => false
    ]
];

📘 Tóm tắt nhanh

🔸 true: nhanh hơn, tốn RAM hơn
🔸 false: tiết kiệm RAM, chậm hơn chút, chỉ một query tại một thời điểm
🔸 CI3 chỉ dùng được nếu chuyển sang dbdriver = 'pdo'
🔸 Nên tắt nếu bạn xử lý dữ liệu lớn hoặc chứa BLOB