sql效率提升篇

mybatis批量更新及其效率问题

一：背景
注意：
第一种：
第二种:
二：批量更新的方式总结：
第一种：
第二种：
三：总结：
最近，负责公司一些旧数据的批量整理和清洗工作，在网上寻找了两种mybatis批量更新的方法。现在在这里总结下和说明下遇到的问题。

一：背景
公司旧数据的清洗，比如图片路径的改变，日期格式的改变（日期格式是varchar），因为数据大体上有一定的规律可寻，所以我的解决思路是用mybatis操作数据库，把需要清洗的数据查询出来并按一定规律进行清洗，在批量更新进数据库中。在这里尝试了两种方式的批量更新。

注意：
public void updateData(List<Map<String, Object>> map);
1
1.这条批量更新的传入参数是一个List<Map<String, Object>> map。

url: jdbc:mysql://localhost:3306/task?useUnicode=true&characterEncoding=utf8&serverTimezone=UTC&allowMultiQueries=true
1
2.jdbc与mysql的连接的url中要添加allowMultiQueries=true参数。本人因为刚开始没有加这个参数，导致一直报错。

### Error updating database. Cause: com.mysql.cj.jdbc.exceptions.PacketTooBigException: Packet for query is too large (12,981,868 > 4,194,304)
1
这是因为mysql数据库限制了处理文件的大小，默认是4MB，修改即可。
修改方法：

第一种：
在mysql的配置文件my.ini中添加 max_allowed_packet =67108864 ，我这里设置的是64MB，各位可以按照需要自行设置，这种方法是修改配置文件，所以就算数据库重启也会生效。

第二种:
set global max_allowed_packet = 64*1024*1024;
1
执行此sql语句可以把文件处理的最大值设置为64MB，需要多少自行决定，这种方式修改，数据库重启之后会重置为默认值。

show VARIABLES like '%max_allowed_packet%';
1
这个sql语句是查询文件处理的最大值是多少。

二：批量更新的方式总结：
第一种：

<update id="updata1">
<foreach collection="list" item="item" separator=";">
update refund_assistant_stqd set images=#{item.images} where id=#{item.id}
</foreach>
</update>

这种方法会生成这样的语句：

update refund_assistant_stqd set images=#{item.images} where id=#{item.id};
update refund_assistant_stqd set images=#{item.images} where id=#{item.id};
......

　　

这样与在java代码里面循环做一个循环没有啥本质上区别，根本就不是批量操作。也使用过了。效率极其之低，并且对数据库的负载相当之大，运行期间磁盘IO达到了百分之百，这种方法是不可取的，望后面的人能警醒。

第二种：

<update id="updateData">
update refund_assistant_stqd
<trim prefix="set" suffixOverrides=",">
<trim prefix="images =case" suffix="end,">
<foreach collection="list" item="item" index="index">
when id=#{item.id} then #{item.images}
</foreach>
</trim>
</trim>
where id in
<foreach collection="list" index="index" item="item" separator="," open="(" close=")">
#{item.id}
</foreach>
</update>

 

这种方法会生成这样的语句：

update refund_assistant_stqd set 
images= (case when id=##{item.id} then #{item.images}),
images= (case when id=##{item.id} then #{item.images}),
.....
where id in (#{item.id},#{item.id},...)

这种方法采用了case when 机制，使得相应的id与images能相匹配，只生成了一条sql语句，所以对数据库的压力会大大的缩小，时间都会花费在sql字符串的拼接上，并且sql的拼接是在你的电脑上完成的，不会对远程数据库的服务器产生不必要的负载。效率提高了很多。本人更新了12万条数据用了367秒，上面的第一种方法运行了20分钟之后我就放弃了，所以并没有测试出时间。

三：总结：
解决完以上背景里面的注意事项之后，本人的任务就执行完成了。
在这里要说一下，mybatis的批量操作原理就是使用sql 字符串的拼接技术，了解其原理之后对mybatis的批量操作处理有了一个大概轮廓了，不再神秘，所以，我们学习技术不仅仅要知其然，更要知其所以然。
这是小编正式写博客的第一篇，小编刚毕业工作半年，以后会在博客上总结一些工作上的细节，和技术学习上的总结。希望多多支持，多多交流。一起进步。

出处：https://blog.csdn.net/qq_40837310/article/details/92385458