大结果集的 PDO/MySQL 内存消耗

2021-12-26 00:00:00 php mysql pdo

我在处理从大约 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.

相关文章