深入 SQL Server 聚合函數演算法優化：流聚合 vs 雜湊聚合、索引覆蓋與監控指標實戰

前言

聚合函數 (SUM, COUNT, AVG, MIN, MAX) 是 OLAP 報表與 OLTP 統計的核心。SQL Server 查詢優化器根據資料分布、索引、記憶體授予動態選擇 **流聚合** 或 **雜湊聚合** 兩種演算法。理解其運作原理、適用場景與優化手段，是編寫高效聚合查詢的關鍵。

一、兩大聚合演算法核心機制

1. 流聚合 - 依賴有序輸入

• 前提：輸入資料流**已按 GROUP BY 欄位排序** (來源為索引掃描/求索引順序、或顯式 Sort 運算子)。
• 運作：逐行掃描，累加器維持當前群組彙總值，遇到群組鍵值變化即輸出上一群組結果、重置累加器。
• 複雜度：時間 O(N)，空間 O(1) (僅需常數記憶體存放累加器)。
• 優點：無額外記憶體授予、無 TempDB 溢寫風險、輸出天然有序。
• 缺點：若輸入未排序，需昂貴的 **Sort 運算子** (N log N，可能溢寫 TempDB)。

2. 雜湊聚合 - 記憶體雜湊表

• 前提：輸入資料流**未排序** 或優化器估計雜湊成本低於排序成本。
• 運作：建立記憶體雜湊表 (Key=群組鍵, Value=彙總累加器)，掃描輸入行計算雜湊值插入/更新桶；最後掃描雜湊表輸出結果。
• 複雜度：平均時間 O(N)，空間 O(G) (G=群組數)。
• 優點：不需排序、適合高基數群組、可並行化 (Parallel Hash Aggregate)。
• 缺點：需**記憶體授予** (Memory Grant)；群組數極大/資料傾斜導致雜湊表超出授予記憶體 → **溢寫 TempDB** (Hash Spill)，效能暴跌。

二、實戰演示：Northwind 資料庫

環境：SQL Server 2019, Northwind 範例資料庫。

案例 1：標量聚合 - COUNT(*)

SELECT COUNT(*) FROM dbo.Orders;

計劃解讀：

1. Clustered Index Scan：掃描叢集索引葉層 (全表掃描)。
2. Stream Aggregate：流聚合計算行數 (無 GROUP BY，單一群組)。
3. Compute Scalar：將內部 bigint 計數轉為 int 輸出。

案例 2：DISTINCT 去重聚合 - 排序代價

-- 查詢 1：ShipCity 有大量重複值
SELECT COUNT(DISTINCT ShipCity) FROM Orders;
-- 查詢 2：OrderID 為主鍵，無重複
SELECT COUNT(DISTINCT OrderID) FROM Orders;

分析：DISTINCT 內部實現為 **Sort -> Stream Aggregate**。ShipCity 重複率高，排序後去重有效；OrderID 唯一，排序純屬浪費。**優化**：「為高基數去重欄位建立索引，避免顯式排序」。

優化實驗：為 ShipCity 建立非叢集索引

CREATE INDEX IX_Orders_ShipCity ON Orders(ShipCity DESC);

索引提供有序輸入，優化器直接雙流聚合 (第一層去重，第二層計數)、**完全消除 Sort**，成本大幅下降。

案例 3：動態選擇 - Hash vs Stream

-- 低基數群組：ShipCountry (少數幾十國) -> Hash Aggregate
SELECT ShipCountry, COUNT(*) FROM Orders GROUP BY ShipCountry;
-- 高基數群組：CustomerID (數百客戶) -> Stream Aggregate (因有索引順序)
SELECT CustomerID, COUNT(*) FROM Orders GROUP BY CustomerID;

核心洞見：優化器根據 **統計資訊估計群組數 (基數)** 決定演算法。**同樣語法，不同資料分布 → 不同計劃**。優化時必須結合實際資料分布，不能只看 SQL 文本。

三、聚合優化五大鐵律

1. 索引覆蓋 GROUP BY 欄位：建立 INCLUDE 覆蓋索引，使掃描輸出天然有序 → 強制流聚合，零記憶體授予、零 TempDB 壓力。
2. 消除不必要的 DISTINCT/SORT：主鍵/唯一欄位勿加 DISTINCT；UNION 改 UNION ALL + 外層去重。
3. 控制群組數量：超高基數群組 (百萬級) 易導致 Hash Spill。考慮分階段聚合 (先局部聚合再全局聚合) 或近似算法 (APPROX_COUNT_DISTINCT SQL 2019+)。
4. 記憶體授予監控：查詢計劃屬性中觀察 Desired Memory vs Granted Memory，若 Spill 到 TempDB (Warnings 有 SpillToTempDb)，需調整 MAX_GRANT_PERCENT 或重寫查詢。
5. 統計資訊維護：UPDATE STATISTICS ... WITH FULLSCAN 確保基數估計準確，避免優化器誤選演算法。

四、效能監控指標儀表板 (效能監視器 / DMV)

五、進階：近似聚合 (SQL Server 2019+)

-- 傳統精確 COUNT DISTINCT (高記憶體、易 Spill)
SELECT COUNT(DISTINCT CustomerID) FROM dbo.Orders;

-- 近似算法 (HyperLogLog)，誤差 < 2%，記憶體固定極小
SELECT APPROX_COUNT_DISTINCT(CustomerID) FROM dbo.Orders;

適用場景：儀表板 KPI、即時分析、百萬級基數估算，**不適用**於財務對帳等需精確場景。

總結

SQL Server 聚合優化的本質是 **「讓資料以有序方式流入聚合運算子」**。通過索引覆蓋 GROUP BY 欄位、消除冗餘排序、監控記憶體授予與 TempDB 溢寫、維護精確統計資訊，即可將聚合查詢效能提升數量級。SQL 2019 引入的近似聚合更為超大基數場景提供了新選擇。建議將本文監控指標納入日常巡檢儀表板，做到「有數據、有預警、有優化」。