MySQL驱动JDBC支不支持批量操作？测试结果

MySQL驱动JDBC支不支持批量操作？测试结果

关于JDBC支不支持批量操作，网上的答案各有各的说法，经过一翻测试，发现MySQL驱动JDBC确实不支持批量操作，如果需要其支持批量操作，需要在MySQL的连接url中加上rewriteBatchedStatements=true，加上后，无论MySQL是循环插入还是批量插入，均有改善

测试环境win7 32位系统 mysql5 JDBC驱动为mysql-connector-java-5.1.16-bin.jar，数据库表user为InnorDB

public static void test_mysql(){    
        String url="jdbc:mysql://localhost:3306/test";    
        String userName="root";    
        String password="123456";    
        Connection conn=null;    
try {          
              Class.forName("com.mysql.jdbc.Driver");          
              conn = (Connection)DriverManager.getConnection(url, userName, password);          
              conn.setAutoCommit(false);          
              String sql = "insert into user(username,password) values(?,?)";          
              PreparedStatement prest = (PreparedStatement)conn.prepareStatement(sql);         
long a=System.currentTimeMillis();    
for(int x = 0; x < 10000; x++){          
                 prest.setString(1, "username"+x);     
                 prest.setString(2, "password"+x);          
                 prest.execute();    
              }          
              conn.commit();          
long b=System.currentTimeMillis();    
              System.out.println("MySql not batch"+ (b-a)+" ms");    
        } catch (Exception ex) {    
            ex.printStackTrace();    
        }finally{    
try {    
if(conn!=null)conn.close();    
            } catch (SQLException e) {    
                e.printStackTrace();    
            }       
        }    
    }    
public static void test_mysql_batch(){    
        String url="jdbc:mysql://localhost:3306/test";    
        String userName="root";    
        String password="123456";    
        Connection conn=null;    
try {          
            Class.forName("com.mysql.jdbc.Driver");          
            conn = (Connection)DriverManager.getConnection(url, userName, password);          
            conn.setAutoCommit(false);
            String sql = "insert into user(username,password) values(?,?)";          
            PreparedStatement prest = (PreparedStatement)conn.prepareStatement(sql);
long a=System.currentTimeMillis();    
for(int x = 0; x < 100000; x++){          
                prest.setString(1, "username"+x);     
                prest.setString(2, "password"+x);          
                prest.addBatch();        
            }          
            prest.executeBatch();          
            conn.commit();          
long b=System.currentTimeMillis();    
            System.out.println("MySql batch"+ (b-a)+" ms");    
        } catch (Exception ex) {    
            ex.printStackTrace();    
        }finally{    
try {    
if(conn!=null)conn.close();    
            } catch (SQLException e) {    
                e.printStackTrace();    
            }       
        }
    }

 

打印结果如下

MySql not batch 13712 ms
MySql batch 13922 ms

 而当MySQL连接参数均加上rewriteBatchedStatements=true时，无论是单条execute执行还是executeBatch执行，均有改善，打印如下

MySql not batch 1470 ms
MySql batch 1930 ms

这似乎说明，JDBC的批量操作似乎并没有本质上的效率提高，要想其支持批量操作，还是得从MySQL客户端入手，为何会如此呢？看了一下JDBC源码后发现，addBatch就相当于将sql保存至列表中，executeBatch没有加rewriteBatchedStatements=true参数时，executeBatch还是将列表中的sql一条一条executeUpdate，为何要这样呢？

exechteBatch部分代码如下：

try {
                clearWarnings();

if (!this.batchHasPlainStatements
&& this.connection.getRewriteBatchedStatements()) {


if (canRewriteAsMultiValueInsertAtSqlLevel()) {
return executeBatchedInserts(batchTimeout);
                    }

if (this.connection.versionMeetsMinimum(4, 1, 0) 
&& !this.batchHasPlainStatements
&& this.batchedArgs != null 
&& this.batchedArgs.size() > 3 /* cost of option setting rt-wise */) {
return executePreparedBatchAsMultiStatement(batchTimeout);
                    }
                }

return executeBatchSerially(batchTimeout);
            } finally {
                clearBatch();
            }

 

当连接MySQL的url设置了rewriteBatchedStatements=true参数时，则会返回executeBatchedInserts(batchTimeout);而在此方法中，sql会编译成批量插入的形式处理

insert into user(username,password) values(?,?),(?,?)...

而如果没有加rewriteBatchedStatements=true，则会返回executeBatchSerially(batchTimeout);而在此方法中，是将此列表循环一条一条的executeUpdate，

for (batchCommandIndex = 0; batchCommandIndex < nbrCommands; batchCommandIndex++) {
                    Object arg = this.batchedArgs.get(batchCommandIndex);

if (arg instanceof String) {
                        updateCounts[batchCommandIndex] = executeUpdate((String) arg);
                    } else {
                        BatchParams paramArg = (BatchParams) arg;

try {
                            updateCounts[batchCommandIndex] = executeUpdate(
                                    paramArg.parameterStrings,
                                    paramArg.parameterStreams, paramArg.isStream,
                                    paramArg.streamLengths, paramArg.isNull, true);
    ....

 

这是何用意呢？看来以后用JDBC得多加注意

posted on 2013-03-26 09:24  liangge0218 阅读( ...) 评论( ...) 编辑 收藏

转载于:https://www.cnblogs.com/liangge0218/archive/2013/03/26/3292391.html