jdbc测试mysql数据库sql预解析(绑定变量)

jdbc测试mysql数据库sql预解析(绑定变量) 2012-02-17 00:07

 

url: http://blog.youkuaiyun.com/yzsind/article/details/7266281

 

      用习惯了oracle，学习mysql，想测试一下mysql绑定变量的效果。以前看网上介绍大部份都说mysql没有sql共享池的概念，所以也不存在sql预解析或绑定变量的说法。
        今天测试了一下（通过网络抓包、查看服务器端sql日志及分析源码等方法），发现mysql还是有sql预解析的实现。
        服务器端是mysql 5.1.58(win32)，用jdbc(5.1.18)做客户端，默认的连接方式是不会有sql预解析效果，即使我们用PreparedStatement对象也差不多，它只是把SQL和变量拼接成一个完整的SQL发送给服务器，如下代码：

[java] view plain copy print ?

1. PreparedStatement pstmt = conn.prepareStatement("select * from t1 where c1=?");  
2. pstmt.setString(1, "abc");  
3. pstmt.execute();  

实际上不会有预解析的过程，而是经过简单的拼接，把如下SQL发送给服务器

[sql] view plain copy print ?

1. select * from t1 where c1='abc'  
              要实现预解析的效果，我们必须设置jdbc Connection的参数useServerPrepStmts=true，再使用PreparedStatement后就OK了，创建PreparedStatement时客户端先把"select * from t1 where c1=?"发送到服务器端预解析，execute时只是把变量传送到服务器执行。
   
   mysql服务器的sql语句缓存可以通过状态变量Prepared_stmt_count查看
   

[sql] view plain copy print ?

1. mysql> show status  like 'Prepared_stmt_count';  
2. +---------------------+-------+   
3. | Variable_name       | Value |  
4. +---------------------+-------+   
5. | Prepared_stmt_count | 1     |  
6. +---------------------+-------+   
7. 1 row in set  

        不过mysql的sql语句缓存与oracle有很大不同，它是会话语句级的，不是全局共享，当会话断开或PreparedStatement.close后这个缓存就没有了。我们需要设置Connection的参数cachePrepStmts=true把PreparedStatement缓存起来，prepStmtCacheSize=xxx来设置每个会话缓存语句的最大数量（很多连接池也有类似的功能）。

        OK，已经知道如何启用预解析了，想看看启用与不启用预解析性能有多少差别，会不会也像oracle那么明显呢？经过简单的测试，发现当没有PreparedStatement缓存(cachePrepStmts=false)时，打开预解析性能下降很多， 当有PreparedStatement缓存(cachePrepStmts=true)时，两者性能基本一样。这个结果让人很失望，个人分析有几个原因：
        启用预解析但没有PreparedStatement缓存时，每次创建PreparedStatement都需要解析一次，execute时又需要交互一次，而预解析的SQL在PreparedStatement.close又不能重用，所以性能反而更差。

        当有PreparedStatement缓存时，预解析的SQL文本缓存在服务器端，但是并不会像oracle一样缓存执行计划，所以每次execute时都需要解析SQL和生成执行计划，因此只是减少了每次execute传输SQL的文本大小，性能差别不大。

注：如果SQL语法错误，那么服务器端预解析会出错，但jdbc收到预解析出错的信息后并不提示出错，而是将取消本条语句预解析的状态，execute时直接把SQL接装发送给服务器，mysql jdbc在PreparedStatement构造函数中代码如下，其中返回ServerPreparedStatement类表示使用了绑定变量，返回PreparedStatement表示未使用绑定变量：

[java] view plain copy print ?

1. try {  
2.     pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql,  
3.             this.database, resultSetType, resultSetConcurrency);  
4.       
5.     pStmt.setResultSetType(resultSetType);  
6.     pStmt.setResultSetConcurrency(resultSetConcurrency);  
7. } catch (SQLException sqlEx) {  
8.     // Punt, if necessary   
9.     if (getEmulateUnsupportedPstmts()) {  
10.         pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);  
11.     } else {  
12.         throw sqlEx;  
13.     }  
14. }  

        经过上面分析，个人认为不需要打开SQL预解析的效果，PreparedStatement对象还是尽量使用，因为虽然不能提升性能，但可以避免SQL注入安全问题 。

2012-02-17