为什么SQL 绑定变量对性能很重要

动态 SQL 的一个常见问题是解析生产中的性能。许多开发人员无法访问生产环境，因此他们没有意识到这个问题究竟是什么问题？

执行计划缓存

现在大多数数据库供应商都提供了执行计划缓存（Oracle 将其称为游标缓存），其中存储了以前解析的 SQL 语句，并缓存了它们的执行计划以供重用。这是绑定变量如此重要的主要原因（另一个原因是防止 SQL 注入）。
通过使用绑定变量，我们可以确保数据库能够轻松识别之前执行的相同 SQL 语句，并能够重新执行之前找到的执行计划。如果我们运行以下查询，让我们看看在各种数据库中会发生什么：

-- First, run them with "inline values" or "constant literals"
SELECT first_name, last_name FROM actor WHERE actor_id = 1;
SELECT first_name, last_name FROM actor WHERE actor_id = 2;
 
-- Then, run the same queries again with bind values
SELECT first_name, last_name FROM actor WHERE actor_id = ?;
SELECT first_name, last_name FROM actor WHERE actor_id = ?;

请注意，查询是从 JDBC、jOOQ、Hibernate 还是数据库中的过程语言（例如 PL/SQL、T-SQL、pgplsql）运行并不重要。结果总是一样的。

示例

这里使用 Oracle 运行以下示例。其他数据库方式类似。我们将运行以下脚本，包括上述查询和一个用于获取所有执行计划的查询：

SELECT first_name, last_name FROM actor WHERE actor_id = 1;
SELECT first_name, last_name FROM actor WHERE actor_id = 2;
 
SET SERVEROUTPUT ON
DECLARE
  v_first_name actor.first_name%TYPE;
  v_last_name  actor.last_name%TYPE;
BEGIN
  FOR i IN 1 .. 2 LOOP
    SELECT first_name, last_name 
    INTO v_first_name, v_last_name
    FROM actor 
    WHERE actor_id = i;
     
    dbms_output.put_line(v_first_name || ' ' || v_last_name);
  END LOOP;
END;
/
 
SELECT s.sql_id, p.*
FROM v$sql s, TABLE (
  dbms_xplan.display_cursor (
    s.sql_id, s.child_number, 'ALLSTATS LAST'
  )
) p
WHERE lower(s.sql_text) LIKE '%actor_id = %';
输出是：
SQL_ID 90rk04nhr45yz，子编号 0
-------------------------------------
从 ACTOR 中选择 FIRST_NAME、LAST_NAME，其中 ACTOR_ID = :B1
 
计划哈希值：457831946
 
-------------------------------------------------- --------
| 身份证 | 操作 | 姓名 | 电子行 |
-------------------------------------------------- --------
| 0 | 选择声明 | | |
| 1 | 按索引 ROWID 访问表| 演员 | 1 |
|* 2 | 索引唯一扫描 | PK_ACTOR | 1 |
-------------------------------------------------- --------
 
谓词信息（由操作 id 标识）：
-------------------------------------------------- -
 
   2 - 访问（“ACTOR_ID”=：B1）
 

SQL_ID 283s8m524c9rk，子编号 0
-------------------------------------
SELECT first_name, last_name FROM actor WHERE actor_id = 2
 
计划哈希值：457831946
 
-------------------------------------------------- --------
| 身份证 | 操作 | 姓名 | 电子行 |
-------------------------------------------------- --------
| 0 | 选择声明 | | |
| 1 | 按索引 ROWID 访问表| 演员 | 1 |
|* 2 | 索引唯一扫描 | PK_ACTOR | 1 |
-------------------------------------------------- --------
 
谓词信息（由操作 id 标识）：
-------------------------------------------------- -
 
   2 - 访问（“ACTOR_ID”=2）
 

SQL_ID 3mks715670mqw，子编号 0
-------------------------------------
SELECT first_name, last_name FROM actor WHERE actor_id = 1
 
计划哈希值：457831946
 
-------------------------------------------------- --------
| 身份证 | 操作 | 姓名 | 电子行 |
-------------------------------------------------- --------
| 0 | 选择声明 | | |
| 1 | 按索引 ROWID 访问表| 演员 | 1 |
|* 2 | 索引唯一扫描 | PK_ACTOR | 1 |
-------------------------------------------------- --------
 
谓词信息（由操作 id 标识）：
-------------------------------------------------- -
 
   2 - 访问（“ACTOR_ID”=1）

计划总是相同的，因为我们正在访问主键值，所以我们总是会得到相同的基数，所以每次执行似乎都没有任何问题。但请注意谓词信息略有不同。当查询一个常量值时，谓词将在其中包含该值，而对于绑定变量，我们不知道谓词值是什么，从计划中。这是完全可以预料的，因为我们希望在查询的两次执行中重用该计划。通过另一个查询，我们可以看到每个语句的执行次数：

SELECT sql_id, sql_text, executions
FROM v$sql
WHERE sql_id IN (
  '90rk04nhr45yz', 
  '283s8m524c9rk', 
  '3mks715670mqw'
);
SQL_ID SQL_TEXT 执行
-------------------------------------------------- --------------------------------------
90rk04nhr45yz 选择 FIRST_NAME, LAST_NAME FROM ACTOR WHERE ACTOR_ID = :B1 2
283s8m524c9rk 选择 first_name，last_name FROM actor WHERE actor_id = 2 1
3mks715670mqw SELECT first_name, last_name FROM actor WHERE actor_id = 1 1

在第二种情况下，我们使用了绑定变量（由 PL/SQL 自动生成），我们可以重用语句、缓存其计划并运行两次。

所以有什么关系？

原因有两个：

个人处决的表现
整个系统的性能

这如何影响单个执行似乎很明显，当能够缓存某些内容时，与不必执行结果被缓存的工作相比，缓存维护会产生轻微的开销。这里讨论的工作是解析 SQL 语句并为其创建执行计划。即使计划是微不足道的，如上面的例子，计算这个计划也有开销。这种开销最好在基准测试中显示：

SET SERVEROUTPUT ON
 
-- Don't run these on production
-- But on your development environment, this guarantees clean caches
ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
 
CREATE TABLE results (
  run     NUMBER(2),
  stmt    NUMBER(2),
  elapsed NUMBER
);
 
DECLARE
  v_ts TIMESTAMP WITH TIME ZONE;
  v_repeat CONSTANT NUMBER := 2000;
  v_first_name actor.first_name%TYPE;
  v_last_name  actor.last_name%TYPE;
BEGIN
 
  -- Repeat whole benchmark several times to avoid warmup penalty
  FOR r IN 1..5 LOOP
    v_ts := SYSTIMESTAMP;
       
    FOR i IN 1..v_repeat LOOP
      BEGIN
        EXECUTE IMMEDIATE '
          SELECT first_name, last_name 
          FROM actor 
          WHERE actor_id = ' || i 
          -- Just fixing a syntax highlighting bug of this blog '
        INTO v_first_name, v_last_name;
      EXCEPTION
        -- Please forgive me
        WHEN OTHERS THEN NULL;
      END;
    END LOOP;
   
    INSERT INTO results VALUES (
      r, 1, SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);
    v_ts := SYSTIMESTAMP;
       
    FOR i IN 1..v_repeat LOOP
      BEGIN
        EXECUTE IMMEDIATE '
          SELECT first_name, last_name 
          FROM actor 
          WHERE actor_id = :i'
        INTO v_first_name, v_last_name
        USING i;
      EXCEPTION
        -- Please forgive me
        WHEN OTHERS THEN NULL;
      END;
    END LOOP;
       
    INSERT INTO results VALUES (
      r, 2, SYSDATE + ((SYSTIMESTAMP - v_ts) * 86400) - SYSDATE);
  END LOOP;
   
  FOR rec IN (
    SELECT
      run, stmt, 
      CAST(elapsed / MIN(elapsed) OVER() AS NUMBER(10, 5)) ratio 
    FROM results
  )
  LOOP
    dbms_output.put_line('Run ' || rec.run || 
      ', Statement ' || rec.stmt || 
      ' : ' || rec.ratio);
  END LOOP;
END;
/
 
DROP TABLE results;

与往常一样，在 jOOQ 博客上，我们不会发布实际执行时间以符合基准发布的许可限制，因此我们仅将每次执行与最快执行进行比较。这是上面的结果：

运行 1，语句 1：83.39893
运行 1，语句 2：1.1685
运行 2，语句 1：3.02697
运行 2，语句 2：1
运行 3，语句 1：2.72028
运行 3，语句 2：1.03996
运行 4，语句 1：2.70929
运行 4，语句 2：1.00866
运行 5，语句 1：2.71895
运行 5，语句 2：1.02198

我们可以看到，使用绑定变量的 SQL 版本的速度是不使用绑定变量的 SQL 版本的 2.5 倍。这种开销对于琐碎的查询非常重要——对于更复杂的查询，执行本身与解析相比需要更多时间，它可能会少一些。但很明显，开销是我们不想付出的代价。我们希望缓存查询及其计划！还要注意第一次执行基准测试有很大的开销，因为所有 2000 个查询都是第一次遇到，然后才被缓存以供第二次运行。不过，这是我们只在第一次运行时付出的代价。这如何影响您的整个系统不仅单个查询执行会受到影响，整个系统也会受到影响。在运行了几次基准测试后，这些是我从 Oracle 游标缓存中获得的执行统计信息：

SELECT
  count(*), 
  avg(executions), 
  min(executions), 
  max(executions)
FROM v$sql
WHERE lower(sql_text) LIKE '%actor_id = %'
AND sql_text NOT LIKE '%v$sql%';

产量：

计数平均值最小值最大值
2001 9.9950 5 10000

目前，我的缓存中有 2000 个查询。已执行 10000 次（基准重复 5 次，每次运行执行 2000 次查询）的查询，以及已执行 5 次的 2000 次查询（基准重复 5 次）。相反，如果我们运行查询 20000 次（记住，查询运行对应于被过滤的ACTOR_ID），那么结果将大不相同！

运行 1，语句 1：86.85862
运行 1，语句 2：1.13546
运行 2，语句 1：78.39842
运行 2，语句 2：1.01298
运行 3，语句 1：72.45254
运行 3，语句 2：1
运行 4，语句 1：73.78357
运行 4，语句 2：2.24365
运行 5，语句 1：84.89842
运行 5，语句 2：1.143

为什么会这样？让我们再次检查游标缓存统计信息：

SELECT
  count(*), 
  avg(executions), 
  min(executions), 
  max(executions)
FROM v$sql
WHERE lower(sql_text) LIKE '%actor_id = %'
AND sql_text NOT LIKE '%v$sql%';

产量：

计数平均值最小值最大值
15738 3.4144 1 20000

这是一个截然不同的结果。我们在游标缓存中没有所有20000 个查询，只有其中一些。这意味着某些语句已从缓存中清除，以便为新语句腾出空间（这对于任何缓存都是合理的行为）。但是清除它们也是有问题的，因为基准的设计方式，它们将在第二次、第三次、第四次和第五次运行中再次出现，所以我们应该将它们保存在缓存中。而且由于我们执行每个查询的次数相同，因此实际上没有任何方法可以确定要清除的“更合理”（即罕见的）查询。

系统中的资源总是有限的，游标缓存大小也是如此。我们在系统中运行的不同查询越多，它们从游标缓存中获得的收益就越少。对于很少运行的查询（包括报告、分析或仅由少数用户运行的某些特殊查询）来说，这不是问题。但是一直在运行的查询应该总是被缓存。

我不能足够强调这可能有多严重：在上述情况下，游标缓存中本应是单个查询的内容激增为20000个查询，从缓存中排除了许多更有用的查询。这不仅会减慢特定查询的执行速度，还会从缓存中清除大量完全不相关的查询，从而以类似的因素减慢整个系统的速度。如果每个人的速度都急剧下降，那么每个人都将开始排队等待解析他们的 SQL 查询，并且您可能会因为这个问题（在最坏的情况下）导致整个服务器瘫痪！

解决方法

一些数据库支持强制解析常量文字以绑定变量。在 Oracle 中，您可以指定CURSOR_SHARING = FORCE为“快速修复”。在 SQL Server 中，它被称为forced parametrization. 但是这种方法有其自身的局限性和开销，因为这意味着每次都需要执行额外的解析工作来识别常量文字并用绑定变量替换它们。然后，此开销将应用于所有查询！

结论

绑定变量对于SQL性能非常重要。经过大量培训人们出于SQL 注入原因使用它们（这已经是一件好事），我们现在看到了它们对于性能的重要性。不对ID、时间戳、名称或任何均匀分布且列中有许多值的值使用绑定变量会产生上述问题。例外情况是只有很少不同值的列的绑定变量（如真/假标志，编码给定状态的代码等），在这种情况下，常量文字可能是一个合理的选择（后续博客文章即将发布，很快）。

但是绑定变量应该始终是您的默认选择。如果您使用的是客户端工具，例如jOOQ或Hibernate，绑定变量往往是默认值，你很好。如果您使用的是存储过程语言，如 PL/SQL 或 T-SQL，绑定变量会自动生成，您也可以。但是，如果您使用 JDBC 或任何 JDBC 包装器（如 Spring 的 JdbcTemplates）或任何其他基于字符串的 API（如 JPA 的本机查询 API），那么您又要靠自己了，并且每次使用绑定变量时都必须明确注意可变输入。

为什么SQL 绑定变量对性能很重要转载

执行计划缓存

示例

所以有什么关系？

解决方法

结论

推荐阅读

专题推荐

本月精选性能专题

本月精选线上案例

本月精选原创好文

联系我们

网媒渠道

友情链接