求助：用HierarchyId优化递归CTE的大数据迁移性能问题

迁移大量数据时遇到性能瓶颈，数据量越大问题越突出。由于无法直接查询子团队与TeamId的关联关系，只能用递归CTE创建视图，但这种方法性能很差。以下说明如何用HierarchyId消除递归，解决性能问题。

原查询语句 #

SET STATISTICS TIME ON

SELECT t.Id as TeamId, t.Name as TeamName, c.Id as CountryId
FROM dbo.MemberCountries mc 
JOIN Countries c ON c.Id = mc.CountryId 
JOIN Teams t ON t.Id = mc.TeamId 
JOIN Members m ON m.Id = mc.MemberId 
WHERE mc.MemberId = 1 -- 示例成员ID
AND mc.CountryId IN (1) -- 示例国家ID

SET STATISTICS TIME OFF

原递归视图 #

TeamHierarchy视图 #

create or alter view [dbo].TeamHierarchy
as 
with cte as (
    select
        t.Id,
        t.Name,
        t.ParentId,
        Id as TeamId,
        0 as Distance
    from [dbo].Teams as t
    union all
    select
        t.Id,
        t.Name,
        t.ParentId,
        c.TeamId,
        c.Distance + 1
    from [dbo].Teams as t
    inner join cte as c on t.Id = c.ParentId
)
select
    t.Id as TeamId,
    t.Name as TeamName,
    cte.Id as ParentId,
    cte.Name as ParentName,
    cte.Distance
from cte
join [dbo].Teams t on t.Id = cte.TeamId
where t.Id != cte.Id

TeamCountries视图 #

create or alter view [dbo].TeamCountries
as
with cte as (
    select
        t.*,
        Id as BaseTeamId
    from
        [dbo].Teams as t
    union all
    select
        t.*,
        c.BaseTeamId
    from
        [dbo].Teams as t
    inner join cte as c on t.Id = c.ParentId
)
select
    t.Id as TeamId,
    cte.Id as SourceId,
    ct.CountriesId as CountryId,
    cast((case when cte.Id = t.Id then 0 else 1 end) as bit) as IsInherited
from cte
    join [dbo].Teams t on t.Id = cte.BaseTeamId
    join [dbo].CountryTeam ct on ct.TeamsId = cte.Id
    join [dbo].Countries co on co.Id = ct.CountriesId

MemberCountries视图 #

create or alter view [dbo].MemberCountries
as
select
    memberCountries.MemberId,
    memberCountries.TeamId,
    memberCountries.CountryId
from (
     select
         mt.MembersId as MemberId,
         tc.TeamId,
         tc.CountryId
     from
         [dbo].TeamCountries tc
         inner join
         [dbo].MemberTeam mt on mt.TeamsId = tc.TeamId
 ) as memberCountries
group by
    memberCountries.MemberId,
    memberCountries.TeamId,
    memberCountries.CountryId

原表结构（DDL） #

-- DROP SCHEMA dbo;

CREATE SCHEMA dbo;

-- app.dbo.Countries 定义
-- Drop table
-- DROP TABLE app.dbo.Countries;

CREATE TABLE app.dbo.Countries 
(
    Id int IDENTITY(1,1) NOT NULL,
    Name nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    CONSTRAINT PK_Countries PRIMARY KEY (Id)
);

-- app.dbo.Members 定义
-- Drop table
-- DROP TABLE app.dbo.Members;

CREATE TABLE app.dbo.Members 
(
    Id int IDENTITY(1,1) NOT NULL,
    Name nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    CONSTRAINT PK_Members PRIMARY KEY (Id)
);

-- app.dbo.[__EFMigrationsHistory] 定义
-- Drop table
-- DROP TABLE app.dbo.[__EFMigrationsHistory];

CREATE TABLE app.dbo.[__EFMigrationsHistory] 
(
    MigrationId nvarchar(150) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    ProductVersion nvarchar(32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    CONSTRAINT PK___EFMigrationsHistory PRIMARY KEY (MigrationId)
);

-- app.dbo.Teams 定义
-- Drop table
-- DROP TABLE app.dbo.Teams;

CREATE TABLE app.dbo.Teams 
(
    Id int IDENTITY(1,1) NOT NULL,
    Name nvarchar(MAX) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    ParentId int NULL,
    CONSTRAINT PK_Teams PRIMARY KEY (Id),
    CONSTRAINT FK_Teams_Teams_ParentId 
         FOREIGN KEY (ParentId) REFERENCES app.dbo.Teams(Id)
);

CREATE NONCLUSTERED INDEX IX_Teams_ParentId 
    ON app.dbo.Teams ( ParentId ASC )
       WITH (PAD_INDEX = OFF, FILLFACTOR = 100, SORT_IN_TEMPDB = OFF, 
             IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
             ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY ] ;

-- app.dbo.CountryTeam 定义
-- Drop table
-- DROP TABLE app.dbo.CountryTeam;

CREATE TABLE app.dbo.CountryTeam 
(
    CountriesId int NOT NULL,
    TeamsId int NOT NULL,
    CONSTRAINT PK_CountryTeam PRIMARY KEY (CountriesId, TeamsId),
    CONSTRAINT FK_CountryTeam_Countries_CountriesId 
         FOREIGN KEY (CountriesId) REFERENCES app.dbo.Countries(Id) 
                 ON DELETE CASCADE,
    CONSTRAINT FK_CountryTeam_Teams_TeamsId 
         FOREIGN KEY (TeamsId) REFERENCES app.dbo.Teams(Id) 
                 ON DELETE CASCADE
);

CREATE NONCLUSTERED INDEX IX_CountryTeam_TeamsId 
    ON app.dbo.CountryTeam (TeamsId ASC)
       WITH (PAD_INDEX = OFF, FILLFACTOR = 100, SORT_IN_TEMPDB = OFF, 
             IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF,
             ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

-- app.dbo.MemberTeam 定义
-- Drop table
-- DROP TABLE app.dbo.MemberTeam;

CREATE TABLE app.dbo.MemberTeam 
(
    MembersId int NOT NULL,
    TeamsId int NOT NULL,
    CONSTRAINT PK_MemberTeam PRIMARY KEY (MembersId, TeamsId),
    CONSTRAINT FK_MemberTeam_Members_MembersId 
         FOREIGN KEY (MembersId) REFERENCES app.dbo.Members(Id) 
                 ON DELETE CASCADE,
    CONSTRAINT FK_MemberTeam_Teams_TeamsId 
         FOREIGN KEY (TeamsId) REFERENCES app.dbo.Teams(Id) 
                 ON DELETE CASCADE
);

CREATE NONCLUSTERED INDEX IX_MemberTeam_TeamsId 
    ON app.dbo.MemberTeam (TeamsId ASC)
       WITH (PAD_INDEX = OFF, FILLFACTOR = 100, SORT_IN_TEMPDB = OFF, 
             IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF, 
             ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY];

-- dbo.MemberCountries 定义
CREATE OR ALTER VIEW [dbo].MemberCountries
AS
    SELECT
        memberCountries.MemberId,
        memberCountries.TeamId,
        memberCountries.CountryId
    FROM
        (SELECT
             mt.MembersId AS MemberId,
             tc.TeamId,
             tc.CountryId
         FROM
             [dbo].TeamCountries tc
         INNER JOIN 
             [dbo].MemberTeam mt ON mt.TeamsId = tc.TeamId
        ) AS memberCountries
    GROUP BY
        memberCountries.MemberId, memberCountries.TeamId,
        memberCountries.CountryId;

解决方案：用HierarchyId替换递归CTE #

步骤1：修改Teams表，添加HierarchyId字段 #

给Teams表新增Node字段，存储团队的层级路径：

ALTER TABLE app.dbo.Teams
ADD Node hierarchyid NULL;

步骤2：填充初始HierarchyId数据 #

用递归CTE一次性生成所有团队的层级路径，写入Node字段：

WITH TeamHierarchyCTE AS (
    -- 根节点（ParentId为NULL的团队）
    SELECT 
        Id, 
        ParentId,
        hierarchyid::GetRoot() AS Node
    FROM app.dbo.Teams
    WHERE ParentId IS NULL

    UNION ALL

    -- 子节点
    SELECT 
        t.Id, 
        t.ParentId,
        c.Node.ToString() + CAST(t.Id AS VARCHAR(20)) + '/' AS Node
    FROM app.dbo.Teams t
    JOIN TeamHierarchyCTE c ON t.ParentId = c.Id
)
UPDATE app.dbo.Teams
SET Node = c.Node
FROM TeamHierarchyCTE c
WHERE Teams.Id = c.Id;

步骤3：添加触发器维护层级数据 #

确保团队ParentId变更时，Node字段自动更新：

CREATE TRIGGER TR_Teams_UpdateNode
ON app.dbo.Teams
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

    -- 更新当前节点的路径
    WITH UpdatedTeams AS (
        SELECT 
            t.Id,
            ISNULL(p.Node.ToString(), '/') + CAST(t.Id AS VARCHAR(20)) + '/' AS NewNode
        FROM inserted t
        LEFT JOIN app.dbo.Teams p ON t.ParentId = p.Id
    )
    UPDATE app.dbo.Teams
    SET Node = hierarchyid::Parse(ut.NewNode)
    FROM UpdatedTeams ut
    WHERE Teams.Id = ut.Id;

    -- 递归更新所有子节点的路径
    DECLARE @UpdatedIds TABLE (Id int);
    INSERT INTO @UpdatedIds SELECT Id FROM inserted;

    WHILE EXISTS (SELECT 1 FROM @UpdatedIds)
    BEGIN
        WITH ChildTeams AS (
            SELECT 
                t.Id,
                p.Node.ToString() + CAST(t.Id AS VARCHAR(20)) + '/' AS NewNode
            FROM app.dbo.Teams t
            JOIN app.dbo.Teams p ON t.ParentId = p.Id
            JOIN @UpdatedIds u ON p.Id = u.Id
            WHERE t.Node != hierarchyid::Parse(p.Node.ToString() + CAST(t.Id AS VARCHAR(20)) + '/')
        )
        UPDATE app.dbo.Teams
        SET Node = hierarchyid::Parse(ct.NewNode)
        FROM ChildTeams ct
        WHERE Teams.Id = ct.Id;

        -- 更新待处理ID为刚更新的子节点ID
        DELETE FROM @UpdatedIds;
        INSERT INTO @UpdatedIds SELECT Id FROM ChildTeams;
    END
END
GO

步骤4：重构视图，替换递归CTE #

重构TeamHierarchy视图

利用HierarchyId的内置方法直接获取层级关系：

CREATE OR ALTER VIEW [dbo].TeamHierarchy
AS
SELECT
    t.Id AS TeamId,
    t.Name AS TeamName,
    p.Id AS ParentId,
    p.Name AS ParentName,
    t.Node.GetLevel() - p.Node.GetLevel() AS Distance
FROM app.dbo.Teams t
JOIN app.dbo.Teams p ON t.Node.IsDescendantOf(p.Node) = 1
WHERE t.Id != p.Id;

重构TeamCountries视图

用IsDescendantOf快速获取所有子团队继承的国家：

CREATE OR ALTER VIEW [dbo].TeamCountries
AS
SELECT
    t.Id AS TeamId,
    cte.Id AS SourceId,
    ct.CountriesId AS CountryId,
    CAST(CASE WHEN cte.Id = t.Id THEN 0 ELSE 1 END AS BIT) AS IsInherited
FROM app.dbo.Teams t
JOIN app.dbo.Teams cte ON t.Node.IsDescendantOf(cte.Node) = 1
JOIN app.dbo.CountryTeam ct ON ct.TeamsId = cte.Id
JOIN app.dbo.Countries co ON co.Id = ct.CountriesId;

重构MemberCountries视图

视图逻辑不变，底层依赖的TeamCountries性能已提升：

CREATE OR ALTER VIEW [dbo].MemberCountries
AS
SELECT
    memberCountries.MemberId,
    memberCountries.TeamId,
    memberCountries.CountryId
FROM (
     SELECT
         mt.MembersId AS MemberId,
         tc.TeamId,
         tc.CountryId
     FROM
         [dbo].TeamCountries tc
         INNER JOIN 
         [dbo].MemberTeam mt ON mt.TeamsId = tc.TeamId
 ) AS memberCountries
GROUP BY
    memberCountries.MemberId,
    memberCountries.TeamId,
    memberCountries.CountryId;

步骤5：添加索引优化HierarchyId查询 #

给Node字段添加索引，提升层级查询效率：

-- 非聚集索引，包含常用字段
CREATE NONCLUSTERED INDEX IX_Teams_Node
ON app.dbo.Teams (Node)
INCLUDE (Id, Name, ParentId);

-- 可选：如果层级查询是核心场景，可将Node设为聚集索引
-- ALTER TABLE app.dbo.Teams DROP CONSTRAINT PK_Teams;
-- ALTER TABLE app.dbo.Teams ADD CONSTRAINT PK_Teams PRIMARY KEY CLUSTERED (Node);

性能验证 #

重新运行原查询，对比执行时间：

SET STATISTICS TIME ON

SELECT t.Id as TeamId, t.Name as TeamName, c.Id as CountryId
FROM dbo.MemberCountries mc 
JOIN Countries c ON c.Id = mc.CountryId 
JOIN Teams t ON t.Id = mc.TeamId 
JOIN Members m ON m.Id = mc.MemberId 
WHERE mc.MemberId = 1 -- 示例成员ID
AND mc.CountryId IN (1) -- 示例国家ID

SET STATISTICS TIME OFF

原理说明 #

HierarchyId是SQL Server专为层级数据设计的类型，它将层级路径编码为二进制值，IsDescendantOf、GetLevel等内置方法基于二进制快速比较，比递归CTE的逐行遍历效率高得多，数据量越大，性能提升越明显。

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