SQL查询性能优化咨询：无需日期差计算的提速方案

一、替换标量子查询为JOIN，消除行级子查询开销 #

原查询中COMP_NM、GPROD_NM、SITE_NM通过标量子查询实现，每返回一行就会执行一次子查询，数据量大时性能极差。改为LEFT JOIN关联对应表，批量获取数据：

SELECT
    T1.APPL_SEQ
    , T1.COMP_CD
    , C.COMP_NM
    , T1.GPROD_CD
    , G.GPROD_NM
    , T1.SITE_CD
    , S.SITE_NM
    -- 其余字段保持不变
FROM
    tb_applicant T1
    LEFT JOIN mm_applicant T2 ON T1.APPL_SEQ = T2.APPL_SEQ
    LEFT JOIN dist_applicant T3 ON T1.APPL_SEQ = T3.APPL_SEQ
    INNER JOIN tb_site T4 ON T4.site_cd = T1.SITE_CD AND T4.comp_cd = T1.COMP_CD AND T4.gprod_cd = T1.GPROD_CD AND T4.is_use = 'Y'
    LEFT JOIN tb_company C ON C.COMP_CD = T1.COMP_CD
    LEFT JOIN tb_gprod G ON G.GPROD_CD = T1.GPROD_CD
    LEFT JOIN tb_site S ON S.SITE_CD = T1.SITE_CD
WHERE
    T1.APPL_SEQ > 147293
    $Sql_Search
ORDER BY
    $Sql_OrderBy

二、重构HP2_COUNT字段逻辑，消除嵌套子查询 #

原HP2_COUNT的嵌套子查询是性能瓶颈核心，现在无需日期差，可根据需求选择以下两种简化方式：

• 仅判断是否存在更早的同HP、GPROD_CD记录：

  , CASE WHEN EXISTS(SELECT 1 FROM tb_applicant T11 WHERE T11.HP = T1.HP AND T11.GPROD_CD = T1.GPROD_CD AND T11.REG_DTM < T1.REG_DTM) THEN 1 ELSE -1 END AS HP2_COUNT
  

• 统计同HP、GPROD_CD且REG_DTM更早的记录数：

  , COUNT(*) OVER(PARTITION BY T1.HP, T1.GPROD_CD ORDER BY T1.REG_DTM ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS HP2_COUNT
  

窗口函数的执行效率远高于嵌套子查询，数据量大时优势显著。

三、添加针对性索引，加速查询匹配与排序 #

根据查询的过滤、关联、排序条件，创建以下索引：

• tb_applicant：创建复合索引 INDEX idx_hp_gprod_regdtm (HP, GPROD_CD, REG_DTM)，加速HP2_COUNT的关联查询；
• tb_applicant：创建索引 INDEX idx_appl_seq (APPL_SEQ)，加速WHERE条件过滤和与T2、T3表的关联；
• tb_site：创建复合索引 INDEX idx_site_comp_gprod_isuse (site_cd, comp_cd, gprod_cd, is_use)，匹配JOIN和WHERE中的is_use='Y'条件；
• tb_company、tb_gprod：确保主键为COMP_CD、GPROD_CD（或创建唯一索引），加速JOIN时的字段匹配；
• 若$Sql_OrderBy为固定字段（如REG_DTM），创建包含排序字段的复合索引，避免文件排序（filesort）。

四、优化加密字段处理 #

原查询中DECRYPTO(T1.NAME)、DECRYPTO(T1.HP)是CPU密集型操作：

• 若业务允许，新增字段存储解密后的NAME、HP，定时同步更新，避免查询时实时解密；
• 必须实时解密时，确保NAME、HP字段数据类型合适，减少解密开销。

五、简化JOIN与WHERE逻辑 #

原查询中WHERE 1=1为冗余条件，直接删除；另外LEFT JOIN tb_site T4后加AND T4.is_use='Y'会将LEFT JOIN转为INNER JOIN，直接改为INNER JOIN可优化执行计划（已在第一部分的SQL示例中调整）。

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