PHP7的PDO ext是否将整个结果集读入内存? [英] Does PHP7's PDO ext read the entire result set into memory?

问题描述

自从我升级到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.

这篇关于PHP7的PDO ext是否将整个结果集读入内存?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！