PHP + MySQL:缓冲查询和非缓冲查询之间的区别 [英] PHP + MySQL: Difference between buffered and unbuffered queries

问题描述

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

I was always under the impression the difference, in simple terms, between PHP/MySQL buffered and unbuffered queries is that buffered (the default) loads all the data into your results set variable and then you can start using them whereas unbuffered loads a row at a time.

假设您先运行SELECT * FROM sometable，然后执行$result = $db->query($query);，$result将包含所有行和补充信息，例如行数.因此，如果您在100MB的数据库上进行操作，则如果没有索引，则$result会占用约100MB的空间.

Say you ran SELECT * FROM sometable and then did $result = $db->query($query);, $result would contain all the rows and supplementary information such as the number of rows. So if you did it on a 100MB database you'd expect $result to take up ~100MB if there were no indices on there).

但是，我遇到了这个 SO溢出问题其中一部分涉及缓冲查询:

However, I came across this SO overflow question part of which says of buffered queries:

[The]结果将包含一些依赖于实现的行缓冲区.它可能是100行或更多或更少.每行返回所有列；随着您获取更多行，客户端最终将向服务器请求更多行.可能是客户端用尽时，也可能是抢先完成时.

[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的峰值内存使用情况.

Is this right, is there really still some buffering going on? If that is the case, do we generally not need to worry about PHP running out of memory when dealing with large result sets? It's odd because I have been running some test buffered queries on a 40MB test table and PHP always reports a peak memory usage of ~5MB.

最后，根据经验，何时选择无缓冲而不是缓冲?您能举个例子吗?

Finally, as a rule of thumb, when do you choose unbuffered over buffered? Can you please provide an example?

谢谢.

(顺便说一句，我正在使用MySQLi.我假设主体是相同的.)

(I am using MySQLi, by the way. I assume the principal is the same).

我现在已经读了更多书，甚至更加困惑.在 http://php.net/manual/en/mysqli.quickstart.statements .php 它说

I have read a bit more now and am even more confused. On http://php.net/manual/en/mysqli.quickstart.statements.php it says

On语句执行后，可以立即检索结果以供客户端或逐行读取进行缓冲.客户端结果集缓冲允许服务器尽早释放与语句结果关联的资源.一般来说，客户端是消耗缓慢的结果集.因此，建议使用缓冲的结果集. mysqli_query()结合了语句执行和结果集缓冲.

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应用程序可以自由浏览缓冲的结果.导航速度很快，因为结果集保存在客户端内存中.请记住，按客户端进行扩展通常比按服务器进行扩展更容易.

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 它说:

由于mysqli_fetch_all()在单个步骤中将所有行作为数组返回，因此它可能比某些类似函数(如mysqli_fetch_array())消耗更多的内存，而mysqli_fetch_array()一次仅从结果集中返回一行.此外，如果您需要遍历结果集，则将需要一个循环结构，这将进一步影响性能.由于这些原因，mysqli_fetch_all()仅应在将获取的结果集发送到另一层进行处理的情况下使用.

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中，那最后的引号为什么会说如果在一步中将所有行作为数组返回，则可能会消耗更多的内存?

This seems somewhat contradictory. What's the difference between "client-side result set buffering" and "consuming result sets"? One says they're held in client memory and the other says read row by row. If the whole thing is buffered to PHP why does that last quote says that if you return all the rows as an array in a single step it may consume more memory?

推荐答案

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

无缓冲的MySQL查询执行查询，然后返回资源 而数据仍在MySQL服务器上等待获取. 这样会在PHP端使用较少的内存，但会增加 服务器.除非从服务器获取了完整的结果集，否则 可以通过同一连接发送进一步的查询.无缓冲 查询也可以称为使用结果".

Unbuffered MySQL queries execute the query and then return a resource while 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 on the server. Unless the full result set was fetched from the server no further queries can be sent over the same connection. Unbuffered queries can also be referred to as "use result".

在遵循这些特征的情况下，如果您只期望有限的结果集或需要在读取所有行之前知道返回的行数，则应使用缓冲的查询.当期望更大的结果时，应使用无缓冲模式.

Following these characteristics buffered queries should be used in cases where you expect only a limited result set or need to know the amount of returned rows before reading all rows. Unbuffered mode should be used when you expect larger results.

默认为缓冲查询.

无缓冲示例:

<?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();
?>

希望这会有所帮助

这篇关于PHP + MySQL:缓冲查询和非缓冲查询之间的区别的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！