文章目录
- 现状
- 优化
- 效果
- 报错
现状
一般来说,批量插入可以使用 MyBatisPlus 中 ServiceImpl 自带的方法 saveBatch
打开 sql 日志,application.yml 添加配置,mapper-locations 配置 mapper 路径
mybatis-plus:configuration:log-impl: org.apache.ibatis.logging.stdout.StdOutImpl #开启sql日志mapper-locations: classpath*:mapper/**/*Mapper.xml
可以发现插入是在同一个 SqlSession,但并不是理想中的批量插入
它的插入算法我没有细究,但从日志观察可以看出它的插入条数是无序的,如果可以一次插入全部,效率应该更高
优化
MyBatisPlus 预留了 insertBatchSomeColumn 方法,可以实现批量插入,下面介绍一下如何配置
- MyBatisPlus 依赖
<dependency><groupId>com.baomidou</groupId><artifactId>mybatis-plus-boot-starter</artifactId><version>3.5.2</version></dependency>
- 新建 Sql 注射器 BatchSqlInjector
import com.baomidou.mybatisplus.annotation.FieldFill;import com.baomidou.mybatisplus.core.injector.AbstractMethod;import com.baomidou.mybatisplus.core.injector.DefaultSqlInjector;import com.baomidou.mybatisplus.core.metadata.TableInfo;import com.baomidou.mybatisplus.extension.injector.methods.InsertBatchSomeColumn;import java.util.List;public class BatchSqlInjector extends DefaultSqlInjector {@Overridepublic List<AbstractMethod> getMethodList(Class<" />> mapperClass, TableInfo tableInfo) {List<AbstractMethod> methodList = super.getMethodList(mapperClass, tableInfo);methodList.add(new InsertBatchSomeColumn(i -> i.getFieldFill() != FieldFill.UPDATE));return methodList;}}
- MybatisPlusConfig 配置 BatchSqlInjector Bean,可忽略这里配置的分页插件
import com.baomidou.mybatisplus.annotation.DbType;import com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor;import com.baomidou.mybatisplus.extension.plugins.inner.BlockAttackInnerInterceptor;import com.baomidou.mybatisplus.extension.plugins.inner.PaginationInnerInterceptor;import org.springframework.context.annotation.Bean;import org.springframework.context.annotation.Configuration;import org.springframework.transaction.annotation.EnableTransactionManagement;@EnableTransactionManagement@Configurationpublic class MybatisPlusConfig {/** * 分页插件 * * @return */ @Beanpublic MybatisPlusInterceptor mybatisPlusInterceptor() {MybatisPlusInterceptor interceptor = new MybatisPlusInterceptor();interceptor.addInnerInterceptor(new BlockAttackInnerInterceptor());PaginationInnerInterceptor pageInterceptor = new PaginationInnerInterceptor(DbType.MYSQL);pageInterceptor.setMaxLimit(500L);pageInterceptor.setOptimizeJoin(true);interceptor.addInnerInterceptor(pageInterceptor);return interceptor;}/** * 批量插入 * * @return */@Beanpublic BatchSqlInjector easySqlInjector() {return new BatchSqlInjector();}}
- 配置 BatchBaseMapper 继承 BaseMapper
import com.baomidou.mybatisplus.core.mapper.BaseMapper;import java.util.Collection;public interface BatchBaseMapper<T> extends BaseMapper<T> {/** * 批量插入 仅适用于mysql * * @param entityList 实体列表 * @return 影响行数 */Integer insertBatchSomeColumn(Collection<T> entityList);}
- 业务 Mapper 继承 BatchBaseMapper
@Repositorypublic interface ISapCustomerMapper extends BatchBaseMapper<SapCustomerPO> {}
- service 创建 createBatch 作为新的批量插入方法
public class SapCustomerServiceImpl extends ServiceImpl<ISapCustomerMapper, SapCustomerPO> {void createBatch(List<SapCustomerPO> entityList) {if(!entityList.isEmpty()){baseMapper.insertBatchSomeColumn(entityList);}}}
注意:不建议直接用 mapper 的 insertBatchSomeColumn 方法,因为当 entityList 为空时会报错
其实就是 INSERT INTO 表名(字段1,字段2,字段3) VALUES 后面为空
NestedRuntimeException:You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 1
效果
3600 条数据
优化前:2058 毫秒
优化后:1293 毫秒
15000 条数据
优化前:8958 毫秒
优化后:2037 毫秒
可以看出,数据越多,优化效果越明细
通过这次测试发现,打开 sql 日志后,会明细拖慢 sql 执行效率,数据越多越明细
报错
Packet for query is too large (82,807,536 > 67,108,864). You can change this value on the server by setting the max_allowed_packet’ variable
原因: 一次插入数量太多的数据,超出了 mysql 默认设置
解决办法: 在 service 层限制插入数量
static int batchSize = 10000;public void createBatch(List<TestPO> entityList) {if (!entityList.isEmpty()) {int size = entityList.size();int idxLimit = Math.min(batchSize, size);int i = 1;List<TestPO> oneBatchList = new ArrayList<>();for (Iterator<TestPO> var7 = entityList.iterator(); var7.hasNext(); ++i) {TestPOelement = var7.next();oneBatchList.add(element);if (i == idxLimit) {baseMapper.insertBatchSomeColumn(oneBatchList);oneBatchList.clear();idxLimit = Math.min(idxLimit + batchSize, size);}}}}