mybatis 插入大量数据效率对比，foreach插入、SqlSession批量插入、sql插入

﹏ヽ暗。殇╰゛Y 2023-03-13 13:48 172阅读 0赞

使用mybatis插入数据执行效率对比，对比三种方式（测试数据库为MySQL），

1 使用 SqlSessionFactory，每一批数据执行一次提交

2 使用mybatis-plus框架的insert方法，for循环，每次执行一次插入

3 使用ibatis，纯sql插入

新增xml执行效率测试：xml执行时间比sql稍慢一些，50000条数据插入时间约为2000毫秒左右，平均时间是sql的2倍左右。

先贴出执行效果（数字代表执行的时间，单位毫秒）：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70][]

测试代码：

//测试类
    @RunWith(SpringRunner.class)
    @SpringBootTest(classes = DemoApplication.class)
    public class Test1 {
    
        @Autowired
        UsersMapper usersMapper;
    
        @Autowired
        SqlSessionFactory sqlSessionFactory;
        public List<Users> list = new ArrayList<>();
    
        @Before
        public void  getList() {
            long start = System.currentTimeMillis();
            Users user;
            for (int i = 1; i <=50000 ; i++) {
                user = new Users();
                user.setId(i);
                user.setName("java");
                user.setAge(200);
                user.setManagerId(222);
                list.add(user);
            }
            System.out.println("拼装数据 耗时："+(System.currentTimeMillis()-start));
            System.out.println(list.size());
        }
    
    
        @Test
        public void batchInsert() {
            SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
            UsersMapper mapper = sqlSession.getMapper(UsersMapper.class);
            System.out.println("batchInsert 插入开始========");
            long start = System.currentTimeMillis();
            for (int i = 0; i < list.size(); i++) {
                mapper.insert(list.get(i));
                if (i%5000==4999) {
                    sqlSession.flushStatements();
    //                sqlSession.commit();
    //                sqlSession.clearCache();
                }
            }
    //        sqlSession.commit();
    //        sqlSession.clearCache();
            sqlSession.flushStatements();
            System.out.println("SqlSession 批量插入耗时："+(System.currentTimeMillis()-start));
        }
    
        @Test
        public void forEachInsert() {
            System.out.println("forEachInsert 插入开始========");
            long start = System.currentTimeMillis();
            for (int i = 0; i < list.size(); i++) {
                usersMapper.insert(list.get(i));
            }
            System.out.println("foreach 插入耗时："+(System.currentTimeMillis()-start));
        }
    
        @Test
        public void sqlInsert() {
            System.out.println("sql 插入开始========");
            long start = System.currentTimeMillis();
            usersMapper.sqlInsert(list);
            System.out.println("sql 插入耗时："+(System.currentTimeMillis()-start));
        }
    //xml批量插入
    
        @Test
        public void xmlInsert() {
            System.out.println("xmlInsert 批量插入开始========");
            long start = System.currentTimeMillis();
            usersMapper.xmlBatchInsert(list);
            System.out.println("xmlInsert 批量插入耗时："+(System.currentTimeMillis()-start));
        }
    
    }
    
    //sql插入相关类
    @Repository
    public interface UsersMapper extends BaseMapper<Users> {
    
        @InsertProvider(type = UsersProvider.class, method = "insertListSql")
        public void sqlInsert(List<Users> list);
    
        public void xmlBatchInsert(@Param("list") List<Users> list);
    }
    
    public class UsersProvider {
    
        public String insertListSql(List<Users> list) {
            StringBuffer sqlList = new StringBuffer();
    
            sqlList.append(" INSERT INTO users(id,name,age,manager_id)  VALUES ");
            for (int i = 0; i < list.size() ; i++) {
                Users user = list.get(i);
                sqlList.append(" (").append(user.getId()).append(",").append("'").append(user.getName()).append("',").append(user.getAge())
                        .append(",").append(user.getManagerId()).append(")");
                if (i < list.size()-1) {
                    sqlList.append(",");
                }
            }
            return sqlList.toString();
        }
    }
    
    //xml 插入mapper文件
    
    <?xml version="1.0" encoding="UTF-8"?>
    <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.example.demo.dao.UsersMapper">
    
      <insert id="xmlBatchInsert">
        INSERT INTO users(id,name,age,manager_id)  VALUES
        <foreach collection="list" item="item" index="index" separator=",">
          (#{item.id},
          #{item.name},
          #{item.age},
          #{item.managerId})
        </foreach>
      </insert>
    </mapper>

总结：

sql插入的效率最高，sqlsession次之，mybatis框架foreach插入效率最低。

执行效率echarts图：

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70 1][]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70]: /images/20230312/c79cf74256e74287b9da766e15451c91.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2NoZW5waW5nMTk5Mw_size_16_color_FFFFFF_t_70 1]: /images/20230312/dc13f499a316450d812bec4951368671.png