问题说明
在使用IService.savebatch
方法批量插入数据时,观察控制台打印的Sql发现并没有像预想的一样,而是以逐条方式进行插入,插1000条数据就得10s多,正常假如批量插入应该是一条语句:
insert table (field1, field2) values (val1, val2), (val3, val4), (val5, val6), ... ;
而我的是这样:
insert table (field1, field2) values (val1, val2);
insert table (field1, field2) values (val3, val4);
...
那肯定很慢
从源码入手:
1、ServiceImpl.java
public boolean saveBatch(Collection<T> entityList, int batchSize) {
String sqlStatement = this.sqlStatement(SqlMethod.INSERT_ONE);
return this.executeBatch(entityList, batchSize, (sqlSession, entity) -> {
sqlSession.insert(sqlStatement, entity);
});
}
protected <E> boolean executeBatch(Collection<E> list, int batchSize, BiConsumer<SqlSession, E> consumer) {
Assert.isFalse(batchSize < 1, "batchSize must not be less than one", new Object[0]);
return !CollectionUtils.isEmpty(list) && this.executeBatch((sqlSession) -> {
int size = list.size();
int i = 1;
for(Iterator var6 = list.iterator(); var6.hasNext(); ++i) {
E element = var6.next();
consumer.accept(sqlSession, element);
if (i % batchSize == 0 || i == size) {
sqlSession.flushStatements();
}
}
});
}
2、MybatisBatchExecutor.java
public int doUpdate(MappedStatement ms, Object parameterObject) throws SQLException {
Configuration configuration = ms.getConfiguration();
StatementHandler handler = configuration.newStatementHandler(this, ms, parameterObject, RowBounds.DEFAULT, (ResultHandler)null, (BoundSql)null);
BoundSql boundSql = handler.getBoundSql();
String sql = boundSql.getSql();
Statement stmt;
if (sql.equals(this.currentSql) && ms.equals(this.currentStatement)) {
int last = this.statementList.size() - 1;
stmt = (Statement)this.statementList.get(last);
this.applyTransactionTimeout(stmt);
handler.parameterize(stmt);
BatchResult batchResult = (BatchResult)this.batchResultList.get(last);
batchResult.addParameterObject(parameterObject);
} else {
Connection connection = this.getConnection(ms.getStatementLog());
stmt = handler.prepare(connection, this.transaction.getTimeout());
if (stmt == null) {
return 0;
}
handler.parameterize(stmt);
this.currentSql = sql;
this.currentStatement = ms;
this.statementList.add(stmt);
this.batchResultList.add(new BatchResult(ms, sql, parameterObject));
}
handler.batch(stmt);
return -2147482646;
}
一顿Step Into后进入了这个doUpdate方法,看了一下,if体内的应该就是批量拼接sql的关键,走了几个循环发现我的代码都是从else体里走了,那他为什么不进if呢,看了下判断条件,每次进来。statement都是一个,那问题就出在sql.equals(currentSql)
上面,我比对了下第二个实体的sql和第一个实体的sql,很快就发现了问题,他们竟然不!一!样!。
原因是在拼接insert语句时,如果实体的某个属性值为空,那他将不参与拼接,所以如果你的数据null值比较多且比较随机的分布在各个属性上,那生成出来的sql就会不一样,也就没法走批处理逻辑了
实体属性为null时,会影响生成的插入sql,进而影响批量保存逻辑
解决方案
定位到了问题,那就也便于解决了,问题原因是生成插入sql时,对null值的处理策略造成的
- 全局配置insertStrategy为IGNORED
mybatis-plus: mapper-locations: classpath*:/mapper/**/*.xml #实体扫描,多个package用逗号或者分号分隔 typeAliasesPackage: com.ts.core.modules.*.entity,com.ts.dataexchange.modules.*.entity global-config: #数据库相关配置 db-config: #主键类型 AUTO:"数据库ID自增", INPUT:"用户输入ID", ID_WORKER:"全局唯一ID (数字类型唯一ID)", UUID:"全局唯一ID UUID"; id-type: AUTO logic-delete-value: -1 logic-not-delete-value: 0 #在 insert 的时候的字段验证策略 insert-strategy: ignored
- 为可能受影响的属性添加注解
@TableField(insertStrategy = FieldStrategy.IGNORED) private String content;
- 自己重写个批量保存方法,自己写xml拼接sql,简单粗暴(小心sql超出最大长度)
<insert id="insertBatch" parameterType="java.util.List"> insert into table_name (id,code,name,content) VALUES <foreach collection ="list" item="entity" index= "index" separator =","> ( #{entity.id}, #{entity.code}, #{entity.name}, #{entity.content} ) </foreach> </insert>