性能优化 - SQL语句in条件超多异常

这是我参与更文挑战的第9天，活动详情查看：更文挑战

服务器报错日志截图：定位相关代码发现问题：

环境：Oracle数据库

sql语句

报错原因： SQL语句使用in()，当括号里的条件个数超出一定的长度，会导致语句报错。

解决方案：方法一：优化SQL语句

    and (ZG.STUDENT_INFO_ID in
    <foreach collection="ids" item="id" index="index"
             open="(" close=")" separator=",">
        <if test="index != 0 and (index%10) == 0">
            null) or  ZG.STUDENT_INFO_ID in (
        </if>
        #{id}
    </foreach>
    )
    order by ZSS.CREATE_TIME desc

方法一的不足：如果数据中ZG.STUDENT_INFO_ID有为null的就会导致查询出来的数据不准确。

方法二：

and (ZG.STUDENT_INFO_ID in
<foreach collection="ids" item="id" index="index"
         open="(" close=")">
    <if test="index != 0 and (index%10) == 0">
        #{id}) or  ZG.STUDENT_INFO_ID in (
    </if>
    <if test="index != (ids.size() -1)">
        #{id},
    </if>
    <if test="index == (ids.size() -1)">
        #{id}
    </if>
</foreach>
)
order by ZSS.CREATE_TIME desc

或者

and (ZG.STUDENT_INFO_ID in
<foreach collection="ids" item="id" index="index"
         open="(" close=")">
    <if test="index != 0 and (index%10) == 0">
        #{id}) or  ZG.STUDENT_INFO_ID in (
    </if>
    #{id},
    <if test="index == (ids.size() -1)">
        #{id}
    </if>
</foreach>
)
order by ZSS.CREATE_TIME desc

方法二的不足：相比第一种方法，增加了if的判断，查询速度会更慢。

方法三：在Java代码层把ids拆分成多次查找，最后把多次查找到的数据整合在一个List里，SQL语句不变

List<GuarDianModelDto> guarDianModelDtos = new ArrayList<>();
            if (studentInfoDtos != null && !studentInfoDtos.isEmpty()){
                Integer pageCount = 0; // 页数
                if(studentInfoDtos.size()%900 == 0) {
                    pageCount = studentInfoDtos.size() / 900;
                }else {
                    pageCount = studentInfoDtos.size() / 900 + 1;
                }

                for(int i=0; i<pageCount; i++) {
                    List<Long> studentIds = new ArrayList<>();
                    studentInfoDto.setIds(null);

                    int fromIndex = i*900;
                    int toIndex = 0;

                    if( i+1 == pageCount) {
                        if(studentInfoDtos.size() < pageCount*900) {
                            toIndex = studentInfoDtos.size();
                        }else {
                            toIndex = i*900 + 900;
                        }
                    }else {
                        toIndex = i*900 + 900;
                    }


                    for(int j=fromIndex; j<toIndex; j++) {
                        studentIds.add(studentInfoDtos.get(j).getId());
                    }
                    studentInfoDto.setIds(studentIds);
                    List<GuarDianModelDto> guarDianModelDtos1 = guardianMapper.guardianListExport(studentInfoDto);
                    guarDianModelDtos.addAll(guarDianModelDtos1);
                }

            }

方法三的不足:代码层面的改动过大。

总结

基于业务流程数据准确性的需求，选择方法二处理SQL语句in条件过长导致的报错。

SQL性能优化 - in

性能优化 - SQL语句in条件超多异常

总结