DB2 SQL查询性能优化咨询：新手低效查询改进建议

开发者阿泽

2026-8-20

DB2 SQL查询性能优化初步建议

我是SQL新手，写了一段DB2查询语句，现在发现它资源占用高、执行效率低。我知道要结合实际业务和系统知识才能给出精准优化方案，但还是希望能得到一些初步的优化方向和改进建议，谢谢大家。

原查询语句

SELECT LIF.EMPID, LIF.BPA_NR , YEAR(LIF.BEG_DTE) ,MONTH(LIF.BEG_DTE) ,LIF.TFIDZ
     , LVA.TRFIDZ, UZA.TRFIDZ, YEAR(LIF.BEG_DTE) - YEAR(KAR.DOB)
     , ((YEAR(APK.TERMDT) - YEAR(APK.BEGDAT)) * 12 + MONTH(APK.TERMDT) - MONTH(APK.BEGDAT))
     , KAR.DTHSUM, LVA.LVAGELD,  UZA.UZAGELD,  APK.EMIRKA
     , LVA.EMILVA, UZA.EMIUZA , APK.EPROM, LVA.EPROM, UZA.EPROM
     , LIF.LOANNR ,LIF.SUMAMT , MONTH(JOU.ZDATE), YEAR(JOU.ZDATE)
     , (YEAR(JOU.ZDATE) - YEAR(APK.BEGDAT))*12 + MONTH(JOU.ZDATE) - MONTH(APK.BEGDAT)
     , COALESCE(JOU.TYP,'OTH') , COALESCE(PRO1.BOOKEMI,0)
     , COALESCE(PRO2.BOOKEMI,0),COALESCE(PRO3.BOOKEMI,0)                  
FROM DEVDBA.LIFE LIF                                                    
LEFT OUTER JOIN (                                                         
    SELECT ( CASE WHEN JU6.REASN = 'AAA' 
                   AND NOT EXISTS (
                      SELECT 1 FROM DEVDBA.LOGTBL JU7             
                      WHERE JU7.EMPID=JU6.EMPID 
                        AND JU7.GANID>JU6.GANID     
                        AND JU7.ZDATE=JU6.ZDATE 
                        AND JU7.REASN='BBB'
                   ) THEN 'KRE'
                  WHEN JU6.REASN IN ('EEE','DDD') 
                     THEN 'SON'                   
                  ELSE 'OTH'                                                     
               END) AS TYP
         , JU6.EMPID, JU6.REASN, JU6.ZDATE, JU6.GANID     
    FROM DEVDBA.LOGTBL JU6                                            
    WHERE JU6.REASN IN ('AAA','EEE','DDD')                            
) JOU                                                               
    ON LIF.EMPID = JOU.EMPID                                  
   AND LIF.ZUNID = JOU.GANID                                  
LEFT OUTER JOIN DEVDBA.LVAGR LVA                                   
    ON JOU.EMPID = LVA.EMPID                                  
   AND JOU.GANID = LVA.ZUNID                                  
LEFT OUTER JOIN DEVDBA.UZAGR UZA                                   
    ON JOU.EMPID = UZA.EMPID                                  
   AND JOU.GANID = UZA.ZUNID                                  
LEFT OUTER JOIN DEVDBA.APKGR APK                                   
    ON  JOU.EMPID = APK.EMPID                                  
   AND JOU.GANID = APK.ZUNID                                  
LEFT OUTER JOIN DEVDBA.KARGR KAR                                  
    ON  JOU.EMPID = KAR.EMPID                                  
   AND JOU.GANID = KAR.ZUNID                                  
LEFT OUTER JOIN (                                                   
    SELECT PR1.EMPID, PR1.GANID, PR1.BOOKEMI       
    FROM DEVDBA.COMMS PR1, DEVDBA.LOGTBL JU1                   
    WHERE PR1.PROVART IN ('789')                             
      AND PR1.EMPID = JU1.EMPID                            
      AND PR1.GANID = JU1.GANID                            
) PRO1                                                   
    ON  JOU.EMPID = PRO1.EMPID                           
   AND JOU.GANID = PRO1.GANID                           
LEFT OUTER JOIN (                                               
    SELECT PR3.EMPID, PR3.GANID, PR3.BOOKEMI
    FROM DEVDBA.COMMS PR3, DEVDBA.LOGTBL JU3             
    WHERE PR3.PROVART IN ('123')                             
      AND PR3.EMPID = JU3.EMPID                            
      AND PR3.GANID = JU3.GANID                            
) PRO2                                                   
    ON  JOU.EMPID = PRO2.EMPID                           
   AND JOU.GANID = PRO2.GANID                           
LEFT OUTER JOIN (                                               
    SELECT PR5.EMPID, PR5.GANID, PR5.BOOKEMI
    FROM DEVDBA.COMMS PR5, DEVDBA.LOGTBL JU5             
    WHERE PR5.PROVART IN ('456')                             
      AND PR5.EMPID = JU5.EMPID                            
      AND PR5.GANID = JU5.GANID                            
) PRO3                                                   
    ON  JOU.EMPID = PRO3.EMPID                           
    AND JOU.GANID = PRO3.GANID                           
WHERE LIF.BPA_NR IN ('001','002') 
  AND JOU.TYP <> 'OTH'  
  AND LIF.BEG_DTE BETWEEN '01.01.2022' AND '31.12.2099'
ORDER BY LIF.BPA_NR,LIF.EMPID WITH UR;

初步优化方向与建议

1. 索引优化，减少全表扫描

给DEVDBA.LIFE表创建复合索引：(BPA_NR, BEG_DTE, EMPID, ZUNID)，既能快速过滤WHERE条件，又能直接满足关联JOU子查询的字段需求，避免回表。
给DEVDBA.LOGTBL表创建复合索引：(EMPID, ZDATE, GANID, REASN)，覆盖NOT EXISTS子查询的所有过滤和关联字段，提升子查询效率。
其他关联表（LVAGR、UZAGR、APKGR、KARGR、COMMS）：给EMPID和GANID创建联合索引，因为所有表连接都依赖这两个字段，索引能大幅降低连接耗时。

2. 合并重复子查询，减少表连接次数

原查询中PRO1、PRO2、PRO3的逻辑高度重复，仅PROVART值不同，可合并为一个子查询，避免三次重复关联表：

LEFT OUTER JOIN (
    SELECT 
        PR.EMPID, 
        PR.GANID,
        SUM(CASE WHEN PR.PROVART = '789' THEN PR.BOOKEMI ELSE 0 END) AS BOOKEMI_789,
        SUM(CASE WHEN PR.PROVART = '123' THEN PR.BOOKEMI ELSE 0 END) AS BOOKEMI_123,
        SUM(CASE WHEN PR.PROVART = '456' THEN PR.BOOKEMI ELSE 0 END) AS BOOKEMI_456
    FROM DEVDBA.COMMS PR
    JOIN DEVDBA.LOGTBL JU ON PR.EMPID = JU.EMPID AND PR.GANID = JU.GANID
    WHERE PR.PROVART IN ('789','123','456')
    GROUP BY PR.EMPID, PR.GANID
) PRO
    ON JOU.EMPID = PRO.EMPID AND JOU.GANID = PRO.GANID

之后将SELECT中的COALESCE(PRO1.BOOKEMI,0)替换为COALESCE(PRO.BOOKEMI_789,0)，以此类推。另外可确认：如果COMMS的EMPID和GANID本身无需LOGTBL验证有效性，可直接去掉JOIN LOGTBL的逻辑，进一步简化。

3. 调整连接类型，提前过滤无效数据

原查询中WHERE JOU.TYP <> 'OTH'会把LIF LEFT JOIN JOU转为隐式内连接（仅JOU有匹配记录时TYP才不为OTH），可直接改为INNER JOIN JOU，或把过滤条件移到JOU子查询中提前生效：

SELECT ...
FROM DEVDBA.LIFE LIF
INNER JOIN (
    SELECT ...
    FROM DEVDBA.LOGTBL JU6
    WHERE JU6.REASN IN ('AAA','EEE','DDD')
    -- 提前过滤掉TYP为OTH的记录
    AND (
        (JU6.REASN = 'AAA' AND NOT EXISTS (...))
        OR JU6.REASN IN ('EEE','DDD')
    )
) JOU ON ...

这样能在连接前就缩小JOU的结果集，减少后续数据处理量。

4. 简化日期计算，利用内置函数

手动计算月份差的逻辑可替换为DB2内置的MONTHS_BETWEEN函数，更简洁且优化器处理效率更高：

原计算：((YEAR(APK.TERMDT) - YEAR(APK.BEGDAT)) * 12 + MONTH(APK.TERMDT) - MONTH(APK.BEGDAT))
替换为：FLOOR(MONTHS_BETWEEN(APK.TERMDT, APK.BEGDAT))

如果经常用到YEAR(LIF.BEG_DTE)、MONTH(LIF.BEG_DTE)，可在LIFE表创建存储型计算列：

ALTER TABLE DEVDBA.LIFE ADD COLUMN BEG_YEAR INT GENERATED ALWAYS AS (YEAR(BEG_DTE)) STORED;
ALTER TABLE DEVDBA.LIFE ADD COLUMN BEG_MONTH INT GENERATED ALWAYS AS (MONTH(BEG_DTE)) STORED;

再给计算列创建索引，避免查询时重复计算日期值。

5. 分析执行计划，定位核心瓶颈

运行DB2的EXPLAIN工具查看执行计划，重点关注：

是否存在大表全表扫描（TABLE SCAN）
表连接顺序是否合理，是否有笛卡尔积风险
索引是否被正确调用（INDEX SCAN/INDEX ONLY SCAN）
通过执行计划能精准定位耗时步骤，再针对性优化。

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