JDBC test MySQL Database SQL pre-resolution (Bind Variable)

JDBC test MySQL Database SQL pre-resolution (Bind Variable)

I used to get used to Oracle and learned mysql. I want to test the effect of MySQL variable binding. I used to read the online Introduction. Most of them said that MySQL does not have the concept of an SQL shared pool, so there is no SQL pre-resolution or variable binding statement.
Today, I tested (capture packets through the network, view SQL logs on the server and analyze the source code), and found that MySQL still has the implementation of SQL pre-resolution.
The server is MySQL 5.1.58 (win32) and uses JDBC (5.1.18) as the client. The default connection method does not have the SQL pre-resolution effect, even if we use the preparedstatement object, it just Concatenates the SQL statement and the variable into a complete SQL statement and sends it to the server. The Code is as follows:

PreparedStatement pstmt = conn.prepareStatement("select * from t1 where c1=?");pstmt.setString(1, "abc");pstmt.execute();

In fact, there is no pre-resolution process, but after simple splicing, the following SQL is sent to the server

select * from t1 where c1='abc'

To achieve the effect of pre-resolution, we must set the JDBC connection parameter useserverprep;ts = true, and then use preparedstatement to get the result, when creating a preparedstatement, the client first sets "select * from T1 where c1 =? "Send to the server for pre-resolution. Execute only transmits the variable to the server for execution.

The SQL statement cache of the MySQL server can be viewed through the status variable prepared_stmt_count.

mysql> show status  like 'Prepared_stmt_count';+---------------------+-------+| Variable_name       | Value |+---------------------+-------+| Prepared_stmt_count | 1     |+---------------------+-------+1 row in set

However, the SQL statement cache of MySQL is very different from that of Oracle. It is session-statement-level, rather than global sharing. When the session is disconnected or preparedstatement. Close, the cache will no longer exist. We need to set the connection parameter cacheprep0000ts = true to cache the preparedstatement, and prepstmtcachesize = xxx to set the maximum number of cache statements for each session (many connection pools also have similar functions ).

OK. I already know how to enable pre-resolution. Would you like to see the differences between enabling and not enabling pre-resolution performance be as obvious as Oracle? After a simple test, it is found that when no preparedstatement cache (cacheprep;ts = false) is available, the performance of enabling preresolution is much lower. When there is a preparedstatement cache (cacheprep;ts = true), the performance of both is basically the same. This result is disappointing. There are several reasons for my analysis:
When pre-resolution is enabled but the preparedstatement cache is not enabled, the preparedstatement needs to be resolved once each time a preparedstatement is created, and the execute needs to interact once again, while the pre-resolution SQL is in preparedstatement. close cannot be reused, so the performance is worse.

When preparedstatement is cached, the pre-Resolved SQL text is cached on the server, but the execution plan is not cached as in Oracle. Therefore, you need to parse the SQL statement and generate the execution plan for each execute operation, therefore, it only reduces the text size of SQL transmitted each time execute, with little performance difference.

Note: If the SQL syntax is incorrect, the server-side pre-resolution will fail. However, after JDBC receives the pre-resolution error message, it does not prompt an error. Instead, it will cancel the pre-resolution status of this statement, execute directly loads the SQL statement and sends it to the server. The code in the preparedstatement constructor of MySQL JDBC is as follows. The serverpreparedstatement class returned indicates that the binding variable is used, and the preparedstatement class returned indicates that the binding variable is not used:

try {pStmt = ServerPreparedStatement.getInstance(getLoadBalanceSafeProxy(), nativeSql,this.database, resultSetType, resultSetConcurrency);pStmt.setResultSetType(resultSetType);pStmt.setResultSetConcurrency(resultSetConcurrency);} catch (SQLException sqlEx) {// Punt, if necessaryif (getEmulateUnsupportedPstmts()) {pStmt = (PreparedStatement) clientPrepareStatement(nativeSql, resultSetType, resultSetConcurrency, false);} else {throw sqlEx;}}

After the above analysis, I personally think that you do not need to enable the SQL pre-resolution effect, the preparedstatement object is still used as much as possible, because although it cannot improve performance, it can avoid SQL Injection security issues.

2012-02-