php PDO::fetchAll 与 PDO::fetch 在循环中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2770630/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PDO::fetchAll vs. PDO::fetch in a loop
提问by Lotus Notes
Just a quick question.
只是一个简单的问题。
Is there any performance difference between using PDO::fetchAll() and PDO::fetch() in a loop (for large result sets)?
在循环中使用 PDO::fetchAll() 和 PDO::fetch() 之间有什么性能差异（对于大型结果集）？
I'm fetching into objects of a user-defined class, if that makes any difference.
我正在获取用户定义类的对象，如果这有什么不同的话。
My initial uneducated assumption was that fetchAll might be faster because PDO can perform multiple operations in one statement while mysql_query can only execute one. However I have little knowledge of PDO's inner workings and the documentation doesn't say anything about this, and whether or not fetchAll() is simply a PHP-side loop dumped into an array.
我最初未受过教育的假设是 fetchAll 可能更快，因为 PDO 可以在一个语句中执行多个操作，而 mysql_query 只能执行一个。但是，我对 PDO 的内部工作知之甚少，文档对此没有任何说明，以及 fetchAll() 是否只是转储到数组中的 PHP 端循环。
Any help?
有什么帮助吗？
回答by Arkh
Little benchmark with 200k random records. As expected, the fetchAll method is faster but require more memory.
带有 20 万条随机记录的小基准。正如预期的那样，fetchAll 方法速度更快，但需要更多内存。
Result :
fetchAll : 0.35965991020203s, 100249408b
fetch : 0.39197015762329s, 440b
The benchmark code used :
使用的基准代码：
<?php
// First benchmark : speed
$dbh = new PDO('mysql:dbname=testage;dbhost=localhost', 'root', '');
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'SELECT * FROM test_table WHERE 1';
$stmt = $dbh->query($sql);
$data = array();
$start_all = microtime(true);
$data = $stmt->fetchAll();
$end_all = microtime(true);
$stmt = $dbh->query($sql);
$data = array();
$start_one = microtime(true);
while($data = $stmt->fetch()){}
$end_one = microtime(true);
// Second benchmark : memory usage
$stmt = $dbh->query($sql);
$data = array();
$memory_start_all = memory_get_usage();
$data = $stmt->fetchAll();
$memory_end_all = memory_get_usage();
$stmt = $dbh->query($sql);
$data = array();
$memory_end_one = 0;
$memory_start_one = memory_get_usage();
while($data = $stmt->fetch()){
$memory_end_one = max($memory_end_one, memory_get_usage());
}
echo 'Result : <br/>
fetchAll : ' . ($end_all - $start_all) . 's, ' . ($memory_end_all - $memory_start_all) . 'b<br/>
fetch : ' . ($end_one - $start_one) . 's, ' . ($memory_end_one - $memory_start_one) . 'b<br/>';
回答by Kendall Hopkins
One thing about PHP that I've found to be true almost alwaysis that a function you implement yourself will almost always be slower than the PHP equivalent. This is because when something is implemented in PHP it doesn't have all the compile time optimizations that C has (which PHP is written in) and there is high overhead of PHP function calls.
我发现几乎总是正确的关于 PHP 的一件事是，您自己实现的函数几乎总是比等效的 PHP 慢。这是因为当某些东西在 PHP 中实现时，它没有 C 具有的所有编译时间优化（用 PHP 编写）并且 PHP 函数调用的开销很高。
回答by iVariable
all benchmarks above which measure "memory footprint" are actually incorrect for the very simple reason.
出于非常简单的原因，所有测量“内存占用”的基准实际上都是不正确的。
PDO by default does load all the things into the memory and it does not care if you use fetch or fetchAll. To really get benefits of unbuffered query you should instruct PDO to use unbuffered queries:
默认情况下，PDO 会将所有内容加载到内存中，并且它不关心您是使用 fetch 还是 fetchAll。要真正获得无缓冲查询的好处，您应该指示 PDO 使用无缓冲查询：
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
$db->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
In that case you will see huge difference in memory footprint of the script
在这种情况下，您将看到脚本内存占用的巨大差异
回答by Mihai Stancu
@Arkh
@阿克
// $data in this case is an array of rows;
$data = $stmt->fetchAll();
// $data in this case is just one row after each loop;
while($data = $stmt->fetch()){}
// Try using
$i = 0;
while($data[$i++] = $stmt->fetch()){}
The memory difference should become neglijable
内存差异应该可以忽略不计
回答by Rihards
As Mihai Stancu was saying, there is almost no memory difference though fetchAll beats fetch + while.
正如 Mihai Stancu 所说，虽然 fetchAll 比 fetch + while 几乎没有内存差异。
Result :
fetchAll : 0.160676956177s, 118539304b
fetch : 0.121752023697s, 118544392b
I got the results above with running while correctly:
我在正确运行时得到了上述结果：
$i = 0;
while($data[$i++] = $stmt->fetch()){
//
}
So the fetchAll consumes less memory, but fetch + while is faster! :)
所以 fetchAll 消耗更少的内存，但 fetch + while 更快！:)
回答by Andy
But surely if you're storing the fetched data in an array, the memory usage will be equal?
但是，如果您将获取的数据存储在数组中，那么内存使用量肯定相等吗？
<?php
define('DB_HOST', 'localhost');
define('DB_USER', 'root');
define('DB_PASS', '');
// database to use
define('DB', 'test');
try
{
$dbh = new \PDO('mysql:dbname='. DB .';host='. DB_HOST, DB_USER, DB_PASS); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = 'SELECT * FROM users WHERE 1';
$stmt = $dbh->query($sql);
$data = array();
$start_all = microtime(true);
$data = $stmt->fetchAll();
$end_all = microtime(true);
$stmt = $dbh->query($sql);
$data = array();
$start_one = microtime(true);
while($data = $stmt->fetch()){}
$end_one = microtime(true);
// Second benchmark : memory usage
$stmt = $dbh->query($sql);
$data = array();
$memory_start_all = memory_get_usage();
$data = $stmt->fetchAll();
$memory_end_all = memory_get_usage();
$stmt = $dbh->query($sql);
$data = array();
$memory_end_one = 0;
$memory_start_one = memory_get_usage();
while($data[] = $stmt->fetch()){
$memory_end_one = max($memory_end_one, memory_get_usage());
}
echo 'Result : <br/>
fetchAll : ' . ($end_all - $start_all) . 's, ' . ($memory_end_all - $memory_start_all) . 'b<br/>
fetch : ' . ($end_one - $start_one) . 's, ' . ($memory_end_one - $memory_start_one) . 'b<br/>';
}
catch ( PDOException $e )
{
echo $e->getMessage();
}
?>
Result :
fetchAll : 2.6941299438477E-5s, 9824b
fetch : 1.5974044799805E-5s, 9824b
回答by StrayObject
I know this is an old topic, but I run across this having the same question. Having run my own simple "benchmark" and reading what others wrote here I came to the conclusion that this is not an exact science and while one should strive to write the quality, light code, there is no point wasting too much time at the start of the project.
我知道这是一个老话题，但我遇到了同样的问题。运行我自己的简单“基准”并阅读其他人在这里写的内容后，我得出的结论是，这不是一门精确的科学，虽然人们应该努力编写高质量、轻量级的代码，但在开始时浪费太多时间是没有意义的项目的。
My suggestion is: Gather data by running the code(in beta?) for a while and then start optimizing.
我的建议是：通过运行代码（测试版？）一段时间来收集数据，然后开始优化。
In my simple benchmark (only tested execution time) I've got results varying between 5% and 50% BOTH ways. I run both options in the same script, but when I run fetch + while first it has been faster than fetchall and vice versa. (I know I should have run them single and couple hundred times get the median and mean and then compare, but - as I have said at the begining - I concluded that in my case it is too early to start doing so.)
在我的简单基准测试（仅测试执行时间）中，我得到的结果在 5% 到 50% 之间变化。我在同一个脚本中运行这两个选项，但是当我首先运行 fetch + 时，它比 fetchall 快，反之亦然。（我知道我应该单独运行它们，几百次得到中位数和平均值然后进行比较，但是 - 正如我在开始时所说的 - 我得出的结论是，就我而言，现在开始这样做还为时过早。）
