Mybatis批量插入的4种方式以及效率对比

分别测试500条，5000条，50000条

    @Autowired
    UserMapper usersMapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;
    public List list = new ArrayList<>();
    
    @org.junit.Test
    public void batchInsert() {
        long start = System.currentTimeMillis();
        User user;
        for (int i = 1; i <=5000 ; i++) {
            user = new User();
            user.setName("java");
            user.setAge(200);
            user.setManagerId(222);
            list.add(user);
        }
        System.out.println("拼装数据 耗时："+(System.currentTimeMillis()-start));
        System.out.println(list.size());

        SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH,false);
        UserMapper mapper = sqlSession.getMapper(UserMapper.class);

        System.out.println("batchInsert 插入开始========");
        long start1 = System.currentTimeMillis();
        for (int i = 0; i < list.size(); i++) {
            mapper.insert(list.get(i));
            if (i%1000==0) {
                sqlSession.flushStatements();
            }
        }
        sqlSession.flushStatements();
        System.out.println("SqlSession 批量插入耗时："+(System.currentTimeMillis()-start1));
    }

500条，批量插入耗时：632ms

5000条，批量插入耗时：1039ms

50000条，批量插入耗时：2891ms

  
   @Autowired
    UserMapper usersMapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;
    public List list = new ArrayList<>();
    
    @org.junit.Test
    public void sqlInsert() {
        long start = System.currentTimeMillis();
        User user;
        for (int i = 1; i <=50000 ; i++) {
            user = new User();
            user.setName("java");
            user.setAge(200);
            user.setManagerId(222);
            list.add(user);
        }
        System.out.println("拼装数据 耗时："+(System.currentTimeMillis()-start));
        System.out.println(list.size());

        System.out.println("sql 插入开始========");
        long start1 = System.currentTimeMillis();
        usersMapper.sqlInsert(list);
        System.out.println("sql 插入耗时："+(System.currentTimeMillis()-start1));
    }

@Mapper
@Repository
public interface UserMapper extends baseMapper {
    @InsertProvider(type = UsersProvider.class, method = "insertListSql")
    void sqlInsert(List list);
    
    void xmlBatchInsert(@Param("list") List list);
}

public class UsersProvider {
    public String insertListSql(List list) {
        StringBuffer sqlList = new StringBuffer();

        sqlList.append(" INSERT INTO t_user(name,age,manager_id)  VALUES ");
        for (int i = 0; i < list.size() ; i++) {
            User user = list.get(i);
            sqlList.append(" (").append("'").append(user.getName()).append("',").append(user.getAge())
                    .append(",").append(user.getManagerId()).append(")");
            if (i < list.size()-1) {
                sqlList.append(",");
            }
        }
        return sqlList.toString();
    }
}

500条，插入耗时：330ms

5000条，插入耗时：559ms

50000条，插入耗时：1346ms

 @Autowired
    UserMapper usersMapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;
    public List list = new ArrayList<>();
 
    @org.junit.Test
    @Transactional
    public void forEachInsert() {
        long start = System.currentTimeMillis();
        User user;
        for (int i = 1; i <=5000 ; i++) {
            user = new User();
            user.setName("java");
            user.setAge(200);
            user.setManagerId(222);
            list.add(user);
        }
        System.out.println("拼装数据 耗时："+(System.currentTimeMillis()-start));
        System.out.println(list.size());

        System.out.println("forEachInsert 插入开始========");
        long start1 = System.currentTimeMillis();
        for (int i = 0; i < list.size(); i++) {
            usersMapper.insert(list.get(i));
        }
        System.out.println("foreach 插入耗时："+(System.currentTimeMillis()-start1));
    }

500条，插入耗时：1030ms

5000条，插入耗时：4947ms

50000条，插入耗时：15633ms

   @Autowired
    UserMapper usersMapper;
    @Autowired
    SqlSessionFactory sqlSessionFactory;
    public List list = new ArrayList<>();

 @org.junit.Test
    public void forEachXml() {
        long start = System.currentTimeMillis();
        User user;
        for (int i = 1; i <=500 ; i++) {
            user = new User();
            user.setName("java");
            user.setAge(200);
            user.setManagerId(222);
            list.add(user);
        }
        System.out.println("拼装数据 耗时："+(System.currentTimeMillis()-start));
        System.out.println(list.size());

        System.out.println("forEachInsert 插入开始========");
        long start1 = System.currentTimeMillis();
      usersMapper.xmlBatchInsert(list);
        System.out.println("foreach 插入耗时："+(System.currentTimeMillis()-start1));
    }

    
        INSERT INTO t_user(id,name,age,manager_id)  VALUES
        
            (#{item.id},
            #{item.name},
            #{item.age},
            #{item.managerId})
        
    

   
        select * from tb_dept_bigdata  where  id in
        
            #{id}
        
    

500条，插入耗时：586ms

5000条，插入耗时：1081ms

50000条, 报错！