世界上的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博客

MySQL PreparedStatement

最新文章