mysql批量插入性能优化:executeBatch如何通过rewriteBatchedStatements参数逆袭

文章目录

前言

前面关于mybatis-plus的文章中提到过内置的批量插入方法saveBatch并不是真正的批量写入，而是通过executeBatch分批提交。所以我们通过sql注入器注入InsertBatchSomeColumn方法实现了insert的多值插入，提升了批量插入的性能。但其实还有更简单的优化方式，只通过添加一个参数，就能让采用executeBatch批量插入数据的性能实现逆袭。

这就是今天给大家介绍的

rewriteBatchedStatements

参数。

一、实战演示

项目工程依然采用之前mybatis-plus系列文章中的工厂。
这里我们通过在不添加rewriteBatchedStatements参数的前后采用executeBatch批量执行插入1万数据，并与InsertBatchSomeColumn方法进行对比。

1、单元测试

@TestpublicvoidtestBatchInsert(){System.out.println("----- batch insert method test ------");long startTime =System.currentTimeMillis();List<User> list =newArrayList<>();for(int i =0; i <10000; i++){User user =newUser();
            user.setName("test");
            user.setAge(13);
            user.setEmail("[email protected]");
            list.add(user);}
        userService.saveBatch(list);System.out.println("耗时:"+(System.currentTimeMillis()- startTime));}

saveBatch方法默认情况下，每次提交1000条sql。

saveBatch方法的底层实现是通过executeBatch批量执行sql

。

defaultbooleansaveBatch(Collection<T> entityList){returnthis.saveBatch(entityList,1000);}publicbooleansaveBatch(Collection<T> entityList,int batchSize){String sqlStatement =this.getSqlStatement(SqlMethod.INSERT_ONE);returnthis.executeBatch(entityList, batchSize,(sqlSession, entity)->{
         sqlSession.insert(sqlStatement, entity);});}

2、不添加rewriteBatchedStatements参数

属性配置：

spring.datasource.url = jdbc:mysql://localhost:3306/seckill?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true
spring.datasource.username = root
spring.datasource.password =123456

测试结果：

说明是一条insert语句插入一条记录。

插入10000条数据，耗时49646ms

3、添加rewriteBatchedStatements参数

在mysql的数据库连接参数中添加

rewriteBatchedStatements=true

spring.datasource.url = jdbc:mysql://localhost:3306/seckill?useUnicode=true&characterEncoding=utf8&useSSL=false&serverTimezone=GMT%2B8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&rewriteBatchedStatements=true
spring.datasource.username = root
spring.datasource.password =123456

执行结果：

添加

rewriteBatchedStatements=true

后，

executeBatch批量提交到mysql的sql语句还是一条insert语句插入一条记录

。

插入10000条数据耗时1289ms

，批量插入的效率得到大幅提升。

4、采用InsertBatchSomeColumn方法

这里我们只需要将UserServiceImpl中采用InsertBatchSomeColumn重写的saveBatch方法的注释放开即可。

@Service@Slf4jpublicclassUserServiceImplextendsServiceImpl<UserMapper,User>implementsUserService{@ResourceprivateUserMapper userMapper;/**
     *
     * @param entityList
     * @param batchSize
     * @return
     */@Override@Transactional(rollbackFor ={Exception.class})publicbooleansaveBatch(Collection<User> entityList,int batchSize){try{int size = entityList.size();int idxLimit =Math.min(batchSize, size);int i =1;//保存单批提交的数据集合List<User> oneBatchList =newArrayList<>();for(Iterator<User> var7 = entityList.iterator(); var7.hasNext();++i){User element = var7.next();
                oneBatchList.add(element);if(i == idxLimit){
                    userMapper.insertBatchSomeColumn(oneBatchList);//每次提交后需要清空集合数据
                    oneBatchList.clear();
                    idxLimit =Math.min(idxLimit + batchSize, size);}}}catch(Exception e){
            log.error("saveBatch fail",e);returnfalse;}returntrue;}}

执行单元测试：
可以看到，采用insertBatchSomeColumn方法进行的批量插入是采用了insert的多值插入，一条insert语句插入多条记录。这里每批插入1000条记录。
最终，插入10000条记录只话费了663ms

insertBatchSomeColumn方法由于底层并不是走的executeBatch批量提交sql，所以性能并不会受rewriteBatchedStatements参数的影响。

二、官方文档

Mysql官方文档：rewriteBatchedStatements

核心：

prepared statements for INSERT into multi-value inserts when executeBatch() is called

rewriteBatchedStatements选项默认是关闭的，

3.1.13

以后的mysql连接驱动都支持该配置。
如果开启该配置

rewriteBatchedStatements=true

，在调用 executeBatch() 批量执行 INSERT语句时，mysql内部会自动将批量提交的sql重写为insert多值插入再执行。

总结

本文主要介绍在采用executeBatch进行mysql批量数据插入时，通过在mysql连接信息中添加

rewriteBatchedStatements=true

使得执行效率大幅提升。
1、批量sql重写开关参数

rewriteBatchedStatements

默认是关闭的，mysql连接驱动器版本

3.1.13

以后支持该配置。
2、其底层原理是：

将通过executeBatch方法批量提交到mysql服务端的sql重写为insert多值插入再执行

。
3、通过测试发现，开启

rewriteBatchedStatements

后，采用executeBatch方法批量插入的性能已经接近InsertBatchSomeColumn真实insert多值批量写入。