MyBatis 批量插入foreach性能问题

方式一：mybatis的foreach语句（默认执行器类型simple）

<insert id="batchInsert" parameterType="java.util.List">
        INSERT INTO
        user_info(name,id)
        VALUES
        <foreach collection="list" item="item" index="index" separator="," >
            (
            #{item.name}
            #{item.id}
        </foreach>
    </insert>

这种方式提升插入速度的原理是将传统的：

insert into user_info (name,id) values (name,id);
insert into user_info (name,id) values (name,id);
insert into user_info (name,id) values (name,id);
insert into user_info (name,id) values (name,id);

转化为：

insert into user_info (name,id) 
values (name,id),values (name,id) ,values (name,id),values (name,id);

理想情况下，这样可以在单个连接中一次性发送许多新的数据，并将所有索引更新和一致性检查延迟到最后才进行，但是此种方式在表的列数较多（20+），以及一次性插入的行数较多（5000+）时，整体插入的耗时十分漫长

Internally, it still generates the same single insert statement with many placeholders as the JDBC code above.

MyBatis has an ability to cache PreparedStatement, but this statement cannot be cached because it contains  element and the statement varies depending on the parameters. As a result, MyBatis has to 1) evaluate the foreach part and 2) parse the statement string to build parameter mapping [1] on every execution of this statement.

And these steps are relatively costly process when the statement string is big and contains many placeholders.

[1] simply put, it is a mapping between placeholders and the parameters.

 从上述资料可知，耗时就耗在，foreach后有5000+个values，所以这个PreparedStatement特别长，包含了很多占位符，对于占位符和参数的映射尤其耗时。并且，查阅相关资料可知，values的增长与所需的解析时间，是呈指数型增长的

所以如果非要使用foreach的方式来插入数据的话，建议一次性插入20~50的数据量是比较合适的，性能可以得到保障

2、使用sqlSessionFactory自己开启session

数据量大时推荐此种方式 ExecutorType.BATCH

SqlSession sqlSession = shardingSqlSessionFactory.openSession( ExecutorType.BATCH,false);
try {
        // 如要插入的业务数据
        List limitDetailDOList = new ArrayList();
        LimitDetailMapper mapper = sqlSession.getMapper( LimitDetailMapper.class );
        limitDetailDOList.forEach(mapper ::insert);
        sqlSession.commit();
} finally {
         sqlSession.close();
}
 
 
// mapper 接口
public interface LimitDetailMapper {
 
   int insert(LimitDetailDO limitDetailDO);
 
}