MySQL准备的语句与普通查询.收益与收益损失 [英] MySQL prepared statement vs normal query. Gains & Losses

问题描述

我正在更新/重做一些数据库代码，我在想，使用准备好的语句我应该真的期望什么.

I'm in the middle of updating/reworking some database code and I was wondering, what I should really expect from using prepared statements.

使用以下示例代码:

$values = '';
for ($i = 0; $i < $count; $i++) {
    $name = mysql_real_escape_string ($list[$i][1]);
    $voc = mysql_real_escape_string ($list[$i][3]);
    $lev = $list[$it][2];
    $lev = is_numeric ($lev)? $lev : 0;

    $values .= ($values == '')? "('$name', '$voc', $lev)" : ", ('$name', '$voc', $lev)";
}
if ($values != '') {
    $core->query ("INSERT INTO onlineCList (name, voc, lev) VALUES $values;");
}

现在，除了在可读性(健全性)方面明显的进步以及max_packet_size不再是一个问题的事实之外，当我重新编码以使用准备好的语句时，我是否应该期望性能上的任何变化?我正在远程连接到MySQL服务器，我担心发送多个小数据包会比发送一个大数据包慢得多.如果是这样，MySQLi/mysqlnd可以缓存这些数据包吗?

Now, apart from the obvious gain in readability (, sanity) and the fact that max_packet_size stops being an issue, am I supposed to expect any changes in performance when I recode this to use prepared statements? I'm connecting remotely to the MySQL server, and I worry that sending multiple small packets would be significantly slower then sending one big packet. If this is the case, can MySQLi/mysqlnd cache these packets?

另一个例子:

$names = '';
while ($row = mysql_fetch_array ($result, MYSQL_ASSOC)) {
    $name = mysql_real_escape_string($row['name']);

    $names .= ($names == '') ? "'$name'" : ", '$name'";
}
if ($names != '') {
    $core->query ("UPDATE onlineActivity SET online = NULL WHERE name IN ($names) AND online = 1;");
}

如上所述，在将其重新编码为使用准备好的语句之后，我是否应该期望出现意外情况?如果它必须运行一个带有大IN子句的查询或运行带有相等性检查(.. WHERE name = $name AND ..)的多个准备好的查询，对MySQL服务器有什么区别?

As above, should I expect the unexpected, after recoding this to use prepared statements? Does it make any difference for the MySQL server, if it has to run one query with a big IN clause, or multiple prepared queries with equality checks (.. WHERE name = $name AND ..)?

假设所有内容都已正确索引.

Assume that everything is properly indexed.

推荐答案

通常，如果仅使用一条准备好的语句代替普通查询，则它会稍微慢一些，因为该查询是分两个步骤而不是一个步骤来准备和执行的.仅当准备语句然后多次执行时，预处理语句才会变得更快.

Normally, if you just use a prepared statement in place of a plain query, it's marginally slower since the query is prepared and executed in two steps instead of one. Prepared statements become faster only when you're preparing the statement and then executing it multiple times.

但是，在这种情况下，您使用的是mysql_real_escape_string，它可以往返数据库.更糟糕的是，您是在循环中执行此操作，因此，每个查询要多次执行.因此，在这种情况下，用一个准备好的语句替换所有这些往返是双赢的.

However, in this case you're using mysql_real_escape_string, which does a roundtrip to the database. Even worse, you're doing it inside a loop, so, executing it multiple times per query. So, in this case replacing all of those roundtrips with a single prepared statement is a win-win-win.

关于您的最后一个问题，没有理由不能像使用普通查询解析器那样在准备好的语句中使用相同的查询(即，没有理由使用IN来执行一个版本，而使用OR来执行另一个版本) ). 准备好的语句可以具有IN (?, ?, ?)，然后只需绑定该数量的参数即可.

Regarding your last question, there's no reason you can't use the same query with a prepared statement as you would through the normal query parser (i.e. no reason to execute one version with an IN and the other with a bunch of ORs). The prepared statement can have IN (?, ?, ?), and then you just bind that number of parameters.

我的建议是始终使用准备好的语句.在增加了少量性能开销的情况下，仍然值得在安全性(没有SQL注入)和可读性方面获得好处.可以肯定的是，每当您发现自己诉诸mysql_real_escape_string时，都应该使用准备好的语句. (对于不需要转义变量输入的简单一次性查询，严格来说，它们不是必需的.)

My advice would be to always use prepared statements. In cases where they add a marginal performance overhead, they're still worth it for the security (no SQL injection) and readability benefits. For sure, anytime you find yourself resorting to mysql_real_escape_string, you should use a prepared statement instead. (For simple one-off queries where there's no need to escape variable inputs, they aren't strictly necessary.)

这篇关于MySQL准备的语句与普通查询.收益与收益损失的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！