mybatis批量更新比对

发表于 更新于

上次发现因为数据库获取新的连接超时，其实是对数据做存在更新不存在做插入。想到对于更新不同数据速度相差多少。

暂时有4种更新方法。
一个个更新

1
2
update simple set code=#{item.code}
where id = #{item.id}

采用batch批量更新，sql如上，只是批量提交请求。
拼装更新sql为一个大的sql

1
2
3
4
<foreach collection="datas" item="item" separator=";">
    update simple set code=#{item.code}
    where id = #{item.id}
</foreach>

对于mysql，需要添加allowMultiQueries=true参数

采用临时表关联更新

1
2
3
4
5
6
7
8
update simple t,
(
<foreach collection="datas" item="item" separator=" UNION ALL ">
    select #{item.id} as id, #{item.code} as code
</foreach>
) v
set t.code = v.code
where t.id = v.id

单线程性能比较

单个更新 批量提交 拼装sql 临时表
10w/1k 122.9 63.99 13.2 3.87

采用的数据量是10w条数据，随机更新10w次，对于批量更新一次提交1000条数据，因为对比结果相差较大，多次测试同类型时间大致相差不大，未统计每次的数据
后续测试了100w条数据更新10w次，比对结果差不多。

多线程性能比较

总数/单次 单个更新 批量提交 拼装sql 临时表
1w/1k 68.44 57.37 45.23 出现死锁
1w/10 - - - 8.25

采用的数据量是10w条数据，随机更新1w次。对于临时表更新（一次1000），出现了死锁，应该是不同的线程里面更新的数据有一样的导致。改为10条后，多次测试未出现死锁，应该是一次更新数据量少了后，不容易重复。

多线程比对的时候需要设置自动提交，避免死锁。

普通索引更新（单线程）

总数/单次 单个更新 批量提交 拼装sql 临时表
10w/1k 445.71 1178.84 343.24 4.31
1w/1k 47.67 42.36 35.25 1.13
1w/100 46.73 41.52 100.46 1.63
  • ?建立普通索引后，如果普通索引更新字段结果如上，对于批量提交10w/1k耗时异常长（多次测试），暂时未找到原因。

普通索引更新（多线程）

总数/单次 单个更新 批量提交 拼装sql 临时表
1w/1k 71.47 68.44 47.59 死锁
1w/100 68.24 59.68 44.97 死锁
1w/10 - - - 9.03

对于多线程版本，比对和主键相差不太大

唯一索引（单线程）

和普通索引差不多，批量提交一样耗时异常长。

唯一索引（多线程）

总数/单次 单个更新 批量提交 拼装sql 临时表
1w/1k 87.17 58.65 47.54 死锁
1w/100 71.12 61.23 46.96 死锁
1w/10 - - - 9.67

对于单个更新发现相对普通索引性能更低了,不确认是否因为测试次数较少偶然导致。

总结

在测试过程中，并未多次测试取平均值，所以只能看到大概比对。常规情况性能比较 单次 < 批量 < 拼装sql < 临时表。
在测试过程了临时表性能相对其他方式性能有大幅度提高，只是会存在死锁情况，对于少量并发少量数据更新的情况下，优选临时表更新。同时临时表更新也支持Sharding-JDBC是一大优点。

批量提交相对单个更新性能优势并没有特别大，优势是只提交一次sql，后面只是多条参数给数据库，相比而言与数据库交互少。不过对于批量提交需要注意的是提交的参数需要一致，如果mybatis的xml中使用了foreach和if导致生成的参数不对，会导致执行失败。
因为批量提交原理是提交一个sql给数据库做预处理，后面提交参数，如果参数不一致就会导致执行异常。

对于拼装sql，如果单条sql更新的字段非常多，且mybatis的xml中如果有很多判断逻辑，导致最终生成的sql比较大（有很多空白行），如果拼装过多就会导致最终一次提交给数据库的sql过大，之前jvm分析中找到的sql就是采用的拼装，导出sql后文件有1M多。

对于超多字段更新情况下并未做参数，可能性能比对相差不是很大，不过也可能因为最后生成sql较大导致性能有差距。
本次参数过程中还缺少记录数据库cup、内存、磁盘占用情况。

测试代码地址：https://github.com/whhxz/blog-example/tree/main/mybatis-batch-handler-test