MySQL批量操作参数:rewriteBatchedStatements


要介绍MySQL的批量操作,就不得不介绍一下JDBC中的executeBatch()这个方法。毋庸置疑,数据库的批量操作会使数据库的执行效率大大提高。批处理(Statement)和预编译(PrepareStatement)的最大区别在于,前者不会预编译SQL,而后者会预编译SQL,创建并保存执行计划。因此,重复执行同一条SQL语句,PrepareStatement效率会提高很多。

现在要介绍批处理了,说白了就是批量执行多条SQL,前面提到的StatementPrepareStatement都可以进行批处理操作。批处理的优点在于,多条SQL一并发送给数据库,让数据库批量执行,而不是发送一条就执行一条。因此,在网络上的开销就会大大降低。

如果这样调用:

for(int i = 0; i < 1000; i++){  
    xxxDAO.insertData();  
}

则会连接数据库1000次,释放连接1000次,效率特别低。用批处理就可以提高效率,所有SQL语句都保存起来,一次连接,全部执行后,一次释放连接。 例:

public static void batchInsert() throws SQLException {  
    ……  
    String sql = "INSERT INTO USER(USERNAME, PASSWORD) VALUES(?, ?);  
    prepareStatement = conection.prepareStatement(sql);  
    for(int I = 0; I < 10000; i++) {
        ps.setString(1,”name”+i);
        ps.setString(2,”pwd”+i);
        ps.addBatch();
    }
    int[] num = prepareStatement.executeBatch();
    ……
    conection.commit();
    conection.close();
}

但是,MySQL的JDBC驱动在默认情况下会无视executeBatch()语句,把我们期望批量执行的一组SQL拆散,一条一条地发给MySQL数据库,直接造成较低的性能。

只有把rewriteBatchedStatements参数置为true,驱动才会帮你批量执行SQLjdbc:mysql://ip:port/db?rewriteBatchedStatements=true)。不过,驱动具体是怎么样批量执行的,你是不是需要看一下内幕,才敢放心地使用这个选项?下文会给出答案。

另外,有人说rewriteBatchedStatements只对INSERT有效,有人说它对UPDATE/DELETE也有效。为此我做了一些实验,结论是:这个选项对INSERT/UPDATE/DELETE都有效,只不过对INSERT它为会预先重排一下SQL语句。


rewriteBatchedStatements设置为false或不设置

未打开rewriteBatchedStatements时,根据wireshark嗅探出的MySQL报文可以看出,

batchDelete(10条记录)  =>  发送10次delete请求
batchUpdatebat(10条记录)  =>  发送10次update请求
batchInsertbatchI(10条记录)  =>  发送10次insert请求

也就是说,batchXXX()的确不起作用。

rewriteBatchedStatements设置为true

打开rewriteBatchedStatements后,根据wireshark嗅探出的mysql报文可以看出

batchDelete(10条记录) => 发送一次请求,内容为“delete from t where id = 1; delete from t where id = 2; delete from t where id = 3; …
batchUpdatebatch(10条记录) => 发送一次请求,内容为“update t set … where id = 1; update t set … where id = 2; update t set … where id = 3 …
batchInsert(10条记录) => 发送一次请求,内容为“insert into t (…) values (…) , (…), (…)

deleteupdate,驱动所做的事就是把多条sql语句累积起来再一次性发出去;而对于insert,驱动则会把多条sql语句重写成一条风格很酷的SQL,然后再发出去。 官方文档说,这种insert写法可以提高性能("This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements")。

注意事项

需要注意的是,即使rewriteBatchedStatements=truebatchDelete()batchUpdate()也不一定会走批量:当batchSize <= 3时,驱动会宁愿一条一条地执行SQL。所以,如果你想验证rewriteBatchedStatements在你的系统里是否已经生效,记得要使用较大的batch

最后可以看下对应的MySQLJDBC驱动的代码,以加深印象:

try {
    statementBegins();
    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) {
            return executePreparedBatchAsMultiStatement(batchTimeout);
        }
    }

    return executeBatchSerially(batchTimeout);
} finally {
    this.statementExecuting.set(false);
    clearBatch();
}



版权声明

本作品采用知识共享署名-非商业性使用 4.0 国际许可协议进行许可。 本站文章除注明转载/出处外,均为本站原创或翻译,转载前请务必署名。