PHP7 的 PDO ext 是否将整个结果集读入内存?

2022-01-07 00:00:00 php mysql pdo php-7 yii2

自从我升级到 PHP7 后，我注意到一些 SQL 语句不再起作用，而是内存不足.

I have noticed since I upgraded to PHP7 that some SQL statements no longer work and instead run out of memory.

我有这个代码:

$query = Yii::$app->db->createCommand('select * from tbl_title')->query();
while ($row = $reader->read()) {
    var_dump($row);
    exit();
}

而 Yii2 的数据库抽象只是 PDO 之上的一个非常薄的层，并没有做任何额外的事情.query() 除了在日志文件(Yii2 的)中添加一行用于分析和 reader->read() 只调用 PDO 流的 fetch() 函数.

And Yii2's database abstraction is just an extremely thin layer over PDO's and does not do anything extra. query() does nothing extra except add a line to a log file (Yii2's) for profiling and reader->read() just calls the PDO stream's fetch() function.

但是引用我的表的大小(使用的空间)时内存不足，即尝试分配 385 MB 的进程内存:

But it runs out of memory quoting the size (space used) of my table, i.e. trying to allocate 385 MB of process memory:

允许的内存大小为 134217728 字节耗尽(尝试分配 385883840 字节)

Allowed memory size of 134217728 bytes exhausted (tried to allocate 385883840 bytes)

作为扳手，如果我使用的查询的结果集完全符合 PHP 进程的 128 MB 限制.

As a spanner, if I use a query whose result set fits entirely in the 128 MB limit of the PHP process works.

那么，PHP7 是否发生了变化，我可以将其改回来吗?

So, has PHP7 changed and can I change it back?

推荐答案

它与 PHP7 没有直接关系.该问题是由于新的 mysqlnd 驱动程序造成的，因此即使使用 PHP 5.x，您也会遇到同样的问题.这实际上是一个错误修正，因为甚至在内存仍然分配之前，但它并不计入 memory_limit.

It is not directly PHP7-related. The issue is due to new mysqlnd driver, so you can experience the the same problem even with PHP 5.x as well. It is actually a bugfix, because even before the memory was still allocated, but it didn't count towards memory_limit.

为了避免内存问题，您必须对大型结果集使用无缓冲查询.

To avoid a memory issue you have to use unbuffered queries for the large resultsets.

因此，对于需要大型数据集的查询，请像这样设置正确的设置:

So, for the query that is expecting a large dataset, set the proper setting like this:

$pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);

为了进一步阅读，我在我的 PDO 教程中有一个不错的解释，感谢Nikic，他的批评反馈非常宝贵.

For the further reading, I've got a decent explanation in my PDO tutorial, thanks to Nikic, whose critical feedback was invaluable.

相关文章