500万条层级数据：寻找比递归CTE性能更优的实现方案

阿华AIGC实验室

2026-5-26

针对你手里500万条层级结构数据的性能优化需求，我结合你的T-SQL示例场景，整理了几个比递归CTE更高效的替代方案，这些方案在大数据量下的性能表现会明显优于递归：

1. 利用层级路径字符串直接拆分计算（避免递归）

你的Hierarchy_No是结构化的路径格式（比如1-1.1.1.），可以通过字符串拆分直接提取所有父级路径，再进行聚合计算，完全不需要递归逻辑。这种方案基于集合操作，性能远高于递归CTE。

示例代码：

-- 先处理路径，去掉末尾多余的点，统一用点分隔层级
WITH SplitHierarchy AS (
    SELECT 
        REPLACE(RTRIM(Hierarchy_No, '.'), '-', '.') AS CleanPath,
        sales
    FROM #Table1
),
-- 生成所有父级路径（按层级顺序拼接）
AllPaths AS (
    SELECT 
        STRING_AGG(value, '.') WITHIN GROUP (ORDER BY ordinal) OVER (PARTITION BY CleanPath ORDER BY ordinal) AS CurrentPath,
        sales
    FROM SplitHierarchy
    CROSS APPLY STRING_SPLIT(CleanPath, '.', 1) -- 启用ordinal参数确保层级顺序（SQL Server 2022+支持）
)
-- 按每个父路径聚合销售数据
SELECT 
    CurrentPath AS Hierarchy_Level,
    SUM(sales) AS Total_Sales
FROM AllPaths
GROUP BY CurrentPath
ORDER BY CurrentPath;

如果你的SQL Server版本低于2022，可以用自定义的带序号的字符串拆分函数替代STRING_SPLIT的ordinal参数，保证层级顺序正确。

2. 预计算扁平化层级表（持久化存储）

如果你的层级结构不会频繁变更，预计算并存储所有层级的聚合结果是性能最优的方案。你可以定时（比如每天凌晨）刷新一个持久化表，把所有层级的父子关系和销售总和提前计算好，查询时直接读取这个表即可。

示例代码：

-- 创建持久化的层级聚合表
IF OBJECT_ID('dbo.HierarchySalesAgg') IS NOT NULL DROP TABLE dbo.HierarchySalesAgg;
CREATE TABLE dbo.HierarchySalesAgg (
    Hierarchy_Level VARCHAR(50) PRIMARY KEY,
    Total_Sales INT NOT NULL
);

-- 预计算所有层级的聚合数据（复用方案1的逻辑）
WITH SplitHierarchy AS (
    SELECT 
        REPLACE(RTRIM(Hierarchy_No, '.'), '-', '.') AS CleanPath,
        sales
    FROM #Table1
),
AllPaths AS (
    SELECT 
        STRING_AGG(value, '.') WITHIN GROUP (ORDER BY ordinal) OVER (PARTITION BY CleanPath ORDER BY ordinal) AS CurrentPath,
        sales
    FROM SplitHierarchy
    CROSS APPLY STRING_SPLIT(CleanPath, '.', 1)
)
INSERT INTO dbo.HierarchySalesAgg (Hierarchy_Level, Total_Sales)
SELECT CurrentPath, SUM(sales)
FROM AllPaths
GROUP BY CurrentPath;

-- 查询时直接读取，性能拉满
SELECT * FROM dbo.HierarchySalesAgg ORDER BY Hierarchy_Level;

3. 转换为SQL Server HierarchyID类型

SQL Server的HierarchyID是专门为层级数据设计的内置类型，它底层用二进制存储层级关系，内置了GetAncestor()、IsDescendantOf()等高效方法，查询性能比递归CTE提升非常明显，尤其适合大数据量的层级场景。

示例代码：

-- 先将现有Hierarchy_No转换为HierarchyID格式
WITH ConvertToHierarchyID AS (
    SELECT 
        -- 把1-1.1.1.转换为/1/1.1/1/格式的HierarchyID
        CAST('/' + REPLACE(REPLACE(RTRIM(Hierarchy_No, '.'), '-', '/'), '.', '/') + '/' AS HierarchyID) AS NodeID,
        sales
    FROM #Table1
),
-- 生成数字表用于遍历层级深度（这里用递归临时生成，建议用持久化数字表）
Numbers AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM Numbers WHERE n <= 10 -- 假设最大层级深度为10，可按需调整
)
-- 计算每个节点及其所有父节点的销售总和
SELECT 
    NodeID.ToString() AS Hierarchy_Path,
    SUM(sales) OVER (PARTITION BY NodeID.GetAncestor(n)) AS Total_Sales
FROM ConvertToHierarchyID
CROSS APPLY Numbers
WHERE n <= NodeID.GetLevel()
ORDER BY NodeID.ToString();