Sql server聚合函數(shù)在實(shí)際工作中應(yīng)對(duì)各種需求使用的還是很廣泛的，對(duì)于聚合函數(shù)的優(yōu)化自然也就成為了一個(gè)重點(diǎn)，一個(gè)程序優(yōu)化的好不好直接決定了這個(gè)程序的聲明周期。Sql server聚合函數(shù)對(duì)一組值執(zhí)行計(jì)算并返回單一的值。聚合函數(shù)對(duì)一組值執(zhí)行計(jì)算，并返回單個(gè)值。除了 COUNT 以外，聚合函數(shù)都會(huì)忽略空值。 聚合函數(shù)經(jīng)常與 SELECT 語(yǔ)句的 GROUP BY 子句一起使用。

一.寫在前面

如果有對(duì)Sql server聚合函數(shù)不熟或者忘記了的可以看我之前的一片博客。

本文中所有數(shù)據(jù)演示都是用Microsoft官方示例數(shù)據(jù)庫(kù)：Northwind，至于Northwind大家也可以在網(wǎng)上下載。

二.Sql server標(biāo)量聚合

2.1.概念：在只包含聚合函數(shù)的 SELECT 語(yǔ)句列列表中指定的一種聚合函數(shù)（如 MIN()、MAX()、COUNT()、SUM() 或 AVG()）。當(dāng)列列表只包含聚合函數(shù)時(shí)，則結(jié)果集只具有一個(gè)行給出聚合值，該值由與 WHERE 子句謂詞相匹配的源行計(jì)算得到。

2.2.探索標(biāo)量聚合：

我們先用Sql server的"包括實(shí)際的執(zhí)行計(jì)劃"來(lái)看看一個(gè)簡(jiǎn)單的流聚合COUNT()來(lái)看看表里數(shù)據(jù)所有的行數(shù)。

再通過(guò)SET SHOWPLAN_ALL ON(關(guān)于輸出中包含的列更多信息可以在鏈接中查看)來(lái)看看有關(guān)語(yǔ)句執(zhí)行情況的詳細(xì)信息，并估計(jì)語(yǔ)句對(duì)資源的需求。

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

• 索引掃描：掃描當(dāng)前表的行數(shù)
• 流計(jì)算：計(jì)算行數(shù)的數(shù)量
• 計(jì)算標(biāo)量：將流計(jì)算出來(lái)的結(jié)果轉(zhuǎn)化為適當(dāng)?shù)念愋汀?因?yàn)樗饕龗呙璩鰜?lái)的結(jié)果是根據(jù)表中數(shù)據(jù)的大小決定的，如果表中數(shù)據(jù)很多的話，COUNT是int類型就會(huì)有問(wèn)題，所以在最終返回的時(shí)候需要將默認(rèn)類型(數(shù)值一般默認(rèn)類型是Big)轉(zhuǎn)成int類型。)
• 小結(jié)：通過(guò)SET SHOWPLAN_ALL ON我們可以查看Sql server聚合函數(shù)在給我們呈現(xiàn)最終效果的時(shí)候，為這個(gè)效果做了些什么事情。

2.3.標(biāo)量聚合優(yōu)化技巧：

我們通過(guò)兩個(gè)比較簡(jiǎn)單的sql查詢來(lái)看看他們的區(qū)別

從上圖中可以看到，其實(shí)這兩個(gè)查詢從語(yǔ)句上來(lái)說(shuō)沒(méi)什么太大的區(qū)別，但是為什么開銷會(huì)不一樣，一個(gè)是查詢城市一個(gè)是查詢訂單號(hào)。這是因?yàn)槠鋵?shí)DISTINCT對(duì)于OrderID查詢來(lái)說(shuō)，是沒(méi)有什么意義的，因?yàn)镺rderID是主鍵，是不會(huì)有重復(fù)的。而ShipCity是會(huì)有重復(fù)的，Sql server的去重機(jī)制在去重的時(shí)候，會(huì)有一個(gè)排序的過(guò)程。這個(gè)排序還是比較消耗資源的。

對(duì)于數(shù)據(jù)量比較大的表其實(shí)不是很建議對(duì)大表排序或者對(duì)大表的某個(gè)重復(fù)次數(shù)多的字段去重運(yùn)算。所以我們這里可以對(duì)ShipCity進(jìn)行優(yōu)化一下?？梢詫?duì)ShipCity創(chuàng)建一個(gè)非聚集索引。

從上圖中可以看到，加了索引以后COUNT(DISTINCT ShipCity)的查詢變成了兩個(gè)流聚合，而沒(méi)有了排序，節(jié)省了開銷。

總結(jié)：對(duì)于標(biāo)量聚合從上面的例子大家可以看到，標(biāo)量聚合優(yōu)缺點(diǎn)很明顯:

• Sql server標(biāo)量聚合優(yōu)點(diǎn)：算法比較簡(jiǎn)單直觀，適合非重復(fù)值的聚合操作。Sql server標(biāo)量聚合缺點(diǎn)：性能較差(需要排序)，不適合重復(fù)值的聚合操作。
• 優(yōu)化技巧:盡量避免排序產(chǎn)生，將分組字(GROUP BY)段鎖定在索引覆蓋范圍內(nèi)

三.Sql server哈希聚合

3.1.概念：

哈希(Hash，一般翻譯做“散列”，也有直接音譯為“哈希”的，就是把任意長(zhǎng)度的輸入（又叫做預(yù)映射， pre-image），通過(guò)散列算法，變換成固定長(zhǎng)度的輸出，該輸出就是散列值。這種轉(zhuǎn)換是一種壓縮映射，也就是，散列值的空間通常遠(yuǎn)小于輸入的空間，不同的輸入可能會(huì)散列成相同的輸出，所以不可能從散列值來(lái)唯一的確定輸入值。簡(jiǎn)單的說(shuō)就是一種將任意長(zhǎng)度的消息壓縮到某一固定長(zhǎng)度的消息摘要的函數(shù)。)

哈希聚合的內(nèi)部實(shí)現(xiàn)方法和哈希連接的實(shí)現(xiàn)機(jī)制一樣，需要哈希函數(shù)的內(nèi)部運(yùn)算，形成不同的哈希值，依次并行掃描數(shù)據(jù)形成聚合值。

3.2.背景：

為了解決流聚合的不足，應(yīng)對(duì)大數(shù)據(jù)的操作，所以哈希聚合就誕生了。

3.3.分析：

來(lái)看看兩個(gè)簡(jiǎn)單的查詢。

ShipCountry和CustomerID的分組查詢看上去很類似，但是為什么執(zhí)行計(jì)劃會(huì)不同呢？這是因?yàn)镾hipCountry包含了大量的重復(fù)值，CustomerID重復(fù)值非常少，所以Sql server系統(tǒng)給ShipCountry推送的哈希聚合，而CustomerID推送的是流聚合。也就是說(shuō)Sql server系統(tǒng)會(huì)動(dòng)態(tài)的根據(jù)查詢的情況選擇合適的聚合方式。所以我們?cè)谧鯯QL優(yōu)化的時(shí)候不能僅根據(jù)SQL語(yǔ)句來(lái)優(yōu)化，還得結(jié)合具體數(shù)據(jù)分布的環(huán)境。

四.運(yùn)算過(guò)程監(jiān)控指標(biāo)

4.1.監(jiān)控元素：

可視化查看運(yùn)行時(shí)間T-sql語(yǔ)句查詢時(shí)間占用內(nèi)存T-sql語(yǔ)句查詢IO

4.2.可視化查看運(yùn)行時(shí)間：

4.3.T-sql語(yǔ)句查詢時(shí)間：

4.4.占用內(nèi)存：

4.5.T-sql語(yǔ)句查詢IO：

關(guān)于監(jiān)控元素還有很多，這里就列舉幾個(gè)。

SQL Server 聚合函數(shù)算法優(yōu)化技巧差不多就介紹到這里，希望對(duì)大家優(yōu)化聚合函數(shù)算法有所幫助。