測試mysql的sql語句先行編譯效果

標籤：

玩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 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 }

結果如下：

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

結果如下：

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是沒有效能上的提升的。

 

測試mysql的sql語句先行編譯效果