測試mysql的sql語句預編譯效果，mysqlsql語句編譯

測試mysql的sql語句預編譯效果，mysqlsql語句編譯

玩Oracle的都比較關注shared pool，特別是library cache，在使用了綁定變量（預編譯sql）之後確實能得到很大的性能提升。現在在轉Mysql之後特別是innodb很多東西都還能和Oracle對得上號的，就像innodb_buffer_pool_size類似於Oracle的database buffer cache，innodb_log_buffer_size類似於redo log buffer，但是innodb_additional_mem_pool_size僅僅類似於shared pool的Data dictionary cache，似乎還缺少和library cache相對應的東西。那就有一個問題了，在Mysql裡面使用預編譯的sql還會有性能提升嗎？

這裡我用Java的jdbc做了一下測試，分別用Statement和PreparedStatement執行1000個sql，並運行10次

1.使用Statement做硬解析：

 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 stub
61         
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 }

結果如下：

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.同樣的sql用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 stub
60         
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 }

結果如下：

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

可以看出兩種方式執行的結果幾乎相同，不像Oracle差距那麼大。而且就算是用PreparedStatement的方式，在Mysql數據庫端抓出來的sql語句也不是以變量id=?的形式出現的，而是實際的數值。後來在網上看到在連接字符串上加上useServerPrepStmts=true可以實現真正的預編譯

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

加上這段後可以在數據庫端可以看到明確的結果：

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

但是實際的運行結果和上面幾乎一樣，性能上沒有任何的提升。由此可以推斷出Mysql由於缺少類似於Oracle的library cache的部件，因此采用預編譯方式執行sql是沒有性能上的提升的。