背景:需要实现批量插入并且得到插入后的ID。
使用for循环进行insert这里就不说了,在海量数据下其性能是最慢的。数据量小的情况下,没什么区别。
【1】saveBatch(一万条数据总耗时:2478ms)
mybatisplus扩展包提供的:com.baomidou.mybatisplus.extension.service.IService#saveBatch(java.util.Collection<T>)
测试代码:
@Test
public void testBatch1(){
List<SysFile> list=new ArrayList<>();
list.add(new SysFile().setFileName("fiel1"));
list.add(new SysFile().setFileName("fiel2"));
list.add(new SysFile().setFileName("fiel3"));
list.add(new SysFile().setFileName("fiel4"));
list.add(new SysFile().setFileName("fiel5"));
list.add(new SysFile().setFileName("fiel6"));
fileService.saveBatch(list);
System.out.println(list);
}
我们分析其实现原理如下:com.baomidou.mybatisplus.extension.service.impl.ServiceImpl#saveBatch
@Transactional(rollbackFor = Exception.class)
@Override
public boolean saveBatch(Collection<T> entityList, int batchSize) {
String sqlStatement = sqlStatement(SqlMethod.INSERT_ONE);
int size = entityList.size();
executeBatch(sqlSession -> {
int i = 1;
for (T entity : entityList) {
sqlSession.insert(sqlStatement, entity);
if ((i % batchSize == 0) || i == size) {
sqlSession.flushStatements();
}
i++;
}
});
return true;
}
其实也就是一条条插入。
【2】集合方式foreach(一万条数据总耗时:474ms)
SysFileMapper 自定义方法batchSaveFiles
public interface SysFileMapper extends BaseMapper<SysFile> {
int batchSaveFiles(List<SysFile> entityList);
}
xml实现
<insert id="batchSaveFiles">
insert into tb_sys_file (file_name) values
<foreach collection="list" item="item" separator=",">
(#{item.fileName})
</foreach>
</insert>
测试代码:
@Test
public void testBatch2(){
List<SysFile> list=new ArrayList<>();
list.add(new SysFile().setFileName("fiel1"));
list.add(new SysFile().setFileName("fiel2"));
list.add(new SysFile().setFileName("fiel3"));
list.add(new SysFile().setFileName("fiel4"));
list.add(new SysFile().setFileName("fiel5"));
list.add(new SysFile().setFileName("fiel6"));
fileMapper.batchSaveFiles(list);
System.out.println(list);
}
测试结果:
注意:这种方式得不到ID哦!
【3】MyBatis-Plus提供的InsertBatchSomeColumn方法(一万条数据总耗时:690ms)
这里mybatisplus版本是3.3.0。
编写MySqlInjector
public class MySqlInjector extends DefaultSqlInjector {
@Override
public List<AbstractMethod> getMethodList(Class<?> mapperClass) {
List<AbstractMethod> methodList = super.getMethodList(mapperClass);
//更新时自动填充的字段,不用插入值
methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));
return methodList;
}
}
注入到配置类
@EnableTransactionManagement
@MapperScan({"com.enodeb.mapper"})
@Configuration
public class MybatisPlusConfig {
@Bean
public MySqlInjector sqlInjector() {
return new MySqlInjector();
}
}
SysFileMapper 自定义方法
public interface SysFileMapper extends BaseMapper<SysFile> {
int insertBatchSomeColumn(List<SysFile> entityList);
测试代码:
@Test
public void testBatch3(){
List<SysFile> list=new ArrayList<>();
list.add(new SysFile().setFileName("fiel1"));
list.add(new SysFile().setFileName("fiel2"));
list.add(new SysFile().setFileName("fiel3"));
list.add(new SysFile().setFileName("fiel4"));
list.add(new SysFile().setFileName("fiel5"));
list.add(new SysFile().setFileName("fiel6"));
fileMapper.insertBatchSomeColumn(list);
System.out.println(list);
}
测试结果
这里不仅实现了【2】的效果,还可以得到插入后的ID。
【4】假设一万条/十万条数据的情况下,执行时间是多少
策略 | 一万条 | 十万条 |
---|---|---|
方式一 | 2478ms | 20745ms |
方式二 | 474ms | 2904ms |
方式三 | 690ms | 8339ms |
① 方式一
@Test
public void testBatch1(){
long start=System.currentTimeMillis();
List<SysFile> list=new ArrayList<>();
SysFile sysFile;
for(int i=0;i<10000;i++){
sysFile=new SysFile();
sysFile.setFileName("file"+i);
list.add(sysFile);
}
fileService.saveBatch(list);
long end=System.currentTimeMillis();
System.out.println("一万条数据总耗时:"+(end-start)+"ms");
}
一万条数据总耗时:2478ms
十万条数据总耗时:20745ms
② 方式二
@Test
public void testBatch2(){
long start=System.currentTimeMillis();
List<SysFile> list=new ArrayList<>();
SysFile sysFile;
for(int i=0;i<10000;i++){
sysFile=new SysFile();
sysFile.setFileName("file"+i);
list.add(sysFile);
}
fileMapper.batchSaveFiles(list);
long end=System.currentTimeMillis();
System.out.println("一万条数据总耗时:"+(end-start)+"ms");
}
一万条数据总耗时:474ms
十万条数据总耗时:2904ms
③ 方式三
@Test
public void testBatch3(){
long start=System.currentTimeMillis();
List<SysFile> list=new ArrayList<>();
SysFile sysFile;
for(int i=0;i<10000;i++){
sysFile=new SysFile();
sysFile.setFileName("file"+i);
list.add(sysFile);
}
fileMapper.insertBatchSomeColumn(list);
long end=System.currentTimeMillis();
System.out.println("一万条数据总耗时:"+(end-start)+"ms");
}
一万条数据总耗时:690ms
十万条数据总耗时:8339ms
【5】百万条数据的情况下进行优化
方式二、方式三都是拼接为一条SQL,也就说有多少直接全部一次性插入,这就可能会导致最后的 sql 拼接语句特别长,超出了mysql 的限制。
这是什么意思呢?以MySQL为例,我们是需要考虑 max_allowed_packet 这个属性配置大小。其决定了你最大可以单次发送包的大小,这里可以修改为64M也就是 67108864。
但是这个不是最优解,最优解应该是控制每次插入的数量,比如一万条插入一次。
@Test
public void testBatch4(){
List<SysFile> list=new ArrayList<>();
SysFile sysFile;
for(int i=0;i<100000;i++){
sysFile=new SysFile();
sysFile.setFileName("file"+i);
list.add(sysFile);
}
//设置每批次插入多少条数据
int batchSize=10000;
int count = (list.size() + batchSize - 1) / batchSize; // 计算总批次数量,确保最后一个批次也能处理
//保存单批提交的数据集合
List<SysFile> oneBatchList = new ArrayList<>(batchSize); // 预分配容量
for (int i = 0; i < count; i++) {
int startIndex = i * batchSize;
int endIndex = Math.min(startIndex + batchSize, list.size());
oneBatchList.addAll(list.subList(startIndex, endIndex));
fileMapper.insertBatchSomeColumn(oneBatchList);
oneBatchList.clear(); // 清空集合以备下次循环使用
}
}
【TIPS】
为了确保批量插入的高效性,还需要进行一些配置和优化。例如,在application.yml中配置数据库连接时,可以开启MySQL的批处理模式【rewriteBatchedStatements=true】:
spring:
datasource:
url: jdbc:mysql://127.0.0.1:3306/testBtach?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai&rewriteBatchedStatements=true
username: root
password: 123456
driver-class-name: com.mysql.cj.jdbc.Driver
此外还可以考虑使用jdbcTemplate.batchUpdate、Spring Batch来实现(这两种未测试)。