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

MySQL PreparedStatement

相關文章