批量更新的实现方式
在数据库上执行批量更新无非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个地方可以设置：
- 在MyBatis全局配置文件
mybatis-config.xml中通过参数defaultStatementTimeout指定（单位：秒）<!-- 设置超时时间，它决定驱动等待数据库响应的秒数。 --> <setting name="defaultStatementTimeout" value="120"/> - 通过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批量更新及其效率问题
转载请注明来源，欢迎对文章中的引用来源进行考证，欢迎指出任何有错误或不够清晰的表达，在下面评论区告诉我^_^^_^
