MySQL pre-compilation and MySQL Compilation

MySQL pre-compilation and MySQL Compilation
 1. Benefits of pre-CompilationYou have used the PreparedStatement interface in JDBC, which has the pre-compilation function. What is the pre-compilation function? What are its advantages? After a customer sends an SQL statement to the server, the server always needs to check whether the syntax format of the SQL statement is correct, compile the SQL statement into an executable function, and finally execute the SQL statement. The validation syntax and compilation may take more time than SQL statement execution. If we need to execute multiple insert statements, but the values for each insert are different, the MySQL server also needs to verify the syntax format and compile of the SQL statement every time, this wastes too much time. If the Pre-compilation function is used, only one syntax checksum and compilation is performed for the SQL statement, so the efficiency is high. 2. MySQL pre-compiled is divided into three steps:

• Execute the pre-compiled statement, for example, prepare myfun from 'select * from t_book where bid =? '
• Set variables, such as: set @ str = 'b1'
• Execute the statement, for example: execute myfun using @ str

If you need to execute myfun again, you no longer need the first step, that is, you do not need to compile the statement again:

• Set variables, such as: set @ str = 'b2'
• Execute the statement, for example: execute myfun using @ str

You can view the MySQL Log to see the execution process:

 

3. Execute pre-compilation using Statement to execute pre-compilation is to execute the preceding 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. The useserverprep0000ts parameter uses PreparedStatement by default and cannot be precompiled. In this case, the useserverprep0000ts = true parameter must be provided in the url. (The Versions earlier than MySQL Server 4.1 do not support precompilation, in Versions later than 5.0.5, Connector/J does not enable the pre-compilation function by default ). Example: jdbc: mysql: // localhost: 3306/test? Useserverprep0000ts = true to ensure that the mysql driver sends the SQL statement to the server for pre-compilation, and then only sends the parameter 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. When different PreparedStatement objects are used to execute the same SQL statement, the cachepreptemts parameter will still be compiled twice because the driver does not cache the compiled function key, this causes secondary compilation. If you want to cache the key of the compiled function, you must set the cacheprepcomputs parameter to true. Example: jdbc: mysql: // localhost: 3306/test? Useserverprepancts = true & cacheprepancts = 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. You also need to enable rewriteBatchedStatements = true when you open a batch for MySQL.