Understanding MySQL precompilation in one article

1. Benefits of precompilation

We have all used the PreparedStatement interface in JDBC, which has a precompilation function. What is the pre-compilation function? What are the benefits of it?

When a client sends an SQL statement to a server, the server always needs to verify whether the syntax of the SQL statement is correct, then compile the SQL statement into an executable function, and finally execute the SQL statement. The time spent on syntax checking and compilation may be more than the time spent on executing the SQL statement.

If we need to execute multiple insert statements, but the values ​​inserted each time are different, the MySQL server also needs to check the syntax format of the SQL statement and compile it each time, which wastes too much time. If the pre-compilation function is used, the SQL statement is only syntax-checked and compiled once, so the efficiency is high.

2. MySQL performs precompilation

MySQL performs precompilation in three steps:

• Execute a prepared statement, for example: prepare myfun from 'select * from t_book where bid=?'
• Set a variable, for example: set @str='b1'
• Execute statements, for example: execute myfun using @str
  

If you need to execute myfun again, you don't need to do the first step, that is, you don't need to compile the statement again:

• Set a variable, for example: set @str='b2'
• Execute statements, for example: execute myfun using @str
  

You can see the execution process by viewing the MySQL log:

3. Use Statement to perform precompilation

Using Statement to execute precompilation is to execute the above SQL statement once.

Connection con = JdbcUtils.getConnection();
Statement stmt = con.createStatement();
stmt.executeUpdate("prepare myfun from 'select * from t_book where bid=?'");
stmt.executeUpdate("set @str='b1'");
ResultSet rs = stmt.executeQuery("execute myfun using @str");
while(rs.next()) {
  System.out.print(rs.getString(1) + ", ");
  System.out.print(rs.getString(2) + ", ");
  System.out.print(rs.getString(3) + ", ");
  System.out.println(rs.getString(4));
}

stmt.executeUpdate("set @str='b2'");
rs = stmt.executeQuery("execute myfun using @str");

while(rs.next()) {
  System.out.print(rs.getString(1) + ", ");
  System.out.print(rs.getString(2) + ", ");
  System.out.print(rs.getString(3) + ", ");
  System.out.println(rs.getString(4));
}

rs.close();
stmt.close();
con.close();

4. useServerPrepStmts parameter

By default, PreparedStatement cannot be used for precompilation. This requires the useServerPrepStmts=true parameter to be given in the URL (MySQL Server versions prior to 4.1 do not support precompilation, and Connector/J versions after 5.0.5 do not enable precompilation by default).

For example: jdbc:mysql://localhost:3306/test?useServerPrepStmts=true

This ensures that the MySQL driver will first send the SQL statement to the server for precompilation, and then only send the parameters to the server when executing executeQuery().

Connection con = JdbcUtils.getConnection();
String sql = "select * from t_book where bid=?";
PreparedStatement pstmt = con.prepareStatement(sql);

pstmt.setString(1, "b1");
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
  System.out.print(rs.getString(1) + ", ");
  System.out.print(rs.getString(2) + ", ");
  System.out.print(rs.getString(3) + ", ");
  System.out.println(rs.getString(4));
}

pstmt.setString(1, "b2");
rs = pstmt.executeQuery();
while(rs.next()) {
  System.out.print(rs.getString(1) + ", ");
  System.out.print(rs.getString(2) + ", ");
  System.out.print(rs.getString(3) + ", ");
  System.out.println(rs.getString(4));
}

rs.close();
pstmt.close();
con.close();

5. cachePrepStmts parameters

When using different PreparedStatement objects to execute the same SQL statement, it will still be compiled twice. This is because the driver does not cache the compiled function key, resulting in secondary compilation. If you want to cache the key of the compiled function, set the cachePrepStmts parameter to true. For example:

jdbc:mysql://localhost:3306/test?useServerPrepStmts=true&cachePrepStmts=true

Connection con = JdbcUtils.getConnection();
String sql = "select * from t_book where bid=?";
PreparedStatement pstmt = con.prepareStatement(sql);

pstmt.setString(1, "b1");
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
  System.out.print(rs.getString(1) + ", ");
  System.out.print(rs.getString(2) + ", ");
  System.out.print(rs.getString(3) + ", ");
  System.out.println(rs.getString(4));
}

pstmt = con.prepareStatement(sql);
pstmt.setString(1, "b2");
rs = pstmt.executeQuery();
while(rs.next()) {
  System.out.print(rs.getString(1) + ", ");
  System.out.print(rs.getString(2) + ", ");
  System.out.print(rs.getString(3) + ", ");
  System.out.println(rs.getString(4));
}

rs.close();
pstmt.close();
con.close();

6. Turn on batch processing

MySQL batch processing also needs to be turned on through parameters:

rewriteBatchedStatements=true

The above is the detailed content of understanding MySQL precompilation in one article. For more information about MySQL precompilation, please pay attention to other related articles on 123WORDPRESS.COM!