【性能】JDBC PreparedStatement和连接池PreparedStatement Cache学习记录

之前看JDBC规范的时候对PreparedStatement只是简单的知道会进行sql预编译，能提高性能。具体原理也没怎么理解。

最近在性能测试遇到一个连接池的调优刚好是和PreparedStatement和PreparedStatementCache相关的。固重新系统的看了点资料学习了点，简单记录一下。

 

1.java.sql.PreparedStatement

首先看wiki对使用PS的解释：

The typical workflow of using a prepared statement is as follows:
    Prepare: The statement template is created by the application and sent to the database management system (DMBS). Certain values are left unspecified, called parameters, placeholders or bind variables (labelled "?" below):
        INSERT INTO PRODUCT (name, price) VALUES (?, ?)
    The DBMS parses, compiles, and performs query optimization on the statement template, and stores the result without executing it.
Execute: At a later time, the application supplies (or binds) values for the parameters, and the DBMS executes the statement (possibly returning a result). The application may execute the statement as many times as it wants with different values. In this example, it might supply 'Bread' for the first parameter and '1.00' for the second parameter.

 简单翻译

一个 PreparedStatement 的执行过程：

1.        prepare ：语句模板被创建，并发送给 DBMS ，具体参数没有指定，知识用占位符替代。(会有一次连接开销，等会介绍PreparedStatemetCache会优化这个开销。 )

2.        编译：数据库收到语句后会预先编译和优化产生执行计划。

3. 执行：客户端传进来绑定参数，数据库根据动态参数拼装并执行产生结果返回给客户端

 

 

再看JDBC java.sql.Connection类的prepareStatement 方法注释：

 

 

public PreparedStatement prepareStatement(String sql)
                                   throws SQLException

    Creates a PreparedStatement object for sending parameterized SQL statements to the database.

    A SQL statement with or without IN parameters can be pre-compiled and stored in a PreparedStatement object. This object can then be used to efficiently execute this statement multiple times.

    Note: This method is optimized for handling parametric SQL statements that benefit from precompilation. If the driver supports precompilation, the method prepareStatement will send the statement to the database for precompilation. Some drivers may not support precompilation. In this case, the statement may not be sent to the database until the PreparedStatement object is executed. This has no direct effect on users; however, it does affect which methods throw certain SQLException objects.

 

 

基本意识就是：

创建一个 PreparedStatement object 给数据库发送参数化的 sql 语句。一个没有参数的语句可以被预先编译并存储在 PreparedStatement 语句里面。这个对象可以用来高效的多次执行语句。

 

以前理解预编译和这个不太一致，罪过罪过。

 

 

2. orachle 的PreparedStatement如何发生效果

具体可以见附件   oracle preparedStatements.ppt   里面有很详细的描述。

Oracle 的 SQL 执行过程可以简单描述如下：

 

 

 

 

 

预编译后的语句的执行计划存在于DBMS的shared pool中，可以省去前两个阶段的操作。直接进入第三阶段。
也有很多DBMS是不支持的。好像mysql就不支持，这样api上虽然看似使用PreparedStatement，实际上驱动实现并没有真正实现这个功能，而是生成statement。

 

3.连接池的PreparedStatement cache
这个概念也是之前我所不了解的。
PreparedStatement是JDBC里面提供的对象，很多连接池都引入了PreparedStatementCache的概念。如Jboss连接池、C3P0,DBCP等。PreparedStatementCache即用于保存与数据库交互的prepareStatement对象。在cache里的ps对象，不需要重新走一次DBMS连接请求去创建。
如Jboss连接池的PreparedStatementCache实现，支付宝DBA的一个总结：JBOSS连接池1-PreparedStatementCache参数的作用及原理
PreparedStatementCache是跟着connection走的。一个connection就会有一个cache。比如一个cache允许缓存20条语句，20个connection就可能缓存400个。

一般连接池可以支持这个的配置。由于会占用较大内存，所以一般配置的时候要特别注意。DBCP里对应的是poolPreparedStatements和maxOpenPreparedStatements两个参数。
具体可以参考：dbcp configuration
这个对性能的优化还是很有价值的。尤其对于应用内sql比较固定的场景，会有很大的性能提升。之前在我们项目里，一个压力场景没有设置这两个参数之前的tps大约是1700，设置之后的tps大约是3100.当然也有一定的内存开销需要特别注意。

 

 

 

 

 

 

 

 

评论

3 楼 单眼皮大娘 2015-09-29  

楼主，真是不错的文章 解答了我的疑惑。

2 楼 singleant 2013-01-11  

hot66hot 写道

引用

也有很多DBMS是不支持的。好像mysql就不支持，这样api上虽然看似使用PreparedStatement，实际上驱动实现并没有真正实现这个功能，而是生成statement。

mysql5.0支持PreparedStatement.
    MySQL 5.0 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol implemented in MySQL 4.1, provided that you use an appropriate client programming interface.
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

谢谢指出，老版本不支持

1 楼 hot66hot 2013-01-09  

引用

也有很多DBMS是不支持的。好像mysql就不支持，这样api上虽然看似使用PreparedStatement，实际上驱动实现并没有真正实现这个功能，而是生成statement。

mysql5.0支持PreparedStatement.
    MySQL 5.0 provides support for server-side prepared statements. This support takes advantage of the efficient client/server binary protocol implemented in MySQL 4.1, provided that you use an appropriate client programming interface.
http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html