【MyBatis】saveBatch 性能調優-有解無憂

最近在壓測一批介面，發現介面處理速度慢的有點超出預期，感覺很奇怪，后面定位發現是資料庫批量保存這塊很慢，

這個專案用的是 mybatis-plus，批量保存直接用的是 mybatis-plus 提供的 saveBatch，我點進去看了下原始碼，感覺有點不太對勁：

繼續追蹤了下，從這個代碼來看，確實是 for 回圈一條一條執行了 sqlSession.insert，下面的 consumer 執行的就是上面的 sqlSession.insert：

然后累計一定數量后，一批 flush，從這點來看，這個 saveBach 的性能肯定比直接一條一條 insert 快，

我直接進行一個粗略的實驗，簡單創建了一張表來對比一波！

1、1000條資料，一條一條插入

@Test
void MybatisPlusSaveOne() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
        StopWatch stopWatch = new StopWatch();
        stopWatch.start("mybatis plus save one");
        for (int i = 0; i < 1000; i++) {
            OpenTest openTest = new OpenTest();
            openTest.setA("a" + i);
            openTest.setB("b" + i);
            openTest.setC("c" + i);
            openTest.setD("d" + i);
            openTest.setE("e" + i);
            openTest.setF("f" + i);
            openTest.setG("g" + i);
            openTest.setH("h" + i);
            openTest.setI("i" + i);
            openTest.setJ("j" + i);
            openTest.setK("k" + i);
            //一條一條插入
            openTestService.save(openTest);
        }
        sqlSession.commit();
        stopWatch.stop();
        log.info("mybatis plus save one：" + stopWatch.getTotalTimeMillis());
    } finally {
        sqlSession.close();
    }
}

可以看到，執行一批 1000 條數的批量保存，耗費的時間是 121011 毫秒，

2、1000條資料用 mybatis-plus 自帶的 saveBatch 插入

@Test
void MybatisPlusSaveBatch() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
        List<OpenTest> openTestList = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            OpenTest openTest = new OpenTest();
            openTest.setA("a" + i);
            openTest.setB("b" + i);
            openTest.setC("c" + i);
            openTest.setD("d" + i);
            openTest.setE("e" + i);
            openTest.setF("f" + i);
            openTest.setG("g" + i);
            openTest.setH("h" + i);
            openTest.setI("i" + i);
            openTest.setJ("j" + i);
            openTest.setK("k" + i);
            openTestList.add(openTest);
        }
        StopWatch stopWatch = new StopWatch();
        stopWatch.start("mybatis plus save batch");
        //批量插入
        openTestService.saveBatch(openTestList);
        sqlSession.commit();
        stopWatch.stop();
        log.info("mybatis plus save batch：" + stopWatch.getTotalTimeMillis());
    } finally {
        sqlSession.close();
    }
}

耗費的時間是 59927 毫秒，比一條一條插入快了一倍，從這點來看，效率還是可以的，

然后常見的還有一種利用拼接 SQL 方式來實作批量插入，我們也來對比試試看性能如何，

3、1000 條資料用手動拼接 SQL 方式插入，搞個手動拼接：

來跑跑下性能如何：

@Test
void MapperSaveBatch() {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    try {
        List<OpenTest> openTestList = new ArrayList<>();
        for (int i = 0; i < 1000; i++) {
            OpenTest openTest = new OpenTest();
            openTest.setA("a" + i);
            openTest.setB("b" + i);
            openTest.setC("c" + i);
            openTest.setD("d" + i);
            openTest.setE("e" + i);
            openTest.setF("f" + i);
            openTest.setG("g" + i);
            openTest.setH("h" + i);
            openTest.setI("i" + i);
            openTest.setJ("j" + i);
            openTest.setK("k" + i);
            openTestList.add(openTest);
        }
        StopWatch stopWatch = new StopWatch();
        stopWatch.start("mapper save batch");
        //手動拼接批量插入
        openTestMapper.saveBatch(openTestList);
        sqlSession.commit();
        stopWatch.stop();
        log.info("mapper save batch：" + stopWatch.getTotalTimeMillis());
    } finally {
        sqlSession.close();
    }
}

耗時只有 2275 毫秒，性能比 mybatis-plus 自帶的 saveBatch 好了 26 倍！

這時，我又突然回想起以前直接用 JDBC 批量保存的介面，那都到這份上了，順帶也跑跑看！

4、1000 條資料用 JDBC executeBatch 插入

@Test
void JDBCSaveBatch() throws SQLException {
    SqlSession sqlSession = sqlSessionFactory.openSession();
    Connection connection = sqlSession.getConnection();
    connection.setAutoCommit(false);

    String sql = "insert into open_test(a,b,c,d,e,f,g,h,i,j,k) values(?,?,?,?,?,?,?,?,?,?,?)";
    PreparedStatement statement = connection.prepareStatement(sql);
    try {
        for (int i = 0; i < 1000; i++) {
            statement.setString(1,"a" + i);
            statement.setString(2,"b" + i);
            statement.setString(3, "c" + i);
            statement.setString(4,"d" + i);
            statement.setString(5,"e" + i);
            statement.setString(6,"f" + i);
            statement.setString(7,"g" + i);
            statement.setString(8,"h" + i);
            statement.setString(9,"i" + i);
            statement.setString(10,"j" + i);
            statement.setString(11,"k" + i);
            statement.addBatch();
        }
        StopWatch stopWatch = new StopWatch();
        stopWatch.start("JDBC save batch");
        statement.executeBatch();
        connection.commit();
        stopWatch.stop();
        log.info("JDBC save batch：" + stopWatch.getTotalTimeMillis());
    } finally {
        statement.close();
        sqlSession.close();
    }
}