MyBatis批量插入、更新数据

大批量插入、更新数据相当消耗性能，如果采用传统的方式，一条一个事务的插入、更新，速度会慢到让人无法接受。

JDBC批量处理

使用JDBC的批量处理功能，可以减少事务的性能消耗。MyBatis Plus的批量操作默认使用这种方式。

其原理是开启JDBC批量处理，并且每1000条SQL语句执行一次sqlSession.flushStatements()。

对于批量处理条数，SQL Server有 1000 条语句的限制。其它数据库则相对宽松。

原生批量插入

SQL insert语句的values可以支持多条记录，使用这种方式批量插入可以极大的提高性能。

<insert id="insertBatch">
    insert into ujcms_visit_log (id_, site_id_, user_id_)
    values
    <foreach collection="list" item="item" separator=",">
        (#{item.id,jdbcType=BIGINT}, #{item.siteId,jdbcType=INTEGER}, #{item.userId,jdbcType=INTEGER}
    </foreach>
</insert>

    /**
     * 批量插入数据
     *
     * @param list 待插入数据
     * @return 插入数据量
     */
    int insertBatch(List<VisitLog> list);

对于的SQL语句类似如下：

insert into ujcms_visit_log
    (id_, site_id_, user_id_)
values
    (?,?,?),
    (?,?,?),
    ...
    (?,?,?)

但需要注意参数的数量，SqlServer 参数限制为2100个，Oracle、PostgreSQL参数限制为32767个，MySQL参数限制为65535个。

如果需要兼容所有的数据库，一次插入的条数大概在100-200条之间。否则可以在1000-2000条左右。

原生批量插入和JDBC批量处理结合

如果同时使用原生批量插入和JDBC批量插入，则可以大大提高性能。

@Autowired
private final SqlSessionTemplate sqlSessionTemplate;

/**
 * FOREACH条数
 * <p>
 * SqlServer 参数限制为2100个，Oracle、PostgreSQL参数限制为32767个，MySQL参数限制为65535个。
 * <p>
 * VisitLog表有17个字段，100条记录为1700个参数。
 */
public static final int FOREACH_SIZE = 100;

/**
 * 批量处理条数。SQL Server有 1000 条语句的限制。
 */
public static final int BATCH_SIZE = 1000;

public void batchInsert(List<VisitLog> all) {
    try (SqlSession session = sqlSessionTemplate.getSqlSessionFactory().openSession(ExecutorType.BATCH, false)) {
        VisitLogMapper visitLogMapper = session.getMapper(VisitLogMapper.class);
        List<VisitLog> list = new ArrayList<>(FOREACH_SIZE);
        int index = 0;
        for (VisitLog bean : all) {
            list.add(bean);
            index += 1;
            if (index % FOREACH_SIZE == 0 || index == all.size()) {
                visitLogMapper.insertBatch(list);
                list.clear();
            }
            if (index % BATCH_SIZE * FOREACH_SIZE == 0 || index == all.size()) {
                session.flushStatements();
                session.commit();
                session.clearCache();
            }
        }
    }
}

Multi Queries 批量更新

使用数据库的MultiQueries特征，即一次可以执行多条SQL语句，多条SQL语句之间用分号隔开。

MyBatis代码：

<update id="updateBatch">
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update mytable
        <set>
            mycol=#{item.mycol}
        </set>
        where id = #{item.id}
    </foreach>
</update>

对应的sql语句类似：

update mytable set mycol=?;
update mytable set mycol=?;
...
update mytable set mycol=?

这种方式不是所有的数据库都支持，已知PostgreSQL、MySQL(JDBC URL需加上allowMultiQueries=true)支持，H2不支持。不应作为首选方案。

case when 批量更新

使用case when语法，将多条更新语句放到一条。

<update id="updateBatch">
    update ujcms_site_buffer
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="views_=case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                when id_=#{item.id} then views_+#{item.views}
            </foreach>
         </trim>
        <trim prefix="self_views_=case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                when id_=#{item.id} then self_views_+#{item.selfViews}
             </foreach>
        </trim>
    </trim>
    where id_ in
    <foreach collection="list" index="index" item="item" separator="," open="(" close=")">
        #{item.id,jdbcType=INTEGER}
    </foreach>
</update>

对应的sql语句类似：

UPDATE ujcms_site_buffer
SET
    views_ = CASE
        WHEN id_=1 THEN 'abc1'
        WHEN id_=2 THEN 'abc2'
        WHEN id_=3 THEN 'abc3'
    END,
    self_views_ = CASE
        WHEN id_=1 THEN 123
        WHEN id_=2 THEN 234
        WHEN id_=3 THEN 345
    END
WHERE id_ IN (1,2,3)

这种方式兼容性好，但也需注意SQL Server的2100个参数限制。

真实项目案例

示例代码来自国内开源Java CMS软件：UJCMS

UJCMS官网
UJCMS源码地址：https://gitee.com/ujcms/ujcms