mybatis-plus批量插入方式对比

在我们进行持久化数据操作时，新增数据不仅仅局限于单数据插入。会涉及到需要批量执行数据操作的业务，此时选用不同的批量插入方式会出现插入时间不同，今天我们针对mybatis插件做持久化时批量插入的四种方式做测试对比。

目录

使用标签foreach循环方式

使用 BatchExecutor 批处理执行器进行批量插入

for循环方式执行批量插入数据

MyBatis-Plus 通用IService中saveBatch方式

对比结果

---

• 使用标签foreach循环方式

foreach标签经常用于遍历集合，构建in条件语句或者批量操作语句。

下面是foreach标签的各个属性

属性	描述
collection	表示迭代集合的名称，可以使用@Param注解指定，该参数为必选 如下图所示
item	表示本次迭代获取的元素，若collection为List、Set或者数组，则表示其中的元素；若collection为map，则代表key-value的value，该参数为必选
open	表示该语句以什么开始，最常用的是左括弧’(’，注意:mybatis会将该字符拼接到整体的sql语句之前，并且只拼接一次，该参数为可选项
close	表示该语句以什么结束，最常用的是右括弧’)’，注意:mybatis会将该字符拼接到整体的sql语句之后，该参数为可选项
separator	mybatis会在每次迭代后给sql语句append上separator属性指定的字符，该参数为可选项
index	在list、Set和数组中,index表示当前迭代的位置，在map中，index代指是元素的key，该参数是可选项。

定义mapper.xml  

    <insert id="foreachToInsert" parameterType="java.util.List">
        insert into base_info (id,name,age,card_num,address,phone)
        values
        <foreach collection="res" item="item" index="index" separator=",">
            (#{item.id},#{item.name},#{item.age},#{item.cardNum},#{item.address},#{item.phone})
        </foreach>
    </insert>

业务层代码，对需要插入数据进行组装，具体批量插入数据时进行时间统计 

/**
     * foreachInsert单条sql方式执行批量插入数据
     * @param insertParam
     */
    public void foreachInsertData(InsertParam insertParam){
        List<BaseInfo> data = new ArrayList<>();
        for (int i = 0; i< insertParam.getSize(); i++){
            BaseInfo baseInfo = new BaseInfo();
            baseInfo.setId(snowflake.nextIdStr());
            baseInfo.setAge(i);
            baseInfo.setAddress("foreach"+i);
            baseInfo.setCardNum("123454"+i);
            baseInfo.setName("foreach"+i);
            baseInfo.setPhone("132433234"+i);
            data.add(baseInfo);
        }
        StopWatch stopWatch = new StopWatch("foreachInsert计时");
        stopWatch.start("foreachInsert计时");
        baseInfoMapper.foreachToInsert(data);
        stopWatch.stop();
        log.info(stopWatch.prettyPrint());
    }

这个方法提升批量插入速度的原理是，将传统的创建多条insert插入语句 合并为单条insert 多参数形式。如图：

INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");

合并为：

INSERT INTO `table1` (`field1`, `field2`) 
VALUES ("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2");

 理想情况下，这样可以在单个连接中一次性发送许多新行的数据，并将所有索引更新和一致性检查延迟到最后才进行。

这样的缺点是，数据库一般有一个默认的设置，就是每次sql操作的数据不能超过4M。这样插入，数据多的时候，数据库会报错Packet for query is too large (6071393 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.，虽然我们可以通过类似修改 my.ini 加上 max_allowed_packet =67108864，67108864=64M，默认大小4194304 也就是4M

修改完成之后要重启mysql服务，如果通过命令行修改就不用重启mysql服务。

完成本次操作，但是我们不能保证项目单次最大的大小是多少，这样是有弊端的。所以不推荐使用

• 使用 BatchExecutor 批处理执行器进行批量插入

业务层代码，对需要插入数据进行组装，具体批量插入数据时进行时间统计 

    @Autowired
    SqlSessionTemplate sqlSessionTemplate;


/**
     * batchInsert方式执行批量插入数据
     * @param insertParam
     */
    public void batchInsertData(InsertParam insertParam){
        SqlSessionFactory sqlSessionFactory = sqlSessionTemplate.getSqlSessionFactory();
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);
        BaseInfoMapper mapper = sqlSession.getMapper(BaseInfoMapper.class);
        List<BaseInfo> data = new ArrayList<>();
        for (int i = 0; i< insertParam.getSize() ; i++){
            BaseInfo baseInfo = new BaseInfo();
            baseInfo.setId(snowflake.nextIdStr());
            baseInfo.setAge(i);
            baseInfo.setAddress("地址"+i);
            baseInfo.setCardNum("123454"+i);
            baseInfo.setName("张兴"+i);
            baseInfo.setPhone("132433234"+i);
            data.add(baseInfo);
        }
        StopWatch stopWatch = new StopWatch("batchInsert计时");
        stopWatch.start("batchInsert计时");
        try {
            for (int i = 0;i<data.size();i++) {
                mapper.insert(data.get(i));
                if(i%insertParam.getCommitSize()== (insertParam.getCommitSize() - 1) || i==data.size()-1) {
                    sqlSession.commit();
                    sqlSession.clearCache();
                    log.info("提交步骤");
                }
            }
        }catch(Exception e) {
            e.printStackTrace();
            sqlSession.rollback();
        }finally {
            sqlSession.close();
            stopWatch.stop();
            log.info(stopWatch.prettyPrint());
        }
    }

 

可以看到batch模式重复使用已经预处理的语句 

Mybatis内置的ExecutorType有3种，默认的是simple单句模式，该模式下它为每个语句的执行创建一个新的预处理语句，单句提交sql；batch模式重复使用已经预处理的语句，并且批量执行所有语句，大批量模式下性能更优。

• 请注意batch模式在Insert操作时事务没有提交之前，是没有办法获取到自增的id，所以请根据业务情况使用。
• 如果需要使用 foreach来优化数据插入的话，需要将每次插入的记录控制在 10-100 左右是比较快的，建议每次100来分割数据，也就是分而治之思想。

• for循环方式执行批量插入数据

/**
     * foreach多条sql多连接方式执行批量插入数据
     * @param insertParam
     */
    public void foreachData(InsertParam insertParam){
        List<BaseInfo> data = new ArrayList<>();
        for (int i = 0; i< insertParam.getSize(); i++){
            BaseInfo baseInfo = new BaseInfo();
            baseInfo.setId(snowflake.nextIdStr());
            baseInfo.setAge(i);
            baseInfo.setAddress("foreachData"+i);
            baseInfo.setCardNum("123454"+i);
            baseInfo.setName("foreachData"+i);
            baseInfo.setPhone("132433234"+i);
            data.add(baseInfo);
        }
        StopWatch stopWatch = new StopWatch("foreachData计时");
        stopWatch.start("foreachData计时");
        for(BaseInfo baseInfo : data){
            baseInfoMapper.insert(baseInfo);
        }
        stopWatch.stop();
        log.info(stopWatch.prettyPrint());
    }

for循环执行sql和Mybatis内置的ExecutorType默认simple单句模式一样，都是为每个语句的执行创建一个新的预处理语句，单句提交sql

 

• MyBatis-Plus 通用IService中saveBatch方式

MyBatis-Plus除了通用的Mapper还是通用的Servcie层，这也减少了相对应的代码工作量，service层需要继承IService，当然实现层也要继承对应的实现类，如下：

public interface IDataManagerService extends IService<BaseInfo> {

}

@Service
public class DataManagerService extends ServiceImpl<BaseInfoMapper, BaseInfo> implements IDataManagerService {

}

具体业务逻辑：

 /**
     * iservice扩展接口执行批量插入数据
     * @param insertParam
     */
    public void extensionData(InsertParam insertParam){
        List<BaseInfo> data = new ArrayList<>();
        for (int i = 0; i< insertParam.getSize(); i++){
            BaseInfo baseInfo = new BaseInfo();
            baseInfo.setId(snowflake.nextIdStr());
            baseInfo.setAge(i);
            baseInfo.setAddress("extension"+i);
            baseInfo.setCardNum("123454"+i);
            baseInfo.setName("extension"+i);
            baseInfo.setPhone("132433234"+i);
            data.add(baseInfo);
        }
        StopWatch stopWatch = new StopWatch("extension计时");
        stopWatch.start( "extension计时");
        this.saveBatch(data,insertParam.getCommitSize());
        stopWatch.stop();
        log.info(stopWatch.prettyPrint());
    }

通过执行发现，IService的saveBatch执行和mybatis内置BatchExecutor的batch模式一样，通过重复使用已经预处理的语句，并且批量执行所有语句。

• 对比结果

通过执行对比不同数据量和批次下各方式执行时间对比如下：size为执行数据量，commitSize为一批次数据量

插入方式	执行数据参数	执行时间
标签foreach循环	size:1000	166ms
BatchExecutor批处理	size:1000 commitSize:100	3008ms
for循环方式	size:1000	4488ms
MyBatis-Plus 通用IService	size:1000 commitSize:100	2713ms
标签foreach循环	size:10000	662ms
BatchExecutor批处理	size:10000 commitSize:100	26808ms （commitSize为1000时28610ms）
for循环方式	size:10000	45026ms
MyBatis-Plus 通用IService	size:10000 commitSize:100	26757ms （commitSize为1000时28414ms）

分析执行数据量和时间可得标签foreach循环方式插入数据更快些，由于据库一般有一个默认的设置，就是每次sql操作的数据不能超过4M，在已知操作数据不会超过的前提下建议使用标签foreach去批量插入。在避免使用for循环方式外，其他方式可根据实际场景数据量情况选择对应批量方式。