mysql8
jdk8
mybatis
经常遇到的场景就是需要进行批量更新,更新同一张表中不同字段。这样常见的单字段批量更新就不行了。
列举常见的批量更新方式
- 代码中foreach update
- 多线程foreach update
- mybatis xml中foreach 多条sql拼接
- mybatis xml中case when
- mybatis-plus方式
- 业务控制-先批量删除再批量新增代替更新
- ON DUPLICATE KEY UPDATE
- REPLACE INTO
方式1 和 方式2
基本只有初学者会使用。
因为重复的IO连接与断开效率极低,导致性能很差。
mybatis xml中foreach 多条sql拼接
mybatis xml中foreach
前置条件:url连接配置必须设置allowMultiQueries=true
<update id="batchUpdate" parameterType="com.TestDO">
<foreach collection="offerList" item="offer" separator=";">
update offer
set
<if test="offer.name!=null and offer.name!=''">
name=#{offer.name},
</if>
update_time=#{offer.updateTime}
where
id = #{offer.id}
</foreach>
</update>
该方式采用mybatis动态拼接方式,多个update语句使用;
隔开,多个语句一次性提交给mysql,达到批量执行。类似于执行sql脚本方式。
allowMultiQueries=true
参数的作用:
- 可以在sql语句后携带分号,实现多语句执行。
- 可以执行批处理,同时发出多个SQL语句。
mybatis xml中case when
<update id="updateBatch">
update offer
<trim prefix="set" suffixOverrides=",">
<trim prefix=" `name` = case " suffix=" end, ">
<foreach collection="list" item="item">
<if test="item.name!= null">
when `id` = #{item.id} then #{item.name}
</if>
</foreach>
</trim>
</trim>
where
`id` in
<foreach collection="list" item="item" open="(" close=")" separator=",">
#{item.id}
</foreach>
</update>
效果
UPDATE offer SET name=
CASE
WHEN id = 1 THEN '测试商品1'
WHEN id = 2 THEN '测试商品2'
END
WHERE id IN ( 1, 2 );
优点:一条sql搞定,比方式一和方式二要快,只与mysql交互了一次。不需要额外配置参数支持。
弊端:如果条件过多,case when 需要判断多次,mysql处理效率会随着条件数量增多而持续下降。
如截图所示
由于是动态拼接,一定要注意条件的写法,多多测试,查看拼接的sql。
mybatis-plus 方式
com.baomidou:mybatis-plus:3.3.0
前置条件:url连接配置必须设置allowMultiQueries=true
rewriteBatchedStatements=true
com.baomidou.mybatisplus.extension.service.IService中有封装方法
源码中可以看到是基于主键id去更新的。
将多个update语句,分批次提交到数据库,默认是1000条。这里的更新方式与 mybatis xml中foreach 多条sql拼接 类似,都是一次性传递多条语句给数据库。
//提交到数据库,保存数据 sqlSession.flushStatements();
非UPDATE_BY_ID更新
基于主键id方式更新多有不便,更多的时候,是要基于where条件去进行更新。
于是需要改造一下,在mapper文件中调用批量更新的方法中重写executeBatch方法
简单举例部分代码如下所示
Assert.notEmpty(entityList, "error: entityList must not be empty");
String sqlStatement = sqlStatement(SqlMethod.UPDATE);
int size = entityList.size();
executeBatch(sqlSession -> {
int i = 1;
for (T entity : entityList) {
MapperMethod.ParamMap<Object> param = new MapperMethod.ParamMap<>();
param.put(Constants.WRAPPER, getQueryWrapper(entity));
param.put(Constants.ENTITY, entity);
sqlSession.update(sqlStatement, param);
if ((i % batchSize == 0) || i == size) {
sqlSession.flushStatements();
}
i++;
}
});
return true;
1、需要修改执行方式,改为UPDATE
sqlStatement(SqlMethod.UPDATE)
2、指定更新条件
param.put(Constants.WRAPPER, getQueryWrapper(entity));
private LambdaUpdateWrapper<NoticePartner> getQueryWrapper(T entity ) {
final LambdaUpdateWrapper<entity > updateWrapper = new UpdateWrapper<entity >().lambda();
......
return updateWrapper;
}
这样就通过WRAPPER条件,灵活多变的生成update语句。
业务控制-先批量删除再批量新增代替更新
这个没得说,先把原来数据全部删除, 在批量新增新的修改数据,控制在同一个事务内,防止数据不一致。有点类似REPLACE INTO。
ON DUPLICATE KEY UPDATE
利用数据库的特性,如果已存在该唯一标示或主键就更新,如果不存在该唯一标示或主键则作为新行插入。
<update id="updateBatch">
insert into t_output_calendar (index,
cal_date, user_type, create_time,
modify_time, delete_flag
)
values
<foreach collection="list" item="item" index="index"
separator=",">
(
#{item.index,jdbcType=INTEGER},
#{item.calDate,jdbcType=TIMESTAMP},
#{item.type,jdbcType=TINYINT},
#{item.createTime,jdbcType=TIMESTAMP},
#{item.modifyTime,jdbcType=TIMESTAMP},
#{item.deleteFlag,jdbcType=TINYINT}
)
</foreach>
ON DUPLICATE KEY UPDATE modify_time = VALUES(modify_time), user_type = VALUES(user_type);
</update>
REPLACE INTO
利用数据库的特性。
msql的replace into跟insert into的用法完全一样,但是它带有更新功能:
如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据,然后插入新的数据。否则,直接插入新数据。
注意,它是先删除数据,然后再插入
,这是和ON DUPLICATE KEY UPDATE不同的地方,如果当前的数据库用户没有删除权限,是不能使用replace into的。
replace into 代替insert into
<insert id="updateBatch" parameterType="java.util.List">
replace into t_output_calendar (index, cal_date, user_type, create_time,
modify_time, delete_flag
)
values
<foreach collection="list" item="item" index="index"
separator=",">
(
#{item.index,jdbcType=INTEGER},
#{item.calDate,jdbcType=TIMESTAMP},
#{item.type,jdbcType=TINYINT},
#{item.createTime,jdbcType=TIMESTAMP},
#{item.modifyTime,jdbcType=TIMESTAMP},
#{item.deleteFlag,jdbcType=TINYINT}
)
</foreach>
</insert>
总结
1、方式1与方式2,为什么慢?
慢在事务和网络io上面,由于默认事务是自动提交。那么在mybatis执行update的时候,每次执行update都是全新的事务,每次都要重新连接创建事务,提交事务,事务提交就要写磁盘,导致效率慢。多次写和一次写是不一样的。
所以才衍生出多条update拼接到一起,一次性发送到数据库,让它们在同一个事务中执行。
mybatis xml中foreach 多条sql拼接、mybatis-plus 方式 都是采用这种方式。
拼接的语句要控制好数量,防止缓冲区溢出。
在MySQL中,未提交的数据实际上存储在内存中的缓冲区中。当我们向数据库发送一个新的数据修改请求时,MySQL会将这些修改暂时存储在内存中,这些修改被称为“未提交的数据”。
如果我们使用了事务,则在提交事务之前,所有的修改都不会被写入到磁盘上的数据文件中,而是存储在内存中的缓冲区中。如果在事务提交之前,数据库崩溃或者出现其它问题导致MySQL停止运行,那么这些未提交的数据将会丢失。
2、多条拼接update语句与case when效率之争
- 多条拼接update语句本质上还是多条sql,需要数据库需要执行多次,只不过是一次传给数据库的,且在同一个事务中,减少网络io事务的开销,相当于给数据库传了一个sql脚本。
- case when是一条sql,数据库执行一遍,数据量允许的情况下第二种比第一种更加的优秀。但是数据量特别大而且条件复杂多变的时候,会给数据库带来压力,效率反而很差。
你会选哪种呢?