Db2递归SQL树查询性能优化求助（150万行数据集）

问题背景 #

从SOURCE_TABLE构建完整父子层级树（含所有父到子、孙辈等路径）的SQL在小数据集下运行正常，但当表数据超过150万行时，运行20小时仍无结果，需优化。

相关信息 #

表结构 #

CREATE TABLE SOURCE_TABLE (
            PARENT_ID VARCHAR(12),
            CHILD_ID VARCHAR(12)
);

测试数据 #

对应的插入语句：

INSERT INTO SOURCE_TABLE (PARENT_ID, CHILD_ID) VALUES (NULL, 'A');
INSERT INTO SOURCE_TABLE (PARENT_ID, CHILD_ID) VALUES (NULL, 'B');
INSERT INTO SOURCE_TABLE (PARENT_ID, CHILD_ID) VALUES (NULL, 'C');
INSERT INTO SOURCE_TABLE (PARENT_ID, CHILD_ID) VALUES ('A', 'A_1');
INSERT INTO SOURCE_TABLE (PARENT_ID, CHILD_ID) VALUES ('A_1', 'A_1_2');
INSERT INTO SOURCE_TABLE (PARENT_ID, CHILD_ID) VALUES ('B', 'B_1');

当前使用的SQL代码 #

WITH PARENTS(ID) AS
(
    SELECT DISTINCT PARENT_ID
    FROM SOURCE_TABLE
    WHERE PARENT_ID IS NOT NULL
),
LVL_TREE(LVL, DIM_PARENT_ID, DIM_CHILD_ID) AS
(
     SELECT 1, PARENT_ID, CHILD_ID
     FROM SOURCE_TABLE
     WHERE PARENT_ID IS NULL
     UNION ALL
     SELECT L.LVL + 1, Q.PARENT_ID, Q.CHILD_ID
     FROM LVL_TREE L, SOURCE_TABLE Q
     WHERE Q.PARENT_ID = L.DIM_CHILD_ID
),         
TREE(DIM_PARENT_ID, DIM_CHILD_ID, DIM_PARENT_LVL, DIM_CHILD_LVL, DIM_LVL, DIM_TYPE) AS
(
    SELECT DIM_PARENT_ID, DIM_CHILD_ID, LVL-1, LVL, 1,
    CASE WHEN DIM_CHILD_ID IN (SELECT ID FROM PARENTS) THEN 'S' ELSE 'B' END AS DIM_TYPE
    FROM LVL_TREE
    UNION ALL
    SELECT T.DIM_PARENT_ID, Q.CHILD_ID, T.DIM_PARENT_LVL, T.DIM_PARENT_LVL + T.DIM_LVL + 1, T.DIM_LVL + 1,
    CASE WHEN Q.CHILD_ID IN (SELECT ID FROM PARENTS) THEN 'S' ELSE 'B' END AS DIM_TYPE
    FROM TREE T, SOURCE_TABLE Q
    WHERE T.DIM_PARENT_ID IS NOT NULL AND Q.PARENT_ID = T.DIM_CHILD_ID
    UNION ALL
    SELECT DIM_CHILD_ID, DIM_CHILD_ID, LVL, LVL, 0, 'B'
    FROM LVL_TREE WHERE DIM_CHILD_ID NOT IN (SELECT ID FROM PARENTS)
)
SELECT
    DIM_PARENT_ID,
    DIM_CHILD_ID,
    DIM_PARENT_LVL,
    DIM_CHILD_LVL,
    DIM_LVL,
    DIM_TYPE
FROM TREE
WHERE DIM_PARENT_ID IS NOT NULL

小数据集下的正确结果 #

优化建议 #

1. 构建高效索引 #

索引是大数据量递归查询的核心优化点：

• 为关联字段创建复合索引：

  CREATE INDEX IX_SOURCE_PARENT_CHILD ON SOURCE_TABLE(PARENT_ID, CHILD_ID);
  

  递归CTE每次关联都依赖PARENT_ID匹配，该索引可将全表扫描转为索引范围扫描，大幅降低IO开销。
• 为CHILD_ID创建唯一索引（若CHILD_ID唯一）：

  CREATE UNIQUE INDEX IX_SOURCE_CHILD ON SOURCE_TABLE(CHILD_ID);
  

  用于快速判断节点是否为父节点（即是否存在于PARENT_ID集合中）。

2. 合并递归CTE，减少中间计算 #

原SQL使用两层递归CTE，导致中间结果集重复存储和计算。可合并为单一层递归CTE，直接生成所有需要的父子路径：

WITH RECURSIVE FULL_TREE AS (
    -- 初始化：根节点的自关联记录
    SELECT 
        CHILD_ID AS DIM_PARENT_ID,
        CHILD_ID AS DIM_CHILD_ID,
        1 AS DIM_PARENT_LVL,
        1 AS DIM_CHILD_LVL,
        0 AS DIM_LVL,
        CASE WHEN EXISTS (SELECT 1 FROM SOURCE_TABLE WHERE PARENT_ID = S.CHILD_ID) THEN 'S' ELSE 'B' END AS DIM_TYPE
    FROM SOURCE_TABLE S
    WHERE PARENT_ID IS NULL
    UNION ALL
    -- 递归生成父节点到所有子节点的路径
    SELECT 
        F.DIM_PARENT_ID,
        S.CHILD_ID,
        F.DIM_PARENT_LVL,
        F.DIM_CHILD_LVL + 1,
        F.DIM_LVL + 1,
        CASE WHEN EXISTS (SELECT 1 FROM SOURCE_TABLE WHERE PARENT_ID = S.CHILD_ID) THEN 'S' ELSE 'B' END AS DIM_TYPE
    FROM FULL_TREE F
    JOIN SOURCE_TABLE S ON F.DIM_CHILD_ID = S.PARENT_ID
    UNION ALL
    -- 生成子节点的自关联记录
    SELECT 
        S.CHILD_ID,
        S.CHILD_ID,
        F.DIM_CHILD_LVL + 1,
        F.DIM_CHILD_LVL + 1,
        0,
        CASE WHEN EXISTS (SELECT 1 FROM SOURCE_TABLE WHERE PARENT_ID = S.CHILD_ID) THEN 'S' ELSE 'B' END AS DIM_TYPE
    FROM FULL_TREE F
    JOIN SOURCE_TABLE S ON F.DIM_CHILD_ID = S.PARENT_ID
)
SELECT DISTINCT
    DIM_PARENT_ID,
    DIM_CHILD_ID,
    DIM_PARENT_LVL,
    DIM_CHILD_LVL,
    DIM_LVL,
    DIM_TYPE
FROM FULL_TREE
WHERE DIM_PARENT_ID IS NOT NULL
ORDER BY DIM_PARENT_ID, DIM_CHILD_LVL;

3. 替换IN子查询为EXISTS，避免重复扫描 #

原SQL中多次使用IN (SELECT ID FROM PARENTS)，大数据量下IN子查询会重复扫描数据，改用EXISTS或临时表优化：

• 提前生成父节点临时表并加索引：

  DECLARE GLOBAL TEMPORARY TABLE SESSION.PARENTS (ID VARCHAR(12)) NOT LOGGED;
  INSERT INTO SESSION.PARENTS SELECT DISTINCT PARENT_ID FROM SOURCE_TABLE WHERE PARENT_ID IS NOT NULL;
  CREATE INDEX IX_SESSION_PARENTS ON SESSION.PARENTS(ID);
  

• 之后将CASE判断改为：

  CASE WHEN EXISTS (SELECT 1 FROM SESSION.PARENTS WHERE ID = 目标字段) THEN 'S' ELSE 'B' END
  

  EXISTS采用短路判断，性能远优于IN子查询。

4. 调整Db2递归参数 #

• 增大递归深度限制：在递归CTE末尾添加OPTION (MAX_RECURSION 10000)（根据实际层级调整数值），避免因层级过深导致的多次循环重试。
• 开启兼容性优化：设置DB2_COMPATIBILITY_VECTOR=ORA，启用Oracle兼容模式下的递归CTE优化逻辑。

5. 检查并清理循环引用 #

若表中存在循环引用（如A→B→A），会导致递归无限循环，拖慢性能。可通过以下SQL检查：

WITH RECURSIVE CYCLE_CHECK AS (
    SELECT PARENT_ID, CHILD_ID, 1 AS LVL, CAST(CHILD_ID AS VARCHAR(1000)) AS PATH
    FROM SOURCE_TABLE
    WHERE PARENT_ID IS NOT NULL
    UNION ALL
    SELECT C.PARENT_ID, S.CHILD_ID, C.LVL + 1, C.PATH || '->' || S.CHILD_ID
    FROM CYCLE_CHECK C
    JOIN SOURCE_TABLE S ON C.CHILD_ID = S.PARENT_ID
    WHERE C.PATH NOT LIKE '%' || S.CHILD_ID || '%'
)
SELECT * FROM CYCLE_CHECK WHERE LVL > 100; -- 层级超过100的记录大概率是循环引用

若发现循环，需清理数据或在递归中添加循环判断逻辑。

6. 分批次处理 #

针对超大规模数据，可按根节点（PARENT_ID为NULL的节点）分批次生成层级树，最后合并结果：

-- 先获取所有根节点
DECLARE GLOBAL TEMPORARY TABLE SESSION.ROOT_NODES (CHILD_ID VARCHAR(12)) NOT LOGGED;
INSERT INTO SESSION.ROOT_NODES SELECT CHILD_ID FROM SOURCE_TABLE WHERE PARENT_ID IS NULL;

-- 创建结果临时表
DECLARE GLOBAL TEMPORARY TABLE SESSION.FINAL_RESULT (
    DIM_PARENT_ID VARCHAR(12),
    DIM_CHILD_ID VARCHAR(12),
    DIM_PARENT_LVL INT,
    DIM_CHILD_LVL INT,
    DIM_LVL INT,
    DIM_TYPE CHAR(1)
) NOT LOGGED;

-- 需通过Db2存储过程实现循环处理每个根节点，以下为核心逻辑示例
FOR EACH root IN SESSION.ROOT_NODES DO
    WITH RECURSIVE TREE AS (
        SELECT root.CHILD_ID AS DIM_PARENT_ID, root.CHILD_ID AS DIM_CHILD_ID, 1 AS DIM_PARENT_LVL, 1 AS DIM_CHILD_LVL, 0 AS DIM_LVL,
               CASE WHEN EXISTS (SELECT 1 FROM SOURCE_TABLE WHERE PARENT_ID = root.CHILD_ID) THEN 'S' ELSE 'B' END AS DIM_TYPE
        UNION ALL
        SELECT T.DIM_PARENT_ID, S.CHILD_ID, T.DIM_PARENT_LVL, T.DIM_CHILD_LVL +1, T.DIM_LVL +1,
               CASE WHEN EXISTS (SELECT 1 FROM SOURCE_TABLE WHERE PARENT_ID = S.CHILD_ID) THEN 'S' ELSE 'B' END AS DIM_TYPE
        FROM TREE T
        JOIN SOURCE_TABLE S ON T.DIM_CHILD_ID = S.PARENT_ID
        UNION ALL
        SELECT S.CHILD_ID, S.CHILD_ID, T.DIM_CHILD_LVL +1, T.DIM_CHILD_LVL +1, 0,
               CASE WHEN EXISTS (SELECT 1 FROM SOURCE_TABLE WHERE PARENT_ID = S.CHILD_ID) THEN 'S' ELSE 'B' END AS DIM_TYPE
        FROM TREE T
        JOIN SOURCE_TABLE S ON T.DIM_CHILD_ID = S.PARENT_ID
    )
    INSERT INTO SESSION.FINAL_RESULT SELECT DISTINCT * FROM TREE;
END FOR;

-- 查询最终结果
SELECT * FROM SESSION.FINAL_RESULT;

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