准备好的语句如何工作? [英] How do prepared statements work?

问题描述

我正在编写一些数据库例程，并且正在使用准备好的语句.我的环境是使用PHP5的PDO.

I'm writing some DB routines and I'm using prepared statements. My environment is PDO with PHP5.

我了解到，准备好的语句主要提供了性能上的好处，并带来了一些额外的好处，例如不必手动对输入数据进行SQL转义.

I understand prepared statements primarily provide a performance benefit, as well as some auxiliary bonuses such as not having to manually SQL-escape input data.

我的问题是关于表演的部分.

My question is about the performance part.

我下面有两个getPrice函数的实现，该实现需要一个产品ID并返回其价格.

I have two implementations of a getPrice function below that takes a product id and returns its price.

getPrice_A在同一脚本执行过程中的后续调用之间重用同一PDOStatement对象.这是必需的还是推荐的?如果是这样，有什么方法可以避免在每个模型中的每个get *()之间重复这些额外的代码?

getPrice_A reuses the same PDOStatement object across subsequent calls within the same script execution. Is this necessary or recommended? If so, is there any way to avoid duplicating this extra code across every single get*() in every single model?

getPrice_B在每次调用时创建一个新的PDOStatement对象. DBMS是否会认识到该语句已经准备好并且仍然可以跳过某些工作?换句话说，这种实现是否适当地利用了预准备语句的性能优势?

getPrice_B creates a new PDOStatement object on every call. Will the DBMS recognize this statement has already been prepared and still be able to skip some work? In other words, does this implementation properly take advantage of the performance benefits of prepared statements?

已经将所有内容写完并阅读了，我想getPrice_B很好，并且getPrice_A在此之上提供的好处微不足道，这可能或可能不值得使您感到额外的麻烦.

Having written all this out and read it over, I imagine getPrice_B is fine and getPrice_A is providing a negligible benefit on top of that, which may or may not be worth the extra complication.

尽管如此，我还是想肯定地听别人的话.

I'd still like to hear for sure from someone more knowledgable though.

在下面的示例中，假定$pdo是有效的，已连接的PDO对象.

Assume that $pdo is a valid, connected PDO object in the examples below.

<?php
class Product {
    static function &getPrice_A($id) {
        static $stmt;
        if (!$stmt) {
            $stmt = $pdo->prepare('SELECT price FROM products WHERE id = ?');
        }
        $stmt->execute(array($id));
        return $stmt->fetchColumn(0);
    }

    static function &getPrice_B($id) {
        $stmt = $pdo->prepare('SELECT price FROM products WHERE id = ?');
        $stmt->execute(array($id));
        return $stmt->fetchColumn(0);
    }
}

// example usage:
$price = Product::getPrice(4982);
echo "Product 4982 costs $price\n";

推荐答案

据我了解，如果准备好的语句是同一条语句，则准备好的语句将重用生成的SQL计划，因此数据库将看到相同的准备好的语句，而不必做这项工作以弄清楚如何查询数据库.我要说的是，将准备好的语句保存在Product::getPrice_A中的额外工作通常不是很有帮助，更多是因为它会使代码模糊而不是性能问题.在处理性能时，我觉得最好总是着眼于代码的清晰度，然后在具有指示问题的实际统计数据时再关注性能.

From what I understand, prepared statements will reuse the generated SQL plan if it is the same statement, so the database will see the same prepared statement and not have to do the work to figure out how to query the database. I would say the extra work of saving the prepared statement in Product::getPrice_A is not typically very helpful, more because it can obscure the code rather than an issue of performance. When dealing with performance, I feel it's always best to focus on code clarity and then performance when you have real statistics that indicate a problem.

我会说是的，多余的工作是不必要的"(不管它是否真的可以提高性能).另外，我不是数据库专家，但是我从其他人那里听到了预准备语句的性能提升，这是在数据库级别上，而不是代码级别上(因此，如果代码实际上是在数据库级别上调用参数化语句)实际的数据库，然后数据库可以执行这些执行计划缓存...尽管取决于数据库，即使没有参数化的语句，您也可能会受益.)

I would say "yes, the extra work is unnecessary" (regardless of if it really boosts performance). Also, I am not a very big DB expert, but the performance gain of prepared statements is something I heard from others, and it is at the database level, not the code level (so if the code is actually invoking a parameterized statement on the actual DB, then the DB can do these execution plan caching... though depending on the database, you may get the benefit even without the parameterized statement).

无论如何，如果您真的很担心(并看到)数据库性能问题，则应该研究一种缓存解决方案……我强烈建议您使用

Anyways, if you are really worried about (and seeing) database performance issues, you should look into a caching solution... of which I would highly recommend memcached. With such a solution, you can cache your query results and not even hit the database for things you access frequently.

这篇关于准备好的语句如何工作?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！