SQL绑定参数会影响性能吗？

Question

SQL绑定参数会影响性能吗？

5

假设我有一个名为项目的表，其中有一列名为预算，具有标准的B-Tree索引。该表有50,000个项目，只有1%的项目的预算超过100万。如果我运行以下SQL查询：

SELECT * From Projects WHERE Budget > 1000000;

规划器将使用索引范围扫描Budget从堆表中获取行。但是，如果我使用以下查询：

SELECT * From Projects WHERE Budget > 50;

规划器很可能会对该表进行顺序扫描，因为它知道这个查询最终将返回大部分或全部行，并且没有理由将索引的所有页面加载到内存中。现在，假设我运行以下查询：

SELECT * From Projects WHERE Budget > :budget;

当我将参数:budget传递到数据库时，查询语句会被缓存，但是无法推断出基数数据。实际上，大多数数据库会假设分布均匀，并且缓存的查询计划也会反映这一点。这让我感到惊讶，因为通常当你阅读关于绑定参数的好处时，主题是防止SQL注入攻击。

显然，如果生成的查询计划相同，则这可能会提高性能，因为不需要编译新计划，但如果:budget的值差异很大，则可能会降低性能。

我的问题：为什么绑定参数在生成和缓存查询计划之前不被解析？现代数据库难道不应该为查询生成最佳计划吗？这应该意味着查看每个参数的值并获取准确的索引统计信息。

注意：这个问题可能不适用于mySql，因为mySql不缓存SQL计划。但是，我对为什么Postgres、Oracle和MS SQL也是如此感兴趣。

- Mike Christensen

1

也许我理解有误，但如果数据库必须知道绑定值本身才能决定优化/执行计划，那么这不就相当于硬解析吗？ - tbone

规划器会知道这个查询最终会返回大部分或全部行，这是真的吗？这些数据库是否维护某种索引值的直方图，以便基于查找值做出决策？ - AndreKR

@AndreKR - 正确的。大多数现代数据库都会记录表基数的统计信息。 - Mike Christensen

是的，但在执行查询之前，你需要更多的信息来了解 Budget > 1000000 比 Budget > 50 返回了多少行。 - AndreKR

@AndreKR - 正确。通常它会存储统计数据，例如不同值的数量、最常见的值、数据的直方图以及相关性（与表中的位置有关）。你应该查看使用索引，卢克 - 它详细介绍了这种情况。 - Mike Christensen

2个回答

6

这让我感到惊讶，因为通常当你阅读有关绑定参数的好处时，它是关于防止SQL注入攻击的。不要混淆参数化查询和预处理语句。两者都提供参数化，但预处理语句提供了查询计划的缓存。为什么在生成和缓存查询计划之前不解析绑定参数？因为有时生成查询计划是一个昂贵的步骤。预处理语句允许您分摊查询计划的成本。然而，如果你只是想要SQL注入保护，请不要使用预处理语句。使用参数化查询。例如，在PHP中，您可以使用http://php.net/pg_query_params来执行一个不缓存查询计划的参数化查询；同时，http://php.net/pg_prepare和http://php.net/pg_execute用于缓存准备语句的计划并稍后执行它。编辑：9.2 显然改变了准备语句的规划方式。

- Frank Farmer

啊，那么如果参数值可能导致完全不同的执行计划（比如不使用索引扫描一百万行），开发人员就需要使用参数化查询而不是预编译语句。我相信这回答了我的问题！ - Mike Christensen

我说Postgres只有在你明确使用PREPARE关键字时才会缓存执行计划，这种说法正确吗？ - Mike Christensen

我相信你的客户端库中某些方法会在没有PREPARE关键字的情况下导致执行计划缓存，例如pg_prepare（php）和PQprepare（libpq）。 - Frank Farmer

1

@MikeChristensen 在PostgreSQL中，低级API也使用协议级别的PREPARE等效项。我知道PgJDBC是这样做的。有点让人困惑，PgJDBC中的“PreparedStatement”最初被执行为参数化查询，而实际上并没有使用服务器端准备好的语句，直到通过prepareThreshold。非常奇怪。顺便说一句，“prepared statements”对性能也有影响，尤其是在9.2之前，当绑定参数的值未知时，它们有时会选择非常糟糕的查询计划。 - Craig Ringer

3

从9.2版本更新日志中：现在，规划器会尝试为特定的参数值生成自定义计划。只有在自定义计划一再证明没有提供任何好处之后，才会使用通用计划。这个变化应该消除以前使用预处理语句时看到的性能惩罚。 - Frank Farmer

显示剩余2条评论

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Justin Cave · Accepted Answer

对于Oracle数据库而言，情况因查询语句的具体情况而异。

从至少9i版本开始，Oracle就支持绑定变量查看。这意味着第一次执行查询时，优化器会查看绑定变量的值，并根据第一个绑定变量的值来估计基数。在大多数情况下，查询的执行都会返回类似大小的结果，这种方法是有意义的。如果99%的查询使用较小的预算值，那么第一次执行将使用较小的值，因此缓存的查询计划将适用于小的绑定变量值。当然，这也意味着当您指定较大的绑定变量值（或者更糟糕的是，如果您运气好，第一次执行就使用较大的值）时，您将得到不太理想的查询计划。

如果您正在使用11g，Oracle可以使用自适应游标共享。这允许优化器为单个查询维护多个查询计划，并根据绑定变量的值选择适当的计划。随着时间的推移，这可能会变得相当复杂。如果您有一个带有N个绑定变量的查询，优化器必须找出如何将该N维空间分成不同的查询计划，以便为不同的绑定变量值重新优化查询并在何时和是否重用早期计划。大部分工作都是在每晚的维护窗口期间完成的，以避免在生产日中产生这些成本。但这也带来了一些问题，关于DBA想要给数据库多少自由发展计划，以及DBA想要控制计划的程度，以使数据库不会突然开始选择导致某些主要系统在随机的一天变得缓慢的差劲计划。