树形结构数据数据查询优化过程

树形结构数据统计查询优化过程

初始方案：

组织树数据结构如下：

数据请求参数：

原技术方案：

 public List<Map<String, List<Long>>> getSelectParam(List<DepartmentQueryDTO> departmentList, String reportName, String dataLevel) {
        if (CollectionUtils.isEmpty(departmentList)) {
            return null;
        }
        //根据report获取mapping
        //从树上查询出需要的所有维度值
        List<Long> orgDimensionIdList = departmentList.stream().map(t -> t.getDimensionMap().keySet()).flatMap(Set::stream).distinct().collect(Collectors.toList());

        //List<Long> orgDimensionIdList = departmentList.stream().map(DepartmentQueryDTO::getDimensionId).distinct().collect(Collectors.toList());
        List<Map<String, List<Long>>> paramList = new ArrayList<>();
        //没有维度值，有组织机构入参，那就是返回没有数据
        if (CollectionUtils.isEmpty(orgDimensionIdList)) {
            Map<String, List<Long>> paramMap = new HashMap<>();
            paramMap.put("1", Lists.newArrayList(0L));
            paramList.add(paramMap);
            return paramList;
        }
        Map<Long, DimensionMapping> dimensionMappingMap = dimensionMappingService.findByReportAndDataLevelGroupByDimensionId(reportName, dataLevel, orgDimensionIdList);
        Assert.notNull(dimensionMappingMap, "请在DIMENSION_MAPPING表中配置组织树相关的参数之后，再尝试!");
        Map<Long, List<Long>> childrenIdMap = departmentList.stream().filter(data -> data.getPid() != null).collect(Collectors.groupingBy(DepartmentQueryDTO::getPid, Collectors.mapping(DepartmentQueryDTO::getId, Collectors.toList())));
        boolean outOfDimensionFlag = Boolean.FALSE;
        for (DepartmentQueryDTO department : departmentList) {
            //记录维度列值及对应ID值
            Map<String, Long> dimensionColumnIdMap = new HashMap<>();
            List<Long> childrenId = childrenIdMap.get(department.getId());
            Map<String, List<Long>> paramMap = new HashMap<>();
            if (CollectionUtils.isEmpty(childrenId)) {
                //子节点
                for (Map.Entry<Long, Long> entry : department.getDimensionMap().entrySet()) {
                    DimensionMapping dimensionMapping = dimensionMappingMap.get(entry.getKey());
                    if (Objects.isNull(dimensionMapping)) {
                        outOfDimensionFlag = true;
                        paramMap.clear();
                        break;
                    }
                    if (department.isSelected() && department.isPermission()) {
                        paramMap.put(dimensionMapping.getColumnName(), Lists.newArrayList(entry.getValue()));
                    }
                    dimensionColumnIdMap.put(dimensionMapping.getColumnName(), entry.getValue());
                }
            } else {
                //父节点
                Map<Long, Long> treeDimensionMap = department.getDimensionMap();
                Set<Long> allDimensionIdList = new HashSet<>(dimensionMappingMap.keySet());
                allDimensionIdList.addAll(treeDimensionMap.keySet());
                for (Long dimensionId : allDimensionIdList) {
                    DimensionMapping dimensionMapping = dimensionMappingMap.get(dimensionId);
                    Long dimensionValueId = treeDimensionMap.get(dimensionId);
                    List<Long> valueList = new ArrayList<>();
                    if (Objects.isNull(dimensionMapping)) {
                        outOfDimensionFlag = true;
                        paramMap.clear();
                        break;
                    }
                    if (dimensionValueId != null) {
                        dimensionColumnIdMap.put(dimensionMapping.getColumnName(), dimensionValueId);
                        valueList.add(dimensionValueId);
                    }
                    if (department.isSelected() && department.isPermission()) {
                        paramMap.put(dimensionMapping.getColumnName(), valueList);
                    }
                }
            }
            if (!paramMap.isEmpty()) {
                paramList.add(paramMap);
            }
            department.setDimensionValueMap(dimensionColumnIdMap);
        }
        //当没有查询条件并且有存在树上的维度多余配置的维度，增加一条不成立的where条件
        if (CollectionUtils.isEmpty(paramList) && outOfDimensionFlag) {
            Map<String, List<Long>> paramMap = new HashMap<>();
            paramMap.put("1", Lists.newArrayList(0L));
            paramList.add(paramMap);
        }
        return paramList;
    }

在代码中封装到departmentList中然后在mapper xml中进行遍历拼接

@ApiModelProperty(value = "department树转换后的查询参数", hidden = true)
private List<Map<String, List<Long>>> departmentList = new ArrayList<>();

<sql id="departmentBaseSql">
        <if test="departmentList != null and departmentList.size() > 0">
            AND
            <foreach collection="departmentList" item="item" separator=" OR " open="(" close=")">
                <foreach collection="item.entrySet()" item="values" index="key" separator=" AND " open="(" close=")">
                    <choose>
                        <when test="values.size() == 0">
                            `${key}` IS NULL
                        </when>
                        <when test='key == "1"'>
                            ${key} =
                            <foreach collection="values" item="value">
                                #{value}
                            </foreach>
                        </when>
                        <when test="values.size() == 1">
                            `${key}` =
                            <foreach collection="values" item="value">
                                #{value}
                            </foreach>
                        </when>
                        <otherwise>
                            `${key}` IN
                            <foreach collection="values" item="value" open="(" close=")" separator=",">
                                #{value}
                            </foreach>
                        </otherwise>
                    </choose>
                </foreach>
            </foreach>
        </if>
    </sql>

<include refid="com.pwc.sdc.OPA.mapping.CommonMapper.departmentBaseSql"/>

查看拼接的sql:

我们会发现有很多条件是重复的，当数据量很大的时候就非常的影响查询的性能；因为接下来的优化就是减少这些不必要的开销

优化方案：

代码中进行递归调用拼接：

public String getMySelectParam(List<DepartmentQueryDTO> departmentList, String reportName, String dataLevel) {
        if (CollectionUtils.isEmpty(departmentList)) {
            return null;
        }
        //根据report获取mapping
        //从树上查询出需要的所有维度值
        List<Long> orgDimensionIdList = departmentList.stream().map(t -> t.getDimensionMap().keySet()).flatMap(Set::stream).distinct().collect(Collectors.toList());

        //List<Long> orgDimensionIdList = departmentList.stream().map(DepartmentQueryDTO::getDimensionId).distinct().collect(Collectors.toList());
        //没有维度值，有组织机构入参，那就是返回没有数据
        if (CollectionUtils.isEmpty(orgDimensionIdList)) {
            return null;
        }
        Map<Long, DimensionMapping> dimensionMappingMap = dimensionMappingService.findByReportAndDataLevelGroupByDimensionId(reportName, dataLevel, orgDimensionIdList);
        Assert.notNull(dimensionMappingMap, "请在DIMENSION_MAPPING表中配置组织树相关的参数之后，再尝试!");
        Map<Long, List<DepartmentQueryDTO>> childrenIdMap = departmentList.stream().filter(data -> data.getPid() != null).collect(Collectors.groupingBy(DepartmentQueryDTO::getPid));
        StringBuilder stringBuilder=new StringBuilder();
        boolean isFirst=true;
        for(int i=0;i<departmentList.size();i++){
            DepartmentQueryDTO department=departmentList.get(i);
            if(Objects.nonNull(department) && Objects.isNull(department.getPid())){
                if(isFirst){
                    stringBuilder.append(" (`");
                    isFirst=false;
                }else {
                    stringBuilder.append(" OR (`");
                }
                DimensionMapping dimensionMapping = dimensionMappingMap.get(department.getDimensionId());
                stringBuilder.append(dimensionMapping.getColumnName());
                stringBuilder.append("` = ");
                stringBuilder.append(department.getDimensionValueId());
                department.getDimensionIdSet().add(department.getDimensionId());
                handleSelectParam(childrenIdMap,department,dimensionMappingMap,stringBuilder,orgDimensionIdList);
                stringBuilder.append(")");
            }
        }
//        log.info(stringBuilder.toString());
        return stringBuilder.toString();
    }

public void handleSelectParam(Map<Long, List<DepartmentQueryDTO>> childrenIdMap,DepartmentQueryDTO parentDepartment,Map<Long, DimensionMapping> dimensionMappingMap,StringBuilder stringBuilder,List<Long> orgDimensionIdList) {
        List<DepartmentQueryDTO> childrenId = childrenIdMap.get(parentDepartment.getId());
        if (!CollectionUtils.isEmpty(childrenId)) {
            stringBuilder.append(" AND (");
            for(int i=0;i<childrenId.size();i++){
                DepartmentQueryDTO department=childrenId.get(i);
                department.getDimensionIdSet().addAll(parentDepartment.getDimensionIdSet());
                department.getDimensionIdSet().add(department.getDimensionId());
                if(i==0){
                    stringBuilder.append(" (`");
                }else {
                    stringBuilder.append(" OR (`");
                }
                DimensionMapping dimensionMapping = dimensionMappingMap.get(department.getDimensionId());
                stringBuilder.append(dimensionMapping.getColumnName());
                stringBuilder.append("` = ");
                stringBuilder.append(department.getDimensionValueId());
                handleSelectParam(childrenIdMap,department,dimensionMappingMap,stringBuilder,orgDimensionIdList);
                stringBuilder.append(") ");
            }
            if(parentDepartment.isSelected() && parentDepartment.isPermission() && !parentDepartment.isLeafNode() && !orgDimensionIdList.isEmpty()){
                boolean flag=true;
                for (int k=0;k<orgDimensionIdList.size();k++) {
                    if(!parentDepartment.getDimensionIdSet().contains(orgDimensionIdList.get(k))){
                        if(flag){
                            flag=false;
                            stringBuilder.append(" OR (`");
                        }else {
                            stringBuilder.append(" AND `");
                        }
                        DimensionMapping dimensionMapping2 = dimensionMappingMap.get(orgDimensionIdList.get(k));
                        stringBuilder.append(dimensionMapping2.getColumnName());
                        stringBuilder.append("` IS NULL ");
                    }
                }
                if(!flag){
                    stringBuilder.append(")");
                }
            }
            stringBuilder.append(") ");
        }

    }

mapper xml中使用

 <choose>
     <when test="condition.condi != null and condition.condi != ''">
     AND (${condition.condi})
     </when>
     <otherwise>
     and 1=0
     </otherwise>
  </choose>

拼接完成的查询sql：

接口优化后比之前的查询性能提高了2-3倍，整个业务系统几乎都是这样的基于树结构数据的查询统计，那么对于整个系统还是有不小的影响