在工作中经常遇到批量新增数据到数据库的业务，我们都知道大数据量操作时，批量操作会带来性能的提升，但选择哪种批量操作会更快呢？带着疑问，我从以下两种方案入手，探究数据库批量操作的性能问题。
测试方案
1、使用PreparedStatement预编译SQL功能，循环操作
2、生成批量插入SQL，单次操作
注：以下为了简便，使用方案一和方案二代替描述
测试环境
本地数据库，规避网络IO耗时问题。
测试数据量
10000条用户模拟数据
预测结果
方案一优于方案二
表结构
图片.png
测试代码
方案一
@Override
@Transactional(rollbackFor = Exception.class)
public void foreachInsert() {
long startTime = System.currentTimeMillis();
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
UserMapper mapper = sqlSession.getMapper(UserMapper.class);
for (User user : users) {
mapper.save(user);
}
sqlSession.commit();
long endTime = System.currentTimeMillis();
log.info("单条插入总耗时 {} ms", (endTime - startTime));
}
<insert id="save">
insert into user(`name`, age, email) values(#{name},#{age},#{email})
</insert>
解析：该方案使用PreparedStatement的预编译功能，可以减少SQL处理时间，包括SQL语句的解析、语法检查以及生成代码等操作。一般来说，处理时间要比执行SQL的时间长，从而提高效率。
方案二
long startTime = System.currentTimeMillis();
userMapper.saveBatch(users);
long endTime = System.currentTimeMillis();
log.info("批量插入总耗时 {} ms", (endTime - startTime));
<insert id="saveBatch">
insert into user(`name`, age, email) values
<foreach collection="users" item="user" separator=",">
(#{user.name},#{user.age},#{user.email})
</foreach>
</insert>
解析：该方案使用整合SQL的，达到批量处理的目的，减少了网络IO次数，提升了性能。但本例在本地数据库测试，这里不探讨网络IO影响。
测试结果
2022-09-21 20:36:07.429 INFO 12484 --- [nio-8080-exec-2] c.c.m.service.impl.UserServiceImpl : 单条插入总耗时 311 ms
2022-09-21 20:36:11.657 INFO 12484 --- [nio-8080-exec-1] c.c.m.service.impl.UserServiceImpl : 批量插入总耗时 410 ms
2022-09-21 20:37:30.958 INFO 11844 --- [nio-8080-exec-2] c.c.m.service.impl.UserServiceImpl : 单条插入总耗时 310 ms
2022-09-21 20:37:32.406 INFO 11844 --- [nio-8080-exec-1] c.c.m.service.impl.UserServiceImpl : 批量插入总耗时 418 ms
由此可以看出测试结果也符合预期。
这里需要注意：数据库连接需要多配置一个参数rewriteBatchedStatements=true，没有配置的话测试结果将不满足预期。因为MySQL连接驱动默认情况下会将我们方案一中SQL一条条发给数据库，批量插入变为单条插入。
总结
在不考虑网络IO的情况下，使用PreparedStatement预编译SQL功能，循环操作 快于 生成批量插入SQL，单次操作。
拓展
Mybatis Plus批量操作方法saveBatch是如何操作的性能如何？我们看看源码：
public static <E> boolean executeBatch(Class<?> entityClass, Log log, Collection<E> list, int batchSize, BiConsumer<SqlSession, E> consumer) {
Assert.isFalse(batchSize < 1, "batchSize must not be less than one");
return !CollectionUtils.isEmpty(list) && executeBatch(entityClass, log, sqlSession -> {
int size = list.size();
int idxLimit = Math.min(batchSize, size);
int i = 1;
for (E element : list) {
consumer.accept(sqlSession, element);
if (i == idxLimit) {
sqlSession.flushStatements();
idxLimit = Math.min(idxLimit + batchSize, size);
}
i++;
}
});
}
解析：先通过数据分片（防止数据超出MySQL限制同时在解析长SQL时也会非常耗时），默认每次1000条数据，然后将数据一条条插入，这里sqlSession我们继续进入方法executeBatch(Class<?> entityClass, Log log, Consumer<SqlSession> consumer)方法
SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
consumer.accept(sqlSession);
sqlSession.commit(!transaction);
return true;
}
可以看出它的做法与我们方案一思路类似，sqlsession是一个特殊批处理session。但在耗时方面，如果数据量大于1000的话，方案一的耗时会少些。
一直在追求思路的传递而非代码的COPY
网友评论