mysql 无缓冲的查询_PHP MySQL：缓冲和无缓冲查询之间的差异

我总是在印象上有所不同,简单来说,在

PHP / MySQL缓冲和无缓冲查询之间,缓冲(默认)将所有数据加载到结果集变量中,然后可以开始使用它们,而无缓冲加载行一次

假设你运行SELECT * FROM something,然后$result = $db-> query($query); $result将包含所有行和补充信息,如行数.所以如果你在一个100MB的数据库上做到这一点,那么如果没有索引的话,那么你可以预期$result将占用100MB.

[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,顺便说一下,我认为校长是一样的).

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.

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为什么最后一个引用说如果你在一个步骤中将所有行作为数组返回,它可能会消耗更多的内存？