SQL Server 彙總函式演算法最佳化技巧

標籤：

原文出處： 頭頭哥   歡迎分享原創到伯樂頭條

部落格前言

Sql server彙總函式在實際工作中應對各種需求使用的還是很廣泛的，對於彙總函式的最佳化自然也就成為了一個重點，一個程式最佳化的好不好直接決定了這個程式的聲明周期。Sql server彙總函式對一組值執行計算並返回單一的值。彙總函式對一組值執行計算，並返回單個值。除了 COUNT 以外，彙總函式都會忽略空值。 彙總函式經常與 SELECT 語句的 GROUP BY 子句一起使用。

1.寫在前面
如果有對Sql server彙總函式不熟或者忘記了的可以看我之前的一片部落格。sql server 基礎教程。

本文中所有資料示範都是用Microsoft官方樣本資料庫：Northwind，至於Northwind大家也可以在網上下載。至於下載方法MSDN已經有了詳細的說明了，這裡就不多說了。

2.Sql server標量彙總

2.1.概念： 

在只包含彙總函式的 SELECT 語句列列表中指定的一種彙總函式（如 MIN()、MAX()、COUNT()、SUM() 或 AVG()）。當列列表只包含彙總函式時，則結果集只具有一個行給出彙總值，該值由與 WHERE 子句謂詞相匹配的源行計算得到。

2.2.探索標量彙總： 

我們先用Sql server的”包括實際的執行計畫”來看看一個簡單的流彙總COUNT()來看看錶裡資料所有的行數。

再通過SET SHOWPLAN_ALL ON(關於輸出中包含的列更多資訊可以在連結中查看)來看看有關語句執行情況的詳細資料，並估計語句對資源的需求。

通過SET SHOWPLAN_ALL ON我們來看看COUNT()具體做了那些事情:

• 索引掃描：掃描當前表的行數
• StreamCompute：計算行數的數量
• 計算標量：將StreamCompute出來的結果轉化為適當的類型。(因為索引掃描出來的結果是根據表中資料的大小決定的，如果表中資料很多的話，COUNT是int類型就會有問題，所以在最終返回的時候需要將預設類型(數值一般預設類型是Big)轉成int類型。)
• 小結：通過SET SHOWPLAN_ALL ON我們可以查看Sql server彙總函式在給我們呈現最終效果的時候，為這個效果做了些什麼事情。

2.3.標量彙總最佳化技巧： 

我們通過兩個比較簡單的sql查詢來看看他們的區別

12	SELECT COUNT(DISTINCT ShipCity) FROM OrdersSELECT COUNT(DISTINCT OrderID) FROM Orders

從中可以看到，其實這兩個查詢從語句上來說沒什麼太大的區別，但是為什麼開銷會不一樣，一個是查詢城市一個是查詢訂單號。這是因為其實DISTINCT對於OrderID查詢來說，是沒有什麼意義的，因為OrderID是主鍵，是不會有重複的。而ShipCity是會有重複的，Sql server的去重機制在去重的時候，會有一個排序的過程。這個排序還是比較消耗資源的。

對於資料量比較大的表其實不是很建議對大表排序或者對大表的某個重複次數多的欄位去重運算。所以我們這裡可以對ShipCity進行最佳化一下。可以對ShipCity建立一個非叢集索引。

12	CREATE INDEX Index_ShipCity On Orders(ShipCity desc)go

從中可以看到，加了索引以後COUNT(DISTINCT ShipCity)的查詢變成了兩個流彙總，而沒有了排序，節省了開銷。

總結：對於標量彙總從上面的例子大家可以看到，標量彙總優缺點很明顯:

• Sql server標量彙總優點：演算法比較簡單直觀，適合非重複值的彙總操作。
• Sql server標量彙總缺點：效能較差(需要排序)，不適合重複值的彙總操作。

最佳化技巧:

• 盡量避免排序產生
• 將分組字(GROUP BY)段鎖定在索引覆蓋範圍內

3.Sql server雜湊彙總

3.1.概念： 

雜湊(Hash，一般翻譯做“散列”，也有直接音譯為“雜湊”的，就是把任意長度的輸入（又叫做預映射， pre-image），通過散列演算法，變換成固定長度的輸出，該輸出就是散列值。這種轉換是一種壓縮映射，也就是，散列值的空間通常遠小於輸入的空間，不同的輸入可能會散列成相同的輸出，所以不可能從散列值來唯一的確定輸入值。簡單的說就是一種將任意長度的訊息壓縮到某一固定長度的訊息摘要的函數。)

雜湊彙總的內部實現方法和雜湊串連的實現機制一樣，需要雜湊函數的內部運算，形成不同的雜湊值，依次並行掃描資料形成彙總值。

3.2.背景： 

為瞭解決流彙總的不足，應對大資料的操作，所以雜湊彙總就誕生了。

3.3.分析： 

來看看兩個簡單的查詢。

ShipCountry和CustomerID的分組查詢看上去很類似，但是為什麼執行計畫會不同呢？這是因為ShipCountry包含了大量的重複值，CustomerID重複值非常少，所以Sql server系統給ShipCountry推送的雜湊彙總，而CustomerID推送的是流彙總。也就是說Sql server系統會動態根據查詢的情況選擇合適的彙總方式。所以我們在做SQL最佳化的時候不能僅根據SQL語句來最佳化，還得結合具體資料分布的環境。

4.運算過程監控指標

4.1.監控元素： 

• 可視化查看已耗用時間
• T-sql語句查詢時間
• 佔用記憶體
• T-sql語句查詢IO

4.2.可視化查看已耗用時間： 

4.3.T-sql語句查詢時間： 

4.4.佔用記憶體： 

4.5.T-sql語句查詢IO： 

關於監控元素還有很多，這裡就列舉幾個。

部落格總結

SQL Server 彙總函式演算法最佳化技巧差不多就介紹到這裡，如果有對sql語句最佳化感興趣的可以看這篇部落格。sql server之資料庫語句最佳化

 1 贊  收藏  1 評論

SQL Server 彙總函式演算法最佳化技巧