IT技(ji)術互動(dong)交(jiao)流(liu)平台

山东彩票官网

作者︰JeffckyWang  來(lai)源(yuan)︰IT165收zhan)nbsp; 發布日期︰2020-02-26 14:41:41

山东彩票官网

之(zhi)前(qian)系列中在查詢計(ji)劃中一直(zhi)出(chu)現Stream Aggregate,當時也(ye)只是做了基本(ben)了解,對za)誆檠 ji)劃中出(chu)現zhi)牟僮鰨 頤嵌du)需要去詳細研究(jiu)下,只有這(zhe)樣才(cai)能對查詢計(ji)劃執行的每(mei)一步操作都(du)了如指掌,所以才(cai)有了本(ben)文的出(chu)現,簡短的內容,深入(ru)的理解,Always to review the basics。

Stream Aggregate

Stream Aggregate通過單列或(huo)者多(duo)列來(lai)對行進(jin)行分組並且對指定的查詢來(lai)計(ji)算(suan)聚合表達(da)式。最(zui)常見的聚合類型如SUM、COUNT、SUM、AVG、MIN、MAX,當我們執行這(zhe)些聚合函數(shu)時在查詢計(ji)劃中就會出(chu)現Stream Aggregate,Stream Aggregate是非常快的,因為它需要在輸入(ru)時通過在GROUP BY中指定的列進(jin)行排序。如果聚合中的數(shu)據沒有進(jin)行排序此(ci)時會通過Sort進(jin)行預(yu)排序或(huo)者使用(yong)索引(yin)查找或(huo)者索引(yin)掃(sao)描來(lai)提前(qian)預(yu)排序數(shu)據。之(zhi)前(qian)我們討論過出(chu)現Stream Aggregate有三種方式分zhi)鷂 壕酆蝦 shu)聚合,分組聚合,DISTINCT聚合,實際上只有兩種,DISTINCT內部(bu)就用(yong)到了分組,這(zhe)里我們將Stream Aggregate分為兩種類型,一種是標量(liang)聚合,另外一種則是分組聚合。我們舉一個標量(liang)聚合的例(li)子,也(ye)就是返回單值(zhi)聚合。

標值(zhi)聚合

USE TSQL2012GOSELECT COUNT(*)FROM Sales.Orders

下面我們再來(lai)分組聚合的例(li)子

USE TSQL2012GOSELECT custidFROM Sales.OrdersGROUP BY custid

上述就是Stream Aggregate兩種類型的例(li)子,關于標量(liang)聚合比(bi)較簡單直(zhi)接利用(yong)聚合函數(shu)就行,下面我們主要詳細講解這(zhe)兩種類型中的分組聚合。

分組聚合

我們來(lai)結合SQL Server 2012基礎教程(cheng)來(lai)看一個簡單的例(li)子

USE TSQL2012GOSELECT custid, COUNT(shipcity) AS [shipcity_count]FROM Sales.OrdersGROUP BY custid

上述查詢計(ji)劃比(bi)較簡單我們來(lai)解釋下,首先通過默(mo)認主鍵(jian)創建的聚集索引(yin)來(lai)讀取表中行數(shu)據,接著通過GROUP BY上指定的列custid來(lai)進(jin)行排序,我們看到其排序操作具體信息就知道,如下。接著遍歷所有custid,所有行被讀取,開始一行行讀取並計(ji)算(suan)其聚合表達(da)式的值(zhi)。重復(fu)處理直(zhi)到完成為止。

對za)諭 liu)聚合對custid進(jin)行分組的示意圖大概如下︰

上述由于未對custid創建索引(yin)導致lv)曰嵬 ort來(lai)進(jin)行排序,毫無(wu)疑問(wen)導致查詢緩慢,這(zhe)里我們對custid創建非聚集索引(yin)再來(lai)看看情況

CREATE NONCLUSTERED INDEX idx_nc_custid ON Sales.Orders(custid)

 

此(ci)時查詢將會充分利用(yong)索引(yin),它會通過使用(yong)索引(yin)排序來(lai)進(jin)行聚合計(ji)算(suan),所以就不會再利用(yong)Sort來(lai)排序導致性能低下,通過上述我們知道,在進(jin)行Stream Aggregate之(zhi)前(qian)事lv)瞪顯謚付 姆腫榱猩洗唇ㄋ饕yin)來(lai)預(yu)先排序會提高查詢性能,而不需要再去利用(yong)Sort進(jin)行排序而耗費不必要的時間。上述我們已經說(shuo)過在進(jin)行排序要麼在GROUP BY上指定的列通過創建索引(yin)查找或(huo)者索引(yin)排序,如果GROUP BY中的列沒有創建索引(yin)此(ci)時利用(yong)Sort來(lai)進(jin)行顯示排序,如下顯示指定ORDER BY custid來(lai)排序和(he)沒有指定的話結果依然都(du)是使用(yong)Sort來(lai)排序,此(ci)時Stream Aggregate,其實這(zhe)種說(shuo)法不太準確,因為在SQL Server中有兩種聚合方式,一種是Stream Aggregate,另外一種則是Hash Match Aggregate。

USE TSQL2012GOSELECT custid, COUNT(shipcity) AS [shipcity_count]FROM Sales.OrdersGROUP BY custidORDER BY custid

自yuan)QL Server 7之(zhi)後就出(chu)現了Stream Aggregate和(he)Hash Aggregate兩種聚合方式,也(ye)就是說(shuo)上述我們稍作修改(gai)查詢計(ji)劃就變(bian)成了Hash Aggregate的形wen)健/p>

USE TSQL2012GODBCC RULEOFF('GbAggToStrm');GOSELECT custid, COUNT(shipcity) AS [shipcity_count]FROM Sales.OrdersGROUP BY custidOPTION(RECOMPILE)GODBCC RULEON('GbAggToStrm');

上述GbAggToStrm是什麼鬼,其實如果查詢計(ji)劃中走的Stream Aggregate操作的話,也(ye)就說(shuo)它走的是GbAggToStrm規則(GROUP BY Aggregate To Stream ),但是這(zhe)里我們關閉了查詢計(ji)劃本(ben)該(gai)走的Stream Aggregate操作即GbAggToStrm規則,所以yuan)ci)時它將只能走Hash Aggregate。所以到這(zhe)里說(shuo)明在排序時即使指定了ORDER BY操作有可能是多(duo)余的,但是如果我們不指定的話,要是我們希望返回的結果集是排序的,此(ci)時要是走的Hash Aggregate,結果返回的結果集將是無(wu)序的,導致我們得不到想要的結果集,所以還是希望在排序時指定ORDER BY操作,這(zhe)樣能夠避免(mian)不必要的na)榭齜?sheng)。

DISTINCT在Hash Match Aggregate和(he)Stream Aggregate和(he)DISTINCT Sort中的使用(yong)

當查詢中用(yong)到了DISTINCT關鍵(jian)字(zi)時,此(ci)時查詢計(ji)劃有可能走Stream Aggregate,也(ye)有可能走的是Hash Match Aggregate。所以在這(zhe)里我們分析下lv)裁詞焙蚧嵊yong)Hash Match Aggregate,什麼時候又會用(yong)到Stream Aggregate。說(shuo)到底(di)DISTINCT關鍵(jian)字(zi)時用(yong)來(lai)去重的,在SQL Server中利用(yong)DISTINCT關鍵(jian)字(zi)來(lai)去重其查詢計(ji)劃走的方式分為兩種,一種是在哈(ha)希表中建立唯一值(zhi),另外一種則是將行進(jin)行排序分配到組中然後只返回組中的一個值(zhi)即可。所以在SQL Server中使用(yong)Hash Match Aggregate來(lai)實現哈(ha)希表,使用(yong)Stream Aggregate或(huo)者DISTINCT Sort來(lai)對數(shu)據進(jin)行排序去重。

山东彩票官网

當我們如下直(zhi)接利用(yong)DISTINCT來(lai)查詢時ben)褪搶yong)的DISTINCT Sort來(lai)排序去重。

USE TSQL2012GOselect DISTINCT custid FROM Sales.Orders

雖然很明確走的Sort,但是這(zhe)是經過SQL查詢引(yin)擎優化過後才(cai)有的,最(zui)原始的na)榭鍪竅冉jin)行Sort接著進(jin)行Stream Aggregate,下面我們關閉Sort的規則看看。

USE TSQL2012GODBCC RULEOFF('GbAggToSort')SELECT DISTINCT custidFROM Sales.OrdersOPTION(RECOMPILE)DBCC RULEON('GbAggToSort')

山东彩票官网

當未在列SomeColumn創建索引(yin)時我們進(jin)行如下查詢

USE TSQL2012GOSELECT DISTINCT SomeColumnFROM dbo.BigTable

接下來(lai)我們在列上創建索引(yin)

CREATE NONCLUSTERED INDEX idx_noncls_somecolumn ON dbo.BigTable(SomeColumn)

在創建索引(yin)時此(ci)時查詢計(ji)劃走的卻是Stream Aggregate,也(ye)就是說(shuo)當利用(yong)DISTINCT關鍵(jian)字(zi)查詢時且列已經進(jin)行了排序,此(ci)時查詢計(ji)劃走Stream Aggregate。那(na)什麼時候用(yong)Hash Match Aggregate呢,上述對列未創建索引(yin)時走的是Hash Match Aggregate因為數(shu)據量(liang)比(bi)較大此(ci)時還利用(yong)了並行計(ji)算(suan),換句(ju)話說(shuo)當對列未創建索引(yin)時且數(shu)據量(liang)非常大同時分組比(bi)較少時,查詢計(ji)劃更加更傾向于走Hash Match Aggregate,輸入(ru)大量(liang)的數(shu)據通過Hash Match Aggregate結合並行計(ji)算(suan)效率(lv)也(ye)非常高,當bi)環腫榻仙俑茫 ci)時不會太佔(zhan)用(yong)哈(ha)希表。接下來(lai)我們限制查詢結果集的條(tiao)數(shu)。

USE TSQL2012GOSELECT DISTINCT TOP 10 SomeColumnFROM dbo.BigTable

此(ci)時查詢計(ji)劃不再是Hash Match Aggregate代替(ti)的是Hash Match(Flow Distinct)我們看下msdn關于Flow Distinct的解釋︰Flow Distinct邏輯運(yun)算(suan)符用(yong)于通過掃(sao)描輸入(ru)來(lai)刪除(chu)重復(fu)項(xiang)。雖然Distinct 運(yun)算(suan)符在生(sheng)成任何wen)淙ru)前(qian)消耗所有的輸入(ru),但FlowDistinct 運(yun)算(suan)符在從輸入(ru)獲得行時返回每(mei)行(除(chu)非該(gai)行是一個重復(fu)項(xiang),若是這(zhe)樣則刪除(chu)該(gai)行)

也(ye)就是說(shuo)DISTINCT直(zhi)接就過濾(lv)了重復(fu)行,而Flow Distict則獲得每(mei)行時並返回每(mei)一行,這(zhe)就是Flow Distinct,它的出(chu)現依賴于在查詢計(ji)劃中估計(ji)唯一值(zhi)的數(shu)量(liang),當我們將TOP的數(shu)量(liang)設(she)置為接近100萬(wan)或(huo)者比(bi)100萬(wan)還少一點(dian)時此(ci)時走的是Hash Match Aggregate。到此(ci)我們關于Hash Match Aggregate和(he)Stream Aggregate的分析算(suan)是結束了,我們下個基本(ben)結論︰

Hash Match Aggregate和(he)Stream Aggregate分析結論︰

(1)查詢中有DISTINCT關鍵(jian)字(zi)時︰當在查詢列上創建索引(yin)時即列進(jin)行了排序時此(ci)時走Stream Aggregate,當數(shu)據量(liang)非常大時且未創建索引(yin)時此(ci)時一般(ban)走的是Hash Match Aggregate並結合並行計(ji)算(suan),其余情況則是走的Distinct Sort。

(2)查詢中沒有DISTINCT關鍵(jian)字(zi)時,對za)詒炅liang)聚合和(he)分組聚合走的是Stream Aggregate。

總結

好了本(ben)節關于Hash Match Aggregate和(he)Stream Aggregate的介(jie)紹就到此(ci)為止,基本(ben)算(suan)是了解,太復(fu)雜(za)的也(ye)沒去過多(duo)探討,這(zhe)是DBA的事情了,下一節我們穿插講講關于計(ji)算(suan)列持久化系列文章,簡短的內容,深入(ru)的理解,我們下節再會。 

  • 山东彩票官网

  • 李華明iOS-Cocos2d游戲開發專(zhuan)題
  • 本(ben)教程(cheng)為 李華明 編(bian)著的iOS-Cocos2d游戲開發系列教程(cheng)︰教程(cheng)涵蓋(gai)關于i...... 詳細
About IT165 - 廣告服務 - 隱私聲明 - 版權申明 - 免(mian)責條(tiao)款 - 網站地(di)圖 - 網友投稿(gao) - 聯系方式
本(ben)站內容來(lai)自za)諢? 僅(jin)供用(yong)于網絡技(ji)術學習,學習中請遵循(xun)相關法律法規
山东彩票官网 | 下一页