Mysql PDO 准备语句效率

发布于 2024-10-27 16:29:19 字数 716 浏览 2 评论 0原文

我正在接管一个 PHP 应用程序，该应用程序每次运行 SQL 语句时都使用 MySQL PDO 准备好的语句。我知道当您要对同一语句进行多次迭代时，准备 SQL 会更加高效。

$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$sth->execute(array(150, 'red'));
$red = $sth->fetchAll();
$sth->execute(array(175, 'yellow'));
$yellow = $sth->fetchAll();

然而，我正在接管的应用程序在 PDO 之上构建了一个层，该层调用通用的“执行”函数，并且它似乎准备了每个 SQL 查询。例如：

$query = self::$DB->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$query->execute($bindvars);

如果应用程序执行数百或数千条“INSERT INTO ...... ON DUPLICATE KEY UPDATE”SQL 语句，则 $DB->prepare() 步骤如果每次运行一次，是否会产生显着的开销时间？

非常感谢，杰森。

原文

I am taking over a PHP app that uses MySQL PDO prepared statements for each time it runs an SQL statement.
I know that Preparing SQL can be more efficient when the you are about to do many iterations of the same statement.

$sth = $dbh->prepare('SELECT name, colour, calories
FROM fruit
WHERE calories < ? AND colour = ?');
$sth->execute(array(150, 'red'));
$red = $sth->fetchAll();
$sth->execute(array(175, 'yellow'));
$yellow = $sth->fetchAll();

However, the app I am taking over has an built a layer on top of PDO that calls a common "execute" function, and it appears that it prepares every single SQL query. For example:

$query = self::$DB->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$query->execute($bindvars);

If the app does many hundreds or thousands of "INSERT INTO ...... ON DUPLICATE KEY UPDATE" SQL statements, does the $DB->prepare() step create a significant overhead if it is run every single time?

Many thanks, Jason.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

魂ガ小子 2024-11-03 16:29:19

从文档中：

调用 PDO::prepare() 并
PDOStatement::execute() for 语句
将多次发布
具有不同的参数值
优化您的性能
应用程序允许驾驶员
协商客户端和/或服务器端
查询计划和元的缓存
信息...

我并没有真正在这里透露任何信息，但“优化性能”的反义词确实是“开销”。至于它是否重要，为什么不运行一个循环并测量呢？然后，您可以自己做出决定，用硬数据来支持您的决定。

回复收藏 0 原文

不…忘初心 2024-11-03 16:29:19

除了查询重用之外，在 PDO 中使用准备好的语句的主要原因是执行占位符绑定。

$query = self::$DB->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$query->execute($bindvars);

在此代码中，$sql 变量中存在的问号（或 :named）占位符将替换为 中的值$bindvars 数组。此替换可确保变量被正确引用和转义，从而使执行 SQL 注入变得更加困难。

准备/执行过程中可能会产生少量开销，但考虑到 SQL 注入的风险，这一点小开销根本不算什么。唯一的其他选项是将 SQL 字符串连接在一起，这可能会带来巨大的安全风险，除非每次都完美地完成。

前任开发人员知道他或她在做什么，至少在这种特定情况下是这样，并且您不应该撤消他或她在这里所做的工作。恰恰相反，您应该在未来的所有代码中继续使用准备好的语句。

（另一方面，我不能保证 MySQL 的游标性能......）

Besides query reuse, the primary reason to use prepared statements in PDO is to perform placeholder binding.

$query = self::$DB->prepare($sql, array(PDO::ATTR_CURSOR => PDO::CURSOR_FWDONLY));
$query->execute($bindvars);

In this code, the question marks (or :named) placeholders present in the $sql variable are replaced with the values in the $bindvars array. This replacement ensures that the variables are properly quoted and escaped, making it much more difficult to perform SQL injection.

There may be a small amount of overhead in the prepare/execute, but that small overhead is nothing given the risk of SQL injection. The only other option is concatenating together the SQL string, and that can be a huge security risk unless it's done perfectly every time.

The previous developer knew what he or she was doing, at least in this specific case, and you should not undo the work he or she did here. Quite the opposite, you should continue using prepared statements in all of your future code.

(On the other hand, I can't vouch for MySQL's cursor performance...)

回复收藏 0 原文