荣耀彩票代理

IT技术互动交流平台

SQL Server聚焦查询计划Stream Aggregate VS Hash Match Aggregate(二十)

作者:JeffckyWang  来源:IT165收集  发布日期:2016-12-16 20:35:41

前言

ZHIQIANXILIEZHONGZAICHAXUNJIHUAZHONGYIZHICHUXIANStream Aggregate,DANGSHIYEZHISHIZUOLEJIBENLEJIE,DUIYUCHAXUNJIHUAZHONGCHUXIANDECAOZUO,WOMENDOUXUYAOQUXIANGXIYANJIUXIA,ZHIYOUZHEIYANGCAINENGDUICHAXUNJIHUAZHIXINGDEMEIYIBUCAOZUODOULERUZHIZHANG,SUOYICAIYOULEBENWENDECHUXIAN,JIANDUANDENEIRONG,SHENRUDELIJIE,Always to review the basics。

Stream Aggregate

荣耀彩票代理Stream AggregateTONGGUODANLIEHUOZHEDUOLIELAIDUIXINGJINXINGFENZUBINGQIEDUIZHIDINGDECHAXUNLAIJISUANJUHEBIAODASHI。ZUICHANGJIANDEJUHELEIXINGRUSUM、COUNT、SUM、AVG、MIN、MAX,DANGWOMENZHIXINGZHEIXIEJUHEHANSHUSHIZAICHAXUNJIHUAZHONGJIUHUICHUXIANStream Aggregate,Stream AggregateSHIFEICHANGKUAIDE,YINWEITAXUYAOZAISHURUSHITONGGUOZAIGROUP BYZHONGZHIDINGDELIEJINXINGPAIXU。RUGUOJUHEZHONGDESHUJUMEIYOUJINXINGPAIXUCISHIHUITONGGUOSortJINXINGYUPAIXUHUOZHESHIYONGSUOYINCHAZHAOHUOZHESUOYINSAOMIAOLAITIQIANYUPAIXUSHUJU。ZHIQIANWOMENTAOLUNGUOCHUXIANStream AggregateYOUSANZHONGFANGSHIFENBIEWEI:JUHEHANSHUJUHE,FENZUJUHE,DISTINCTJUHE,SHIJISHANGZHIYOULIANGZHONG,DISTINCTNEIBUJIUYONGDAOLEFENZU,ZHEILIWOMENJIANGStream AggregateFENWEILIANGZHONGLEIXING,YIZHONGSHIBIAOLIANGJUHE,LINGWAIYIZHONGZESHIFENZUJUHE。WOMENJUYIGEBIAOLIANGJUHEDELIZI,YEJIUSHIFANHUIDANZHIJUHE。

标值聚合

USE TSQL2012
GO

SELECT COUNT(*)
FROM Sales.Orders

荣耀彩票代理XIAMIANWOMENZAILAIFENZUJUHEDELIZI

USE TSQL2012
GO

SELECT custid
FROM Sales.Orders
GROUP BY custid

荣耀彩票代理SHANGSHUJIUSHIStream AggregateLIANGZHONGLEIXINGDELIZI,GUANYUBIAOLIANGJUHEBIJIAOJIANDANZHIJIELIYONGJUHEHANSHUJIUXING,XIAMIANWOMENZHUYAOXIANGXIJIANGJIEZHEILIANGZHONGLEIXINGZHONGDEFENZUJUHE。

分组聚合

WOMENLAIJIEHESQL Server 2012JICHUJIAOCHENGLAIKANYIGEJIANDANDELIZI

USE TSQL2012
GO

SELECT custid, COUNT(shipcity) AS [shipcity_count]
FROM Sales.Orders
GROUP BY custid

荣耀彩票代理SHANGSHUCHAXUNJIHUABIJIAOJIANDANWOMENLAIJIESHIXIA,SHOUXIANTONGGUOMORENZHUJIANCHUANGJIANDEJUJISUOYINLAIDUQUBIAOZHONGXINGSHUJU,JIEZHETONGGUOGROUP BYSHANGZHIDINGDELIEcustidLAIJINXINGPAIXU,WOMENKANDAOQIPAIXUCAOZUOJUTIXINXIJIUZHIDAO,RUXIA。JIEZHEBIANLISUOYOUcustid,SUOYOUXINGBEIDUQU,KAISHIYIXINGXINGDUQUBINGJISUANQIJUHEBIAODASHIDEZHI。ZHONGFUCHULIZHIDAOWANCHENGWEIZHI。

DUIYUTONGGUOLIUJUHEDUIcustidJINXINGFENZUDESHIYITUDAGAIRUXIA:

SHANGSHUYOUYUWEIDUIcustidCHUANGJIANSUOYINDAOZHISUOYIHUITONGGUOSortLAIJINXINGPAIXU,HAOWUYIWENDAOZHICHAXUNHUANMAN,ZHEILIWOMENDUIcustidCHUANGJIANFEIJUJISUOYINZAILAIKANKANQINGKUANG

CREATE NONCLUSTERED INDEX idx_nc_custid ON Sales.Orders(custid)

 

荣耀彩票代理CISHICHAXUNJIANGHUICHONGFENLIYONGSUOYIN,TAHUITONGGUOSHIYONGSUOYINPAIXULAIJINXINGJUHEJISUAN,SUOYIJIUBUHUIZAILIYONGSortLAIPAIXUDAOZHIXINGNENGDIXIA,TONGGUOSHANGSHUWOMENZHIDAO,ZAIJINXINGStream AggregateZHIQIANSHISHISHANGZAIZHIDINGDEFENZULIESHANGCHUANGJIANSUOYINLAIYUXIANPAIXUHUITIGAOCHAXUNXINGNENG,ERBUXUYAOZAIQULIYONGSortJINXINGPAIXUERHAOFEIBUBIYAODESHIJIAN。SHANGSHUWOMENYIJINGSHUOGUOZAIJINXINGPAIXUYAOMEZAIGROUP BYSHANGZHIDINGDELIETONGGUOCHUANGJIANSUOYINCHAZHAOHUOZHESUOYINPAIXU,RUGUOGROUP BYZHONGDELIEMEIYOUCHUANGJIANSUOYINCISHILIYONGSortLAIJINXINGXIANSHIPAIXU,RUXIAXIANSHIZHIDINGORDER BY custidLAIPAIXUHEMEIYOUZHIDINGDEHUAJIEGUOYIRANDOUSHISHIYONGSortLAIPAIXU,CISHIStream Aggregate,QISHIZHEIZHONGSHUOFABUTAIZHUNQUE,YINWEIZAISQL ServerZHONGYOULIANGZHONGJUHEFANGSHI,YIZHONGSHIStream Aggregate,LINGWAIYIZHONGZESHIHash Match Aggregate。

USE TSQL2012
GO

SELECT custid, COUNT(shipcity) AS [shipcity_count]
FROM Sales.Orders
GROUP BY custid
ORDER BY custid

ZICONGSQL Server 7ZHIHOUJIUCHUXIANLEStream AggregateHEHash AggregateLIANGZHONGJUHEFANGSHI,YEJIUSHISHUOSHANGSHUWOMENSHAOZUOXIUGAICHAXUNJIHUAJIUBIANCHENGLEHash AggregateDEXINGSHI。

USE TSQL2012
GO

DBCC RULEOFF('GbAggToStrm');
GO

SELECT custid, COUNT(shipcity) AS [shipcity_count]
FROM Sales.Orders
GROUP BY custid
OPTION(RECOMPILE)
GO

DBCC RULEON('GbAggToStrm');

SHANGSHUGbAggToStrmSHISHENMEGUI,QISHIRUGUOCHAXUNJIHUAZHONGZOUDEStream AggregateCAOZUODEHUA,YEJIUSHUOTAZOUDESHIGbAggToStrmGUIZE(GROUP BY Aggregate To Stream ),DANSHIZHEILIWOMENGUANBILECHAXUNJIHUABENGAIZOUDEStream AggregateCAOZUOJIGbAggToStrmGUIZE,SUOYICISHITAJIANGZHINENGZOUHash Aggregate。SUOYIDAOZHEILISHUOMINGZAIPAIXUSHIJISHIZHIDINGLEORDER BYCAOZUOYOUKENENGSHIDUOYUDE,DANSHIRUGUOWOMENBUZHIDINGDEHUA,YAOSHIWOMENXIWANGFANHUIDEJIEGUOJISHIPAIXUDE,CISHIYAOSHIZOUDEHash Aggregate,JIEGUOFANHUIDEJIEGUOJIJIANGSHIWUXUDE,DAOZHIWOMENDEBUDAOXIANGYAODEJIEGUOJI,SUOYIHAISHIXIWANGZAIPAIXUSHIZHIDINGORDER BYCAOZUO,ZHEIYANGNENGGOUBIMIANBUBIYAODEQINGKUANGFASHENG。

DISTINCT在Hash Match Aggregate和Stream Aggregate和DISTINCT Sort中的使用

DANGCHAXUNZHONGYONGDAOLEDISTINCTGUANJIANZISHI,CISHICHAXUNJIHUAYOUKENENGZOUStream Aggregate,YEYOUKENENGZOUDESHIHash Match Aggregate。SUOYIZAIZHEILIWOMENFENXIXIASHENMESHIHOUHUIYONGHash Match Aggregate,SHENMESHIHOUYOUHUIYONGDAOStream Aggregate。SHUODAODIDISTINCTGUANJIANZISHIYONGLAIQUZHONGDE,ZAISQL ServerZHONGLIYONGDISTINCTGUANJIANZILAIQUZHONGQICHAXUNJIHUAZOUDEFANGSHIFENWEILIANGZHONG,YIZHONGSHIZAIHAXIBIAOZHONGJIANLIWEIYIZHI,LINGWAIYIZHONGZESHIJIANGXINGJINXINGPAIXUFENPEIDAOZUZHONGRANHOUZHIFANHUIZUZHONGDEYIGEZHIJIKE。SUOYIZAISQL ServerZHONGSHIYONGHash Match AggregateLAISHIXIANHAXIBIAO,SHIYONGStream AggregateHUOZHEDISTINCT SortLAIDUISHUJUJINXINGPAIXUQUZHONG。

使用DISTINCT关键字走DISTINCT Sort

荣耀彩票代理DANGWOMENRUXIAZHIJIELIYONGDISTINCTLAICHAXUNSHIJIUSHILIYONGDEDISTINCT SortLAIPAIXUQUZHONG。

USE TSQL2012
GO

select DISTINCT custid 
FROM Sales.Orders

SUIRANHENMINGQUEZOUDESort,DANSHIZHEISHIJINGGUOSQLCHAXUNYINQINGYOUHUAGUOHOUCAIYOUDE,ZUIYUANSHIDEQINGKUANGSHIXIANJINXINGSortJIEZHEJINXINGStream Aggregate,XIAMIANWOMENGUANBISortDEGUIZEKANKAN。

USE TSQL2012
GO

DBCC RULEOFF('GbAggToSort')
SELECT DISTINCT custid
FROM Sales.Orders
OPTION(RECOMPILE)

DBCC RULEON('GbAggToSort')

使用DISTINCT关键字走Hash Match Aggregate

荣耀彩票代理DANGWEIZAILIESomeColumnCHUANGJIANSUOYINSHIWOMENJINXINGRUXIACHAXUN

USE TSQL2012
GO

SELECT DISTINCT SomeColumn
FROM dbo.BigTable

JIEXIALAIWOMENZAILIESHANGCHUANGJIANSUOYIN

CREATE NONCLUSTERED INDEX idx_noncls_somecolumn ON dbo.BigTable(SomeColumn)

ZAICHUANGJIANSUOYINSHICISHICHAXUNJIHUAZOUDEQUESHIStream Aggregate,YEJIUSHISHUODANGLIYONGDISTINCTGUANJIANZICHAXUNSHIQIELIEYIJINGJINXINGLEPAIXU,CISHICHAXUNJIHUAZOUStream Aggregate。NEISHENMESHIHOUYONGHash Match AggregateNI,SHANGSHUDUILIEWEICHUANGJIANSUOYINSHIZOUDESHIHash Match AggregateYINWEISHUJULIANGBIJIAODACISHIHAILIYONGLEBINGXINGJISUAN,HUANJUHUASHUODANGDUILIEWEICHUANGJIANSUOYINSHIQIESHUJULIANGFEICHANGDATONGSHIFENZUBIJIAOSHAOSHI,CHAXUNJIHUAGENGJIAGENGQINGXIANGYUZOUHash Match Aggregate,SHURUDALIANGDESHUJUTONGGUOHash Match AggregateJIEHEBINGXINGJISUANXIAOLVYEFEICHANGGAO,DANGRANFENZUJIAOSHAOGENGHAO,CISHIBUHUITAIZHANYONGHAXIBIAO。JIEXIALAIWOMENXIANZHICHAXUNJIEGUOJIDETIAOSHU。

USE TSQL2012
GO

SELECT DISTINCT TOP 10 SomeColumn
FROM dbo.BigTable

CISHICHAXUNJIHUABUZAISHIHash Match AggregateDAITIDESHIHash Match(Flow Distinct)WOMENKANXIAmsdnGUANYUFlow DistinctDEJIESHI:Flow DistinctLUOJIYUNSUANFUYONGYUTONGGUOSAOMIAOSHURULAISHANCHUZHONGFUXIANG。SUIRANDistinct YUNSUANFUZAISHENGCHENGRENHESHURUQIANXIAOHAOSUOYOUDESHURU,DANFlowDistinct YUNSUANFUZAICONGSHURUHUODEXINGSHIFANHUIMEIXING(CHUFEIGAIXINGSHIYIGEZHONGFUXIANG,RUOSHIZHEIYANGZESHANCHUGAIXING)

荣耀彩票代理YEJIUSHISHUODISTINCTZHIJIEJIUGUOLVLEZHONGFUXING,ERFlow DistictZEHUODEMEIXINGSHIBINGFANHUIMEIYIXING,ZHEIJIUSHIFlow Distinct,TADECHUXIANYILAIYUZAICHAXUNJIHUAZHONGGUJIWEIYIZHIDESHULIANG,DANGWOMENJIANGTOPDESHULIANGSHEZHIWEIJIEJIN100WANHUOZHEBI100WANHAISHAOYIDIANSHICISHIZOUDESHIHash Match Aggregate。DAOCIWOMENGUANYUHash Match AggregateHEStream AggregateDEFENXISUANSHIJIESHULE,WOMENXIAGEJIBENJIELUN:

Hash Match AggregateHEStream AggregateFENXIJIELUN:

(1)CHAXUNZHONGYOUDISTINCTGUANJIANZISHI:DANGZAICHAXUNLIESHANGCHUANGJIANSUOYINSHIJILIEJINXINGLEPAIXUSHICISHIZOUStream Aggregate,DANGSHUJULIANGFEICHANGDASHIQIEWEICHUANGJIANSUOYINSHICISHIYIBANZOUDESHIHash Match AggregateBINGJIEHEBINGXINGJISUAN,QIYUQINGKUANGZESHIZOUDEDistinct Sort。

(2)CHAXUNZHONGMEIYOUDISTINCTGUANJIANZISHI,DUIYUBIAOLIANGJUHEHEFENZUJUHEZOUDESHIStream Aggregate。

总结

HAOLEBENJIEGUANYUHash Match AggregateHEStream AggregateDEJIESHAOJIUDAOCIWEIZHI,JIBENSUANSHILEJIE,TAIFUZADEYEMEIQUGUODUOTANTAO,ZHEISHIDBADESHIQINGLE,XIAYIJIEWOMENCHUANCHAJIANGJIANGGUANYUJISUANLIECHIJIUHUAXILIEWENZHANG,JIANDUANDENEIRONG,SHENRUDELIJIE,WOMENXIAJIEZAIHUI。 

Tag标签:               
  • 专题推荐

About IT165 - 广告服务 - 隐私声明 - 版权申明 - 免责条款 - 网站地图 - 网友投稿 - 联系方式
本站内容来自于互联网,仅供用于网络技术学习,学习中请遵循相关法律法规