项目中有很多批处理，mysql提供了一个参数rewriteBatchedStatements，可以实现高性能的批量插入
MySQL的JDBC连接的url中要加rewriteBatchedStatements参数，并保证5.1.13以上版本的驱动，才能实现高性能的批量插入。
MySQL JDBC驱动在默认情况下会无视executeBatch()语句，把我们期望批量执行的一组sql语句拆散，一条一条地发给MySQL数据库，批量插入实际上是单条插入，直接造成较低的性能。
只有把rewriteBatchedStatements参数置为true, 驱动才会帮你批量执行SQL另外这个选项对INSERT/UPDATE/DELETE都有效
验证
- 准备一个表
CREATE TABLE `t_user` (
`id` bigint(20) NOT NULL,
`user_name` varchar(45) DEFAULT NULL,
`password` varchar(45) DEFAULT NULL,
`nick_name` varchar(45) DEFAULT NULL,
`id_card` varchar(45) DEFAULT NULL,
`create_time` datetime DEFAULT NULL,
`update_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
- 代码，批量插入100000条数据
@GetMapping("/save")
public String save() {
List<User> users = new ArrayList<>();
Random random = new Random();
StopWatch stopWatch = new StopWatch("test");
stopWatch.start("create user");
for (int i = 0; i < 100000; i++) {
User user = new User();
user.setNickName("haha"+i);
user.setIdCard("4304811992"+random.nextInt(10000)+random.nextInt(10000));
user.setPassword(random.nextInt(10000)+"");
user.setCreateTime(new Timestamp(System.currentTimeMillis()));
user.setUpdateTime(new Timestamp(System.currentTimeMillis()));
user.setUserName("xiaohei"+i);
users.add(user);
}
stopWatch.stop();
stopWatch.start("saveBatch");
userService.saveBatch(users);
stopWatch.stop();
System.out.println(stopWatch.prettyPrint());
return "新增用户成功";
}
1. 无rewriteBatchedStatements参数
项目中数据源配置如下，不添加rewriteBatchedStatements参数
spring:
# 配置数据源
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true
username: root
password: root
执行结果
StopWatch 'test': running time = 86006762062 ns
---------------------------------------------
ns % Task name
---------------------------------------------
201687212 000% create user
85805074850 100% saveBatch
2. 有rewriteBatchedStatements参数
项目中数据源配置添加rewriteBatchedStatements参数
spring:
# 配置数据源
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/demo?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true
username: root
password: root
再次运行
StopWatch 'test': running time = 25266180502 ns
---------------------------------------------
ns % Task name
---------------------------------------------
227148131 001% create user
25039032371 099% saveBatch
结论
jdbc的连接串中使用rewriteBatchedStatements参数，批处理性能提升3倍。
