MySQL的JDBC驱动源码解析 预编译开启

一、背景

        现在我们淘宝持久化大多数是采用iBatis+MySQL做开发的,大家都知道,iBatis内置参数,形如#xxx#的,均采用了sql预编译的形式,举例如下:

<span style="font-size:18px;"><select id=”queryUserById” returnType=”userResult”>  
   SELECT * FROM user WHERE id =#id#
</select> </span>
       查看日志后,会发现这个sql执行时被记录如下,SELECT * FROM user WHERE id = ?
       看过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。然后写了如下的代码:

<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>
执行这些代码后,打开刚才配置的mysql日志文件mysql_log.txt,日志记录如下:

<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>
从MySQL日志可以清晰看到,server端执行了一次预编译Prepare及执行了一次Execute,预编译sql模板为“select * from user where id= ?”,说明MySQL5.1.19+ mysql-connector-java-5.0.3是默认开启预编译的。但还是有很多疑惑,为什么之前查阅资料,都说开启预编译是跟 useServerPrepStmts 参数有关的,于是将刚才代码里的JDBC连接修改如下:

<span style="font-size:18px;">DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root&useServerPrepStmts=false")</span>


执行代码后,再次查看mysql日志:
<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>
       执行该程序后,查看mysql日志:
<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>
       按照日志看来,PreparedStatement重新设置sql参数后,并没有重新prepare,看来预编译起到了效果。但刚才我们使用的是同一个stmt,如果将stmt关闭,重新获取一个stmt呢?
<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>
mysql日志打印如下:
<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>
        很明显,关闭stmt后再执行第二个sql,mysql就重新进行了一次预编译,这样是无法提高sql执行效率的。而在实际的应用场景中,我们不可能保持同一个statement。那么,mysql如何缓存预编译结果呢?
        搜索一些资料后得知,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>
再执行代码后,发现mysql日志记录又变成了这样:
<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>
   (每次查询一个RPC,每一个查询,都会在mysql server端做一次编译及一次执行)
      Mysql协议:xx xx xx xx QUERY .. .. .. .. .. ..
      Mysql协议:xx xx xx xx QUERY .. .. .. .. .. ..

      开启预编译,但不开启预编译缓存时(String url= "jdbc:mysql://localhost:3306/studb?useServerPrepStmts=true"),做10次测试,100000个select总时间为(单位毫秒)
<span style="font-size:18px;"> 21349,22860,27237,26848,27772,28100,23114,22897,20010,23211</span>
    (每次查询需要两个RPC,第一个RPC是编译,第二个RPC是执行,进测试数据可以看到这种其实与不打开预编译相比居然还慢,因为多了一次RPC,网络开销在那里)
      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>
    (第一次两个RPC,之后都是一个RPC,第一次会因为编译sql模板走一次RPC,后面都只需要执行一次RPC,在 mysql server端不需要编译,只需要执行)
      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
    点赞
  • 4
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值