mysql sql使用绑定变量 sql绑定变量的优点

转载

梦想启航吧 2023-07-30 20:51:06

文章标签 mysql sql使用绑定变量 SQL 绑定变量 sql 文章分类 MySQL 数据库

绑定变量的优点是可以在library cache中共享游标，可以避免硬解析以及相关的额外开销。 1、使用绑定变量，共享同一个游标。 SQL variable n number; SQL exec :n := 1; PL/SQL procedure successfully completed. SQL select name from zhhtest where id= :n

绑定变量的优点是可以在library cache中共享游标，可以避免硬解析以及相关的额外开销。

1、使用绑定变量，共享同一个游标。

SQL> variable n number;

SQL> exec :n := 1;
PL/SQL procedure successfully completed.
SQL> select name from zhhtest where id= :n;

NAME

----------

zheng
SQL> exec :n := 2;

PL/SQL procedure successfully completed.
SQL> select name from zhhtest where id= :n;

NAME

----------

zheng
共享同一个子游标：

SQL> select sql_id,child_number,executions

  2  from v$sql

  3  where sql_text = 'select name from zhhtest where id= :n'

  4  ;
SQL_ID        CHILD_NUMBER EXECUTIONS

------------- ------------ ----------

6wd28h73xfym8            0          2
2、绑定变量分级 bind  graduation

SQL> variable c varchar2(32);

SQL> exec :c := 'zheng';
PL/SQL procedure successfully completed.
SQL> select id from zhhtest where name= :c;

SQL> variable c varchar2(33);

SQL> exec :c := 'zheng';
PL/SQL procedure successfully completed.
SQL> select id from zhhtest where name= :c;

SQL> variable c varchar2(129);

SQL> exec :c := 'zheng';
PL/SQL procedure successfully completed.
SQL> select id from zhhtest where name= :c;

SQL> variable c varchar2(2001);

SQL> exec :c := 'zheng';
PL/SQL procedure successfully completed.
SQL> select id from zhhtest where name= :c;

同一个父游标，4个子游标：

SQL> select sql_id,child_number,executions

  2  from v$sql

  3  where sql_text = 'select id from zhhtest where name= :c';
SQL_ID        CHILD_NUMBER EXECUTIONS

------------- ------------ ----------

1kgjgqq2grb79            0          1

1kgjgqq2grb79            1          1

1kgjgqq2grb79            2          1

1kgjgqq2grb79            3          1
我们查询v$sql_shared_cursor视图，看看是什么原因不能共享子游标：

SQL> select child_number,bind_mismatch from v$sql_shared_cursor where sql_id='1kgjgqq2grb79';
CHILD_NUMBER BIND_MISMATCH

------------ ---------------

           0 N

           1 Y

           2 Y

           3 Y

可以看到是因为绑定变量不匹配导致不能共享子游标。
这是由于绑定变量分级（bind graduation）。这个功能的目的是为了最小化子游标的数量，它是根据绑定变量的长短将绑定变量（各个大小不同）分为四个级别。在32个字节以内被分在第一个级别，33到128个字节的被分在第二个级别，129到2000个字节的被分在第三个级别，其余的大于2000个字节的被分在第四个级别。 NUMBER类型的绑定变量被分在它的最大长度22个字节上的级别上。

视图v$sql_bind_metadata显示了级别的最大长度。注意，即使在子游标1的变量长度只有33的时候，也是使用最大长度为128的级别。

SQL>select s.child_number, m.position, m.max_length,

  2         decode(m.datatype,1,'varchar2',2,'number',m.datatype) as datatype

  3  from v$sql s, v$sql_bind_metadata m

  4  where s.sql_id = '1kgjgqq2grb79'

  5  and s.child_address = m.address

  6* order by 1, 2
CHILD_NUMBER   POSITION MAX_LENGTH DATATYPE

------------ ---------- ---------- ----------------------------------------

           0          1         32 varchar2

           1          1        128 varchar2

           2          1       2000 varchar2

           3          1       4000 varchar2

每次创建子游标的时候，都会创建一个新的执行计划；新的执行计划是否与另一个子游标使用的执行计划一致，也依赖于绑定变量的值。

3、绑定变量窥视（bind variable peeking）

在 WHERE子句中使用绑定变量的缺点是会有一些至关重要的信息对查询优化器不可见。事实上，对查询优化器来讲，使用直接文本要比使用绑定变量来的更好。使用直接文本可以提高成本估算的准确性。当检查一个值是否在可用数值范围以外（小于存储在这个字段的最小值，或者大于最大值）或者是否利用到直方图（histogram）时，就更是这样了。

SQL> create table t (id number(4));

Table created.

SQL> begin

  2  for i in 1 .. 1000 loop

  3  insert into t values(i);

  4  end loop;

  5  commit;

  6  end;

  7  /
PL/SQL procedure successfully completed.
SQL> alter table t add constraint pk_t primary key (id);

Table altered.

如果查询id小于900的所有记录，查询优化器知道（根据对象的统计信息或者动态抽样）有差不多90％的记录被筛选。因此，它会选择使用基于全表扫描的执行计划。同时请注意，估算出的基数（执行计划中Rows字段的信息）与查询语句实际返回的记录数是否相符。

以下测试基于10.2.0.4版本，并非全表扫描，而是INDEX FAST FULL SCAN。

SQL> set autot on explain

SQL> select count(1) from t where id<900;
  COUNT(1)

----------

       899
Execution Plan

----------------------------------------------------------

Plan hash value: 949213647
------------------------------------------------------------------------------

| Id  | Operation             | Name | Rows  | Bytes | Cost (%CPU)| Time     |

------------------------------------------------------------------------------

|   0 | SELECT STATEMENT      |      |     1 |    13 |     3   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE       |      |     1 |    13 |            |          |

|*  2 |   INDEX FAST FULL SCAN| PK_T |   899 | 11687 |     3   (0)| 00:00:01 |

------------------------------------------------------------------------------
当另外一个查询id小于10的所有记录时，查询优化器知道只有大约1％的记录会被选中。因此，它会选择使用基于索引扫描的执行计划。在这个例子中，也请注意估算的准确与否。

SQL> select count(1) from t where id<10;
  COUNT(1)

----------

         9
Execution Plan

----------------------------------------------------------

Plan hash value: 394162607
--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |    13 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |      |     1 |    13 |            |          |

|*  2 |   INDEX RANGE SCAN| PK_T |     9 |   117 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------

只要是使用到绑定变量，查询优化器都会忽略它们的具体值。从而，前面例子中的准确的估算就不太可能会出现。为了解决这个问题，Oracle9i中引入了一个被称为绑定变量窥视（bind variable peeking）的功能。
绑定变量窥视的概念是比较简单的。在物理优化阶段，查询优化器会窥视绑定变量的值，将它作为文本来使用。这种方法的问题是它生成的执行计划会依赖第一次生成执行计划时所提供的值。

SQL> variable id number;

SQL> exec :id := 900
PL/SQL procedure successfully completed.
SQL> select count(1) from t where id< :id;

  COUNT(1)

----------

       899
Execution Plan

----------------------------------------------------------

Plan hash value: 394162607
--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     3 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |

|*  2 |   INDEX RANGE SCAN| PK_T |    50 |   150 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------
SQL> exec :id := 10

PL/SQL procedure successfully completed.
SQL> select count(1) from t where id< :id;

  COUNT(1)

----------

         9
Execution Plan

----------------------------------------------------------

Plan hash value: 394162607
--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     3 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |

|*  2 |   INDEX RANGE SCAN| PK_T |    50 |   150 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------
清空share pool，更换一下变量值的顺序：

SQL> conn / as sysdba

Connected.

SQL> alter system flush shared_pool;
System altered.

SQL> set autot on exp

SQL> variable id number;

SQL> exec :id := 10;
PL/SQL procedure successfully completed.
SQL> select count(1) from t where id< :id;

  COUNT(1)

----------

         9
Execution Plan

----------------------------------------------------------

Plan hash value: 394162607
--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     3 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |

|*  2 |   INDEX RANGE SCAN| PK_T |    50 |   150 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------
SQL> exec :id := 900;

PL/SQL procedure successfully completed.
SQL> select count(1) from t where id< :id;

  COUNT(1)

----------

       899
Execution Plan

----------------------------------------------------------

Plan hash value: 394162607
--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |     1 |     3 |     2   (0)| 00:00:01 |

|   1 |  SORT AGGREGATE   |      |     1 |     3 |            |          |

|*  2 |   INDEX RANGE SCAN| PK_T |    50 |   150 |     2   (0)| 00:00:01 |

--------------------------------------------------------------------------
上述10g测试，可以看出，使用绑定变量的执行计划，并没有使用直接文本的执行计划准确。

只要游标还保存在library cache中并且可以被共享，就可以被重用。不管与它相关的执行计划的效率如何，这种事情都会发生。

SQL> select prev_sql_id from v$session where sid=(select sid from v$mystat where rownum=1);
PREV_SQL_ID

-------------

a3bh2rczz6xqs
SQL> select sql_id,child_number,sql_text,optimizer_mode,plan_hash_value,executions from v$sql where sql_id='a3bh2rczz6xqs';

SQL_ID        CHILD_NUMBER SQL_TEXT                                           OPTIMIZER_ PLAN_HASH_VALUE EXECUTIONS

------------- ------------ -------------------------------------------------- ---------- --------------- ----------

a3bh2rczz6xqs            0 select count(1) from t where id< :id               ALL_ROWS         394162607          2

为了解决这个问题，Oracle11g中引入了一个称为扩展的游标共享（extended cursor sharing，也称为适应性游标共享，adaptive cursor sharing）的新功能。它的目的是在重用一个已经存在的但是会导致执行效率低下的游标时能够自动进行识别。11g中对v$sql视图进行了扩展，而且新增了v$sql_cs_statistics、v$sql_cs_selectivity和v$sql_cs_histogram三个视图，可用来进一步分析生成多个游标的原因。

4、最佳实践
使用任何特性都需要权衡利弊得失。有些情况下，这是比较容易决定的。例如，在不涉及到Where从句（如普通的插入语句）的时候，就没理由不使用绑定变量。另一方面，在柱状图信息对查询优化器有很大影响的情况下，最好不要使用绑定变量。否则，可能会在进行绑定变量窥视的时候遇到较大负面风险。不过，还有以下两个关键案例可供参考：

(1)SQL语句处理少量数据：每逢被处理的数据量很少的时候，解析时间有可能会接近甚至高于执行时间。在这种情况下，使用绑定变量就经常是一种较优选择。特别是在SQL语句将会频繁执行的情况下。数据资料系统（data entry system，常常也称之为OLTP系统）是典型的使用这种SQL语句的系统。
(2)SQL 语句处理大量数据：在大量数据被处理的情况下，解析时间常常比执行时间要少好几个数量级。在这种情况下，使用绑定变量对于总的响应时间（response time）几乎没有影响，但是它也会提高查询优化器生成低效的执行计划的风险。因此不要使用绑定变量。批量任务处理（batch job）、报表生成或者运用OLAP工具的数据仓库（data warehouse）环境是使用这种SQL的典型场景。

本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。