python 预编译sql_测试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 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

10 public classadddata {11

12 private static longworker(){13 Date begin = newDate();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 = newDate();56 return end.getTime()-begin.getTime();57 }58

59 public static voidmain(String[] args) {60 //TODO Auto-generated method stub

61

62 longelapsed,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 packageexmysql;2

3 import java.sql.*;4 importjava.util.Date;5

6 public classinsert_data {7

8 private static longworker(){9 Date begin = newDate();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 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

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 = newDate();55 return end.getTime()-begin.getTime();56 }57

58 public static voidmain(String[] args) {59 //TODO Auto-generated method stub

60

61 longelapsed,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是没有性能上的提升的。