Test MySQL SQL statement precompilation effect

Playing with Oracle is a bit more focused on the shared pool, especially the library cache, which can really get a lot of performance gains after using bound variables (precompiled SQL). Now after the turn to MySQL, especially innodb a lot of things can also be with Oracle to the number, just like innodb_buffer_pool_size similar to Oracle's database buffer Cache,innodb_log_ Buffer_size is similar to redo log buffer, but innodb_additional_mem_pool_size is just like the data dictionary cache of the shared pool, which seems to be missing and the library The cache corresponds to something. So there's a problem, is there a performance boost in MySQL using precompiled SQL?

Here I use Java JDBC to do a test, respectively, with statement and PreparedStatement execute 1000 SQL, and run 10 times

1. Use statement to do hard parsing:

1  PackageExmysql;2 3 Importjava.sql.Connection;4 ImportJava.sql.DriverManager;5 ImportJava.sql.ResultSet;6 Importjava.sql.SQLException;7 Importjava.sql.Statement;8 Importjava.util.Date;9 Ten  Public classAddData { One      A     Private Static Longworker () { -Date begin =NewDate (); -          theString driver= "Com.mysql.jdbc.Driver"; -  -String url= "Jdbc:mysql://172.16.2.7:3306/testdb"; -          +Connection conn=NULL; -Statement stmt=NULL; +ResultSet rs=NULL; A          at         Try{ - Class.forName (driver); -Conn=drivermanager.getconnection (URL, "Dbaadmin", "123456"); -stmt=conn.createstatement (); - String SQL; -              for(inti=1;i<=5000;i++){ inSql= "SELECT * from Test1 where id=" +i; -rs=stmt.executequery (SQL); to             } +         } -         Catch(SQLException |classnotfoundexception e) { the e.printstacktrace (); *         } $         Panax Notoginseng         if(stmt!=NULL){ -             Try{ the stmt.close (); +             } A             Catch(SQLException e) { the e.printstacktrace (); +             } -         } $          $         if(conn!=NULL){ -             Try{ - conn.close (); the             } -             Catch(SQLException e) {Wuyi e.printstacktrace (); the             } -         } Wu          -Date end =NewDate (); About         returnEnd.gettime ()-begin.gettime (); $     } -  -      Public Static voidMain (string[] args) { -         //TODO auto-generated Method Stub A          +         LongElapsed,average; theAverage=0; -          for(inti=1;i<=10;i++){ $Elapsed=worker (); theSystem.out.println ("Elapsed Time (ms):" +elapsed); theAverage=average+elapsed; the         } theSystem.out.println ("Average Time (ms):" +AVERAGE/10); -     } in  the}

The results are as follows:

Elapsed time (ms): 24652
Elapsed time (ms): 13380
Elapsed time (ms): 13250
Elapsed time (ms): 13877
Elapsed time (ms): 13275
Elapsed time (ms): 13193
Elapsed time (ms): 19022
Elapsed time (ms): 13558
Elapsed time (ms): 14138
Elapsed time (ms): 13364
Average Time (ms):15170

2. The same SQL is performed with PreparedStatement precompilation

1  PackageExmysql;2 3 ImportJava.sql.*;4 Importjava.util.Date;5 6  Public classInsert_data {7 8     Private Static Longworker () {9Date begin =NewDate ();Ten          OneString driver= "Com.mysql.jdbc.Driver"; A  -String url= "Jdbc:mysql://172.16.2.7:3306/testdb"; -          theConnection conn=NULL; -PreparedStatement pstm=NULL; -ResultSet rs=NULL; -          +         Try{ - Class.forName (driver); +Conn=drivermanager.getconnection (URL, "Dbaadmin", "123456"); AConn.setautocommit (false); atString sql= "SELECT * from Test1 where id=?"; -pstm=conn.preparestatement (SQL); -              for(inti=1;i<=5000;i++){ -Pstm.setint (1, i); -rs=pstm.executequery (); -             } in conn.commit (); -              to         } +         Catch(SQLException |classnotfoundexception e) { - e.printstacktrace (); the         } *          $         if(pstm!=NULL){Panax Notoginseng             Try{ - pstm.close (); the             } +             Catch(SQLException e) { A e.printstacktrace (); the             } +         } -          $         if(conn!=NULL){ $             Try{ - conn.close (); -             } the             Catch(SQLException e) { - e.printstacktrace ();Wuyi             } the         } -          WuDate end =NewDate (); -         returnEnd.gettime ()-begin.gettime (); About     } $      -      Public Static voidMain (string[] args) { -         //TODO auto-generated Method Stub -          A         LongElapsed,average; +Average=0; the          for(inti=1;i<=10;i++){ -Elapsed=worker (); $System.out.println ("Elapsed Time (ms):" +elapsed); theAverage=average+elapsed; the         } theSystem.out.println ("Average Time (ms):" +AVERAGE/10); the     } -  in}

The results are as follows:

Elapsed time (ms): 14773
Elapsed time (ms): 16352
Elapsed time (ms): 14797
Elapsed time (ms): 15800
Elapsed time (ms): 12069
Elapsed time (ms): 14953
Elapsed time (ms): 13238
Elapsed time (ms): 12366
Elapsed time (ms): 15263
Elapsed time (ms): 13089
Average Time (ms):14270

You can see that the results are almost identical in both ways, not as large as the Oracle gap. And even in the PreparedStatement way, the SQL statement that was caught on the MySQL database is not in the form of variable id=, but the actual value. Later on the net to see on the connection string plus useserverprepstmts=true can be achieved real precompilation

        String url= "Jdbc:mysql://172.16.2.7:3306/testdb";        URL  =url+ "Useserverprepstmts=true";

Plus, you can see clear results on the database side:

Mysql> show global status like ' Com_stmt_prepare ';
+------------------+-------+
| variable_name | Value |
+------------------+-------+
| Com_stmt_prepare | 11 |
+------------------+-------+
1 row in Set (0.00 sec)

But the actual results are almost the same as above, with no improvement in performance. It can be inferred that MySQL does not have a performance boost due to the lack of a component similar to Oracle's library cache, so the use of precompiled execution of SQL.

Test MySQL SQL statement precompilation effect