MySQL Jdbc驱动在默认情况下会无视executeBatch()语句,把我们期望批量执行的一组sql语句拆散,一条一条地发给MySQL数据库,直接造成较低的性能。
只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL。打开rewriteBatchedStatements后,根据wireshark嗅探出的mysql报文可以看出:
batchDelete(10条记录) => 发送一次请求,内容为”delete from t where id = 1; delete from t where id = 2; delete from t where id = 3; ….”
batchUpdate(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.
代码demo
@Test
public void testInsertData() {
List<SysPost> sysPostList = new ArrayList<>();
SysPost sysPost = null;
for (int i = 0; i < 1000; i ++) {
sysPost = new SysPost();
sysPost.setPostCode("test_code_" + i);
sysPost.setPostName("test_name_" + i);
sysPost.setPostSort(i);
sysPost.setStatus(1);
sysPostList.add(sysPost);
}
long startTime = new Date().getTime();
sysPostService.saveBatch(sysPostList);
long endTime = new Date().getTime();
long l = endTime - startTime;
System.out.println("插入完成时间:" + endTime + ",用时:" + l);
}