萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server CTE 實戰:重構複雜查詢提升 2 倍性能的遞歸與非遞歸應用模式

同事的一條 10 秒慢查詢,通過 CTE 重構降至 5 秒。本文實戰拆解這個優化案例,展示 CTE 如何消除重複子查詢、簡化邏輯、提升可讀性,並延伸介紹遞歸 CTE 在層次結構處理中的威力。

🐢 原始慢查詢剖析

SELECT S.Name, S.AccountantCode,
    (SELECT COUNT(*) FROM (
        SELECT DISTINCT BusinessBackupId 
        FROM Biz_BusinessBackupCustomer 
        WHERE Id IN (
            SELECT BusinessBackupCustomerId 
            FROM Rpt_RegistForm 
            WHERE (SignatureCPA1Id = S.Id OR SignatureCPA2Id = S.Id) 
              AND DocStatus = 30
        )
    ) T) AS BNum,
    CASE WHEN R.Id IS NULL THEN 0 ELSE 1 END AS Num,
    R.ReportBackupDate
FROM Base_Staff S
LEFT JOIN Rpt_RegistForm R 
    ON (R.SignatureCPA1Id = S.Id OR R.SignatureCPA2Id = S.Id) 
   AND R.DocStatus = 30
WHERE S.UserType = 3;

核心問題

  1. 重複計算:子查詢與 LEFT JOIN 重複關聯 Base_Staff 和 Rpt_RegistForm
  2. 相關子查詢逐行執行:外層每行員工都觸發一次子查詢
  3. DISTINCT + IN 子查詢:優化器難以生成高效計劃

🚀 CTE 重構版:5 秒搞定

WITH StaffRegist AS (
    -- 1. 一次性完成核心關聯,複用多次
    SELECT 
        S.Name, 
        S.AccountantCode,
        R.BusinessBackupCustomerId
        -- R.Id, R.SignatureCPA1Id, R.SignatureCPA2Id -- 按需輸出
    FROM Base_Staff S
    LEFT JOIN Rpt_RegistForm R 
        ON (R.SignatureCPA1Id = S.Id OR R.SignatureCPA2Id = S.Id)
       AND R.DocStatus = 30
    WHERE S.UserType = 3
),
ReportAgg AS (
    -- 2. 聚合報告數
    SELECT Name, AccountantCode, COUNT(BusinessBackupCustomerId) AS ReportCount
    FROM StaffRegist
    GROUP BY Name, AccountantCode
)
SELECT 
    T0.*,
    -- 3. 關聯業務表計算約定書數,利用 CTE 結果集
    (SELECT COUNT(*) FROM (
        SELECT DISTINCT BusinessBackupId
        FROM Biz_BusinessBackupCustomer B
        INNER JOIN StaffRegist SR ON B.Id = SR.BusinessBackupCustomerId
        WHERE T0.AccountantCode = SR.AccountantCode
    ) T1) AS AgreementCount
FROM ReportAgg T0;

📈 優化原理解析

優化點原始寫法CTE 重構收益
核心關聯執行 2 次(子查詢 + LEFT JOIN)CTE 執行 1 次,複用 2 次掃描/連接成本減半
聚合邏輯分散在 SELECT 列表專用 CTE 聚合計劃更簡單、可並行
相關子查詢逐行執行轉為 JOIN + 聚合批次處理,利用索引
可讀性嵌套 4 層,難維護分步 CTE,邏輯清晰維護成本大幅降低

💡 COUNT 列與 NULL 的微妙關係

代碼中 COUNT(BusinessBackupCustomerId) 而非 COUNT(*):

  • COUNT(column) 忽略 NULL 值
  • LEFT JOIN 無匹配行時 BusinessBackupCustomerId 為 NULL
  • 完美符合「無報告則計 0」的業務語義,無需 CASE WHEN

🌳 遞歸 CTE 實戰:組織架構深度查詢

-- 需求:查詢某員工的所有下屬(含間接),按層級排序
WITH Subordinates AS (
    SELECT EmployeeID, ManagerID, LoginID, 1 AS Level
    FROM HumanResources.Employee
    WHERE ManagerID = @ManagerId  -- 定點:直屬下屬
    UNION ALL
    SELECT e.EmployeeID, e.ManagerID, e.LoginID, s.Level + 1
    FROM HumanResources.Employee e
    JOIN Subordinates s ON e.ManagerID = s.EmployeeID
    WHERE s.Level < 10 -- 安全閥
)
SELECT * FROM Subordinates ORDER BY Level, LoginID
OPTION (MAXRECURSION 100);

🎯 CTE 適用場景判斷樹

  1. 查詢中存在相同/相似子查詢重複出現 → CTE 物化複用
  2. 邏輯可自然分解為多個步驟(過濾 → 聚合 → 關聯 → 計算) → CTE 管道化
  3. 需要遞歸遍歷樹/圖結構 → 遞歸 CTE
  4. 需要在同一語句中多次引用中間結果 → CTE
  5. 單純簡單查詢 → 直接寫,別過度設計

⚠️ 避坑提醒

  • CTE **不物化結果**(除非加索引提示或改用臨時表),每次引用都重新執行
  • 遞歸 CTE 默認 MAXRECURSION 100,**生產環境必須顯式設定**
  • CTE 作用域僅限緊隨其后的單條語句(SELECT/INSERT/UPDATE/DELETE/MERGE)
  • 不要在高併發 OLTP 核心鏈路濫用複雜 CTE,評估編譯開銷

實戰感悟:CTE 是「邏輯重構工具」,不是「性能銀彈」。真正的性能提升來自於消除冗餘計算、引導優化器生成更優計劃、將相關子查詢轉為集合操作。寫出清晰 CTE 的過程,本質上是對查詢邏輯的深度梳理——這才是最大的收穫。

copyright © 萬盛學電腦網 all rights reserved