优化层级父节点遍历SQL查询：避免冗余路径提升性能

问题背景 #

当前使用基于表变量的循环递归SQL查询遍历树形结构，结果正确但大数据集下性能不佳。需求是在避免冗余路径遍历的前提下，将指定子节点向上遍历至根节点，以此提升查询效率。

现有查询实现 #

DECLARE @lvl AS INT
DECLARE @rows AS INT
DECLARE @foo AS TABLE(
    parent_id INT,
    child_id INT,
    lvl INT)

-- 锚点条件：获取初始子节点范围
INSERT @foo (parent_id, child_id, lvl)
SELECT parent_id, child_id, 0
FROM bar WHERE child_id IN (SELECT child_id from bar WHERE child_id BETWEEN 50 AND 150)

SET @rows=@@ROWCOUNT
SET @lvl=0

-- 循环递归向上遍历父节点
WHILE @rows > 0
BEGIN
    SET @lvl = @lvl + 1

    INSERT @foo (parent_id, child_id, lvl)
    SELECT DISTINCT b.parent_id, b.child_id, @lvl
    FROM bar b
    INNER JOIN @foo f ON b.child_id = f.parent_id
    -- 避免重复访问：若父节点已在表中则跳过
    LEFT JOIN @foo dup ON dup.child_id = b.parent_id 
    WHERE f.lvl = @lvl-1 AND dup.parent_id IS NULL

    SET @rows=@@ROWCOUNT 
END

SELECT * FROM @foo ORDER BY lvl DESC 

表结构说明 #

测试用表bar的创建及数据生成逻辑如下：

CREATE TABLE bar(
    parent_id INT,
    child_id INT
);

DECLARE @counter INT = 1
WHILE @counter <= 10000
BEGIN
    DECLARE @parent_id INT;
    DECLARE @child_id INT;
    
    -- 生成子节点ID
    SET @child_id = @counter;
    
    -- 生成父节点ID：每10个子节点归属于一个父节点
    SET @parent_id = FLOOR((@counter - 1) / 10) + 1;
    
    INSERT INTO bar (parent_id, child_id)
    VALUES (@parent_id, @child_id);
    
    SET @counter = @counter + 1;
END;

性能优化建议与替代方案 #

1. 给表变量添加索引 #

表变量默认无索引，循环中多次JOIN操作会触发全表扫描，大幅降低效率。可添加复合主键或非聚集索引覆盖常用查询字段：

DECLARE @foo AS TABLE(
    parent_id INT,
    child_id INT,
    lvl INT,
    -- 添加聚簇主键，优化JOIN与查询性能
    PRIMARY KEY CLUSTERED (child_id, lvl)
)

2. 替换表变量为临时表 #

临时表（#foo）会生成统计信息，SQL Server优化器能据此生成更优执行计划，在大数据场景下性能比表变量更稳定：

CREATE TABLE #foo(
    parent_id INT,
    child_id INT,
    lvl INT,
    PRIMARY KEY CLUSTERED (child_id, lvl)
)

-- 后续逻辑将@foo替换为#foo即可

3. 简化锚点查询 #

原锚点查询中的嵌套子查询完全冗余，直接过滤child_id范围即可：

INSERT @foo (parent_id, child_id, lvl)
SELECT parent_id, child_id, 0
FROM bar WHERE child_id BETWEEN 50 AND 150

4. 优化递归CTE实现 #

若之前的递归CTE性能不佳，可尝试以下版本：通过路径跟踪避免冗余遍历，同时指定递归深度限制：

WITH TreeCTE AS (
    -- 锚点：初始子节点
    SELECT 
        parent_id, 
        child_id, 
        0 AS lvl,
        CAST(child_id AS VARCHAR(MAX)) AS path -- 记录路径，避免循环遍历
    FROM bar 
    WHERE child_id BETWEEN 50 AND 150

    UNION ALL

    -- 递归向上遍历父节点
    SELECT 
        b.parent_id, 
        b.child_id, 
        t.lvl + 1 AS lvl,
        t.path + ',' + CAST(b.parent_id AS VARCHAR(MAX)) AS path
    FROM bar b
    INNER JOIN TreeCTE t ON b.child_id = t.parent_id
    -- 避免重复路径：父节点不在当前路径中
    WHERE CHARINDEX(',' + CAST(b.parent_id AS VARCHAR(MAX)) + ',', ',' + t.path + ',') = 0
)
SELECT parent_id, child_id, lvl 
FROM TreeCTE 
ORDER BY lvl DESC
OPTION (MAXRECURSION 0); -- 允许无限递归（可根据实际层级调整数值）

5. 预计算树形结构元数据（适用于静态/低频更新场景） #

如果树形结构不频繁更新，可预先计算并存储每个节点的根节点ID、层级深度、完整路径等信息，查询时直接读取，完全避免递归开销：

-- 添加预计算字段
ALTER TABLE bar ADD 
    root_id INT,
    depth INT,
    full_path VARCHAR(MAX);

-- 一次性计算所有节点的元数据（可通过递归CTE或循环实现）
-- 后续查询直接过滤：SELECT * FROM bar WHERE child_id BETWEEN 50 AND 150 OR root_id IN (...)

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