PHP PDO:重新准备语句如何影响性能 [英] PHP PDO: how does re-preparing a statement affect performance
问题描述
我正在编写一个半简单的数据库包装器类，并希望有一种提取方法，该方法可以自动运行 :它应该只在第一次时准备每个不同的语句，然后绑定并执行查询连续的通话.
我猜主要问题是:重新准备相同的MySql语句如何工作，PDO会神奇地识别该语句(因此我不必这样做)并停止操作吗?
如果 not ，我打算通过为每个不同的查询生成一个唯一键，并将准备好的语句保留在数据库对象的私有数组中-在其唯一键下，以实现此目的.我打算以下列方式之一获取阵列键(我都不喜欢).按照优先顺序:
- 让程序员在调用方法时传递一个额外的，始终相同的参数-类似于
basename(__FILE__, ".php") . __LINE__的内容(仅当在循环中调用我们的方法时，此方法才有效-多数情况下是这种情况此功能是必需的) - 让程序员传递一个完全随机的字符串(很可能是事先生成的)作为额外的参数
- 使用传递的查询本身来生成密钥-获取查询的哈希值或类似的内容
- 通过调用
debug_backtrace与第一个项目符号(上方)相同
有人有类似的经历吗?尽管我正在使用的系统确实需要进行优化(它相当大，并且每周都会增长)，但也许我不担心任何事情，并且在做我的事情时没有性能上的好处我在做什么?
好吧，因为我一直在抨击为缓存键入查询的方法，而不是仅仅使用查询字符串本身，所以我做了一个幼稚的基准测试.以下是使用普通查询字符串与首先创建md5哈希的比较:
$ php -v
$ PHP 5.3.0-3 with Suhosin-Patch (cli) (built: Aug 26 2009 08:01:52)
$ ...
$ php benchmark.php
$ PHP hashing: 0.19465494155884 [microtime]
$ MD5 hashing: 0.57781004905701 [microtime]
$ 799994
代码:
<?php
error_reporting(E_ALL);
$queries = array("SELECT",
"INSERT",
"UPDATE",
"DELETE",
);
$query_length = 256;
$num_queries = 256;
$iter = 10000;
for ($i = 0; $i < $num_queries; $i++) {
$q = implode('',
array_map("chr",
array_map("rand",
array_fill(0, $query_length, ord("a")),
array_fill(0, $query_length, ord("z")))));
$queries[] = $q;
}
echo count($queries), "\n";
$cache = array();
$side_effect1 = 0;
$t = microtime(true);
for ($i = 0; $i < $iter; $i++) {
foreach ($queries as $q) {
if (!isset($cache[$q])) {
$cache[$q] = $q;
}
else {
$side_effect1++;
}
}
}
echo microtime(true) - $t, "\n";
$cache = array();
$side_effect2 = 0;
$t = microtime(true);
for ($i = 0; $i < $iter; $i++) {
foreach ($queries as $q) {
$md5 = md5($q);
if (!isset($cache[$md5])) {
$cache[$md5] = $q;
}
else {
$side_effect2++;
}
}
}
echo microtime(true) - $t, "\n";
echo $side_effect1 + $side_effect2, "\n";
I'm writing a semi-simple database wrapper class and want to have a fetching method which would operate automagically: it should prepare each different statement only the first time around and just bind and execute the query on successive calls.
I guess the main question is: How does re-preparing the same MySql statement work, will PDO magically recognize the statement (so I don't have to) and cease the operation?
If not, I'm planning to achieve do this by generating a unique key for each different query and keep the prepared statements in a private array in the database object - under its unique key. I'm planning to obtain the array key in one of the following ways (none of which I like). In order of preference:
- have the programmer pass an extra, always the same parameter when calling the method - something along the lines of
basename(__FILE__, ".php") . __LINE__(this method would work only if our method is called within a loop - which is the case most of the time this functionality is needed) - have the programmer pass a totally random string (most likely generated beforehand) as an extra parameter
- use the passed query itself to generate the key - getting the hash of the query or something similar
- achieve the same as the first bullet (above) by calling
debug_backtrace
Has anyone similar experience? Although the system I'm working for does deserve some attention to optimization (it's quite large and growing by the week), perhaps I'm worrying about nothing and there is no performance benefit in doing what I'm doing?
OK, since I've been bashing methods of keying the queries for the cache, other than simply using the query string itself, I've done a naive benchmark. The following compares using the plain query string vs first creating the md5 hash:
$ php -v
$ PHP 5.3.0-3 with Suhosin-Patch (cli) (built: Aug 26 2009 08:01:52)
$ ...
$ php benchmark.php
$ PHP hashing: 0.19465494155884 [microtime]
$ MD5 hashing: 0.57781004905701 [microtime]
$ 799994
The code:
<?php
error_reporting(E_ALL);
$queries = array("SELECT",
"INSERT",
"UPDATE",
"DELETE",
);
$query_length = 256;
$num_queries = 256;
$iter = 10000;
for ($i = 0; $i < $num_queries; $i++) {
$q = implode('',
array_map("chr",
array_map("rand",
array_fill(0, $query_length, ord("a")),
array_fill(0, $query_length, ord("z")))));
$queries[] = $q;
}
echo count($queries), "\n";
$cache = array();
$side_effect1 = 0;
$t = microtime(true);
for ($i = 0; $i < $iter; $i++) {
foreach ($queries as $q) {
if (!isset($cache[$q])) {
$cache[$q] = $q;
}
else {
$side_effect1++;
}
}
}
echo microtime(true) - $t, "\n";
$cache = array();
$side_effect2 = 0;
$t = microtime(true);
for ($i = 0; $i < $iter; $i++) {
foreach ($queries as $q) {
$md5 = md5($q);
if (!isset($cache[$md5])) {
$cache[$md5] = $q;
}
else {
$side_effect2++;
}
}
}
echo microtime(true) - $t, "\n";
echo $side_effect1 + $side_effect2, "\n";
这篇关于PHP PDO:重新准备语句如何影响性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
