The following analysis is based on mysql-connector-Java-5.1.43 and mysql Server Version 5.6.

From the beginning of JDBC, you can learn how to use PrepareStatement to precompile SQL without having to re-parse and compile SQL for each Statement. Compared with using Statement, the program performance can be improved. So how much of a performance boost is it from prepareStatements?

With example code:

import java.sql.*; /** * Created by ZHUKE on 2017/8/18. */ public class Main { public static void main(String[] args) throws ClassNotFoundException, SQLException { Class.forName("com.mysql.jdbc.Driver"); The Connection conn = DriverManager. GetConnection (" JDBC: mysql: / / 127.0.0.1 / test ", "root", "root"); String prepareSql = "select * from user_info where firstName = ?" ; PreparedStatement preparedStatement = conn.prepareStatement(prepareSql); Statement statement = conn.createStatement(); String statementSql = "select * from user_info where firstName= 'zhuke'"; long nowTime = System.currentTimeMillis(); int count = 100000; for (int i = 0; i < count; i++) { preparedStatement.setString(1, "zhuke"); preparedStatement.execute(); } long nowTime1 = System.currentTimeMillis(); System.out.println("preparedStatement execute " + count + " times consume " + (nowTime1 - nowTime) + " ms"); long nowTime2 = System.currentTimeMillis(); for (int i = 0; i < count; i++) { statement.execute(statementSql); } long nowTime3 = System.currentTimeMillis(); System.out.println("statement execute " + count + " times consume " + (nowTime3 - nowTime2) + " ms"); }}Copy the code

Execute the same statement 100,000 times and get the following result:





The test results

14588:14477, this is the performance improvement I always believe in??

There must be something wrong with the PrepareStatement, which will have parameter placeholders. The server parses and compiles the SQL statement and returns the compiled SQL ID to the client. The client needs to send parameter values and SQL IDS to the server next time. This saves the server the cost of compiling the same SQL statement multiple times, and also saves some network overhead because you don’t have to send the entire SQL each time.

So why didn’t PrepareStatement achieve any performance improvements in the above code? Check out the execution of a PrepareStatement by enabling mysql detailed logs.

preparedStatement.setString(1, "zhuke");
preparedStatement.execute();
Copy the code

Mysql > log





PrepareStatement Executes mysql logs

As can be seen from the mysql log, the SQL statement sent to the mysql server after the completion of parameter stitching is still complete each time it is executed in the form of PrepareStatement, and the above server precompilation features are not taken advantage of.

Use the mysql-connector-Java (version 5.1.43) connection driver source code to find out why.

public java.sql.PreparedStatement prepareStatement(String sql, int resultSetType, Int resultSetConcurrency) throws SQLException {synchronized (getConnectionMutex()) {...... if (this.useServerPreparedStmts && getEmulateUnsupportedPstmts()) { canServerPrepare = canHandleAsServerPreparedStatement(nativeSql); } // If useServerPreparedStmts is set to true and the server supports SQL precompilation optimization, Execute SQL server to optimize the if (this. UseServerPreparedStmts && canServerPrepare) {if (this. GetCachePreparedStatements ()) {synchronized (this serverSideStatementCache) {... } else {// otherwise perform local precompilation...... } return pStmt; }}Copy the code

If the server supports precompilation, then only useServerPreparedStmts controls whether or not the server is precompiled. The default value is false. So if you do not explicitly configure useServerPreparedStmts =true, server precompilation is not performed, and only local precompilation is performed.

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). The driver file will be set to false after version 5.0.5, so you need to manually specify and enable server precompilation. Dev.mysql.com/doc/relnote…

Enable server precompilation by adding the parameter useServerPreparedStmts =true to the URL link. Now we can see the mysql log as follows:





Mysql log information when useServerPreparedStmts =true

Mysql server prepares for precompilation after server precompilation is enabled

select * from user_info where firstName = ?
Copy the code

Statements.

Experiment with the above code again and see how much performance is improved:





The result is executed after useServerPreparedStmts is enabled

13312-14535, improved performance by 8.4%.

The corresponding parameter, cachePrepStmts, indicates whether the server needs to cache the Prepare precompiled object.

// Create two preparedStatement class.forname (" com.mysql.jdbc.driver ") when closing cachePrepStmts; The Connection conn = DriverManager. GetConnection (" JDBC: mysql: / / 127.0.0.1 / test? useServerPrepStmts=true", "root", "root"); String prepareSql = "select * from user_info where firstName = ?" ; PreparedStatement preparedStatement = conn.prepareStatement(prepareSql); preparedStatement.setString(1, "zhuke"); preparedStatement.execute(); preparedStatement.close(); preparedStatement = conn.prepareStatement(prepareSql); preparedStatement.setString(1, "zhuke1"); preparedStatement.execute(); preparedStatement.close();Copy the code





Create two PreparedStatements when you close cachePrepStmts

You can see that at this point, the server precompiled twice for the exact same SQL statement.

What about when we enable cachePrepStmts?

// Create two preparedStatement class.forname (" com.mysql.jdbc.driver ") when closing cachePrepStmts; The Connection conn = DriverManager. GetConnection (" JDBC: mysql: / / 127.0.0.1 / test? useServerPrepStmts=true&cachePrepStmts=true", "root", "root"); String prepareSql = "select * from user_info where firstName = ?" ; PreparedStatement preparedStatement = conn.prepareStatement(prepareSql); preparedStatement.setString(1, "zhuke"); preparedStatement.execute(); preparedStatement.close(); preparedStatement = conn.prepareStatement(prepareSql); preparedStatement.setString(1, "zhuke1"); preparedStatement.execute(); preparedStatement.close();Copy the code





Enable mysql logs when cachePrepStmts is enabled

As you can see, when cachePrepStmts is enabled, the mysql server does only one precompilation.

As you can see from reading the source code, when cachePrepStmts is enabled, the client uses the SQL statement as the key and the PrepareStatement of the precompiled object as the value, which is stored in a Map for reuse and caching.

// When prepareStatement is closed, Public void close() throws SQLException {MySQLConnection locallyScopedConn = this.connection; if (locallyScopedConn == null) { return; // already closed } synchronized (locallyScopedConn.getConnectionMutex()) { if (this.isCached && isPoolable() && ! this.isClosed) { clearParameters(); this.isClosed = true; / / this cache the precompiled object. Connection. RecachePreparedStatement (this); return; } realClose(true, true); } } public void recachePreparedStatement(ServerPreparedStatement pstmt) throws SQLException { synchronized (getConnectionMutex()) { if (getCachePreparedStatements() && pstmt.isPoolable()) { synchronized (this.serverSideStatementCache) { Object oldServerPrepStmt = this.serverSideStatementCache.put(makePreparedStatementCacheKey(pstmt.currentCatalog, pstmt.originalSql), pstmt); if (oldServerPrepStmt ! = null) { ((ServerPreparedStatement) oldServerPrepStmt).isCached = false; ((ServerPreparedStatement) oldServerPrepStmt).realClose(true, true); } } } } }Copy the code

conclusion

UseServerPrepStmts =true to enable server precompilation and cachePrepStmts=true to enable client caching of precompiled objects.

Resources: dev.mysql.com/doc/refman/… www.cnblogs.com/justfortast…