递归SQL优化：替换Java递归方法排查性能瓶颈及查询异常

安全阿岳

2026-8-15

性能优化求助：递归层级数据处理耗时过长

问题背景

新入职后首要任务是优化一项用户操作耗时可达40分钟的功能。经分析，该功能采用Java递归方法处理父子层级关系数据，每次递归都会发起数据库网络调用，这是性能低下的核心原因。计划通过单次递归SQL查询获取所有层级数据来优化，但自行编写的递归SQL运行6-7分钟仍未完成，请求协助排查SQL问题或提供其他优化方案。

现有代码实现

服务层递归方法

private void processRecommendedEvaluationMetaForReadyToExport(EvaluationMeta parentEvaluationMeta,
                                                              Set<EvaluationMeta> childEvaluationMetas,
                                                              Map<String, MCGEvaluationMetadata> mcgHsimContentVersionEvaluationMetadataMap) {

        // 获取给定父评估对应的子评估推荐列表
        Map<String, List<MCGEvaluationMetaMaster>> hsimAndchildEvaluationDefinitionsMap = mcgEvaluationMetaDao.findRecommendedChildEvaluationMeta(parentEvaluationMeta.getId());

        Iterator childEvaluationDefinitionsMapIterator = hsimAndchildEvaluationDefinitionsMap.entrySet().iterator();
        while (childEvaluationDefinitionsMapIterator.hasNext()) {
            Map.Entry childEvaluationDefinition = (Map.Entry) childEvaluationDefinitionsMapIterator.next();

            if (childEvaluationDefinition.getValue() != null && !((List<MCGEvaluationMetaMaster>) childEvaluationDefinition.getValue()).isEmpty()) {
                for (MCGEvaluationMetaMaster mcgEvaluationMetaMaster : (List<MCGEvaluationMetaMaster>) childEvaluationDefinition.getValue()) {

                    MCGEvaluationMetadata mcgEvaluationMetadata = mcgHsimContentVersionEvaluationMetadataMap.get(mcgEvaluationMetaMaster.getHsim() + mcgEvaluationMetaMaster.getMcgContentVersion().getContentVersion());
                    // 仅将已发布/禁用状态的评估定义标记为可导出，同时跳过父评估元（后续会单独标记）
                    
                    if (canMcgEvaluationBeMarkedAsReadyToExport(mcgEvaluationMetadata) && !childEvaluationMetas.contains(mcgEvaluationMetadata.getEvaluationMeta()) &&
                            !parentEvaluationMeta.getResource().getName().equals(mcgEvaluationMetadata.getEvaluationMeta().getResource().getName())) {

                        childEvaluationMetas.add(mcgEvaluationMetadata.getEvaluationMeta());
                        processRecommendedEvaluationMetaForReadyToExport(mcgEvaluationMetadata.getEvaluationMeta(), childEvaluationMetas, mcgHsimContentVersionEvaluationMetadataMap);
                    }
                }
            }
        }
    }

DAO层代码

private static final String GET_RECOMMENDED_CHILD_EVALUATIONS =
            "MCGEvaluationMetaRecommendation.getRecommendedChildEvaluations";  



  public Map<String, List<MCGEvaluationMetaMaster>> findRecommendedChildEvaluationMeta(final String evaluationMetaId) {
            Map<String, List<MCGEvaluationMetaMaster>> recommendedChildGuidelineInfo = new HashMap<>();
            if (evaluationMetaId != null) {
                final Query query = getEntityManager().createNamedQuery(GET_RECOMMENDED_CHILD_ASSESSMENTS);
                query.setParameter(ASSESSMENT_META_ID, evaluationMetaId);
                List<MCGEvaluationMetaRecommendation> resultList = query.getResultList(); // 获取MCGEvaluationMetaRecommendation
    
                // 处理给定的父评估元ID对应的结果
                if (resultList != null && !resultList.isEmpty()) {
                    for (MCGEvaluationMetaRecommendation mcgEvaluationMetaRecommendation : resultList) {
                        populateRecommendedChildGuidelineInfo(mcgEvaluationMetaRecommendation, recommendedChildGuidelineInfo);
                    }
                }
            }
            return recommendedChildGuidelineInfo;
        }   
    
    private void populateRecommendedChildGuidelineInfo(MCGEvaluationMetaRecommendation mcgEvaluationMetaRecommendation,
                                                            Map<String, List<MCGEvaluationMetaMaster>> recommendedChildGuidelineInfo){
            if (mcgEvaluationMetaRecommendation.getParentEvaluationResponseDefinition() != null) {
                List<MCGEvaluationMetaMaster> mcgEvaluationMetaMasterList;
                String evaluationResponseDefinitionId = mcgEvaluationMetaRecommendation.getParentEvaluationResponseDefinition().getId();
                MCGEvaluationMetaMaster mcgEvaluationMetaMaster = mcgEvaluationMetaRecommendation.getChildMCGEvaluationMetaMaster();
                if (recommendedChildGuidelineInfo.get(evaluationResponseDefinitionId) != null) {
                    mcgEvaluationMetaMasterList = recommendedChildGuidelineInfo.get(evaluationResponseDefinitionId);
                    // 检查当前评估定义是否已存在于列表中，不存在则添加
                    if (mcgEvaluationMetaMasterList != null && !mcgEvaluationMetaMasterList.contains(mcgEvaluationMetaMaster)) {
                        mcgEvaluationMetaMasterList.add(mcgEvaluationMetaMaster);
                    }
                } else {
                    mcgEvaluationMetaMasterList = new ArrayList<>();
                    mcgEvaluationMetaMasterList.add(mcgEvaluationMetaMaster);
                    recommendedChildGuidelineInfo.put(evaluationResponseDefinitionId, mcgEvaluationMetaMasterList);
                }
            }
        }

Hibernate命名查询

<query name="MCGEvaluationMetaRecommendation.getRecommendedChildEvaluations">
        <![CDATA[
      SELECT mcgEvaluationMetaRecommendation
      FROM com.casenet.domain.evaluation.mcg.MCGEvaluationMetaRecommendation mcgEvaluationMetaRecommendation
      INNER JOIN mcgEvaluationMetaRecommendation.parentMCGEvaluationMetadata parentMCGEvaluationMeta
      WHERE parentMCGEvaluationMeta.evaluationMeta.id = :evaluationMetaId
      AND mcgEvaluationMetaRecommendation.obsolete = 0
      AND parentMCGEvaluationMeta.obsolete = 0
    ]]>
    </query>

简化表结构

表: MCGEvaluationMetaRecommendation

mcg_evaluation_meta_recommendation_id
obsolete
parent_evaluation_response_definition_id
child_mcg_evaluation_meta_master_id
parent_mcg_evaluation_metadata_id

表: MCGEvaluationMetadata

mcg_evaluation_metadata_id
evaluation_meta_id
mcg_evaluation_meta_master_id
created_date
obsolete

超时的递归SQL

WITH parent_child AS (
    SELECT 
        meta.mcg_evaluation_metadata_id                 METADATA_ID,
        meta.mcg_evaluation_meta_master_id              META_MASTER_ID, 
        meta.evaluation_meta_id                         META_ID,
        meta.obsolete                                   META_OBSOLETE,
        rec.mcg_evaluation_meta_recommendation_id       REC_META_RECOMM_ID,
        rec.parent_evaluation_response_definition_id    REC_PARENT_EVALUATION_RESPONSE_DEF_ID,
        rec.child_mcg_evaluation_meta_master_id         REC_CHILD_EVALUATION_META_MASTER_ID,
        rec.parent_mcg_evaluation_metadata_id           REC_PARENT_EVALUATION_METADATA_ID,
        rec.obsolete                                    REC_OBSOLETE  
    FROM
        MCGevaluationMetaRecommendation rec,
        MCGevaluationMetadata meta
    WHERE 
        rec.parent_mcg_evaluation_metadata_id = meta.mcg_evaluation_metadata_id 
),
generation AS (
    SELECT
        METADATA_ID,
        META_MASTER_ID, 
        META_ID,
        META_OBSOLETE,
        REC_META_RECOMM_ID,
        REC_PARENT_EVALUATION_RESPONSE_DEF_ID,
        REC_CHILD_EVALUATION_META_MASTER_ID,
        REC_PARENT_EVALUATION_METADATA_ID,
        REC_OBSOLETE,
        0 AS level
    FROM
        parent_child child
        WHERE child.META_ID = 'root-id-passed-as-query-param'
        AND   child.META_OBSOLETE = 0
        AND   child.REC_OBSOLETE = 0
        
    UNION ALL

    SELECT
        child.METADATA_ID,
        child.META_MASTER_ID, 
        child.META_ID,
        child.META_OBSOLETE,
        child.REC_META_RECOMM_ID,
        child.REC_PARENT_EVALUATION_RESPONSE_DEF_ID,
        child.REC_CHILD_EVALUATION_META_MASTER_ID,
        child.REC_PARENT_EVALUATION_METADATA_ID,
        child.REC_OBSOLETE,
        level+1 AS level
    FROM
        parent_child child
        JOIN generation g
        ON g.REC_CHILD_EVALUATION_META_MASTER_ID = child.META_MASTER_ID                                                          
)
SELECT  * 
FROM generation g
JOIN parent_child parent
ON g.REC_PARENT_EVALUATION_METADATA_ID = parent.METADATA_ID
ORDER BY level DESC
OPTION (MAXRECURSION 0);

优化建议

1. 修复递归SQL的逻辑问题

当前递归SQL存在几个明显性能瓶颈：

冗余关联：最终查询中JOIN parent_child parent完全多余，generation已包含parent_child的所有字段，无需重复关联。
初始CARTESIAN JOIN风险：parent_child CTE直接关联两张表，若无索引支持会触发全表扫描，数据量大时性能极差。
递归关联逻辑存疑：g.REC_CHILD_EVALUATION_META_MASTER_ID = child.META_MASTER_ID的关联是否匹配业务层级关系？需确认REC_CHILD_EVALUATION_META_MASTER_ID是否与META_MASTER_ID为同一维度的ID。

修改后的递归SQL示例：

WITH RECURSIVE generation AS (
    -- 初始节点：直接从根ID关联查询
    SELECT 
        meta.mcg_evaluation_metadata_id AS METADATA_ID,
        meta.mcg_evaluation_meta_master_id AS META_MASTER_ID,
        meta.evaluation_meta_id AS META_ID,
        meta.obsolete AS META_OBSOLETE,
        rec.mcg_evaluation_meta_recommendation_id AS REC_META_RECOMM_ID,
        rec.parent_evaluation_response_definition_id AS REC_PARENT_EVALUATION_RESPONSE_DEF_ID,
        rec.child_mcg_evaluation_meta_master_id AS REC_CHILD_EVALUATION_META_MASTER_ID,
        rec.parent_mcg_evaluation_metadata_id AS REC_PARENT_EVALUATION_METADATA_ID,
        rec.obsolete AS REC_OBSOLETE,
        0 AS level
    FROM MCGEvaluationMetaRecommendation rec
    INNER JOIN MCGEvaluationMetadata meta 
        ON rec.parent_mcg_evaluation_metadata_id = meta.mcg_evaluation_metadata_id
    WHERE meta.evaluation_meta_id = 'root-id-passed-as-query-param'
      AND meta.obsolete = 0
      AND rec.obsolete = 0

    UNION ALL

    -- 递归节点：关联下一层级数据
    SELECT 
        meta.mcg_evaluation_metadata_id AS METADATA_ID,
        meta.mcg_evaluation_meta_master_id AS META_MASTER_ID,
        meta.evaluation_meta_id AS META_ID,
        meta.obsolete AS META_OBSOLETE,
        rec.mcg_evaluation_meta_recommendation_id AS REC_META_RECOMM_ID,
        rec.parent_evaluation_response_definition_id AS REC_PARENT_EVALUATION_RESPONSE_DEF_ID,
        rec.child_mcg_evaluation_meta_master_id AS REC_CHILD_EVALUATION_META_MASTER_ID,
        rec.parent_mcg_evaluation_metadata_id AS REC_PARENT_EVALUATION_METADATA_ID,
        rec.obsolete AS REC_OBSOLETE,
        g.level + 1 AS level
    FROM generation g
    INNER JOIN MCGEvaluationMetaRecommendation rec 
        ON g.REC_CHILD_EVALUATION_META_MASTER_ID = rec.child_mcg_evaluation_meta_master_id
    INNER JOIN MCGEvaluationMetadata meta 
        ON rec.parent_mcg_evaluation_metadata_id = meta.mcg_evaluation_metadata_id
    WHERE meta.obsolete = 0
      AND rec.obsolete = 0
)
SELECT * 
FROM generation
ORDER BY level DESC
OPTION (MAXRECURSION 0);

2. 添加必要索引

为查询添加索引，减少全表扫描：

MCGEvaluationMetadata：CREATE INDEX idx_meta_eval_id_obsolete ON MCGEvaluationMetadata(evaluation_meta_id, obsolete);
MCGEvaluationMetaRecommendation：CREATE INDEX idx_rec_parent_meta_id_obsolete ON MCGEvaluationMetaRecommendation(parent_mcg_evaluation_metadata_id, obsolete);
MCGEvaluationMetaRecommendation：CREATE INDEX idx_rec_child_master_id ON MCGEvaluationMetaRecommendation(child_mcg_evaluation_meta_master_id);

3. 替代方案：内存中构建层级关系

如果递归SQL优化后仍不理想，可尝试：

一次性查询所有非废弃的MCGEvaluationMetaRecommendation和MCGEvaluationMetadata数据到内存
用Map构建父子映射关系（比如Map<String, List<MCGEvaluationMetaRecommendation>>按父ID分组）
在Java内存中执行递归逻辑，彻底消除多次数据库调用的网络开销

4. 优化现有Java递归逻辑

若暂时无法修改SQL，可优化代码减少无效操作：

替换Set.contains()：改用Map<String, EvaluationMeta>存储已处理节点，通过ID判断是否存在（避免依赖对象equals的低效判断）
提前过滤mcgHsimContentVersionEvaluationMetadataMap中的无效数据，减少循环内的重复判断

内容的提问来源于stack exchange，提问作者Akash Sharma

递归SQL优化：替换Java递归方法排查性能瓶颈及查询异常

问题背景 #

现有代码实现 #

服务层递归方法 #

DAO层代码 #

Hibernate命名查询 #

简化表结构 #

表: MCGEvaluationMetaRecommendation #

表: MCGEvaluationMetadata #

超时的递归SQL #

优化建议 #

1. 修复递归SQL的逻辑问题 #

2. 添加必要索引 #

3. 替代方案：内存中构建层级关系 #

4. 优化现有Java递归逻辑 #