聚合函數(COUNT、SUM、AVG 等)在海量資料下的效能差異極大。本文透過 Northwind 範例資料庫,實測標量聚合、流式聚合、雜湊聚合三種演算法的運作機制、執行計劃差異、監控指標,並給出索引覆蓋優化實戰技巧。
單一彙總值(如 SELECT COUNT(*) FROM Orders)。執行計劃包含:索引掃描 → 流式聚合 → 計算標量。
SET SHOWPLAN_ALL ON 可見:Stream Aggregate 直接累加計數,最後 Compute Scalar 將 BigInt 轉 Int。
優點:演算法簡單、無需記憶體授予、適合無重複值欄位(如 PK)。
缺點:需預先排序,重複值多時排序成本高。
-- 有重複值:ShipCity SELECT COUNT(DISTINCT ShipCity) FROM Orders; -- 計劃出現 Sort + Stream Aggregate -- 無重複值:OrderID (PK) SELECT COUNT(DISTINCT OrderID) FROM Orders; -- 僅 Stream Aggregate,無 Sort
優化手段:對高重複欄位建立非叢集索引:
CREATE INDEX IX_Orders_ShipCity ON Orders(ShipCity DESC);
索引本身有序,查詢改為 兩個 Stream Aggregate 疊加(Partial + Global),完全省去 Sort,成本大幅下降。
原理:雜湊函數將群組鍵映射到雜湊桶,平行掃描累加。適用於高基數、大資料量、無序輸入場景。
觸發條件:優化器根據資料分佈動態選擇。如 GROUP BY ShipCountry(重複多)走 Hash Aggregate;GROUP BY CustomerID(基數高)走 Stream Aggregate。
關鍵洞見:優化器選擇取決於實際資料分佈,而非單純 SQL 寫法。優化時必須結合統計資訊與直方圖判斷。
| 指標 | 取得方式 | 說明 |
|---|---|---|
| 執行時間 | SSMS「包含實際執行計劃」/ SET STATISTICS TIME ON | CPU Time vs Elapsed Time |
| 記憶體授予/使用 | 執行計劃屬性 Memory Grant / sys.dm_exec_query_memory_grants | Hash Aggregate 需記憶體,不足會 Spill 到 TempDB |
| IO 統計 | SET STATISTICS IO ON | Logical/Physical/Read-Ahead Reads |
| 等待類型 | sys.dm_os_waiting_tasks / Extended Events | RESOURCE_SEMAPHORE、PAGEIOLATCH 等 |
APPROX_COUNT_DISTINCT 以極小記憶體獲取近似值,適合大數據儀表板。聚合演算法非黑即白,優化器會根據統計資訊動態切換。DBA 應掌握:建立支援排序的索引消除 Sort、維護統計資訊引導正確選擇、監控記憶體授予防止 Spill,三管齊下才能在百萬級資料下穩定獲得高效能聚合查詢。