MySQL批量操作参数:rewriteBatchedStatements
要介绍MySQL的批量操作,就不得不介绍一下JDBC
中的executeBatch()
这个方法。毋庸置疑,数据库的批量操作会使数据库的执行效率大大提高。批处理(Statement
)和预编译(PrepareStatement
)的最大区别在于,前者不会预编译SQL
,而后者会预编译SQL
,创建并保存执行计划。因此,重复执行同一条SQL
语句,PrepareStatement
效率会提高很多。
现在要介绍批处理了,说白了就是批量执行多条SQL
,前面提到的Statement
和PrepareStatement
都可以进行批处理操作。批处理的优点在于,多条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
,驱动才会帮你批量执行SQL
(jdbc:mysql://ip:port/db?rewriteBatchedStatements=true
)。不过,驱动具体是怎么样批量执行的,你是不是需要看一下内幕,才敢放心地使用这个选项?下文会给出答案。
另外,有人说rewriteBatchedStatements
只对INSERT
有效,有人说它对UPDATE/DELETE
也有效。为此我做了一些实验,结论是:这个选项对INSERT/UPDATE/DELETE
都有效,只不过对INSERT它为会预先重排一下SQL
语句。
rewriteBatchedStatements设置为false或不设置
未打开rewriteBatchedStatements
时,根据wireshark嗅探出的MySQL报文可以看出,
batchDelete
(10条记录) => 发送10次delete
请求batchUpdate
bat(10条记录) => 发送10次update
请求batchInsert
batchI(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; …
”batchUpdate
batch(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 (…) , (…), (…)
”
对delete
和update
,驱动所做的事就是把多条sql
语句累积起来再一次性发出去;而对于insert
,驱动则会把多条sql
语句重写成一条风格很酷的SQL
,然后再发出去。 官方文档说,这种insert
写法可以提高性能("This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements")。
注意事项
需要注意的是,即使rewriteBatchedStatements=true
,batchDelete()
和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 国际许可协议进行许可。 本站博文除注明转载/出处外,均为本站原创或翻译,转载前请务必署名。