批量更新的实现方式

在数据库上执行批量更新无非2种方式：
其一，在应用层通过循环的方式多次执行单条UPDATE语句，使用该方式性能最差，每次执行UPDATE操作都需要经历"建立连接 -> 执行SQL语句 -> 释放链接"的流程。
其二，在数据库层一次性执行单个或多个SQL语句，该方式将批量操作放在数据库，会导致SQL语句比较大，但是只需要经历一次"建立连接"和"释放连接"的过程，相比第一种方式效率会得到很大改进。

如下通过具体的代码测试进行说明：

• 表数据总量：50w
• 批量更新数量：1w

在应用层执行批量更新

特点：一条记录update一次，性能比较差，容易造成阻塞。

通过代码实现逐条更新

for (int i = 0; i < studentList.size(); i++) {
    Student student = studentList.get(i);
    sqlSession.update("org.test.mapper.update", student);
}

耗时：437587 ms，大约：7分钟。

通过MyBatis实现逐条更新

使用MyBatis实现循环批量更新，首先得设置MySql支持批量操作，在jdbc链接中需要附加&allowMultiQueries=true属性才行，否则会报错：

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax;

<!-- 批量更新方式1：逐条更新 -->
<update id="updateStudentBatch1" parameterType="java.util.List">
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update student
        <set>
            age = #{item.age}
        </set>
        where id = #{item.id}
    </foreach>
</update>

生成的SQL语句是使用;分隔的多条SQL语句：

update student SET age = 0 where id = 0;update student SET age = 1 where id = 1;

如果执行时遇到如下报错：

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request

需要将MyBatis的执行超时时间设置大一些，2个地方可以设置：

1. 在MyBatis全局配置文件mybatis-config.xml中通过参数defaultStatementTimeout指定（单位：秒）

<!-- 设置超时时间，它决定驱动等待数据库响应的秒数。  -->
<setting name="defaultStatementTimeout" value="120"/>

2. 通过MyBatis中select/update/delete标签的timeout属性指定（单位：秒）

<!-- 批量更新方式1：逐条更新 -->
<update id="updateStudentBatch1" parameterType="java.util.List" timeout="180">
    <foreach collection="list" item="item" index="index" open="" close="" separator=";">
        update student
        <set>
            age = #{item.age}
        </set>
        where id = #{item.id}
    </foreach>
</update>

在实际测试时发现，即使将time参数往大调整，比如设置为：500，再一次性批量更新10000条记录时依然会报错：

Exception in thread "main" org.apache.ibatis.exceptions.PersistenceException: 
### Error updating database.  Cause: com.mysql.cj.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request

但是数据却又被实际更新了！

报错time参数值为500，将单次批量更新数量调整为5000，执行耗时为：378890 ms，大约：6分钟（预估单次批量更新10000条记录耗时约在12分钟左右）。

从测试结果看，通过MyBatis的逐条更新方式比直接在代码层循环更新效率还要低！预估耗时大约相差2倍。

在数据库层执行批量更新

通过MyBatis拼接批量更新SQL，转换为when...then...语法。

更新单个字段

<!-- 批量更新方式：转换为when...then...语法，更新单个字段 -->
<update id="updateStudentBatch2" parameterType="java.util.List">
    update student
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="age = case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                when id = #{item.id} then #{item.age}
            </foreach>
        </trim>
    </trim>
    where id in
    <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

生成的批量更新SQL如下：

update student
set
  age = case
    when id = ? then ?
    when id = ? then ?
    when id = ? then ?
  end
where
  id in (?, ?, ?)

批量更新10000条记录，耗时：3295 ms，从执行效果看，批量更新的效率大大提升了。

使用该方式批量更新时，务必注意：一定要加where条件，参数中的id为需要更新的数据的id；如果不加where条件，则会全部更新。

还可以对参数进行判断处理，当传递的参数不为空时才执行更新：

<!-- 批量更新方式：转换为when...then...语法，更新单个字段 -->
<update id="updateStudentBatch2" parameterType="java.util.List">
    update student
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="age = case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.age != null"> <!-- 对参数进行校验判断 -->
                    when id = #{item.id} then #{item.age}
                </if>
            </foreach>
        </trim>
    </trim>
    where id in
    <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

更新多个字段

使用when...then...进行批量更新，当需要更新多个字段时，将如下片段拷贝多次并更改prefix和when...then...的内容即可。

<trim prefix="age = case" suffix="end,">
    <foreach collection="list" item="item" index="index">
        <if test="item.age != null">
            when id = #{item.id} then #{item.age}
        </if>
    </foreach>
</trim>

<!-- 批量更新方式3：转换为when...then...语法，更新多个字段 -->
<update id="updateStudentBatch3" parameterType="java.util.List">
    update student
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="age = case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.age != null">
                    when id = #{item.id} then #{item.age}
                </if>
            </foreach>
        </trim>
        <trim prefix="name = case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                <if test="item.name != null">
                    when id = #{item.id} then #{item.name}
                </if>
            </foreach>
        </trim>
    </trim>
    where id in
    <foreach collection="list" item="item" index="index" separator="," open="(" close=")">
        #{item.id}
    </foreach>
</update>

生成的批量更新SQL如下：

update student
set
  age = case
    when id = ? then ?
    when id = ? then ?
    when id = ? then ?
  end,
  name = case
    when id = ? then ?
    when id = ? then ?
    when id = ? then ?
  end
where
  id in (?, ?, ?)

批量更新10000条记录，耗时：10175 ms 。显然，批量更新耗时跟需要更新的字段数量成正比例关系，需要更新的字段越多，批量更新耗时越大。

【参考】
Mybatis中进行批量更新（updateBatch）
mybatis批量更新及其效率问题