简述:今天项目中发现mysql in 最大值超出上限的问题,针对此问题提出相关优化方案,提供大家参考,异常代码如下:
**IN items count 2723 exceeds the threshold 2000
at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:129) ~[mysql-connector-j-8.0.31.jar:8.0.31]
at**
目前,了解有四种方式:
- 修改mysql in 参数配置大小值,对与性能有较大压力;
- 改成批量查询,切片用户ID,最后再合并数据,这种情况下需要解决分页带来的问题,和数据多差带来的性能消耗;
- 改成子查询,目前业务设计跨库查询肯能要迁移到adb处理,改动代码较多,相对于性能比上面两种更好;
- 代码切片超出in 参数ID值 , List<List> list 参数,最终入参拼接mysql 执行语句, 类似与:select * from table where a or in () ro a in () 模式,此方法与第二种有点类同,只是把切割方式,拼接参数,作为入参控制in 值大小,次方法对于性能也有弊端,如果list 过大会导致sql 过长问题,或者是性能问题,list 越大,性能越差。
还有更好的优化方式,可以留言讨论~!
正题:
此次优化是针对第4方案,做出的评估方案进行优化
为何选此方案:
1.是根据业务设计来的,历史遗留原因,数据库跨实例,同账号权限分配等原因无法通过,** from a.Y left jon b.U on a.id =b.id 做关系数据查询结果集。
2.评估代码改动较大,目前项目已经运行六七年,UId 贯彻整个系统,如果调整关联查询改动面积较大,需要做更多的评估和可实施方案调研。
3.改动面积小,in 值大小通过参数配置可控,整体性能在控制范围内
代码参考:
ids 分片方法:
public static <F> List<List<F>> idSplitList(List<F> list) {
if (CollectionUtil.isEmpty(list)) {
return null;
}
int groupSize = Integer.valueOf(20);
int length = list.size();
// 计算可以分成多少组
int num = (length + groupSize - 1) / groupSize;
List<List<F>> newList = new ArrayList<>(num);
for (int i = 0; i < num; i++) {
// 开始位置
int fromIndex = i * groupSize;
// 结束位置
int toIndex = Math.min((i + 1) * groupSize, length);
newList.add(new ArrayList<F>(list.subList(fromIndex, toIndex)));
}
return newList;
}
mybaits 对应mapper.xml 文件代码
<if test ="idsSlice != null and idsSlice .size >0">
and (
<foreach collection="idsSlice " item="idsItem" separator=" " index="index" >
<if test="index !=0">or</if> id in
<foreach collection="idsItem" item="item" separator="," close=")" open="(">
#{item}
</foreach>
</foreach>
)
</if>
代码解释:
1.idSplitList 方法是对List ids 进行切片分割,按照20个分割一次。
2.xml 解释,此代码语法是参考mybaitis mapper.xml 语法来写的,动态拼接sql, 会基本语法的这大概都能看懂,index 值 要判断第一个下标不用or 拼接sql ,其它下标都需要拼接成:“ where a in () or a in () or a in (*) ” 格式
大致思路就介绍这里,具体可以参考这个链接:
mybatis-plus in查询条件超过1000条报错
备注:选择那种方案,需要根据自己系统的情况来评估选择适合自己的优化方案,如果是ADB库,或者是只读库,建议使用左右链接模式更好,但是如果在业务库又没办法拆分开只能选择折中模式,最终的方案都是为了解决系统瓶颈问题为目的。希望此文章对你有所启发和帮助!
欢迎大家评论区,讨论并提出更好的方案!