Test the effect of mysql SQL statement pre-compilation and mysql SQL statement compilation.

Test the effect of mysql SQL statement pre-compilation and mysql SQL statement compilation.

Oracle users are more concerned with shared pools, especially the library cache. After using the Bind Variable (pre-compiled SQL), the performance can be greatly improved. Now, after Mysql is switched, many items in innodb can be numbered with Oracle, just like innodb_buffer_pool_size is similar to the database buffer cache of Oracle, and innodb_log_buffer_size is similar to the redo log buffer, however, innodb_additional_mem_pool_size is similar to the Data dictionary cache of the shared pool. It seems that there is still something that corresponds to the library cache. So there is a problem. Will the performance of pre-compiled SQL statements be improved in Mysql?

 

Here, I used Java jdbc for a test. I used Statement and PreparedStatement to execute 1000 SQL statements and run them 10 times.

1. Use Statement for hard parsing:

 1 package exmysql; 2  3 import java.sql.Connection; 4 import java.sql.DriverManager; 5 import java.sql.ResultSet; 6 import java.sql.SQLException; 7 import java.sql.Statement; 8 import java.util.Date; 9 10 public class adddata {11     12     private static long worker(){13         Date begin = new Date(); 14         15         String driver="com.mysql.jdbc.Driver";16 17         String url="jdbc:mysql://172.16.2.7:3306/testdb";18         19         Connection conn=null;20         Statement stmt=null;21         ResultSet rs=null;22         23         try{24             Class.forName(driver);25             conn=DriverManager.getConnection(url,"dbaadmin","123456");26             stmt=conn.createStatement();27             String sql;28             for (int i=1;i<=5000;i++){29                 sql="select * from test1 where id="+i;30                 rs=stmt.executeQuery(sql);31             }32         }33         catch(SQLException | ClassNotFoundException e){34             e.printStackTrace();35         }36         37         if(stmt!=null){38             try{39                 stmt.close();40             }41             catch(SQLException e){42                 e.printStackTrace();43             }44         }45         46         if(conn!=null){47             try{48                 conn.close();49             }50             catch(SQLException e){51                 e.printStackTrace();52             }53         }54         55         Date end = new Date(); 56         return end.getTime()-begin.getTime();57     }58 59     public static void main(String[] args) {60         // TODO Auto-generated method stub61         62         long elapsed,average;63         average=0;64         for (int i=1;i<=10;i++){65             elapsed=worker();66             System.out.println("elapsed time(ms):"+elapsed);67             average=average+elapsed;68         }69         System.out.println("average time(ms):"+average/10);70     }71 72 }

The result is 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. Pre-compile and execute the same SQL statement with PreparedStatement

 

 1 package exmysql; 2  3 import java.sql.*; 4 import java.util.Date; 5  6 public class insert_data { 7  8     private static long worker(){ 9         Date begin = new Date(); 10         11         String driver="com.mysql.jdbc.Driver";12 13         String url="jdbc:mysql://172.16.2.7:3306/testdb";14         15         Connection conn=null;16         PreparedStatement pstm=null;17         ResultSet rs=null;18         19         try{20             Class.forName(driver);21             conn=DriverManager.getConnection(url,"dbaadmin","123456");22             conn.setAutoCommit(false);23             String sql="select * from test1 where id=?";24             pstm=conn.prepareStatement(sql);25             for(int i=1;i<=5000;i++){26                 pstm.setInt(1, i);27                 rs=pstm.executeQuery();28             }29             conn.commit();30             31         }32         catch(SQLException | ClassNotFoundException e){33             e.printStackTrace();34         }35         36         if(pstm!=null){37             try{38                 pstm.close();39             }40             catch(SQLException e){41                 e.printStackTrace();42             }43         }44         45         if(conn!=null){46             try{47                 conn.close();48             }49             catch(SQLException e){50                 e.printStackTrace();51             }52         }53         54         Date end = new Date(); 55         return end.getTime()-begin.getTime();56     }57     58     public static void main(String[] args) {59         // TODO Auto-generated method stub60         61         long elapsed,average;62         average=0;63         for (int i=1;i<=10;i++){64             elapsed=worker();65             System.out.println("elapsed time(ms):"+elapsed);66             average=average+elapsed;67         }68         System.out.println("average time(ms):"+average/10);69     }70 71 }

The result is 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

 

We can see that the results of the two methods are almost the same, unlike the Oracle gap. In addition, even if PreparedStatement is used, the SQL statements captured on the Mysql database are not variable id =? The actual value. Later, we can see on the Internet that adding useserverprep;ts = true to the connection string can implement real pre-compilation.

        String url="jdbc:mysql://172.16.2.7:3306/testdb";        url=url+"?useServerPrepStmts=true";

After this section is added, you can see the clear results on the database:

Mysql> show global status like 'com _ stmt_prepare ';
+ ------------------ + ------- +
| Variable_name | Value |
+ ------------------ + ------- +
| Com_stmt_prepare | 11 |
+ ------------------ + ------- +
1 row in set (0.00 sec)

 

However, the actual running results are almost the same as above, and there is no improvement in performance. It can be inferred that Mysql has no performance improvement because it lacks components similar to the Oracle library cache and adopts the pre-Compilation Method for SQL Execution.