Mybatis批量更新数据及其优化

news/2024/11/27 4:04:10/

需求场景：定时任务中，从其他平台同步数据，并更新当前平台数据库，表数据3W+，分批更新某个字段，耗时巨大，约30min，尝试性能优化。

批量更新的几种常见方式：

1.foreach 循环

在mybatis的xml文件中，使用foreach动态标签拼接SQL语句，每一条数据的更新语句对应一条update语句，多条语句最终使用";"号进行拼接。

<update id="updateStudentInfoById"><foreach collection="list" item="item" separator=";">updatet_studentsetname = #{item.name},age = #{item.age}whereid = #{item.id}</foreach>
</update>

2.先删后增，取出原数据内存中更新后，先将全表数据删除，再insert插入；或者设置标志字段，先增后删，思路一致

3.使用replace into 若主键存在则更新，不存在插入

REPLACE INTO t_student (id, name, code, hobby) 
values (#{item.id}, #{item.name}, #{item.code}, #{item.hobby})

4.批量新增数据，若键重复则更新

<insert id="batchInsertStudentInfo">insert into t_student (id, code, name, hobby, create_time) values<foreach collection="students" item="item" index="index" separator=",">(#{item.id},#{item.code},#{item.name},#{item.hobby},#{item.createTime})</foreach>on duplicate key updatecreate_time = values(create_time)
</insert>

5.when case 更新

UPDATE `t_student` 
SET `name` =
CASEWHEN `id` = 1 THEN'张三' WHEN `id` = 2 THEN'李四' WHEN `id` = 3 THEN'王五' WHEN `id` = 4 THEN'赵六' END,`age` =
CASEWHEN `id` = 1 THEN40 WHEN `id` = 2 THEN34 WHEN `id` = 3 THEN55 WHEN `id` = 4 THEN76 END 
WHERE`id` IN ( 1, 2, 3, 4 )

场景分析：当前场景需要去更新某个字段，且数据量较大，几百条数据每批进行更新，应用foreach循环更新时，耗时巨大；

性能优化：使用临时表关联全表更新，一次关联，一次更新；

<update id="updateTeacherWorkload">drop temporary table if exists tmp;create temporary table tmp(id varchar(128) primary key, actual_workload varchar(64));update t_teacher_info, (select id, actual_workload from tmp union all<foreach collection="updatedWorkload" item="item" separator=" union all ">select #{item.id}, #{item.actualWorkload}</foreach>) as tmpset t_teacher_info.actual_workload = tmp.actual_workload where t_teacher_info.id = tmp.id;
</update>

结果评估：使用临时表后总体耗费时间为12s，较原先30min，缩短150倍；

注意点：临时关联更新操作不能应用在Trascational事务中，创建临时表的操作在事务中不支持，需要做其他处理；正常小数量的更新且有事务管理要求，则优先使用foreach或其他操作。