Mybatis 大数据量批量写优化的案例详解

Mybatis 大数据量批量写优化

在项目中使用批量数据插入，经常会用到 mybatis的 foreach，如下：

<insert id="batchInsert" parameterType="java.util.List">
    insert into USER (id, name) values
    <foreach collection="list" item="model" index="index" separator=","> 
        (#{model.id}, #{model.name})
    </foreach>
</insert>

就是将

INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");

转换成

INSERT INTO `table1` (`field1`, `field2`) 
VALUES ("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2");

从理论上将，复用conn，将多次io，转换成一次io，应该是提升效率的。但是实际上当数据量比较大的时候，用foreach效率非常低，速度非常慢

当表的列数较多（20+），以及一次性插入的行数较多（5000+）时，整个插入的耗时十分漫长，达到了14分钟，这是不能忍的

那为什么使用使用foreach效率如此之低呢？？

Mybatis默认执行器类型为Simple，默认会为每一个sql产生一个PrepareStatement，而且对于foreach无法使用缓存。如果字段和行数非常多，那么sql必然也会很长，占位符也会非常多，除此之外还要建立占位符和参数之间的映射，那么解析时间必然会长。因此如果values行数越多，那么解析时间必然很长。执行效率低。

在这里插入图片描述

如果非要使用 foreach 的方式来进行批量插入的话，可以考虑减少一条 insert 语句中 values 的个数，最好能达到上面曲线的最底部的值，使速度最快。一般按经验来说，一次性插20~50行数量是比较合适的，时间消耗也能接受。

那么如果要用批量插入，改如何优化呢？

SqlSession session = sqlSessionFactory.openSession(ExecutorType.BATCH);
try {
    SimpleTableMapper mapper = session.getMapper(SimpleTableMapper.class);
    List<SimpleTableRecord> records = getRecordsToInsert(); // not shown
    BatchInsert<SimpleTableRecord> batchInsert = insert(records)
            .into(simpleTable)
            .map(id).toProperty("id")
            .map(firstName).toProperty("firstName")
            .map(lastName).toProperty("lastName")
            .map(birthDate).toProperty("birthDate")
            .map(employed).toProperty("employed")
            .map(occupation).toProperty("occupation")
            .build()
            .render(RenderingStrategy.MYBATIS3);
    batchInsert.insertStatements().stream().forEach(mapper::insert);
    session.commit();
} finally {
    session.close();
}

基本思想是将 MyBatis session 的 executor type 设为 Batch ，然后通过遍历多次执行插入语句

就类似于JDBC的下面语句一样。

Connection connection = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/mydb?useUnicode=true&characterEncoding=UTF-8&useServerPrepStmts=false&rewriteBatchedStatements=true","root","root");
connection.setAutoCommit(false);
PreparedStatement ps = connection.prepareStatement(
        "insert into tb_user (name) values(?)");
for (int i = 0; i < stuNum; i++) {
    ps.setString(1,name);
    ps.addBatch();
}
ps.executeBatch();
connection.commit();
connection.close();

附录：Mybatis批量处理优化

Mybatis内置的ExecutorType有3种，默认的是simple单句模式，该模式下它为每个语句的执行创建一个新的预处理语句，单句提交sql；batch模式重复使用已经预处理的语句，并且批量执行所有语句，大批量模式下性能更优。

请注意batch模式在Insert操作时事务没有提交之前，是没有办法获取到自增的id，所以请根据业务情况使用。

使用simple模式提交10000条数据，时间为19s，batch模式为6s ，大致情况如此，优化的具体还要看提交的语句情况。

如果需要使用 foreach来优化数据插入的话，需要将每次插入的记录控制在 10-100 左右是比较快的，建议每次100来分割数据，也就是分而治之思想。

普通插入

默认的插入方式是遍历insert语句，单条执行，效率肯定低下，如果成堆插入，更是性能有问题。

INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");
INSERT INTO `table1` (`field1`, `field2`) VALUES ("data1", "data2");

foreach 优化插入

如果要优化插入速度时，可以将许多小型操作组合到一个大型操作中。理想情况下，这样可以在单个连接中一次性发送许多新行的数据，并将所有索引更新和一致性检查延迟到最后才进行。

<insert id="batchInsert" parameterType="java.util.List">
    insert into table1 (field1, field2) values
    <foreach collection="list" item="t" index="index" separator=","> 
        (#{t.field1}, #{t.field2})
    </foreach>
</insert>

翻译成sql语句也就是

INSERT INTO `table1` (`field1`, `field2`) 
VALUES ("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2"),
("data1", "data2");

Mybatis 大数据量批量写优化的案例详解

目录