PDO fetchall()性能注意事项? [英] PDO fetchall() performance considerations?

问题描述

在阅读了几篇有关PHP的PDO fetchall()函数对性能的影响的文章之后，我很好奇是否还有另一种方法可以在不调用fetchall()的情况下实现相同的结果.

After reading a few articles on the performance impact of PHP's PDO fetchall() function, I am curious if there is another way to accomplish the same outcome without invoking fetchall().

为了解释，大多数人同意fetchall()往往是资源密集型的.就我而言，我认为这不会成为太大的问题.是的，我必须从数据库中提取整个表并将其显示给用户，但是最多只能有100行.我认为这不会成为问题.但是，假设地，如果我需要拉100,000行，哪个是最佳的解决方案?

To explain, most agree that fetchall() tends to be resource intensive. In my case, I don't believe it will be too big of an issue. Yes, I am having to pull an entire table from my database and display it to the user, but it is going to be about a 100 rows at the very most; I don't believe that will be an issue. Hypothetically, though, if I were needing to pull 100,000 rows, what would be a more optimal solution?

推荐答案

假设，如果您需要在单个响应中输出所有100,000行，则应将PDO::MYSQL_ATTR_USE_BUFFERED_QUERY设置为false，执行查询，并迭代使用获取一次结果集.详细地说，如果PDO::MYSQL_ATTR_USE_BUFFERED_QUERY设置为true，则无论是否调用fetchAll()，php都会缓冲整个结果集.

Hypothetically, if you need to output all 100,000 rows in a single response, you should set PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false, execute the query, and iterate the resultset one row at a time using fetch. To elaborate, php buffers the entire resultset regardless of whether or not you call fetchAll() if PDO::MYSQL_ATTR_USE_BUFFERED_QUERY is set to true.

好处是您可以大大减少脚本的最大内存消耗，并且可以更快地开始流输出，尽管完成完成的总时间可能会或可能不会更长.

The benefit is that you drastically reduce the max memory consumption of the script, and you get to start streaming output sooner, although total time to completion may or may not take longer.

我忽略了在这种极端情况下应该考虑的其他事项，例如输出缓冲等...

I'm ignoring other things you should consider in such extreme circumstances, like output buffering etc...

这篇关于PDO fetchall()性能注意事项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！