绑定变量

最新推荐文章于 2024-09-12 15:31:55 发布

wacthamu

最新推荐文章于 2024-09-12 15:31:55 发布

阅读量6.6k

点赞数

分类专栏： sql 文章标签： sql 数据库 object oracle string db2

sql 专栏收录该内容

30 篇文章 0 订阅

订阅专栏

1、认识绑定变量：

　　绑定变量是为了减少解析，比如你有个语句这样的：

　　select aaa,bbb from ccc where ddd=eee;

　　如果经常通过改变eee这个谓词赋值来查询，如下：

　　select aaa,bbb from ccc where ddd=fff;
　　select aaa,bbb from ccc where ddd=ggg;
　　select aaa,bbb from ccc where ddd=hhh;

　　每条语句都要被数据库解析一次，这样比较浪费资源，如果把eee换成绑定变量形式，无论ddd后面是什么值，都不需要重复解析

　　Java实现绑定变量的方法：

PreparedStatement pstmt = con.prepareStatement("UPDATE employees SET salay = ? WHERE id = ?");
pstmt.setBigDecimal(1, 15.00);
pstmt.setInt(2, 110592);
//result statmement: UPDATE employees SET salay = 15.00 WHERE id =110592
pstmt.executeQuery();

　　假设要将id从1到10000的员工的工资都更新为150.00元，不使用绑定变量，则：

sql.executeQuery("UPDATE employees SET salay = 150.00 WHERE id = 1");
sql.executeQuery("UPDATE employees SET salay = 150.00 WHERE id = 2");
sql.executeQuery("UPDATE employees SET salay = 150.00 WHERE id = 3");
sql.executeQuery("UPDATE employees SET salay = 150.00 WHERE id = 4");
....
sql.executeQuery("UPDATE employees SET salay = 150.00 WHERE id = 10000");

　　使用绑定变量，则：

PreparedStatement pstmt;
for (id = 1; id < 10000; id++)
{
   if (null == pstmt)
     pstmt = con.prepareStatement("UPDATE employees SET salay = ? WHERE id = ?");
   pstmt.setBigDecimal(1, 150.00);
   pstmt.setInt(2, id);
   pstmt.executeQuery();
}

　　二者区别在于，不用绑定变量，则相当于反复解析、执行了1w个sql语句。使用绑定变量，解析sql语句只用了一次，之后的9999次复用第一次生成的执行计划。显然，后者效率会更高一些。

　　2、什么时候不应该/不必要使用绑定变量

　　a）如果你用数据仓库，一条大查询一跑几个小时，根本没必要做绑定变量，因为解析的消耗微乎其微。

　　b）变量对优化器产生执行计划有很重要的影响的时候：绑定变量被使用时，查询优化器会忽略其具体值，因此其预估的准确性远不如使用字面量值真实，尤其是在表存在数据倾斜（表上的数据非均匀分布）的列上会提供错误的执行计划。从而使得非高效的执行计划被使用。

　　3、绑定变量在OceanBase中的实现

　　目前OceanBase中实现了绑定变量，目的主要是为了编程方便，而不是为了降低生成执行计划的代价。为什么呢？因为OceanBase中目前使用的是一种”静态执行计划“，无论什么Query，执行流程都一样。OB在前端代理ObConnector中实现绑定变量，将用户传入的变量进行to_string()操作，替代SQL语句中相应的部分，形成一个完整的SQL。然后这个SQL传递给MS，MS按照标准流程来解析和执行。相信不远的将来，OB将会实现真正意义上的绑定变量，让用户享受到绑定变量带来的好处。

###############################################################################################################################

说动态SQL之前先来说下静态SQL

静态SQL语句

语句中主变量的个数与数据类型在预编译时都是确定的，我们称这类嵌入式SQL语句为静态SQL语句。

与之相对应的就是动态SQL

动态SQL方法允许在程序运行过程中临时“组装”SQL语句。

那么他们之间的区别是什么呢？

静态sql的执行计划（DB2称存取路径）是在运行前就确定好的

动态sql的执行计划（DB2称存取路径）是在运行时动态生成的。由于是在运行时动态生成执行计划，因此生成的执行计划（DB2称存取路径）相对更优，但考虑到生成执行计划（DB2称存取路径）的开销，有可能应用程序的运行时间相对会比静态sql长些。

绑定变量

在 SQL 语句中，绑定变量是一个占位符。例如，为了查询员工号为 123 的员工的信息，可以查询：

1 ） select * from emp where empno=123;

另外，也可以查询：

2 ） select * from emp where empno=:empno 。

那么每次查询都是一个新查询，即在数据库共享池中以前没有过的查询。每次查询必须经过分析、限定（名称解析）、安全检查、优化等等，简单地说，执行的每条语句在每次执行时都将必须经过编译。

在第二个查询 2 ）中使用了绑定变量 :empno ，它的值在查询执行时提供。查询经过一次编译后，查询方案将存储在共享池中，可以用来检索和重用。在性能和可伸缩性方面，这两者的差异是巨大的，甚至是惊人的

从上所述，很明显看出，分析一个带有硬编码量的语句将比重用一条已分析过的查询方案花费更长的时间和消耗更多的资源，不明显的是前者将减少系统所能支持的用户数量。很明显，部分原因是由于增加资源消耗量，但更主要的因素是在解析 sql 语句的过程中对共享池中锁存器（ latch ）的争抢

通过使用绑定变量，应用程序提交的相似的 sql 语句只需要解析一次，就可以重复使用，这非常有效，这也是Oracle 数据库要求使用的工作方式。不仅使用较少的资源，而且可以减少锁存（ latch ）时间，降低锁存（ latch ）次数，这将提高应用系统性能，并且大大提高可伸缩性。

在IBATS中，采用的是动态SQL加绑定变量的方式。

IBATS中是根据传递过来的变量是否空来拼接SQL的，这就是动态SQL。

而传递来的参数是根据绑定变量来执行的。

比如下面的例子，我要查询用户信息。

在前台页面输入用户名，zhangsan

传到IBATS后，执行SQL，在数据库查看刚刚执行的SQL

查看方法如下：

Sql代码

select sql_text from v$sql where sql_text like 'select t.username ,t.password, t.sex ,t.mobile from users t%'

注意：在ORACLE中，要查看v$sql 这个视图，得较高的权限，我是用DBA权限进去查看的。

看到这样的语句：

Sql代码

select t.username ,t. password , t.sex ,t.mobile from users t where t.username = :1

如果再查lisi的用户信息，在数据库看执行的SQL。发现还是上面那条SQL。

所以，绑定变量的好处就是每次都执行的同一条SQL，只是输入的变量值不同。

下面直接在数据量中执行下面的两个SQL:

Sql代码

select t.username ,t. password , t.sex ,t.mobile from users t where t.username = 'zhangsan' ;
select t.username ,t. password , t.sex ,t.mobile from users t where t.username = 'lisi' ;

再看数据库中执行的SQL，发现是下面的结果：

可以看到，不使用绑定变量时，是执行的不同的SQL。

总结：ibats采用的是动态SQL+绑定变量的方式。动态SQL在运行时编译，所以执行计划更优，但相对于静态编译的静态SQL或者存储过程更耗性能。

绑定变量可以重复利用相似的SQL，使效率更高。

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

附一段jdbc访问数据库时绑定和不绑定变量的测试例子：

import java.sql.*;

import oracle.jdbc.driver.*;

　　class ConOra {
　　public static void main(String args[] ) throws SQLException{
　　DriverManager.registerDriver(neworacle.jdbc.driver.OracleDriver());
　　Connection conn =DriverManager.getConnection("jdbc:oracle:thin:@192.168.1.152:1521:whx","system","lukewhx");
　　PreparedStatement stmt ;
　　ResultSet rset ;
　　String v_sql;

　　/*不绑定
　　for (int i =1;i<=1000;i++){
　　v_sql="select object_name from objects where object_id="+i;
　　stmt =conn.prepareStatement(v_sql);
　　rset=stmt.executeQuery();
　　stmt.close();
　　}
　　*/

//绑定

　　for (int i =1 ;i<=1000;i++ ) {
　　v_sql = "select object_name from objects where object_id= :x ";
　　stmt=conn.prepareStatement(v_sql);
　　stmt.setString(1,Integer.toString(i));
　　rset = stmt.executeQuery();
　　stmt.close();
　　}

　　System.out.println("Execute OK");
　　}
　　}