一、背景
现在我们淘宝持久化大多数是采用iBatis+MySQL做开发的，大家都知道，iBatis内置参数，形如#xxx#的，均采用了sql预编译的形式，举例如下：
<span style="font-size:18px;"><select id=”queryUserById” returnType=”userResult”>
SELECT * FROM user WHERE id =#id#
</select> </span>看过iBatis源码发现底层使用的就是JDBC的PreparedStatement，过程是先将带有占位符（即”?”）的sql模板发送至mysql服务器，由服务器对此无参数的sql进行编译后，将编译结果缓存，然后直接执行带有真实参数的sql。查询了相关文档及资料后, 基本结论都是，使用预编译，可以提高sql的执行效率，并且有效地防止了sql注入。但是一直没有亲自去测试下，趁着最近看MySQL_JDBC的源码的契机，好好研究测试了下。测试结果出乎意料，发现原来一直以来我对PreparedStatement的理解是有误的。我们平时使用的不管是JDBC还是ORM框架iBatis默认都没有真正开启预编译，形如PreparedStatement( SELECT * FROMuser WHERE id = ? )，每次都是驱动拼好完整带参数的SQL( SELECT * FROM user WHERE id = 5 )，然后再发送给MySQL服务端，压根就没用到如PreparedStatement名字的功能。咨询了淘宝相关DBA 和相关TDDL同学，确认了现在我们线上使用的TDDL（JDBC）默认都是没有打开预编译的，但是经过测试确实预编译会快一点，DBA那边之后会详细测试并推广到线上。
接下来我会把探究过程跟大家分享并记录下。
二、问题
我的疑问有两点：1.MySQL是否默认开启了预编译功能？若没有，将如何开启？ 2.预编译是否能有效提升执行SQL的性能？
三、探究一
MySQL是否默认开启了预编译？
首先针对第一个问题。我的电脑上已经安装了MySQL，版本是5.1.9，打开配置文件my.ini，在"port=3306" 这一行下面加了配置：log=d:/logs/mysql_log.txt，这样就开启了MySQL日志功能，该日志主要记录MySQL执行sql的过程。重启MySQL，并建立一个库studb，在该库下建一个叫user的表，有id(主键)和username和password三个字段。
接着，我建立了一个简单的Java工程，引入JDBC驱动包mysql-connector-java-5.0.3-bin.jar。然后写了如下的代码：
接着，我建立了一个简单的Java工程，引入JDBC驱动包mysql-connector-java-5.0.3-bin.jar。然后写了如下的代码：
<span style="font-size:18px;">public static void main(String[] args) throws Exception{
String sql = "select * from userwhere id= ?";
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
try{
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?user=root&password=root");
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1,5);
ResultSet rs = stmt.executeQuery();
rs.close();
stmt.close();
}catch(Exception e){
e.printStackTrace();
}finally{
conn.close();
}
} </span><span style="font-size:18px;"> Query SET NAMES utf8
Query SET character_set_results = NULL
Query SHOW VARIABLES
Query SHOW WARNINGS
Query SHOW COLLATION
Query SET autocommit=1
Prepare select *from user where id = ?
Execute select * from user where id= 5
Close stmt
Quit </span><span style="font-size:18px;">DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root&useServerPrepStmts=false")</span>
<span style="font-size:18px;">Query SET NAMES utf8
Query SET character_set_results = NULL
Query SHOW VARIABLES
Query SHOW WARNINGS
Query SHOW COLLATION
Query SET autocommit=1
Query select * from user where id= 5
Quit </span>
果然，日志没有了prepare这一行，说明MySQL没有进行预编译。这意味着useServerPrepStmts这个参数是起效的，且默认值为true。
最后意识到useServerPrepStmts这个参数是JDBC的连接参数，这说明此问题与JDBC驱动程序可能有关系。打开MySQL官网，发现在线的官方文档很强大，支持全文检索，于是我将“useServerPrepStmts”做为关键字，搜索出了一些信息，原文如下：
Important change: Due to a number ofissues with the use of server-side prepared statements, Connector/J5.0.5
has disabled their use by default. The disabling of server-side prepared statements does not affect the operation of the connector in any way.To enable server-side prepared statements, add the following configuration property to your connectorstring:
useServerPrepStmts=true
The default value of thisproperty is false (that is,Connector/J does not use server-side prepared statements)
这段文字说，Connector/J在5.0.5以后的版本，默认useServerPrepStmts参数为false，Connector/J就是我们熟知的JDBC驱动程序。看来，如果我们的驱动程序为5.0.5或之后的版本，想启用mysql预编译，就必须设置useServerPrepStmts=true。我的JDBC驱动用的是5.0.3，这个版本的useServerPrepStmts参数默认值是true。于是我将Java工程中的JDBC驱动程序替换为5.0.8的版本，去掉代码里JDBC连接中的useServerPrepStmts参数，再执行，发现mysql_log.txt的日志打印如下：
<span style="font-size:18px;"> Query SHOW SESSIONVARIABLES
Query SHOW WARNINGS
Query SHOW COLLATION
Query SET NAMES utf8
Query SET character_set_results = NULL
Query SET autocommit=1
Query select * from user where id= 5
Quit</span>果然，在mysql_log.txt日志里，prepare关键字没有了,说明 useServerPrepStmts 参数确实跟JDBC驱动版本有关。另外还查阅了相关MySQL的官方文档后，发现MySQL服务端是在4.1版本才开始支持预编译的，之后的版本都默认支持预编译。
第一个问题解决了，结论就是：要打开预编译功能跟MySQL版本及 MySQL Connector/J（JDBC驱动）版本都有关，首先MySQL服务端是在4.1版本之后才开始支持预编译的，之后的版本都默认支持预编译，并且预编译还与 MySQL Connector/J（JDBC驱动）的版本有关， Connector/J 5.0.5之前的版本默认支持预编译， Connector/J 5.0.5之后的版本默认不支持预编译， 所以我们用的Connector/J 5.0.5驱动以后版本的话默认都是没有打开预编译的 （如果需要打开预编译，需要配置 useServerPrepStmts 参数）
预编译是否能有效提升执行SQL的性能？
首先，我们要明白MySQL执行一个sql语句的过程。查了一些资料后，我得知，mysql执行脚本的大致过程如下：prepare（准备）-> optimize（优化）-> exec（物理执行），其中，prepare也就是我们所说的编译。前面已经说过，对于同一个sql模板，如果能将prepare的结果缓存，以后如果再执行相同模板而参数不同的sql，就可以节省掉prepare（准备）的环节，从而节省sql执行的成本。明白这一点后，我写了如下测试程序：
<span style="font-size:18px;">public static void main(String []a) throws Exception{
String sql = "select * from user whereid = ?";
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
try{
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?user=root&password=root&useServerPrepStmts=true");
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1,5);
ResultSet rs1 = stmt.executeQuery(); //第一次执行
s1.close();
stmt.setString(1,9);
ResultSet rs2 = stmt.executeQuery(); //第二次执行
rs2.close();
stmt.close();
}catch(Exception e){
e.printStackTrace();
}finally{
conn.close();
}
} </span><span style="font-size:18px;">Query SHOW SESSION VARIABLES
Query SHOW WARNINGS
Query SHOW COLLATION
Query SET NAMES utf8
Query SET character_set_results = NULL
Query SET autocommit=1
Prepare select * from userwhere id = ?
Execute select * from user where id = 5
Execute select * from user where id = 9
Close stmt
Quit</span><span style="font-size:18px;">public static void main(String []a) throws Exception{
String sql = "select * from userwhere id = ?";
Class.forName("com.mysql.jdbc.Driver");
Connection conn = null;
try{
conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/studb?user=root&password=root&useServerPrepStmts=true");
PreparedStatement stmt = conn.prepareStatement(sql);
stmt.setString(1,5);
ResultSet rs1 = stmt.executeQuery(); //第一次执行
rs1.close();
stmt.close();
stmt = conn.prepareStatement(sql); //重新获取一个statement
stmt.setString(1,9);
ResultSet rs2 = stmt.executeQuery(); //第二次执行
rs2.close();
stmt.close();
}catch(Exception e){
e.printStackTrace();
}finally{
conn.close();
}
} </span><span style="font-size:18px;">Query SHOW SESSION VARIABLES
Query SHOW WARNINGS
Query SHOW COLLATION
Query SET NAMES utf8
Query SET character_set_results = NULL
Query SET autocommit=1
Prepare select * from user where id=?
Execute select * from user where id= 5
Close stmt
Prepare select *from user where id = ?
Execute select * from user where id = 9
Close stmt
Quit </span>搜索一些资料后得知，JDBC连接参数中有另外一个重要的参数：cachePrepStmts ，设置为true后可以缓存预编译结果。于是我将测试代码中JDBC连接串改为了这样：
<span style="font-size:18px;">conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root&useServerPrepStmts=true&cachePrepStmts=true");</span><span style="font-size:18px;">Prepare select * from user where id = ?
Execute select * from user where id = 5
Execute select * from user where id = 9</span>OK，现在我们才正式开启了预编译，并开启了缓存预编译的功能。那么接下来我们对预编译语句("select * from userwhere id = ?")进行性能测试，测试数据如下：
当不开启预编译功能时（String url ="jdbc:mysql://localhost:3306/studb"），做10次测试，100000个select总时间（单位毫秒）
<span style="font-size:18px;">12321，12173，12159，12132，12604，12349，12621，12356，12899，12287</span>Mysql协议：xx xx xx xx QUERY .. .. .. .. .. ..
Mysql协议：xx xx xx xx QUERY .. .. .. .. .. ..
<span style="font-size:18px;"> 21349，22860，27237，26848，27772，28100，23114，22897，20010，23211</span>Mysql协议：xx xx xx xx PREPARE .. .. .. .. .. ..
Mysql协议：xx xx xx xx EXECUTE PS_ID ..
Mysql协议：xx xx xx xx PREPARE .. .. .. .. .. ..
Mysql协议：xx xx xx xx EXECUTE PS_ID ..
开启预编译，并开启预编译缓存时（String url ="jdbc:mysql://localhost:3306/studb?useServerPrepStmts=true&cachePrepStmts=true"），做10次测试，100000个select总时间为
<span style="font-size:18px;">8732，8655，8678，9693，8624，9874，8444，9660，8607，8780</span>Mysql协议：xx xx xx xx PREPARE .. .. .. .. .. ..
Mysql协议：xx xx xx xx EXECUTE PS_ID ..
Mysql协议：xx xx xx xx EXECUTE PS_ID ..
从测试结果看来，若开启预编译，但不开启预编译缓存，查询效率会有明显下降，因为需要走多次RPC，且每个查询都需要编译及执行；开启预编译并且打开预编译缓存的明显比不打开预编译的查询性能好30%左右（这个是本机测试，还需要更多验证）。
结论：对于Connector/J5.0.5以后的版本，若使用useServerPrepStmts=true开启预编译，则一定需要同时使用cachePrepStmts=true 开启预编译缓存，否则性能会下降，只有二者都开启，才算是真正开启了预编译功能，性能会比不开启预编译提升30%左右（这个可能是我测试程序的原因，有待进一步研究）
四、预编译JDBC驱动源码剖析
首先对于打开预编译的URL（String url ="jdbc:mysql://localhost:3306/studb?useServerPrepStmts=true&cachePrepStmts=true"）获取数据库连接之后，本质是获取预编译语句pstmt = conn.prepareStatement(sql)时会向MySQL服务端发送一个RPC，发送一个预编译的SQL模板（驱动会拼接mysql预编译语句prepare s1 from 'select * fromuser where id = ?'），然会MySQL服务端会编译好收到的SQL模板，再会为此预编译模板语句分配一个serverStatementId发送给JDBC驱动，这样以后PreparedStatement就会持有当前预编译语句的服务端的serverStatementId,并且会把此 PreparedStatement缓存在当前数据库连接中，以后对于相同SQL模板的操作pstmt.executeUpdate()，都用相同的PreparedStatement，执行SQL时只需要发送serverStatementId和参数，节省一次SQL编译, 直接执行。并且对于每一个连接(驱动端及Mysql服务端)都有自己的preparecache,具体的源码实现是在com.mysql.jdbc.ServerPreparedStatement中实现。
扫一扫
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
