php - 缓冲查询和非缓冲查询之间的区别

Question

我一直认为 PHP/MySQL 缓冲查询和非缓冲查询之间的区别是缓冲(默认)将所有数据加载到结果集变量中，然后 然后可以开始使用它们，而无缓冲一次加载一行。

假设你运行了 SELECT * FROM sometable 然后执行了 $result = $db->query($query);, $result将包含所有行和补充信息，例如行数。因此，如果您在 100MB 的数据库上执行此操作，如果那里没有索引，您预计 $result 会占用约 100MB)。

但是，我遇到了这个 SO overflow question其中一部分提到了缓冲查询:

[The] result will contain some buffer of rows that is implementation dependent. It might be 100 rows or more or less. All columns are returned for each row; As you fetch more rows eventually the client will ask the server for more rows. This may be when the client runs out or it may be done preemptively.

是这样吗，真的还有一些缓冲吗？如果是这样的话，我们一般不需要担心 PHP 在处理大型结果集时会耗尽内存吗？这很奇怪，因为我一直在 40MB 的测试表上运行一些测试缓冲查询，而 PHP 总是报告大约 5MB 的峰值内存使用量。

最后，根据经验，您什么时候选择无缓冲而不是缓冲？可以举个例子吗？

(顺便说一下，我使用的是 MySQLi。我假设主体是相同的)。

我现在读了更多，而且更加困惑。在 http://php.net/manual/en/mysqli.quickstart.statements.php它说

On After statement execution results can be retrieved at once to be buffered by the client or by read row by row. Client-side result set buffering allows the server to free resources associated with the statement results as early as possible. Generally speaking, clients are slow consuming result sets. Therefore, it is recommended to use buffered result sets. mysqli_query() combines statement execution and result set buffering.

PHP applications can navigate freely through buffered results. Navigation is fast because the result sets are held in client memory. Please, keep in mind that it is often easier to scale by client than it is to scale the server.

在 http://php.net/manual/en/mysqli-result.fetch-all.php 上它说:

As mysqli_fetch_all() returns all the rows as an array in a single step, it may consume more memory than some similar functions such as mysqli_fetch_array(), which only returns one row at a time from the result set. Further, if you need to iterate over the result set, you will need a looping construct that will further impact performance. For these reasons mysqli_fetch_all() should only be used in those situations where the fetched result set will be sent to another layer for processing.

这似乎有些矛盾。 “客户端结果集缓冲”和“消费结果集”有什么区别？一个说它们保存在客户端内存中，另一个说逐行读取。如果整个事情都被缓冲到 PHP 中，为什么最后一句话说如果您在一个步骤中将所有行作为数组返回，它可能会消耗更多内存？

Answer 1

见:http://php.net/manual/en/mysqlinfo.concepts.buffering.php

Unbuffered MySQL queries execute the query and then return a resourcewhile the data is still waiting on the MySQL server for being fetched.This uses less memory on the PHP-side, but can increase the load onthe server. Unless the full result set was fetched from the server nofurther queries can be sent over the same connection. Unbufferedqueries can also be referred to as "use result".

根据这些特征，缓冲查询应该用于您只希望获得有限结果集或需要在读取所有行之前知道返回行数的情况。当您期望获得更大的结果时，应使用无缓冲模式。

缓冲查询是默认的。

无缓冲示例:

<?php
$mysqli  = new mysqli("localhost", "my_user", "my_password", "world");
$uresult = $mysqli->query("SELECT Name FROM City", MYSQLI_USE_RESULT);

if ($uresult) {
   while ($row = $uresult->fetch_assoc()) {
       echo $row['Name'] . PHP_EOL;
   }
}
$uresult->close();
?>