前言
今天跟人对接口,发现他底使用的是mybatis的循环修改,会影响我的业务处理速度。不同表条件,修改不同值时,一搜mybeits批量修改,一般都是出来for循环单条修改,希望大家不要使用for循环单条操作数据库,先写推荐的,后分析原因。
一、sql批量修改(推荐)
sql语句
# 表面为aa,修改字段na、delete_flag
update aa set na = case id when 1 then 'a' when 2 then 'b' end,
delete_flag = case id when 1 then 1 when 2 then 0 end
where id in (1, 2);
mybatis实现
如果修改字段过可在代码中拼接,如果又更好的实现方式请留言,我知道了会再次更新。
<update id="batchUpdate">
update aa set na = case id
<foreach collection="list" item="item" close="end," separator=" ">
when #{item.id} then #{item.na}
</foreach>
delete_flag = case id
<foreach collection="list" item="item" close="end," separator=" ">
when #{item.id} then #{item.deleteFlag}
</foreach>
where id in
<foreach collection="list" item="item" open="(" close=")" separator=", ">
#{item.id}
</foreach>
</update>
二、mybatis批量修改(不推荐)
其实这知识mybetis框架代替在代码层写for循环,本质还是一样的,不推荐这种算法,一样后面分析。
使用前提url增加 &allowMultiQueries=true 参数
<update id="batchUpdate">
<foreach collection="list" item="item" separator=";">
update aa
<set>
`na`= #{item.na},
`delete_flag` = #{item.deleteFlag}
</set>
where `id` = #{item.id}
</foreach>
</update>
本地测试
id没有索引
mysql,修改5000条数据,使用sql(一)批量修改大约700ms,mybetis批量(二)需要26秒。
id为索引
普通索引(未测b-tree索引,估计也差不多)
mysql,修改5000条数据,使用sql批量修改大约700ms,mybetis批量需要9秒。
说明&细节
- 不规矩用for循环批量操作单数据的原因主要是查询与mysql服务链接使用的是TCP,大量操作是通讯时间浪费比较多,本地给http的java服务链接返回都需要50ms,数据库有优化也不会快太多,更何况数据库与通常不在同一服务器上。
- mysql对in的数量没有上限设置,oracle的上限是1000,超过会报错(最新版本不清楚有没有优化)
- Mysql对in么有上限,但对sql长度有上限,5.7上限是4M, 8.0默认是64M,最大可设置1G(官方文档)
结束语
现在的技术挺成熟了,如果你觉得某样东西用得不顺手,可以百度,或看官网,一般都能解决你的痛点,如果没有,恭喜你,发现了新商机。