批量更新的两种方式效率对比

一种用for循环，循环出N条update语句，

另一种用 case when 循环出一个长sql语句

方式一：拼接多个update

<update id="updateBatch" parameterType="java.util.List">
        <foreach collection="list" item="item" separator=";">
            UPDATE table_test
            SET
            container_id = #{item.containerId,jdbcType=BIGINT},
            ...省略其他字段
        </foreach>
</update>

运行起来的样子（其实就是多个update语句以 ；分隔，一次性传输给数据库执行）

UPDATE table_test SET container_id = 4163823751071650816 .... WHERE container_id = 4163823751071650816 ; 

UPDATE table_test SET container_id = 7159856530952156160 .... WHERE container_id = 7159856530952156160 ; 

......

方式二：case when 拼接成一个长长的sql语句

<update id="batchModifyBoolAttrInfo">
    update table_test 
    <!-- 拼接case when 每多一个字段，需要多一个循环 -->
    <trim prefix="set" suffixOverrides=",">
        <trim prefix="bool_attr_info =case" suffix="end,">
            <foreach collection="list" item="item" index="index">
                when container_id = #{item.containerId} then 1024
            </foreach>
        </trim>
    </trim>
    ...省略其他字段
    where container_id in
    <foreach collection="list" index="index" item="item" separator="," open="("            
        close=")"> #{item.containerId} 
    </foreach>
</update>

准备测试

String sql = "select * from table_test where name = 'TEST' limit 2000";
List<ContainerInfo> containerInfos = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(ContainerInfo.class));
containerInfos.forEach(a -> a.setBoolAttrInfo(2048L));
StopWatch stopWatch = new StopWatch();
stopWatch.start();
containerDao.updateBatch2(containerInfos);
stopWatch.stop();
System.out.printf("执行时长: %d 毫秒",stopWatch.getTotalTimeMillis());

以下是测试3次更新5个字段的值在  2000条，1000条，500条，100条 时各自的耗时对比：

	updateBatch	case when长语句
2000条	5.8s  6.9s  5.9s	16s   17s   16s
1000条	2.9s  2.3s  2.3s	5.7s  7.0s  7.7s
500条	0.85s  0.76s  1.2s	1.48s  1.50s  1.50s
100条	0.19s  0.1s  0.1s	0.32s  0.1s  0.1s

可以看到 case when 效率是低于 updateBatch 的，原因在于 updateBatch 更新无论更新几个字段都只有一个循环体 foreach，而 case when 每多更新一个字段，就要多一个循环体去拼接sql

<trim prefix="bool_attr_info =case" suffix="end,">
    <foreach collection="list" item="item" index="index">
        when container_id = #{item.containerId} then 1024
    </foreach>
</trim>
<trim prefix="c_group_code =case" suffix="end,">
    <foreach collection="list" item="item" index="index">
        when container_id = #{item.containerId} then '2023'
    </foreach>
</trim>
......            

但当更新的字段减少时，case when的效率又会反超 updateBatch，如下是更新2000条记录，一个字段和两个字段的耗时：

	updateBatch	case when长语句
两个字段	3.5s  3.0s   3.4s	5s  7s  5.5s
一个字段	2.8s  3.5s  2.6s	1.6s   1.4s   1.5s

结论：case when的效率受制于循环体的个数，当循环体的数量最少即只更新一个字段时，case when的速度要快于updateBatch批处理，其他情况还是使用updateBatch批处理的方式更快。

另外：这两种方式都需要控制参数的个数，否则会造成异常，比如一个表有65个字段，而我一次性更新65个字段并且记录数超过2000，就会有12万个参数，导致异常。

PreparedStatement can have at most 65,535 parameters. 
Please consider using arrays, or splitting the query in several ones, 
or using COPY. Given query has 121,600 parameters