SQL层级数据性能调优：批量删除子节点查询优化

问题背景 #

我有一张存储层级化地点数据的临时表#tmpPlaceGroupData，结构及数据如下：

Place                 Complete_PlaceId
----------------------------------------
India                 1
  Maharashtra         1|MH
     Sangli           1|MH|SA
       Sangli_A       1|MH|SA|SA_A
       Sangli_B       1|MH|SA|SA_B
     Akola            1|MH|AK
       Akola_A        1|MH|AK|AK_A
       Akola_B        1|MH|AK|AK_B
     Aurangabad       1|MH|AU
 Telangana            1|TG
    Adilabad          1|TG|AD
       Adilabad_A     1|TG|AD|AD_A
       Adilabad_B     1|TG|AD|AD_B
    Karimnagar        1|TG|KA
    Nizamabad         1|TG|NZ
 AndhraPradesh        1|AP
   Krishna            1|AP|KR 
   Guntur             1|AP|GU
   Nellore            1|AP|NE

用户可在国家、州、地区、村层级保存地点，保存的ID会插入到#tblPlaceDataInserted表中。当用户在州层级（如马哈拉施特拉邦）保存时，需要删除该州下所有子节点（如桑利及下辖村庄、阿科拉及下辖村庄、奥兰加巴德）。

原删除查询如下，执行耗时约20-25秒（#tmpPlaceGroupData表约有5万+条数据）：

delete T
FROM #tmpPlaceGroupData AS T
INNER JOIN #tblPlaceDataInserted AS E ON iif(left(T.Complete_PlaceId, LEN(E.CompletePlaceID) + 1) = E.CompletePlaceID + '|', 1, 0) = 1

性能优化方案 #

1. 简化JOIN条件，消除函数嵌套 #

原查询用iif嵌套left函数，导致数据库无法利用索引，只能全表扫描。直接改写为更直观的前缀匹配逻辑：

方案A：使用LIKE匹配前缀

DELETE T
FROM #tmpPlaceGroupData AS T
INNER JOIN #tblPlaceDataInserted AS E 
    ON T.Complete_PlaceId LIKE E.CompletePlaceID + '|%'

方案B：使用CHARINDEX定位前缀

DELETE T
FROM #tmpPlaceGroupData AS T
INNER JOIN #tblPlaceDataInserted AS E 
    ON CHARINDEX(E.CompletePlaceID + '|', T.Complete_PlaceId) = 1

两种写法都能让优化器更容易识别匹配逻辑，避免不必要的函数计算。

2. 给核心字段创建索引 #

#tmpPlaceGroupData的Complete_PlaceId是过滤核心，创建非聚集索引可大幅减少扫描行数：

CREATE NONCLUSTERED INDEX IX_tmpPlaceGroupData_CompletePlaceId ON #tmpPlaceGroupData (Complete_PlaceId)

如果删除时需要访问Place等其他字段，创建包含索引避免书签查找：

CREATE NONCLUSTERED INDEX IX_tmpPlaceGroupData_CompletePlaceId_INCL ON #tmpPlaceGroupData (Complete_PlaceId) INCLUDE (Place)

同时给#tblPlaceDataInserted的CompletePlaceID创建索引，加速关联匹配：

CREATE NONCLUSTERED INDEX IX_tblPlaceDataInserted_CompletePlaceID ON #tblPlaceDataInserted (CompletePlaceID)

3. 去重后再执行删除 #

如果#tblPlaceDataInserted存在重复的CompletePlaceID，先去重避免重复匹配：

WITH DistinctInserted AS (
    SELECT DISTINCT CompletePlaceID FROM #tblPlaceDataInserted
)
DELETE T
FROM #tmpPlaceGroupData AS T
INNER JOIN DistinctInserted AS E 
    ON T.Complete_PlaceId LIKE E.CompletePlaceID + '|%'

4. 分批次删除，避免锁表和日志暴涨 #

若单次删除数据量极大，分批次删除可降低系统负载：

DECLARE @BatchSize INT = 1000;
WHILE EXISTS (
    SELECT 1 FROM #tmpPlaceGroupData AS T
    INNER JOIN #tblPlaceDataInserted AS E 
        ON T.Complete_PlaceId LIKE E.CompletePlaceID + '|%'
)
BEGIN
    DELETE TOP (@BatchSize) T
    FROM #tmpPlaceGroupData AS T
    INNER JOIN #tblPlaceDataInserted AS E 
        ON T.Complete_PlaceId LIKE E.CompletePlaceID + '|%'
END

根据实际情况调整@BatchSize，平衡删除速度与系统压力。

5. 重构数据模型（长期优化） #

字符串拼接的层级ID虽直观，但性能瓶颈明显。改用邻接列表模型，新增ParentId字段存储父节点ID：

通过递归CTE快速定位所有子节点，配合ParentId索引，性能远高于字符串匹配：

WITH ChildPlaces AS (
    SELECT Complete_PlaceId FROM #tblPlaceDataInserted
    UNION ALL
    SELECT T.Complete_PlaceId 
    FROM #tmpPlaceGroupData T
    INNER JOIN ChildPlaces CP ON T.ParentId = CP.Complete_PlaceId
)
DELETE T
FROM #tmpPlaceGroupData T
INNER JOIN ChildPlaces CP ON T.Complete_PlaceId = CP.Complete_PlaceId

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