MyBastis 三种批量插入方式的性能比较

数据库使用的是MySQL，JDK版本1.8，运行在SpringBoot环境下

本文章源代码：https://github.com/runbeyondmove/mybatis-batch-demo

对比3种可用的方式

1、反复执行单条插入语句
2、xml拼接sql
3、批处理执行

先说结论：少量插入请使用反复插入单条数据，方便。数量较多请使用批处理方式。（可以考虑以有需求的插入数据量20条左右为界吧，在我的测试和数据库环境下耗时都是百毫秒级的，方便最重要）。无论何时都不用xml拼接sql的方式。

1. xml映射文件中的代码

<insert id="insert" parameterType="top.spanrun.bootssm.model.UserInf" useGeneratedKeys="true" keyProperty="id">
        <!--
        @mbggenerated  generator自动生成，注意order的before和after
        -->
        <!--<selectKey keyProperty="id" order="AFTER" resultType="java.lang.Integer">
            SELECT LAST_INSERT_ID()
        </selectKey>-->
        insert into user_inf (id, uname, passwd, gentle, email, city)
        values (#{id,jdbcType=INTEGER}, #{uname,jdbcType=VARCHAR}, #{passwd,jdbcType=VARCHAR}, 
            #{gentle,jdbcType=VARCHAR}, #{email,jdbcType=VARCHAR}, #{city,jdbcType=VARCHAR}
            )
    </insert>
    <insert id="insertWithXML" parameterType="java.util.List" useGeneratedKeys="true" keyProperty="id">
        insert into user_inf (id, uname, passwd, gentle, email, city)
        values
        <foreach collection="list" item="user" index="index" separator=",">
            (#{user.id,jdbcType=INTEGER}, #{user.uname,jdbcType=VARCHAR}, #{user.passwd,jdbcType=VARCHAR},
            #{user.gentle,jdbcType=VARCHAR}, #{user.email,jdbcType=VARCHAR}, #{user.city,jdbcType=VARCHAR})
        </foreach>
    </insert>

2. Mapper接口

@Mapper
public interface UserInfMapper {

    int insert(UserInf record);

    int insertWithXML(@Param("list") List<UserInf> list);
}

3. Service实现，接口声明省略

@Service
public class UserInfServiceImpl implements UserInfService{
    private static final Logger LOGGER = LoggerFactory.getLogger(UserInfServiceImpl.class);

    @Autowired
    SqlSessionFactory sqlSessionFactory;

    @Autowired
    UserInfMapper userInfMapper;

    @Transactional
    @Override
    public boolean testInsertWithBatch(List<UserInf> list) {
        LOGGER.info(">>>>>>>>>>>testInsertWithBatch start<<<<<<<<<<<<<<");
        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
        UserInfMapper mapper = sqlSession.getMapper(UserInfMapper.class);

        long startTime = System.nanoTime();

        try {
            List<UserInf> userInfs = Lists.newArrayList();
            for (int i = 0; i < list.size(); i++) {
　　　　　　　　　　// 每1000条提交一次
                if ((i+1)%1000 == 0){
                    sqlSession.commit();
                    sqlSession.clearCache();
                }
                mapper.insert(list.get(i));
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            sqlSession.close();
        }

        LOGGER.info("testInsertWithBatch spend time:{}",System.nanoTime()-startTime);
        LOGGER.info(">>>>>>>>>>>testInsertWithBatch end<<<<<<<<<<<<<<");

        return true;
    }

    @Transactional
    @Override
    public boolean testInsertWithXml(List<UserInf> list) {
        LOGGER.info(">>>>>>>>>>>testInsertWithXml start<<<<<<<<<<<<<<");
        long startTime = System.nanoTime();

        userInfMapper.insertWithXML(list);

        LOGGER.info("testInsertWithXml spend time:{}",System.nanoTime()-startTime);
        LOGGER.info(">>>>>>>>>>>testInsertWithXml end<<<<<<<<<<<<<<");
        return true;
    }

    @Transactional
    @Override
    public boolean testInsertWithForeach(List<UserInf> list) {
        LOGGER.info(">>>>>>>>>>>testInsertWithForeach start<<<<<<<<<<<<<<");
        long startTime = System.nanoTime();

        for (int i = 0; i < list.size(); i++) {
            userInfMapper.insert(list.get(i));
        }
        LOGGER.info("testInsertWithForeach spend time:{}",System.nanoTime()-startTime);
        LOGGER.info(">>>>>>>>>>>testInsertWithForeach end<<<<<<<<<<<<<<");

        return true;
    }

    @Transactional
    @Override
    public boolean testInsert(UserInf userInf) {
        LOGGER.info(">>>>>>>>>>>testInsert start<<<<<<<<<<<<<<");
        long startTime = System.nanoTime();

        LOGGER.info("insert before,id=" + userInf.getId());

        userInfMapper.insert(userInf);

        LOGGER.info("insert after,id=" + userInf.getId());

        LOGGER.info("testInsert spend time:{}",System.nanoTime()-startTime);
        LOGGER.info(">>>>>>>>>>>testInsert end<<<<<<<<<<<<<<");

        return true;
    }
}

　

4. Controller控制器

@RestController
public class UserInfController {

    @Autowired
    UserInfService userInfService;

    @RequestMapping(value = "test/{size}/{type}")
    public void testInsert(@PathVariable(value = "size") Integer size,@PathVariable(value = "type") Integer type){
        System.out.println(">>>>>>>>>>>>type = " + type + "<<<<<<<<<<<<<");
        switch (type){
            case 1:
                userInfService.testInsertWithForeach(generateList(size));
                break;
            case 2:
                userInfService.testInsertWithXml(generateList(size));
                break;
            case 3:
                userInfService.testInsertWithBatch(generateList(size));
                break;
            default:
                UserInf userInf = new UserInf();
                userInf.setUname("user_single");
                userInf.setGentle("1");
                userInf.setEmail("123@123.com");
                userInf.setCity("广州市");
                userInf.setPasswd("123456");
                userInfService.testInsert(userInf);
        }

    }

    private List<UserInf> generateList(int listSize){
        List<UserInf> list = Lists.newArrayList();

        UserInf userInf = null;
        for (int i = 0; i < listSize; i++) {
            userInf = new UserInf();
            userInf.setUname("user_" + i);
            userInf.setGentle("1");
            userInf.setEmail("123@123.com");
            userInf.setCity("广州市");
            userInf.setPasswd("123456");
            list.add(userInf);
        }

        return list;
    }
}

　　

测试结果（单位是纳秒）：

1000
testInsertWithForeach spend time:431526521
testInsertWithXml     spend time:118772867
testInsertWithBatch   spend time:175602346

10000
testInsertWithForeach spend time:2072525050
testInsertWithXml     spend time:685605121
testInsertWithBatch   spend time:894647254

100000
testInsertWithForeach spend time:18950160161
testInsertWithBatch   spend time:8469312537


testInsertWithXml报错
### Cause: com.mysql.jdbc.PacketTooBigException: Packet for query is too large (9388970 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.
; Packet for query is too large (9388970 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.; nested exception is com.mysql.jdbc.PacketTooBigException: Packet for query is too large
 (9388970 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.] with root cause

com.mysql.jdbc.PacketTooBigException: Packet for query is too large (9388970 > 4194304). You can change this value on the server by setting the max_allowed_packet' variable.

　　查看xml sql拼接的异常信息，可以发现，最大只能达到4194304，也就是4M，所以这种方式不推荐

结论

循环插入单条数据虽然效率极低，但是代码量极少，如果在使用tk.Mapper的插件情况下，仅需代码,：

@Transactional
public void add1(List<Item> itemList) {
    itemList.forEach(itemMapper::insertSelective);
}

因此，在需求插入数据数量不多的情况下肯定用它了。

xml拼接sql是最不推荐的方式，使用时有大段的xml和sql语句要写，很容易出错，工作效率很低。更关键点是，虽然效率尚可，但是真正需要效率的时候你挂了，要你何用？

批处理执行是有大数据量插入时推荐的做法，使用起来也比较方便。

其他在使用中的补充：

1. 使用mybatis generator生成器生成中的一些坑

代码说明：数据库是MySQL，且主键自增，用generator 生成的mapper.xml中的代码，自增ID，使用的是selectKey来获取。

问题描述：insert的时候，添加的时候，第一条数据添加成功，接着添加第二条数据的时候会提示失败，失败的原因是ID还是使用的上一个ID值，主键重复导致插入失败。异常如下：

Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '4' for key 'PRIMARY'

问题原因：BEFORE还是AFTER的问题

<selectKey keyProperty="id" order="BEFORE" resultType="java.lang.Integer">
　　SELECT LAST_INSERT_ID()
</selectKey>

需要注意的是，Oracle使用before，MySQL使用after

其实在使用Mybatis generator生成带代码的时候可以通过identity="true"来指定生成的selectKey是before还是after

<generatedKey column="id" sqlStatement="Mysql" identity="true" />

注：在select标签中使用useGeneratedKeys="true" keyProperty="id" 不存在该问题。

2. mybatis的版本

升级Mybatis版本到3.3.1

3. 在批量插入的拼接xml sql时注意foreach是没有使用open和close的，但是在批量查询修改删除时才使用到open和close

<foreach collection="list" item="user" index="index" separator=",">
            (#{user.id,jdbcType=INTEGER}, #{user.uname,jdbcType=VARCHAR}, #{user.passwd,jdbcType=VARCHAR},
            #{user.gentle,jdbcType=VARCHAR}, #{user.email,jdbcType=VARCHAR}, #{user.city,jdbcType=VARCHAR})
        </foreach>

　　

 4. 使用批量提交注意的事项

　　a. 事务

　　　　由于在 Spring 集成的情况下，事务连接由 Spring 管理（SpringManagedTransaction），所以这里不需要手动关闭 sqlSession，在这里手动提交（commit）或者回滚（rollback）也是无效的。

 　　b. 批量提交

　　　　批量提交只能应用于 insert, update, delete。

　　　　并且在批量提交使用时，如果在操作同一SQL时中间插入了其他数据库操作，就会让批量提交方式变成普通的执行方式，所以在使用批量提交时，要控制好 SQL 执行顺序