MySQL PreparedStatement

世界上的PreparedStatement有两种
一种是PreparedStatement
另一种是MySQL的PreparedStatement

MySQL版本5.6.14
JDBC版本mysql-connector-java-5.1.6-bin.jar

MySQL JDBC的PreparedStatement仅仅在JAVA端进行了参数的替换。
和Oracle的软解析，软软解析什么的真是一点关系也没有。
测试程序如下:

public class Test {
    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {
        long start = System.currentTimeMillis();
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager
                .getConnection(
                        "jdbc:mysql://127.0.0.1:3306/xx",
                        "xx", "xx");

        connection.setAutoCommit(false);
        PreparedStatement cmd = connection
                .prepareStatement("insert into test values(?,?)");
        
        for (int i = 0; i < 10; i++) {
            cmd.setInt(1, i);
            cmd.setString(2, "test");
            cmd.executeUpdate();
        }
        connection.commit();
        
        cmd.close();
        connection.close();
        
        long end = System.currentTimeMillis();
        System.out.println(end - start);
        
    }
}

开启MySQL查询日志
mysql> set @@global.general_log=on;
Query OK, 0 rows affected (0.00 sec)

结果如下,明显只是变量的替换，并没有任何预解析的过程。

95 Query SET autocommit=1 95 Query SET autocommit=0 95 Query insert into test values(0,'test') 95 Query insert into test values(1,'test') 95 Query insert into test values(2,'test') 95 Query insert into test values(3,'test') 95 Query insert into test values(4,'test') 95 Query insert into test values(5,'test') 95 Query insert into test values(6,'test') 95 Query insert into test values(7,'test') 95 Query insert into test values(8,'test') 95 Query insert into test values(9,'test') 95 Query commit 95 Query rollback 95 Quit

使用useServerPrepStmts和cachePrepStmts显式开启预解析
程序如下:

public class Test {
    public static void main(String[] args) throws ClassNotFoundException,
            SQLException {
        StringBuilder builder=new StringBuilder(50);
        builder.append("jdbc:mysql://127.0.0.1:3306/xx?");
        builder.append("useServerPrepStmts=true&");
        builder.append("cachePrepStmts=true&");
        builder.append("prepStmtCacheSqlLimit=256&");
        builder.append("prepStmtCacheSize=256");
        
        long start = System.currentTimeMillis();
        Class.forName("com.mysql.jdbc.Driver");
        Connection connection = DriverManager
                .getConnection(
                        builder.toString(),
                        "xx", "xx");

        connection.setAutoCommit(false);
        PreparedStatement cmd = connection
                .prepareStatement("insert into test values(?,?)");

        for (int i = 0; i < 10; i++) {
            cmd.setInt(1, i);
            cmd.setString(2, "test");
            cmd.executeUpdate();
        }
        connection.commit();

        cmd.close();
        connection.close();

        long end = System.currentTimeMillis();
        System.out.println(end - start);

    }
}

查看MySQL查询日志

96 Query SET autocommit=1 96 Query SET autocommit=0 96 Prepare insert into test values(?,?) 96 Execute insert into test values(0,'test') 96 Execute insert into test values(1,'test') 96 Execute insert into test values(2,'test') 96 Execute insert into test values(3,'test') 96 Execute insert into test values(4,'test') 96 Execute insert into test values(5,'test') 96 Execute insert into test values(6,'test') 96 Execute insert into test values(7,'test') 96 Execute insert into test values(8,'test') 96 Execute insert into test values(9,'test') 96 Query commit 96 Query rollback 96 Quit

显式开启预解析之后，确实可以做到一次解析，多次执行。

但是程序测试的结果，性能相差无几。
我感觉MySQL 解析之后，能够缓存复用的信息太少,所以没有明显的提升。

参考:
http://cs-css.iteye.com/blog/1847772
http://blog.csdn.net/axman/article/details/6913527

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29254281/viewspace-1151799/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29254281/viewspace-1151799/