CTE与NFSE数据查询方案性能对比及优化建议咨询

我来帮你分析这两种查询方案的性能差异，并给出适合Java端实现的最优选择，同时补充一些实用的优化建议。

方案1：先关联所有表再UNION #

SELECT DISTINCT 'CTE' as docTypeID , cte.isqn_mstr_cd as ctePk , cte.ct_e_cd , cter.stat_desc , awb.CREATE_DT , awb.AWB_NBR , awb.SHPR_NM , awbs.DEST_LOC_CD , nfe.NT_FSCL_CD , nfe.FSCL_DOC_NBR 
FROM cte_identity_master cte 
INNER JOIN cte_response_detail cter ON (cte.isqn_mstr_cd = cter.isqn_mstr_cd) 
LEFT JOIN match_ref_awb mawb ON (cte.isqn_ref_cd = mawb.isqn_mstr_cd) 
LEFT JOIN awb_cust_master awb ON (mawb.awb_nbr = awb.awb_nbr) 
LEFT JOIN awb_shipment_detail awbs ON (awb.awb_nbr = awbs.awb_nbr) 
LEFT JOIN match_ref_nfe mnfe ON (cte.isqn_ref_cd = mnfe.isqn_mstr_cd) 
LEFT JOIN nfe_identity_master nfe ON (mnfe.nt_fscl_cd = nfe.nt_fscl_cd) 

UNION 

SELECT DISTINCT 'NFSE' as docTypeID , nfse.isqn_mstr_cd as nfsePk , nfse.rp_s_id , nfser.stat_desc , awb.CREATE_DT , awb.AWB_NBR , awb.SHPR_NM , awbs.DEST_LOC_CD , nfe.NT_FSCL_CD , nfe.FSCL_DOC_NBR 
FROM nfse_request_detail nfse 
INNER JOIN nfse_response_detail nfser ON (nfse.isqn_mstr_cd = nfser.isqn_mstr_cd) 
LEFT JOIN match_ref_awb mawb ON (nfse.isqn_ref_cd = mawb.isqn_mstr_cd) 
LEFT JOIN awb_cust_master awb ON (mawb.awb_nbr = awb.awb_nbr) 
LEFT JOIN awb_shipment_detail awbs ON (awb.awb_nbr = awbs.awb_nbr) 
LEFT JOIN match_ref_nfe mnfe ON (nfse.isqn_ref_cd = mnfe.isqn_mstr_cd) 
LEFT JOIN nfe_identity_master nfe ON (mnfe.nt_fscl_cd = nfe.nt_fscl_cd)

方案2：先UNION核心数据再关联共享表 #

SELECT ctnf.* , awb.CREATE_DT , awb.AWB_NBR , awb.SHPR_NM , awbs.DEST_LOC_CD , nfe.NT_FSCL_CD , nfe.FSCL_DOC_NBR 
FROM ( 
    SELECT DISTINCT 'CTE' as docTypeID , cte.isqn_mstr_cd as docPk , cte.ct_e_cd as docNbr , cter.stat_desc as docStat , cte.isqn_ref_cd as matchRef 
    FROM cte_identity_master cte 
    INNER JOIN cte_response_detail cter ON (cte.isqn_mstr_cd = cter.isqn_mstr_cd) 

    UNION 

    SELECT DISTINCT 'NFSE' as docTypeID , nfse.isqn_mstr_cd as docPk , nfse.rp_s_id as docNbr , nfser.stat_desc as docStat , nfse.isqn_ref_cd as matchRef 
    FROM nfse_request_detail nfse 
    INNER JOIN nfse_response_detail nfser ON (nfse.isqn_mstr_cd = nfser.isqn_mstr_cd) 
) ctnf 
LEFT JOIN match_ref_awb mawb ON (ctnf.matchRef = mawb.isqn_mstr_cd) 
LEFT JOIN awb_cust_master awb ON (mawb.awb_nbr = awb.awb_nbr) 
LEFT JOIN awb_shipment_detail awbs ON (awb.awb_nbr = awbs.awb_nbr) 
LEFT JOIN match_ref_nfe mnfe ON (ctnf.matchRef = mnfe.isqn_mstr_cd) 
LEFT JOIN nfe_identity_master nfe ON (mnfe.nt_fscl_cd = nfe.nt_fscl_cd)

共用WHERE子句 #

WHERE lower(cte.sttn_cd) = lower(:stationId) 
  and (:documentType is null or lower(:documentType) = 'cte') 
  and (:shipperName is null or lower(awb.shipperNm) like lower(concat(concat('%',:shipperName),'%'))) 
  and (:awbCreated is null or to_char(awb.createDt, 'MM-DD-YYYY') = :awbCreated) 
  and (:awbNumber is null or m2.awbNbr like concat(concat('%',:awbNumber),'%')) 
  and (:serviceType = 0 or awbs.baseServiceCd = :serviceType) 
  and (:commitmentDate is null or awbs.commitmentDate = :commitmentDate) 
  and (:ursa is null or lower(awbs.ursaCd) like lower(concat(concat('%',:ursa),'%'))) 
  and (:destLocationId is null or lower(awbs.destLocCd) like lower(concat(concat('%',:destLocationId),'%'))) 
  and (:nfeNumber is null or nfe.fiscalDocumentNumber like concat(concat('%',:nfeNumber),'%'))

方案对比与性能分析 #

毫无疑问，方案2的执行速度会远快于方案1，核心原因有三点：

• 避免重复关联操作：方案1中CTE和NFSE两条查询要重复执行完全相同的LEFT JOIN逻辑（关联awb、nfe等表），相当于数据库做了两次冗余的表连接计算。而方案2只需要执行一次共享表的关联，直接砍掉了一半的重复工作量。
• 更小的中间数据集：方案2先通过UNION获取CTE和NFSE的核心数据（字段更少、行数更少），再基于这个小数据集去关联其他表，IO开销和计算量都会显著降低。而方案1的两条子查询都会先关联出大结果集，再做UNION去重，成本极高。
• 更低的去重开销：UNION会自动去重，方案1是在两个大结果集上去重，而方案2是在小的核心数据集上去重，去重的计算成本差了一个量级。

Java端实现适配性 #

方案2更适合Java端落地：

• 语句结构更清晰，核心业务逻辑（CTE/NFSE数据提取）和关联逻辑分离，后续维护、修改或扩展都更方便。
• 性能优势直接转化为Java接口的响应速度提升，尤其是在数据量较大时，能明显减少接口超时的概率。

关键修正与优化建议 #

1. 修正方案2的字段缺失问题 #

原方案2的子查询缺少sttn_cd字段，导致无法直接使用给定的WHERE子句。需要在子查询中加入该字段（注意NFSE表对应的字段名可能不同，需根据实际表结构调整）：

SELECT ctnf.* , awb.CREATE_DT , awb.AWB_NBR , awb.SHPR_NM , awbs.DEST_LOC_CD , nfe.NT_FSCL_CD , nfe.FSCL_DOC_NBR 
FROM ( 
    SELECT DISTINCT 
        'CTE' as docTypeID , 
        cte.isqn_mstr_cd as docPk , 
        cte.ct_e_cd as docNbr , 
        cter.stat_desc as docStat , 
        cte.isqn_ref_cd as matchRef,
        cte.sttn_cd  -- 加入CTE的station字段
    FROM cte_identity_master cte 
    INNER JOIN cte_response_detail cter ON (cte.isqn_mstr_cd = cter.isqn_mstr_cd) 

    UNION 

    SELECT DISTINCT 
        'NFSE' as docTypeID , 
        nfse.isqn_mstr_cd as docPk , 
        nfse.rp_s_id as docNbr , 
        nfser.stat_desc as docStat , 
        nfse.isqn_ref_cd as matchRef,
        nfse.sttn_cd  -- 加入NFSE对应的station字段
    FROM nfse_request_detail nfse 
    INNER JOIN nfse_response_detail nfser ON (nfse.isqn_mstr_cd = nfser.isqn_mstr_cd) 
) ctnf 
LEFT JOIN match_ref_awb mawb ON (ctnf.matchRef = mawb.isqn_mstr_cd) 
LEFT JOIN awb_cust_master awb ON (mawb.awb_nbr = awb.awb_nbr) 
LEFT JOIN awb_shipment_detail awbs ON (awb.awb_nbr = awbs.awb_nbr) 
LEFT JOIN match_ref_nfe mnfe ON (ctnf.matchRef = mnfe.isqn_mstr_cd) 
LEFT JOIN nfe_identity_master nfe ON (mnfe.nt_fscl_cd = nfe.nt_fscl_cd)

同时修改WHERE子句中的lower(cte.sttn_cd)为lower(ctnf.sttn_cd)，并修正笔误m2.awbNbr为mawb.awb_nbr。

2. 通用性能优化 #

• 替换UNION为UNION ALL：如果能保证CTE和NFSE的核心数据没有重复行（即docTypeID + docPk的组合唯一），用UNION ALL代替UNION，去掉去重开销，性能会再上一个台阶。
• 添加索引：给关联字段（isqn_mstr_cd、isqn_ref_cd、awb_nbr等）和WHERE子句中用到的过滤字段（sttn_cd、shipperNm、createDt等）创建合适的索引，大幅提升查询速度。
• 移除不必要的DISTINCT：检查DISTINCT是否真的必要，如果INNER JOIN和后续关联不会产生重复行，直接去掉，减少去重成本。
• Java端使用PreparedStatement：用参数化查询避免SQL注入，同时让数据库缓存执行计划，提升重复查询的效率。

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