探究mysql预编译

一．背景：

用Mybatis+mysql的架构做开发，大家都知道，Mybatis内置参数，形如#{xxx}的，均采用了sql预编译的形式，举例如下：

<select id=”aaa” parameterType=”int” returnType=”Blog”>
                   select * from blog where id = #{id}
    </select>

 

         查看日志后，会发现这个sql执行时被记录如下：

         

select * from blog where id =?

 

        之前上网查过一些资料，大致知道mybatis底层使用PreparedStatement，过程是先将带有占位符（即”?”）的sql模板发送至mysql服务器，由服务器对此无参数的sql进行编译后，将编译结果缓存，然后直接执行带有真实参数的sql。网上关于这个问题的资料较少，但基本结论是，使用预编译，可以提高sql的执行效率，并且有效地防止了sql注入。我一直对这个结论深信不疑，直到看了一篇名叫“Java中连结MySQL启用预编译的先决条件是useServerPstmts=true.”的文章，说mysql是默认不开启预编译的，大多数持久层框架（例如mybatis）和jdbc代码都没有做到真实开启预编译，文章链接如下：http://blog.csdn.net/axman/article/details/6913527  ，另外还有一些文章说mysql是不支持预编译的，总之，众说纷纭，因为项目中使用的就是mysql，所以我决定花一些时间来探究一下这个问题。

二．问题：

       我的疑问有两点：1.mysql是否默认开启了预编译功能？若没有，将如何开启？ 2.预编译是否能有效地节省sql执行的成本？

三．探究一：mysql是否默认开启预编译？

       首先针对第一个问题。懒得开linux虚拟机了，我电脑上已经安装了mysql，版本是5.0.18，打开配置文件my.ini，在“port=3306“这一行下面加了配置：log=d:/logs/mysql50_log.txt，这样就开启了mysql日志功能，该日志主要记录mysql执行sql的过程。重启mysql，并建立一个库prepare_stmt_test，在该库下建一个叫users的表，有id（主键）和name两个字段。

         接着，我建立了一个简单的java工程，引入jdbc驱动包mysql-connector-java-5.0.3-bin.jar。然后写了如下的代码：

        

 public static void main(String []a) throws Exception{
        String sql = "select * from users where name = ?";
        Class.forName("com.mysql.jdbc.Driver");
        Connection conn = null;
        try{
            conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root");
            PreparedStatement stmt = conn.prepareStatement(sql);
            stmt.setString(1, "aaa");
            ResultSet rs = stmt.executeQuery();
            rs.close();
            stmt.close();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
            conn.close();
        }
    }

 

    执行这些代码后，打开刚才配置的mysql日志文件mysql50_log.txt，日志记录如下：

    1 Query       SET NAMES utf8

                         1 Query       SET character_set_results = NULL

                         1 Query       SHOW VARIABLES

                         1 Query       SHOW COLLATION

                         1 Query       SET autocommit=1

                         1 Prepare     [1]

                         1 Execute     [1] select * from users where name = 'aaa'

                         1 Quit      

         日志格式有点奇怪，明明打出了prepare关键字，但没有我设定的预编译的语句“select * from users where name = ?”，更令人疑惑的是，刚才说的那篇名叫“Java中连结MySQL启用预编译的先决条件是useServerPstmts=true.”的文章里提到的，若jdbc连接没有加useServerPrepStmts =true，mysql日志里连prepare关键字都不会记录。而我的测试结果是，不加useServerPrepStmts =true，prepare关键字是有的，但没有预编译的sql模板“select * from users where name = ?”。

         可能是我的mysql版本比较老吧，于是我停掉mysql5.0服务，安装了mysql5.5，依照刚才那样建库建表，并启用了一个新的mysql日志文件mysql55_log.txt。一切OK后，我又一次执行了刚才的测试程序，然后打开日志文件mysql55_log.txt，发现了这样的记录：

            1 Query      SET NAMES utf8

                       1 Query    SET character_set_results = NULL

                       1 Query    SHOW VARIABLES

                       1 Query    SHOW WARNINGS

                       1 Query    SHOW COLLATION

                       1 Query    SET autocommit=1

                       1 Prepare          select * from users where name = ?

                       1 Execute          select * from users where name = 'aaa'

                       1 Close stmt   

                       1 Quit      

         终于看到sql模板“select * from users where name = ?”了，但仍然有很多疑惑，首先，刚才的mysql5.0到底开启预编译了吗？其次，我并没有加useServerPrepStmts =true配置，但mysql5.5的确是做了预编译的操作的，这与“Java中连结MySQL启用预编译的先决条件是useServerPrepStmts =true.<!--[if !supportNestedAnchors]--><!--[endif]-->”这篇文章的测试结果大相径庭。

         带着这些问题，又仔细阅读了一下CSDN上这篇文章，作者的结论是：jdbc连接mysql时配置useServerPrepStmts参数为true后才能开启mysql预编译功能。看来这个useServerPrepStmts参数是很重要的，于是我将刚才代码里的jdbc连接修改如下：

DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root&useServerPrepStmts =false")

 

执行代码后，再次查看mysql日志：

1 Query    SET NAMES utf8

                       1 Query    SET character_set_results = NULL

                       1 Query    SHOW VARIABLES

                       1 Query    SHOW WARNINGS

                       1 Query    SHOW COLLATION

                       1 Query    SET autocommit=1

                       1 Query    select * from users where name = 'aaa'

130410 15:06:48          1 Quit      

果然，日志了没有了prepare这一行，说明mysql没有进行预编译。这意味着useServerPrepStmts这个参数是起效的，且默认值为true。那么，为什么在刚才那篇文章里，作者得出的结论是useServerPrepStmts默认为false呢？

继续思考了一阵，我突然意识到，useServerPrepStmts这个参数是jdbc的连接参数，这说明此问题与jdbc驱动程序可能有关系。打开mysql官网，发现在线的官方文档很强大，支持全文检索，于是我将“useServerPrepStmts”做为关键字，搜索出了一些信息，原文如下：

Important change: Due to a number of issues with the use of server-side prepared statements, Connector/J 5.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 connector string:

useServerPrepStmts=true

The default value of this property 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参数，再执行，发现mysql5.5的日志打印如下：

           2 Query         SHOW SESSION VARIABLES

                       2 Query    SHOW WARNINGS

                       2 Query    SHOW COLLATION

                       2 Query    SET NAMES utf8

                       2 Query    SET character_set_results = NULL

                       2 Query    SET autocommit=1

                       2 Query    select * from users where name = 'aaa'

                       2 Quit      

         那么，mysql5.0呢？我停掉mysql5.5服务，开启mysql5.0，再执行java代码，查看mysql5.0的日志：

         1 Query       SHOW SESSION VARIABLES

                         1 Query       SHOW COLLATION

                         1 Query       SET NAMES utf8

                         1 Query       SET character_set_results = NULL

                         1 Query       SET autocommit=1

                         1 Query       select * from users where name = 'aaa'

                         1 Quit 

         果然，在mysql5.0日志里，prepare关键字没有了。mysql5.0的日志格式和mysql5.5的不太一样，5.0日志只打印一个“prepare”关键字，而不打印预编译sql模板。

   第一个问题解决了，结论就是：mysql是否默认开启预编译，与MySQL server的版本无关，而与 MySQL Connector/J（驱动程序）的版本有关，Connector/J 5.0.5及以后的版本默认不支持预编译，Connector/J 5.0.5之前的版本默认支持预编译。

四．探究二：预编译是否能有效地节省sql执行的成本？

       首先，我们要明白mysql执行一个sql语句的过程。查了一些资料后，我得知，mysql执行脚本的大致过程如下：prepare（准备）-> optimize（优化）-> exec（物理执行），其中，prepare也就是我们所说的编译。开篇时已经说过，对于同一个sql模板，如果能将prepare的结果缓存，以后如果再执行相同模板而参数不同的sql，就可以节省掉prepare（准备）的环节，从而节省sql执行的成本。明白这一点后，我写了如下测试程序：

      

 public static void main(String []a) throws Exception{
       String sql = "select * from users where name = ?";
       Class.forName("com.mysql.jdbc.Driver");
       Connection conn = null;
       try{
           conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root&useServerPrepStmts=true");
           PreparedStatement stmt = conn.prepareStatement(sql);
           stmt.setString(1, "aaa");
           ResultSet rs1 = stmt.executeQuery();//第一次执行
rs1.close();
           stmt.setString(1, "ddd");
           ResultSet rs2 = stmt.executeQuery();//第二次执行
           rs2.close();
           stmt.close();
       }catch(Exception e){
           e.printStackTrace();
       }finally{
           conn.close();
       }
    }

 

    执行该程序后，查看mysql日志：

1 Query    SHOW SESSION VARIABLES

                       1 Query    SHOW WARNINGS

                       1 Query    SHOW COLLATION

                       1 Query    SET NAMES utf8

                       1 Query    SET character_set_results = NULL

                       1 Query    SET autocommit=1

                       1 Prepare          select * from users where name = ?

                       1 Execute          select * from users where name = 'aaa'

                       1 Execute          select * from users where name = 'ddd'

                       1 Close stmt   

                       1 Quit      

按照日志看来，PreparedStatement重新设置sql参数后，并没有重新prepare，看来预编译起到了效果。但刚才我使用的是同一个stmt，如果将stmt关闭呢？

public static void main(String []a) throws Exception{
       String sql = "select * from users where name = ?";
       Class.forName("com.mysql.jdbc.Driver");
       Connection conn = null;
       try{
           conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root&useServerPrepStmts=true");
           PreparedStatement stmt = conn.prepareStatement(sql);
           stmt.setString(1, "aaa");
           ResultSet rs1 = stmt.executeQuery();//第一次执行
           rs1.close();
           stmt.close();
           stmt = conn.prepareStatement(sql);//重新获取一个statement
           stmt.setString(1, "ddd");
           ResultSet rs2 = stmt.executeQuery();//第二次执行
           rs2.close();
           stmt.close();
       }catch(Exception e){
           e.printStackTrace();
       }finally{
           conn.close();
       }
    }

 

mysql日志打印如下：

1 Query    SHOW SESSION VARIABLES

                       1 Query    SHOW WARNINGS

                       1 Query    SHOW COLLATION

                       1 Query    SET NAMES utf8

                       1 Query    SET character_set_results = NULL

                       1 Query    SET autocommit=1

                       1 Prepare          select * from users where name = ?

                       1 Execute          select * from users where name = 'aaa'

                       1 Close stmt   

                       1 Prepare          select * from users where name = ?

                       1 Execute          select * from users where name = 'ddd'

                       1 Close stmt   

                       1 Quit

很明显，关闭stmt后再执行第二个sql，mysql就重新进行了一次预编译，这样是无法提高sql执行效率的。而在实际的应用场景中，我们不可能保持同一个statement。那么，mysql如何缓存预编译结果呢？

搜索一些资料后得知，jdbc连接参数中有另外一个重要的参数：cachePrepStmts，设置为true后可以缓存预编译结果。于是我将测试代码中jdbc连接串改为了这样：

          

 conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/prepare_stmt_test?user=root&password=root&useServerPrepStmts=true&cachePrepStmts=true");

 

再执行代码后，发现mysql日志记录又变成了这样：

1 Prepare          select * from users where name = ?

                           1 Execute          select * from users where name = 'aaa'

                       1 Execute          select * from users where name = 'ddd'

OK，现在我们开启了预编译，并开启了缓存预编译的功能，那么开始性能测试。我向刚才的单表中插入了10000条数据，并做10000次同样sql模板，不同参数的select。记录结果如下：

当不开启预编译功能时，做5次测试，10000个select总时间为（单位毫秒）：

49172,49172,49000,49047,48922

开启预编译，但不开启预编译缓存时，测试数值如下：

50797,50860,50672,50750,50703

开启预编译，开启预编译缓存，测试数值如下：

49547,49250,49593,49093,49078.

从测试结果看来，若开启预编译，但不开启预编译缓存，查询效率会有明显下降；但开启预编译且开启预编译缓存时，查询效率比不开启缓存有提高，但和不开启预编译基本持平。

结论出来了：对于Connector/J5.0.5以上的版本，若使用useServerPrepStmts=true开启预编译，则一定要同时使用cachePrepStmts=true 开启预编译缓存，否则性能会下降，若二者都开启，性能并没有显著的提高，这个可能是我测试程序的原因，有待进一步研究。

五．总结：

       经过这一系列的探究，能够得出一些结论：

首先批一下《Java中连结MySQL启用预编译的先决条件是useServerPstmts=true.》这篇文章吧，文章写得很不科学，作者并没有关注mysql和Connector/J的版本之间的差异，对于mysql，他说mysql一定支持预编译，事实上，经过我查询官方文档后，得知MySQL Server 4.1之前的版本是不支持预编译的；对于Connector/J，他也没有关注5.0.5这个版本节点。所以，虽然被浏览转载了很多次，但这篇文章的结论仍然是错误的，应该也误导了不少人；对于开启预编译和预编译缓存后对性能的影响，这篇文章也没有涉及，事实上经过我测试，若jdbc驱动是5.0.5及之后的版本，同时开启预编译和预编译缓存，sql的执行性能并没有显著提高，若jdbc驱动是5.0.5之前的版本，默认开启了预编译，则一定要加cachePrepStmts=true，否则mysql的执行效率会比较低。总之，预编译和预编译缓存一定要同时开启或同时关闭，不同Connector/J的版本，useServerPrepStmts的默认值会有所不同。

再谈谈SQL预编译这个东西，其实“预编译”这个叫法不是很准确，官方文档里把它叫做“预准备”。经过我测试，对于mysql，开启了预编译缓存后，不同connection之间，预编译的结果是独立的，是无法共享的，一个connection无法得到另外一个connection的预编译缓存结果，对于这一点，我想mysql的开发人员应该会在以后逐步改进吧。再一点，关于预编译缓存的内容，我查了相关的资料后得知，mysql执行一个预编译操作后，会将sql模板（即带占位符“?”的sql），以及参数列表（模板中用各个占位符表示）缓存，下一次有同样的sql模板发来的时候，直接将参数传给这个模板，拼好后execute。虽然mysql的预编译功能对性能影响不大，但在jdbc中使用PreparedStatement是必要的，可以有效地防止sql注入，这一点大家都明白。