問題描述

我在處理從大約 30,000 行的表中進行選擇時遇到了奇怪的時間.

I'm having a strange time dealing with selecting from a table with about 30,000 rows.

似乎我的腳本使用了大量的內存來實現一個簡單的、僅向前遍歷查詢結果的內容.

It seems my script is using an outrageous amount of memory for what is a simple, forward only walk over a query result.

請注意，這個例子是一個有點人為的、絕對最低限度的例子，它與真實代碼幾乎沒有相似之處，不能用簡單的數據庫聚合來代替.旨在說明不需要在每次迭代中保留每一行的觀點.

Please note that this example is a somewhat contrived, absolute bare minimum example which bears very little resemblance to the real code and it cannot be replaced with a simple database aggregation. It is intended to illustrate the point that each row does not need to be retained on each iteration.

<?php
$pdo = new PDO('mysql:host=127.0.0.1', 'foo', 'bar', array(
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
));
$stmt = $pdo->prepare('SELECT * FROM round');
$stmt->execute();

function do_stuff($row) {}

$c = 0;
while ($row = $stmt->fetch()) {
    // do something with the object that doesn't involve keeping 
    // it around and can't be done in SQL
    do_stuff($row);
    $row = null;
    ++$c;
}

var_dump($c);
var_dump(memory_get_usage());
var_dump(memory_get_peak_usage());

輸出:

int(39508)
int(43005064)
int(43018120)

我不明白為什么在任何時候幾乎不需要保存任何數據的情況下使用 40 兆內存.我已經計算出通過將SELECT *"替換為SELECT home, away"，我可以將內存減少大約 6 倍，但是我認為即使這種用法也非常高，而且表只會變得更大.

I don't understand why 40 meg of memory is used when hardly any data needs to be held at any one time. I have already worked out I can reduce the memory by a factor of about 6 by replacing "SELECT *" with "SELECT home, away", however I consider even this usage to be insanely high and the table is only going to get bigger.

是否有我遺漏的設置，或者 PDO 中是??否存在一些我應該注意的限制?如果 mysqli 不支持，我很高興擺脫 PDO 以支持 mysqli，所以如果這是我唯一的選擇，我將如何使用 mysqli 執行此操作?

Is there a setting I'm missing, or is there some limitation in PDO that I should be aware of? I'm happy to get rid of PDO in favour of mysqli if it can not support this, so if that's my only option, how would I perform this using mysqli instead?

推薦答案

創建連接后，需要設置PDO::MYSQL_ATTR_USE_BUFFERED_QUERY 為假:

After creating the connection, you need to set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false:

<?php
$pdo = new PDO('mysql:host=127.0.0.1', 'foo', 'bar', array(
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION,
));
$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

// snip

var_dump(memory_get_usage());
var_dump(memory_get_peak_usage());

輸出:

int(39508)
int(653920)
int(668136)

無論結果大小如何，內存使用量幾乎保持不變.

Regardless of the result size, the memory usage remains pretty much static.

這篇關于大結果集的 PDO/MySQL 內存消耗的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！