Pre-compilation and batch processing of MySQL

MySQL pre-compilation feature

Pre-compilation Benefits

Everyone uses the PreparedStatement interface in JDBC, which has pre-compiled functionality. What is a precompiled feature? What good does it have?

When a client sends an SQL statement to the server, the server always needs to verify that the SQL statement is syntactically formatted correctly, then compiles the SQL statement into an executable function, and finally executes the SQL statement. It may take more time to validate the syntax, and compile than to execute the SQL statement.

If we need to execute multiple INSERT statements, but only if the value of each insert is different, the MySQL server needs to verify the syntax format of the SQL statement and compile it every time, which wastes too much time. If you use the precompilation feature, then only one syntax checksum is compiled for the SQL statement, so the efficiency is high.

?

MySQL performs precompilation

MySQL performs pre-compilation into three steps:

• Executes the precompiled statement, for example: Prepare Myfun from ' select * from T_book where bid=? '
• Set the variable, for example: set @str = ' B1 '
• Execute statements, for example: Execute myfun using @str

?

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

• Set the variable, for example: set @str = ' B2 '
• Execute statements, for example: Execute myfun using @str

?

You can see the process of execution by looking at the MySQL log:

?

Using statement to perform precompilation

Using statement to do 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 ();

?

Useserverprepstmts Parameters

The default use of PreparedStatement is not precompiled, which requires the useserverprepstmts=true parameter in the URL (the version prior to MySQL Server 4.1 does not support precompilation, and connector/ J after the 5.0.5 version, the default is not to turn on the pre-compilation feature).

Example: Jdbc:mysql://localhost:3306/test?useserverprepstmts=true

This ensures that the MySQL driver first sends the SQL statement to the server for precompilation, and then simply sends 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 ();

?

Cacheprepstmts parameters

When using different PreparedStatement objects to execute the same SQL statement, there is still a compilation of two occurrences, because the driver does not cache the compiled function key, resulting in two compilations. If you want to cache key for the post-compilation 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 ();

?

Open Batch Processing

MySQL batch processing also needs to be opened by parameters: Rewritebatchedstatements=true

?

?

Pre-compilation and batch processing of MySQL