MyBatis批量插入数据优化

MyBatis批量插入数据优化(3)

时间：2023-12-31 10:48:21 来源：IT袋作者：马勇

导读：MyBatis批量插入数据优化，mybatis-plus 的 saveBatch 方法现在尝试 mybatis-plus 提供的 saveBatch 方法，期望它能够提高性能。 @Test public void MybatissaveBatch(){ SqlSession sqlSession = sqlSessionFactory.

MyBatis批量插入数据优化

MyBatis批量插入数据优化

mybatis-plus 的 saveBatch 方法

现在尝试 mybatis-plus 提供的 saveBatch 方法，期望它能够提高性能。

 @Test
    public void MybatissaveBatch(){
        SqlSession sqlSession = sqlSessionFactory.openSession();
        try {
            List<Student> students = new ArrayList<>();
            StopWatch stopWatch = new StopWatch();
            stopWatch.start("mybatis plus save batch");
            for (int i = 0; i < 100000; i++) {
                Student student = new Student();
                student.setStuid("6840"+i);
                student.setName("zhangsan"+i);
                student.setAge((i%100));
                if(i%2==0){
                    student.setSex(0);
                }else {
                    student.setSex(1);
                }
                student.setDept("计算机学院");
                student.setAddress("广东省广州市番禺"+i+"号");
                //一条一条插入
                students.add(student);
            }
            studentService.saveBatch(students);
            sqlSession.commit();
            stopWatch.stop();
            System.out.println("mybatis plus save batch：" + stopWatch.getTotalTimeMillis());
        } finally {
            sqlSession.close();
        }
    }

发现花费9204毫秒,比一条条插入数据性能提高十几倍

MyBatis批量插入数据优化

手动拼接 SQL：挑战传统的方式

<insert id="saveBatch2">
    insert into springboot_mp.tb_student ( stuid, name, age, sex, dept, address)
    values
    <foreach collection="students" item="stu" index="index" separator=",">
          ( #{stu.stuid}, #{stu.name}, #{stu.age}, #{stu.sex}, #{stu.dept}, #{stu.address})
    </foreach>
</insert>

发现花费10958毫秒,比一条条插入数据性能提高十几倍,但是和saveBatch性能相差不大

MyBatis批量插入数据优化

既然都验证都这了，我就在想，要不要使用JDBC批量插入进行验证一下，看会不会出现原始的才是最好的结果

JDBC 的 executeBatch 方法

尝试直接使用 JDBC 提供的 executeBatch 方法，看是否有意外的性能提升。

@Test
    public void JDBCSaveBatch() throws SQLException {
        SqlSession sqlSession = sqlSessionFactory.openSession();
        Connection connection = sqlSession.getConnection();
        connection.setAutoCommit(false);
        String sql ="insert into springboot_mp.tb_student ( stuid, name, age, sex, dept, address) values (?, ?, ?, ?, ?, ?);";
        try (PreparedStatement statement = connection.prepareStatement(sql)) {
            List<Student> students = new ArrayList<>();
            StopWatch stopWatch = new StopWatch();
            stopWatch.start("mybatis plus JDBCSaveBatch");
            for (int i = 0; i < 100000; i++) {
                statement.setString(1,"6840"+i);
                statement.setString(2,"zhangsan"+i);
                statement.setInt(3,(i%100));
                if(i%2==0){
                    statement.setInt(4,0);
                }else {
                    statement.setInt(4,1);
                }
                statement.setString(5,"计算机学院");
                statement.setString(6,"广东省广州市番禺"+i+"号");
                statement.addBatch();
            }
            statement.executeBatch();
            connection.commit();
            stopWatch.stop();
            System.out.println("mybatis plus JDBCSaveBatch：" + stopWatch.getTotalTimeMillis());
        }
        catch (Exception e){
            System.out.println(e.getMessage());
        }
        finally {
            sqlSession.close();
        }

JDBC executeBatch 的性能会好点，耗费6667毫秒