大结果集的PDO/MySQL内存消耗 - PDO/MySQL memory consumption with large result set

我有一个奇怪的时间处理从大约30,000行表中选择。

似乎我的脚本使用了惊人的内存量，这是一个简单的，只向前走的查询结果。

请注意，这个例子是一个人为的，绝对最小的例子，与实际代码几乎没有相似之处，它不能被简单的数据库聚合所取代。它的目的是说明不需要在每次迭代中保留每一行。

<?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倍的内存，但是我认为即使这样的使用率也非常高，并且表只会变得更大。

是否有我缺少的设置，或者PDO中是否存在我应该意识到的一些限制?我很高兴摆脱PDO有利于mysqli，如果它不能支持这一点，所以如果这是我唯一的选择，我将如何使用mysqli来执行这个?

创建连接后，需要将PDO::MYSQL_ATTR_USE_BUFFERED_QUERY设置为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)

无论结果大小如何，内存使用基本保持不变。

另一个选项是:

$i = $c = 0;
$query = 'SELECT home, away FROM round LIMIT 2048 OFFSET %u;';
while ($c += count($rows = codeThatFetches(sprintf($query, $i++ * 2048))) > 0)
{
    foreach ($rows as $row)
    {
        do_stuff($row);
    }
}

整个结果集(所有30,000行)在您开始查看它之前被缓冲到内存中。

你应该让数据库做聚合，只要求它提供你需要的两个数字。

SELECT SUM(home) AS home, SUM(away) AS away, COUNT(*) AS c FROM round

实际情况是，如果您获取所有行，并期望能够在PHP中遍历所有行，那么它们将存在于内存中。

如果你真的不认为使用SQL表达式和聚合是解决方案，你可以考虑限制/分块你的数据处理。不要一次获取所有行，可以这样做:

1)  Fetch 5,000 rows
2)  Aggregate/Calculate intermediary results
3)  unset variables to free memory
4)  Back to step 1 (fetch next set of rows)

我以前没有在PHP中这样做过，但是您可以考虑使用可滚动游标来获取行—参见获取文档中的示例。

它不是将查询的所有结果一次返回给PHP脚本，而是将结果保存在服务器端，您可以使用游标遍历它们，每次获取一个。

虽然我还没有测试过，但它肯定有其他缺点，例如使用更多的服务器资源，并且很可能由于与服务器的额外通信而降低性能。

改变获取方式也可能会产生影响，因为默认情况下，文档表明它将存储一个关联数组和一个数字索引数组，这必然会增加内存使用。

正如其他人所建议的，如果可能的话，首先减少结果的数量可能是一个更好的选择。