MySQL precompilation function

1. Benefits of precompilation

Everyone has usually used the PreparedStatement interface in JDBC, which has a precompilation function. What is the precompilation function? What are its benefits?

When the client sends a SQL statement to the server, the server always needs to verify whether the syntax format 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 verification and compilation may be more than the time spent executing SQL statements.

If we need to execute multiple insert statements, but only the values ​​inserted are different each time, the MySQL server also needs to verify the syntax format of the SQL statement and compile it each time, which wastes too much time. time. If you use the precompilation function, 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 precompiled statements, for example: prepare myfun from 'select * from t_book where bid=?'

• Set variables, for example: set @str='b1'

• Execute statements, for example :execute myfun using @str

If you need to execute myfun again, then the first step is no longer needed, that is, there is no need to compile the statement:

• Set variables, for example: set @str='b2'

• Execute statements, for example: execute myfun using @str

##pass Check the MySQL log to see the execution process:

3. Use Statement to execute pre-compilation

Use 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

The default use of PreparedStatement cannot perform precompilation. This requires the useServerPrepStmts=true parameter to be given in the url (MySQL Server Versions before 4.1 do not support precompilation, and versions of Connector/J after 5.0.5 do not enable precompilation by default).

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

This can ensure that the mysql driver will first send the SQL statement to the server for pre-compilation, and then execute executeQuery () just sends the parameters to the server.

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 parameter

When using different PreparedStatement objects to execute the same SQL statement, the phenomenon of compiling twice will still occur. This It 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, you need to 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. Open batch processing

MySQL’s batch processing also needs to be turned on through parameters: rewriteBatchedStatements=true

The above is the detailed content of MySQL precompilation function. For more information, please follow other related articles on the PHP Chinese website!