具有较大结果集的PDO/MySQL内存消耗 [英] PDO/MySQL memory consumption with large result set

问题描述

在处理从大约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来执行此操作?

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?

推荐答案

创建连接后，需要设置

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内存消耗的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！