萬盛學電腦網

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

SQL Server 聚合函數演算法優化深度解析:Stream Aggregate vs Hash Aggregate

SQL Server 聚合函數演算法優化深度解析:Stream Aggregate vs Hash Aggregate

聚合函數(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 ONCPU Time vs Elapsed Time
記憶體授予/使用執行計劃屬性 Memory Grant / sys.dm_exec_query_memory_grantsHash Aggregate 需記憶體,不足會 Spill 到 TempDB
IO 統計SET STATISTICS IO ONLogical/Physical/Read-Ahead Reads
等待類型sys.dm_os_waiting_tasks / Extended EventsRESOURCE_SEMAPHORE、PAGEIOLATCH 等

五、優化技巧總結

  1. 避免不必要的 DISTINCT:PK/Unique 欄位移除 DISTINCT。
  2. 索引覆蓋分組欄位:讓 Stream Aggregate 直接讀取有序索引,免排序。
  3. 預估資料分佈:更新統計資訊、檢視直方圖,引導優化器選擇正確演算法。
  4. 控制記憶體授予:Resource Governor 限制單查詢最大記憶體,防止 Hash Aggregate 搶佔導致並發下降。
  5. 適度使用近似聚合:SQL Server 2019+ APPROX_COUNT_DISTINCT 以極小記憶體獲取近似值,適合大數據儀表板。

小結

聚合演算法非黑即白,優化器會根據統計資訊動態切換。DBA 應掌握:建立支援排序的索引消除 Sort、維護統計資訊引導正確選擇、監控記憶體授予防止 Spill,三管齊下才能在百萬級資料下穩定獲得高效能聚合查詢。

copyright © 萬盛學電腦網 all rights reserved