MySQL PDO比查询准备得更快?这就是这个简单的测试所显示的 [英] MySQL PDO prepared faster than query? That's what this simple test shows
问题描述
这是我运行的一个简单测试，以快速了解使用MySQL PDO准备的语句与使用直接查询所要付出的性能损失.人员表中有2801行. MySQL版本5.5.28和PHP版本5.3.15.带有默认参数的Vanilla安装.测试可以在8GB的iMac上运行.
Here's a simple test I ran to get a quick idea of the performance penalty I would pay for using MySQL PDO prepared statements vs. using a straight query. There are 2801 rows in the person table. MySQL version 5.5.28 and PHP version 5.3.15. Vanilla installations, with whatever the default parameters are. Tests run on an iMac with 8GB.
$pdo = new PDO('mysql:host=localhost;dbname=cwadb_local', 'root', "");
$start = microtime(true);
for ($i = 0; $i < 200; $i++) {
$pdo->query("select * from person where name_last = 'smith' or true");
}
echo "<p>query: " . (microtime(true) - $start);
$start = microtime(true);
for ($i = 0; $i < 200; $i++) {
$stmt = $pdo->prepare("select * from person where name_last = :last or true");
$stmt->execute(array('last' => 'smith'));
}
echo "<p>prepare/execute: " . (microtime(true) - $start);
这是输出:
query: 21.010436058044
prepare/execute: 20.74036192894
哪个都没有表现出任何惩罚.可能性:
Which shows no penalty at all. Possibilities:
-
缓存准备好的语句确实有效. (注意，我将prepare函数保留在循环中.)
Caching of the prepared statement is really working. (Notice I kept the prepare function inside the loop.)
这是一个虚假测试，因为它太简单了.
It's a bogus test because it's too simple.
没有理论上的理由说明为什么准备/执行应该变慢，并且厌倦了不断的批评，MySQL/PDO/PHP开发人员为使它们变快而付出了更多的努力，以期使我们所有人关闭
There's no theoretical reason why prepare/execute should be slower, and, tired of the constant criticisms, the MySQL/PDO/PHP developers have worked extra hard to make them faster in an attempt to get us all to shut up.
其他?
这里已经多次说过，使用预备语句比使用查询更安全，并且使用PDO中的命名参数(Mysqli没有它们)，处理参数非常方便.但是，人们经常注意到，如果每次执行时都必须准备该语句，则会对性能造成不利影响.
It's been said many times here that using prepared statements is more secure than using query and, with the named parameters in PDO (Mysqli doesn't have them), dealing with the parameters is pretty convenient. But, it's just as often noted that there's a performance penalty if the statement has to be prepared each time it's executed.
那么，有人可以提供一些与我的简单测试相抵触的测试吗?或者，我们现在是否应该承认没有理由不使用准备好的语句?
So, can someone supply some tests that contradict my simple test? Or, shall we just now admit that there's no reason not to use prepared statements?
推荐答案
有一点要提.默认情况下，PDO只是模拟准备好的语句.
在仿真模式下，它运行相同的旧查询，而没有实际准备单个语句:)
There is one little thing to mention. By default, PDO just emulate prepared statements.
And while in emulation mode, it runs the same old query without actually preparing a single statement :)
首先，
$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, FALSE);
打开真实的准备好的语句.
to turn real prepared statements on.
人们经常注意到，这会降低性能
it's just as often noted that there's a performance penalty
还有一点要提.
不幸的是，世界上几乎没有 real 知识.尤其是在问答网站的世界中.人们倾向于重复他们已经阅读并认为合理的信息.无需运行任何测试即可证明，甚至无需动手即可.因此，经常注意到"根本不应该被视为可靠来源.
There is another little thing to mention.
Sadly, there are very little real knowledge in the world. And especially in the world of Q&A sites. People tend to repeat the information they had read and found reasonable. Without running any tests to proof or even without laying their hands on. So, "often noted" shouldn't be considered as a reliable source at all.
回到问题所在:尽管应该有一些惩罚，但在大多数情况下应该是微不足道的.如果是，则必须调整系统.
Back to the matter: though there should be some penalty, it should be insignificant most of time. If it is - you have to tune your system up.
无论如何，在仿真模式下，它既快速"又安全.
Anyway, in the emulation mode you got it both "fast" and safe.
更新
好吧，在对数据进行测试之后，我要说的是，如果您的大型数据集的差异是3倍，则您的数据库有问题.
Update
Well, after running your tests on my data, I've got to say that there is something wrong with your database if you have 3 times difference on a large dataset.
用于闪电查询
select title from Board where id = 1
结果是
emulation on off
query 0.07 0.130
prepare 0.075 0.145
而对于繁琐的查询
select title from Board where id > 1
结果是
emulation on off
query 0.96 0.96
prepare 0.96 1.00
因此，正如我们所看到的，在大型数据集上，差异变得不明显.
So, as we can see, on a large dataset the difference become unnoticeable.
对于雷电查询，有一些区别，但是，因为它仅需0,0003秒(对于单个查询)，我想这是"indifference"一词的完美示例.
For the lightning query there is some difference, but, as it takes only 0,0003th faction of second (for a single query) - I'd say that's perfect example for the word "indifference".
对于query()/prepare()之间相等的结果-我只有一个主意-PDO对所有查询都使用prepare/execute，即使那些没有绑定的查询也是如此.
For the equal results between query()/prepare() - I have only one idea - PDO uses prepare/execute for all queries, even those without bindings.
由于编码问题.
是的，怪异的GBK问题确实影响了5.3.3之前的版本的PDO.这些版本无法设置正确的编码，并且不可避免(在仿真模式下).但是，由于5.3.3 PDO支持在DSN中设置编码，因此现在一切正常.
对于mysqli，出于这个目的，必须使用mysqli_set_charset()来获得相同(不可渗透)的结果.
Yes, weird GBK problem does affect PDO for versions prior 5.3.3. These versions had no way to set the proper encoding and were unavoidable vulnerable (in emulation mode). But since 5.3.3 PDO supports setting encoding in DSN, and now everything is all right with it.
For mysqli one have to use mysqli_set_charset() for this very purpose with the very same (impenetrable) result.
在我自己的基于mysqli的类中，我正在使用自己的占位符实现，根本不使用任何准备好的语句.不是出于性能原因，而是为了提高可靠性.
In my own class which is based on mysqli, I am using my own placeholder implementation and use no prepared statements at all. Not for performance reasons but for better reliability.
这篇关于MySQL PDO比查询准备得更快?这就是这个简单的测试所显示的的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
