SQL Server 第二章 第十節(jié) 執(zhí)行計劃

1.1執(zhí)行計劃1?1?1執(zhí)行計劃操作符詳解——斷言（Assert）1.1.1.1前言：很多地方對于語句的優(yōu)化，一般比較靠譜的回復(fù)即是——把執(zhí)行計劃發(fā)出來看看。當(dāng)然那些只看語句就說如何如何改代碼，我一直都是拒絕的，因為這種算是純蒙。根據(jù)本人經(jīng)驗，大量的性能問題單純從語句來看很難發(fā)現(xiàn)瓶頸，同一個語句，由于環(huán)境的不同，差距非常大，所以比較合適的還是分析執(zhí)行計劃。那么對于執(zhí)行計劃，一般使用圖形化執(zhí)行計劃就差不多了，但是用過的人也有一些疑惑，里面的圖標(biāo)（稱為操作符）并不非常直觀。所以從本文開始，會整理一些不怎么常見但又比較重要的操作符并進(jìn)行解釋，對于那些表掃描、索引掃描、聚集索引掃描、索引查找、聚集索引查找這些非常常見的操作符，暫時不打算介紹。只有了解一些重要且常見的操作符，才能對語句進(jìn)行準(zhǔn)確有效的性能分析和優(yōu)化。本系列文章預(yù)計包含下面操作符：斷言：Assert（英文版本圖形化界面的名字，中文版本中XML格式的執(zhí)行計劃和TEXT格式的執(zhí)行計劃的名字。下同）串聯(lián)：Concatenation計算標(biāo)量：ComputeScalar鍵查找：KeyLookup假脫機：Spools表假脫機：LazySpool索引假脫機：IndexSpool行計數(shù)假脫機：RowCountSpool流聚合：StreamAggregate排序：Sort合并聯(lián)接：MergeJoin合并間隔：MergeInterval拆分、折疊：Split，Collapse1?1?1?2斷言：Assert運算符是一個物理運算符。在執(zhí)行計劃中，如果為中文版圖形化執(zhí)行計劃，被稱為“斷言”，在英文版及非圖形化執(zhí)行計劃中顯示為Assert。其圖標(biāo)為：Assert運算符用于驗證條件。例如，驗證引用完整性或確保標(biāo)量子查詢返回一行。對于每個輸入行，Assert運算符都要計算執(zhí)行計劃的Argument列中的表達(dá)式。如果此表達(dá)式的值為NULL，則通過Assert運算符傳遞該行，并且查詢執(zhí)行將繼續(xù)。如果此表達(dá)式的值非空，則將產(chǎn)生相應(yīng)的錯誤。1.1.1.3斷言與Check約束：先來看看這段代碼，在服務(wù)器執(zhí)行時，先創(chuàng)建測試環(huán)境，使用TempDB是不錯的選擇：USEtempdbGOIFOBJECT_ID('TableAssert')ISNOTNULLDROPTABLETableAssertGOCREATETABLETableAssert(IDINTEGER,GenderCHAR(1))GOALTERTABLETableAssertADDCONSTRAINTck_Gender_M_FCHECK(GenderIN('M','F'))GO選中下面代碼，不要執(zhí)行，選擇“顯示估計的執(zhí)行計劃”，如圖：代碼如下:INSERTINTOTableAssert(ID,Gender)IDINTEEER.Gender匚EAR1INSERT_HTOTab_eA3BsrrID,GenderVALUES(1r'Xr」消息4按行計劃背詞1:（月1茂批有關(guān)旳:,查詞卄鉗：10■沁VALUES(1,'X')GO從上圖可見有一個操作符叫“斷言（Assert）”，那么這個里面是什么東西呢？把鼠標(biāo)移到這個操作符上面可以看到下圖：

注意上面的解釋：用于驗證指定的條件是否存在，這個解釋很直觀，并且看謂詞部分，說明了實際驗證的內(nèi)容，判斷Gender字段的插入值是否屬于F/M兩種，如果不是則返回NULL。斷言操作符會針對驗證返回值進(jìn)行處理，如果驗證返回NULL，則返回錯誤信息，也就是如果你直接執(zhí)行INSERT語句就可以看到報錯：消息阿級別％妝態(tài)第工行INSERT語句與CHECK約束11ckSenderMF"沖突。該沖突發(fā)生于數(shù)據(jù)庫“”珂業(yè)、、表ll-dha.Ta.bleAgsert11r亡語句已終止。1.1.1.4斷言與外鍵約束：下面來看個關(guān)于外鍵約束的例子：usetempdbgoALTERTABLETableAssertADDID_GendersINTGOIFOBJECT_ID('TableFOREIGN')ISNOTNULLDROPTABLETableFOREIGNGOCREATETABLETableFOREIGN(IDIntegerPRIMARYKEY,GenderCHAR(1))GOINSERTINTOTableFOREIGN(ID,Gender)VALUES(1,'F')INSERTINTOTableFOREIGN(ID,Gender)VALUES(2,'M')INSERTINTOTableFOREIGN(ID,Gender)VALUES(3,'N')GOALTERTABLETableAssertADDCONSTRAINTfk_Tab2FOREIGNKEY(ID_Genders)REFERENCESTableFOREIGN(ID)GO同樣，我們使用估計執(zhí)行計劃測試一下INSERT語句:語句如下：INSERTINTOTableAssert(ID,ID_Genders,Gender)VALUES(1,4,'X')這次我們使用另外一個工具:SETSHOWPLAN_TEXTON按這種方式執(zhí)SETSHOWPLAN_TEXTONGOINSERTINTOTableAssert(ID,ID_Genders,Gender)VALUES(1,4,'X')會看到兩個結(jié)果，第一個是語句，不用關(guān)，我們看第二個結(jié)果：|--Assert(WHERE:(CASEWHENNOT[Pass1009]AND[Expr1008]ISNULLTHEN(0)ELSENULLEND))|--NestedLoops(LeftSemiJoin,PASSTHRU:([tempdb].[dbo].[TableAssert].[ID_Genders]ISNULL),OUTERREFERENCES:([tempdb].[dbo].[TableAssert].[ID_Genders]),DEFINE:([Expr1008]=[PROBEVALUE]))|--Assert(WHERE:(CASEWHEN[tempdb].[dbo].[TableAssert].[Gender]<>'F'AND[tempdb].[dbo].[TableAssert].[Gender]<>'M'THEN(0)ELSENULLEND))||--TableInsert(OBJECT:([tempdb].[dbo].[TableAssert]),SET:([tempdb].[dbo].[TableAssert].[ID]=[@1],[tempdb].[dbo].[TableAssert].[ID_Genders]=[@2],[tempdb].[dbo].[TableAssert].[Gender]=[Expr1004]),DEFINE:([Expr1004]=CONVERT_IMPLICIT(char(1),[@3],0)))|--ClusteredIndexSeek(OBJECT:([tempdb].[dbo].[TableFOREIGN].[PK__TableFOR__3214EC27173876EA]),SEEK:([tempdb].[dbo].[TableFOREIGN].[ID]=[tempdb].[dbo].[TableAssert].[ID_Genders])ORDEREDFORWARD)這個結(jié)果內(nèi)容較多可能不直觀，讀者可以執(zhí)行測試看結(jié)果?？梢钥吹嚼锩嬗袃纱蜛ssert，自下而上地閱讀，第一個Assert(也就是下面那個，針對于圖形化界面而言是右邊那個，因為圖形化執(zhí)行計劃是從右到左地閱讀)是前面用于CHECK約束的，如果返回0則繼續(xù)運行語句，否則返回錯誤。對于第二個Assert用于檢測兩表關(guān)聯(lián)的結(jié)果,其中“[Exprl008]ISNULL”(注意[Exprl008]不是固定的，根據(jù)每臺機器可能返回不同值，在本人機器上的SQL2008/2012分別執(zhí)行都得到不同的[Expr]值)，我們需要知道[Expr1008]是什么，內(nèi)容中有DEFINE:([Expr1008]=[PROBEVALUE])，這就是表關(guān)聯(lián)的結(jié)果。如果INSERT語句中ID_Gender的值已經(jīng)存在與TableFOREIGN，那么這個Probe(探測器)會返回關(guān)聯(lián)值。否則返回NULL。所以這個“斷言”是檢查TableForeign中的值，如果沒有找到INSERT中傳入的值，斷言會返回一個異常。如果ID_Genders的值為NULL，那么SQLServer不能返回異常，而是返回“0”并繼續(xù)運行語句。如果運行上面的INSERT語句，SQLServer會返回異常，因為值為X違反了check約束：但是如果把X換成F再運行，還是會報錯，因為違反了外鍵約束:1319F")丄消息F消息5豎』級劃二S狀志S第二；」、INS3R7語句匸FOREIGNKZY約束吒k_hh2”；【=l突o該沖突發(fā)生于數(shù)據(jù)庫"tEipdh”』表irdha.ZabLeIGN,rTc語句已終止。INSERTINTOTahleAssertJTDID&皂nd皂工已fG皂Tider}VALUES(1F45ET5HOKFLAH_TEXTCFFGO但是當(dāng)把4換成NULL或1或2或3之后，再運行插入語句，就不會產(chǎn)生異常：GG1819INSERTINTOTah-leissert(IDfIDfGender}VALUES(lf2f1F'}丄消息<1行受影響)1.1.1.5斷言與子查詢：斷言操作符同樣可以用于檢查子查詢，對于標(biāo)量子查詢不能返回多個值,但是有時候?qū)懛ê蛿?shù)據(jù)的變動會引發(fā)多值錯誤。此時斷言扮演著校驗標(biāo)量子查詢是否返回一個值的角色。下面來看看這兩個語句：INSERTINTOTableAssert(ID,Gender)VALUES((SELECTIDFROMTableAssert),'F')INSERTINTOTableAssert(ID,Gender)VALUES((SELECTIDFROMTableAssert),'F')用上面的方法查看一下執(zhí)行計劃：SETSHOWPLAN_TEXTONGOINSERTINTOTableAssert(ID,Gender)VALUES((SELECTIDFROMTableAssert),'F')INSERTINTOTableAssert(ID,Gender)VALUES((SELECTIDFROMTableAssert),'F')觀察語句大概可以知道發(fā)生什么情況，第一個insert會成功(除非你已經(jīng)修改過里面的數(shù)據(jù))，因為VALUES中的SELECT部分只返回一個值，但是第二個INSERT由于VALUES中的SELECT有兩個值(第一個INSERT加入的)，所以會報錯。結(jié)果如下：|--Assert(WHERE:([Expr1013]))|--ComputeScalar(DEFINE:([Expr1013]=CASEWHEN[tempdb].[dbo].[TableAssert].[Gender]<>'F'AND[tempdb].[dbo].[TableAssert].[Gender]<>'M'THEN(0)ELSENULLEND))|--TableInsert(OBJECT:([tempdb].[dbo].[TableAssert]),SET:([tempdb].[dbo].[TableAssert].[ID]=[Expr1009],[tempdb].[dbo].[TableAssert].[Gender]=[Expr1010],[tempdb].[dbo].[TableAssert].[ID_Genders]=NULL))|--Top(TOPEXPRESSION:((1)))5.|--ComputeScalar(DEFINE:([Expr1009]=[Expr1012],[Expr1010]='F'))|--NestedLoops(LeftOuterJoin)|--ConstantScan|--Assert(WHERE:(CASEWHEN[Expr1011]>(1)THEN(0)ELSENULLEND))9.|--StreamAggregate(DEFINE:([Expr1011]=Count(*),[Expr1012]=ANY([tempdb].[dbo].[TableAssert].[ID])))10.|--TableScan(OBJECT:([tempdb].[dbo].[TableAssert]))注意最內(nèi)層的Assert：StmrtTeMt其serf麗頑可面而I礪芯rnputE互ml前□口匸石[編「1帀亍=^5匸皿口譎嘉111前乩[品0]斤￡匚羸壷訂后&^斯5匸工耐禎喬不]「[晶了.甬區(qū)品羸[1-TableInsert00BJECT:\&empdb].[dbo].[TableAssertj,SET:;'&empdb].[dbo],[FableAssert].[ID]=[ExpriC[>&],mpdb].[dbc].[Tabl-TapfTDPEXPRE5SI0N:((1))>l-ComputeScalartDEFINLiIExprlDD&JMBcprlOig.[Expr1D1D]='H)-NestedLoopE(LeftOuterJoin)l-ConstantSen-Assert(WHERE:(]CASEWHEN|[Expr1O11]X1)THEN(0)ELSENULLENQ))-StreamAggr^ate(DEFINE:{[Expr1O11]=Cbuntn[Expr1012J=AN￥{&ernpdb].[dbo].[TableAssert],[ID^J-TableSean(0EkJECT:ffempdb].[dbo],|TableAssertJJ)可以看到SQLServer創(chuàng)建一個StreamAggregate（流匯聚，可從預(yù)估執(zhí)行計劃中看到其解釋，后續(xù)會專門介紹）去計算子查詢會返回多少數(shù)據(jù)，然后把這個值傳遞給斷言用于檢測。作為已經(jīng)商業(yè)化二十幾年的產(chǎn)品，其核心（查詢優(yōu)化器）已經(jīng)經(jīng)過了很多年的積累和改進(jìn)，高版本的SQLServer（如2008R2及以上版本，這個沒有絕對標(biāo)準(zhǔn)），會對語句和表結(jié)構(gòu)的當(dāng)前情況來判斷是否需要使用“斷言，Assert”操作符。比如：INSERTINTOTableAssert（ID,Gender）VALUES（（SELECTIDFROMTableAssertWHEREID1),'F')INSERTINTOTableAssert(ID,Gender)VALUES((SELECTTOP1IDFROM'F')TableAssert'F')先不執(zhí)行，開啟估計執(zhí)行計劃再看圖形化界面，可以看到如下結(jié)果:因為優(yōu)化器檢測到第二個語句里面包含了TOP1,僅返回一行數(shù)據(jù)，所以沒有必要引入斷言來檢測。UNIONUNIONALL1.1.1.6總結(jié)：到這里為止，對這個操作符的介紹已經(jīng)完畢，下一篇會介紹串聯(lián)操作符。對于這個斷言操作符，我們需要知道它是用來“驗證”某些條件，但是每個操作符的引入都必將帶來一定的開銷，可是這些操作符的引入又是必須的，因為需要它們完成一些任務(wù)。如果需要改進(jìn)，不妨先看看它是用來檢驗什么，比如上面提到的子查詢，可以通過使用TOP1、添加唯一約束等方式來減少這種校驗。但是所有改進(jìn)都應(yīng)該做充分的測試和論證。1.1.2執(zhí)行計劃操作符詳解——串聯(lián)(Concatenation)1.1.2.1前言：本文開始講述另外一個操作符串聯(lián)(Concatenation)，讀者可以根據(jù)這個詞(中英文均可)先幻想一下是干嘛的。其實還是挺直觀，就是把東西連起來，那么下面我們來看看到底連什么？怎么連？什么時候連？1.1.2.2簡介：串聯(lián)操作符既是物理操作符，也是邏輯操作符，在中文版SQLServer“Concatenation”。其圖標(biāo)為:的圖形化執(zhí)行計劃中稱為“串聯(lián)”，在其他格式及英文版本中稱為“Concatenation”。其圖標(biāo)為:，它掃描多個輸入并返回每個掃描的行。通常用于實現(xiàn)T-SQL中的UNIONALL。它可以有多個輸入，但只有一個輸出，就如多個集合UNIONALL一樣，最終返回一個結(jié)果集，注意這里一直使用“集合/集”，關(guān)系數(shù)據(jù)庫是基于集合論的，所以使用關(guān)系數(shù)據(jù)庫時要以集合的思維去考慮問題。在執(zhí)行計劃中的每個操作符，都要實現(xiàn)三個方法/函數(shù)：Init()、GetNext()和Close()。前面說了，串聯(lián)操作符是其中一種可以接受多個輸入的操作符，這些輸入會在Init()方法中處理。在Init()方法中，串聯(lián)初始化然后建立所需的數(shù)據(jù)結(jié)構(gòu)。然后在運行GetNext()方法讀取輸入集中的第一行及后續(xù)行，直到把輸入集合里面的所有數(shù)據(jù)讀取完畢為止。1.1.2.3環(huán)境搭建：下面創(chuàng)建一個測試表并循環(huán)插入10000行數(shù)據(jù)。USEtempdbGOIFOBJECT_ID('TEST','U')ISNOTNULLDROPTABLETESTGOCREATETABLETest(IDINTIdentity(1,1)PRIMARYKEY,NomeVARCHAR(250)DEFAULTNewID())GOSETNOCOUNTONGOINSERTINTOTestDEFAULTVALUESGO100001.1.2.4串聯(lián)演示：前面提到，串聯(lián)主要用于實現(xiàn)T-SQL的UNIONALL，那么現(xiàn)在就來看看UNIONALL的情況：開啟實際執(zhí)行計劃并運行下面語句：SELECT*FROMTESTUNIONALLSELECT*FROMTESTUNIONALLSELECT*FROMTEST

SELECT*FROMTEST執(zhí)行計劃如下:SELECT"SELECT"FROMTESTUNIONUNION匚二匸3!4|SELEC2-FROMUNION3!4|SELEC2-FROMUNIONALLSELECTxFROM2ES2ZE5Z\<vX-X-\-X-X-X-UNIONUNION盤匸匸SELECT"SELECT"FROMTESTSELECT*FROM=Zii￡iT可MlOHJUT」SESELECT*FROM=Zii￡iT可MlOHJUT」SELEGT*FROMTESTTJNIOtTALLSELECT*FRDHTEST秦集奈引押錨栄集索引掃插:Clustered).[PKTess3214：—卄￥肝冨召Z1結(jié)果消息6*°執(zhí)行計劃魚詞I：(^1^批有天旳｝聲円片珀：叢隼索引掃插.[PKT^s-—開￥肛轉(zhuǎn)韋如果使用SETSHOWPLAN_TEXTON來查看的話可以看到如下結(jié)果:

QGOFROMTESTJ2-ICC!SELECT*FROMTE5T一一W^EREIDBETWEEN101JCID10-3-3JNIONFHOMTEST一一W^iEBEID3ETWEE?10017CD^00UNICMjtrntTad-SELECT*FROMTEST■vVvvvVvvJ結(jié)果消息-Concstenation1-OustBfedIndexScan：GBJEJCT:lft&mE-db.dbo.[Test.PKTest3214EC27KBAES77l-QustefedIndexScan：OBJEJCT:lftempdb.QGOFROMTESTJ2-ICC!SELECT*FROMTE5T一一W^EREIDBETWEEN101JCID10-3-3JNIONFHOMTEST一一W^iEBEID3ETWEE?10017CD^00UNICMjtrntTad-SELECT*FROMTEST■vVvvvVvvJ結(jié)果消息-Concstenation這個圖的含義是把4個“ClusteredIndexSea的結(jié)果塞到一個結(jié)果集，然后調(diào)用lnit()和GetNext()方法去遍歷這些數(shù)據(jù)，然后輸出。另外需要說明的是這個操作符是根據(jù)T-SQL中結(jié)果集的出現(xiàn)順序來處理的，為了證明這個想法，我們來改寫一下語句：SETSHOWPLAN_TEXTONGOSELECT*FROMTESTWHEREID<100UNIONALLSELECT*FROMTESTWHEREIDBETWEEN101AND1000UNIONALLSELECT*FROMTESTWHEREIDBETWEEN1001AND5000SELECT*FROMTESTWHEREID>5001然后看看輸出:對比一下參數(shù)可得每個ClusteredIndexSeek的順序和語句的出現(xiàn)順序是一致的。另外讀者可能留意到每行最后的ORDEREDFORWARD，其含義是掃描索引的順序是按照聚集索引的順序并向前掃描。1.1.2.5總結(jié)：本文主要演示了串聯(lián)操作符的情況，并且主要以T-SQL中的UNIONALL來觸發(fā)。由于目前沒有任何資料顯示是否僅UNIONALL才會使用，所以這里也不做絕對的判斷，讀者只需要知道這個操作符的含義、常見情景即可。另外讀者可以使用UNION來檢查執(zhí)行計劃，實際上UNION是不用串聯(lián)的，因為它本質(zhì)上需要去重，所以使用不同的操作符來實現(xiàn)，比如MergeJoin，在后續(xù)再介紹。1.1.3執(zhí)行計劃操作符詳解——計算標(biāo)量(ComputeScalar)1.1.3.1前言：第三個常見的操作符計算標(biāo)量(ComputeScalar)。這個操作符的名字比較直觀進(jìn)行一個標(biāo)量計算并返回計算值。官方說明：ComputeScalar運算符通過對表達(dá)式求值來生成計算標(biāo)量值。該值可以返回給用戶、在查詢中的其他位置引用或二者皆可。例如，在篩選謂詞或聯(lián)接謂詞中就會出現(xiàn)二者皆可的情況。

該操作符的圖標(biāo)為：，它既是一個邏輯操作符，也是一個物理操作符。這個操作符可能不容易引起用戶注意，因為一般我們看執(zhí)行計劃是因為語句有問題，而有問題的語句又通常是比較復(fù)雜或混亂的，這些語句生成的執(zhí)行計劃往往也非常復(fù)雜。相對于整個執(zhí)行計劃來說，這個操作符通常是比較小開銷的。但是這個操作符之所以重要或常見，是因為它通常是由于游標(biāo)處理或其他一些大范圍查找引起的，這些操作可能在CPU存在壓力時變得雪上加霜。演示：使用TempDB做測試是一個不錯的選擇，簡單重啟一下SQL服務(wù)即可清空過去的操作，不過如果你發(fā)現(xiàn)重啟后還在，那不妨檢查一下是否建到Model數(shù)據(jù)庫或者設(shè)置為啟動時運行。下面代碼在TempDB中創(chuàng)建一個表，插入10000行數(shù)據(jù)后，循環(huán)100次進(jìn)行數(shù)據(jù)檢查：USEtempdbGOCREATETABLEtest(IDIntIdentity(1,1)PRIMARYKEY,NameVarChar(250)DEFAULTNewID())GOSETNOCOUNTONGOINSERTINTOtestDEFAULTVALUESGO10000--循環(huán)插入行數(shù)據(jù)GO一下面代碼循環(huán)100次，判斷是否存在某個IDDECLARE@IIntSET@I=0WHILE@I<100

BEGINIFEXISTS(SELECTIDFROMtestWHEREID=@I)BEGINPRINT'存在這個ID'ENDSET@I=@I+1;END看一下圖形化執(zhí)行計劃:5ET@1=0EWHILE@1<100-BEGINIFEXISTS(SELECTIDFROMtest曲EREID=@1-BEGINPRINT'存在這個ID'EITOSET@1=01+1END100%査詞1：批閆天旳｝章詞卄旃：僥IFEXISTS(SELECTIDFROMteatWHEREID=@1]查詞2:（與該批胃黃旳｝背詞卄銷：僥IFEXISTS（SELECTIDFROM查詞2:（與該批胃黃旳｝背詞卄銷：僥IFEXISTS（SELECTIDFROM七已三七WHEREID=@1）COHDWITHQUERY幵謂：0%CLeftCOITDWITHCUETiY幵諂：0*卄店二0烏截圖中紅框部分表明使用了計算標(biāo)量操作符，使用前面的方法，檢查文本化執(zhí)行計劃：SETSHOWPLAN_TEXTONGODECLARE@IIntSET@I=0WHILE@I<100BEGINIFEXISTS(SELECTIDFROMtestWHEREID=@I)BEGINPRINT'存在這個ID'ENDSET@I=@I+1;ENDStmtTent：SETSHDWPLANTESTON：七Text■DECLARE(SI工ntSET=0WHILE?E<100BEGINStmtTeKt—ComputeScalar(DEFINE:([EorW02]=CA5EWHEW[Expi-1003]THEff(1)ELSE(0)END))—Nested.Loops(LeftSemiToiibDEFI1TE:—CluEtereiInier(DEJECT：([tempdb].[dto],[FKt羽t3214EC27E8827737]FRIHT存在這個口)SET=@1+1:可以看到執(zhí)行計劃使用計算標(biāo)量操作符來檢查嵌套循環(huán)(NestedLoop)是否返回了值，也就是說用于實現(xiàn)IFEXISTS操作。EventCl—下龍巴7汩環(huán)』OiJ三審至三孩壬密.一.如果使用Profiler來抓取信息，記住一下CPU開銷:--下蘭吧貯潔出.豈計曼召嚴(yán)三梵.一.F面改寫一下語句來避免這個操作符:DECLARE@IInt,@VarIntSET@I=0WHILE@I<100BEGINSELECT@Var=IDFROMtestWHEREID=@IIF@@ROWCOUNT>0BEGINPRINT'存在這個ID'ENDSET@I=@I+1;ENDGO及Profiler信息:EventCL乞T謎tDmtakpplxantigiRiiritITTUsbNain*L;ginlTtel^s-StirtSQL:E旦tchStartins-DECLARE-3：Znt.數(shù)且匸:ntSETSZ-...MlEJDioft5<J...i2QL;￡at=hCc=U3-letdKECL犧E9：:nt.凱呂匸:ntSBP迂-...Hi.m..如果再檢查文本化執(zhí)行計劃就可以看到只有一個操作符:

|--ClusteredIndexSeek(OBJECT:([tempdb].[dbo].[test].[PKtest3214EC27D8827737]),SEEK:([tempdb].[dbo].[test].[ID]=[@I])ORDEREDFORWARD)對比Profiler中的數(shù)據(jù)，沒有使用計算標(biāo)量的執(zhí)行計劃消耗更少的CPU和運行時間去完成結(jié)果，這里主要是演示計算標(biāo)量，所以不對寫法做更深入的研究。但是從寫法上看，使用了@@rowcount函數(shù)替代IFEXISTS，有時候會有一定的幫助，當(dāng)然，并不是絕對的。如果你覺得是數(shù)據(jù)量的原因，不妨再看看下面的腳本：DECLARE@TabTABLE(IDSmallIntPRIMARYKEY)SELECT'A'+'-'+'B'SELECT'A'+'-'+'B'FROM@TabSELECT然后看看圖形化執(zhí)行計劃:晉詞1;（匸諛批有天旳｝彎詞卄鈉；10*5ELE匚T?AT4T-T+'B1FROMUTabCamputeScaLar開諸：a%CluatiEEe-dIndeKScsr[?Tab].[PK_#?A7DE52_3214EC27-3194E_.—卄蓿：%和文本化執(zhí)行計劃：|--ComputeScalar(DEFINE:([Expr1002]='A-B'))|--ClusteredIndexScan(OBJECT:(@Tab))這個語句只是簡單地進(jìn)行字符串拼接，但是也使用了計算標(biāo)量運算符,原因可以查看執(zhí)行計劃的解釋:EDECLJiEE?TabTABLE(IDSnalllntPRIlrtARYKEYSELECT'A：+'-’+FROMOTab100%?二結(jié)果J消息r撫行計劃查詞1：（與該批有關(guān)時｝背詞卄梢：丄。拄5E1ECT'AT+T-r4?FROM&Tab物理運算ComputeScalarLogicalOp&rationCompirteScalar怙計甜RowEstimatedOperatorCostO.GOCCOOI(0%)EstimatedI/OCo-st0EstimatedCPUCost0.CCCCCO1EstimatedGubtre-eCost0.003263^EstimatedNumberofEyecutions1EstimatedNumberofRows1EstimatedRowSin托字節(jié)節(jié)點ID0OutputLirt鼻護(hù)*口曲比廿仔I莊Ewprl002［心廠川1.1.3.3總結(jié)：正如一直以來的解釋，每個操作符的出現(xiàn)都有其原因和作用，并不能簡單地下定論這個操作符是好還是壞，但是某些操作符確實意味著性能問題，所以如果精力允許，也應(yīng)該對常見的操作符進(jìn)行一定程度的研究。當(dāng)發(fā)現(xiàn)某個低效查詢中出現(xiàn)這個操作符時，不妨想想其原因，并嘗試是否能進(jìn)行優(yōu)化，優(yōu)化的原則則是根據(jù)其含義而定，既然這個操作符是根據(jù)現(xiàn)有值計算新值，那么我們的核心方案應(yīng)該是減少這種操作的數(shù)據(jù)量或者預(yù)先計算新值。總的而言，具體情況具體分析。根據(jù)執(zhí)行計劃優(yōu)化性能——理解哈希、合并、嵌套循環(huán)連接策略1.1.4.1前言：對于性能優(yōu)化，需要集中處理以下的問題：1、為你的環(huán)境創(chuàng)建性能基線。2、監(jiān)控現(xiàn)在的性能并發(fā)現(xiàn)瓶頸。3、解決瓶頸以便得到更好的性能。一個預(yù)估執(zhí)行計劃是描述查詢將會如何執(zhí)行的一個藍(lán)圖，而一個實際執(zhí)行計劃就是一個查詢執(zhí)行時實際發(fā)生的鏡像。通過對比兩個執(zhí)行計劃，可以發(fā)現(xiàn)查詢是否真的按照預(yù)估執(zhí)行計劃來執(zhí)行。在執(zhí)行計劃中，有一些非常重要的操作符需要清楚：1、Join策略：SQLServer有3種策略一一哈希、合并、嵌套循環(huán)。每種策略都有其優(yōu)缺點，本章將講述這部分。2、掃描和查找是SQLServer用于讀取數(shù)據(jù)的兩種方式，這兩種方式在性能優(yōu)化中是核心概念。將會在下一篇中講述。3、鍵查找有時候會成為主要的性能問題。因為存儲引起必須從非聚集索引中跳到聚集索引，一邊找到非聚集索引中的非鍵值列的值。這樣的行為通常很耗時間。1.1.4.2理解哈希、合并、嵌套循環(huán)連接策略SQLServer提供了3中JOIN的策略，它們沒有絕對的好和壞之分。1、哈希（HashJoin）：SQLServer選擇哈希關(guān)聯(lián)作為物理操作符，一邊對于大容量數(shù)據(jù)，且未排序或者沒有索引時進(jìn)行查詢。兩個進(jìn)程關(guān)聯(lián)起來進(jìn)行哈希關(guān)聯(lián)，它們?yōu)椤窘ⅰ亢汀咎綔y】，在【建立】進(jìn)程中，會從建立輸入（即join的左表中，但是可能這個左表會在優(yōu)化過程中交換位置，使得不一定就是實際上的左表。）讀取所有行，然后在內(nèi)存中創(chuàng)建一個符合關(guān)聯(lián)條件的哈希表。在【探測】進(jìn)程中，會從探測表（輸入的右表）中讀取所有

2、的行，并根據(jù)關(guān)聯(lián)條件，與之前創(chuàng)建的內(nèi)存哈希表匹配。2、合并(MergeJoin)：如果關(guān)聯(lián)表中已經(jīng)排序，SQLServer會選擇合并關(guān)聯(lián)。合并關(guān)聯(lián)要求關(guān)聯(lián)條件中最少有一個是已經(jīng)被排序了的。如果數(shù)據(jù)量不大的時候，這比哈希關(guān)聯(lián)更加有效，它并不是重負(fù)荷關(guān)聯(lián)的方式。3、嵌套循環(huán)(NestedLoop)：在最少兩個結(jié)果集中，使用嵌套循環(huán)會比較有效，這兩個結(jié)果集中，作為外部表的集合要小，而內(nèi)部循環(huán)結(jié)果集具有有效的索引。這種方式不適用于大結(jié)果集。1.1.4.3準(zhǔn)備工作：下面將創(chuàng)建兩個表，然后看看各種關(guān)聯(lián)方式的執(zhí)行計劃：USEAdventureWorksGOIFOBJECT_ID('SalesOrdHeaderDemo')ISNOTNULLBEGINDROPTABLESalesOrdHeaderDemoENDGOIFOBJECT_ID('SalesOrdDetailDemo')ISNOTNULLBEGINDROPTABLESalesOrdDetailDemoENDGOSELECT*INTOSalesOrdHeaderDemoFROMSales.SalesOrderHeaderGOSELECT*INTOSalesOrdDetailDemoFROMSales.SalesOrderDetailGO1.1.4.4步驟：1、執(zhí)行一下查詢，并開啟執(zhí)行計劃(Ctrl+M)：SELECTsh.*FROMSalesOrdDetailDemoASsdINNERJOINSalesOrdHeaderDemoASshONsh.salesorderID=sd.salesorderid

GO2、然后從執(zhí)行計劃截圖中可以看到使用了哈希連接:站結(jié)果I由[贏'執(zhí)行計劃查詢L：（與該批有并的）查詢卄銷：SSIZICT日h?*ITOMOrdDetaLLDeir.oZimiRJ0：WJaLesO^dHeade^Deir.oASstSELECTIGO2、然后從執(zhí)行計劃截圖中可以看到使用了哈希連接:站結(jié)果I由[贏'執(zhí)行計劃查詢L：（與該批有并的）查詢卄銷：SSIZICT日h?*ITOMOrdDetaLLDeir.oZimiRJ0：WJaLesO^dHeade^Deir.oASstSELECTI：InjnerTainJ開誚…*S1克掃描[Sm1ee3rdH■&dd&rD?no][sh]幵銷：2咚表掃描[Sa.1ssOrdZstai-lD-BBia]開消：1€*3、現(xiàn)在先創(chuàng)建唯一的聚集索引在兩個表中:CREATEUNIQUECLUSTEREDINDEXidx_salesorderheaderdemo_SalesOrderIDONsalesordheaderdemo（SalesOrderID）GOCREATEUNIQUECLUSTEREDINDEXidx_SalesDetail_SalesOrderIDONSalesOrdDetailDemo（SalesOrderID,SalesOrderDetailID）GO4、再次執(zhí)行步驟1的語句：5、截圖是第二次執(zhí)行的執(zhí)行計劃，可以發(fā)現(xiàn)變成了合并連接，并且表掃描變成了聚集索引掃描：馬"結(jié)果區(qū)消息霽執(zhí)行計劃查詞二（與該批育關(guān)的）査詢卄銷：二憑SZ1ZCTah.*ETICMSalesdrdDetaLL2enoASZimznTOZM已mClHdH已md已二DenciASs臺并聯(lián)接IimErToini

祁I(lǐng)S和黑隼索引掃描（Clustered：[SalesariHeaderDemo].[ic±K_salesorde.?開梢：出烏■6、現(xiàn)在來看看嵌套循環(huán)關(guān)聯(lián),的結(jié)果集：聚集索引掃插（Clustered：

[SalEsOrdZEt^ilDEiro].[idii_SaleeZ：et

幵誚：5￡雖-在上面的查詢中添加where條件來限定查詢SELECTsh.*FROMSalesOrdDetailDemosd.salesorderid43659WHEREsh.salesorderid43659GO7、從執(zhí)行結(jié)果中看到現(xiàn)在關(guān)聯(lián)變成了嵌套循環(huán):I啤噩集倉引夏拚上知曬吹“[Sales^xaiBeI啤噩集倉引夏拚上知曬吹“[Sales^xaiBe：a.-Je：fZs□□].[i4三縣丄2口匸3e_幵銷：50+I奇寰篥篥引童拔1皿攻旺■町a(chǎn)le=Criine：tai1Zero].[±dxSiaiL已上匚上匕—幵書：&pt■:船■■出行計炯晉詢i:瀉該!ih.*E^=3NSsLcsCrdE-七&二二ZiunvzASsdINKERJTISS*L=aOrdHeadej:3=eldASnhGEah.aaIcaardcxIL1.1.4.5分析：前面已經(jīng)提到，哈希關(guān)聯(lián)工作在大數(shù)據(jù)量且關(guān)聯(lián)字段沒有排序的關(guān)聯(lián)中。所以在步驟1中，由于沒有索引或者預(yù)先排序，數(shù)據(jù)的關(guān)聯(lián)會使用哈希關(guān)聯(lián)。在步驟3中，創(chuàng)建了一個唯一的聚集索引，所以表已經(jīng)通過聚集索引排序了，此時優(yōu)化器會選擇合并關(guān)聯(lián)。在步驟6中，由于使用了where條件限制數(shù)據(jù)集的大小，同時由于已經(jīng)排序，所以使用了嵌套循環(huán)關(guān)聯(lián)。每一種關(guān)聯(lián)方法都有其優(yōu)缺點，視乎如何優(yōu)化而已。有時候哈希關(guān)聯(lián)有其非常重要的作用，但是如果可以，強烈建議每個表都應(yīng)該有一個唯一的聚集索引，一邊使用合并關(guān)聯(lián)，如果不可以，千萬別嘗試使用OPTION提示符來把關(guān)聯(lián)改成合并或者嵌套循環(huán)，這可能會降低性能。而嵌套循環(huán)僅在小結(jié)果集的時候運行的最好。根據(jù)執(zhí)行計劃優(yōu)化性能——查找表/索引掃描1.1.5.1前言：在絕大部分情況下，特別是從一個大表中返回少量數(shù)據(jù)時，表掃描或者索引掃描并不是一種高效的方式。這些必須找出來并解決它們從而提高性能，因為掃描將遍歷每一行，查找符合條件的數(shù)據(jù)，然后返回結(jié)果。這種處理是相當(dāng)耗時耗資源的。在性能優(yōu)化過程中，一般集中于：1、CPU2、Network3、磁盤IO而掃描操作會增加這三種資源的開銷。1.1.5.2準(zhǔn)備工作：下面將創(chuàng)建兩個表來查看不同的物理關(guān)聯(lián)操作的不同影響。創(chuàng)建腳本已經(jīng)在本系列的第一篇中給出，這里不再顯示。1.1.5.3步驟：1、打開執(zhí)行計劃并運行下面查詢：SELECTsh.*FROMSalesOrdDetailDemoASsdINNERJOINSalesOrdHeaderDemoASshONsh.salesorderID=sd.salesorderidWHEREsh.orderdate='2004-07-3100:00:00.000'GOCREATEUNIQUECLUSTEREDINDEXidx_salesorderheaderdemo_SalesOrderIDONsalesordheaderdemo(SalesOrderID)GO3、再次運行步驟1中的查詢，看看執(zhí)行計劃是否已經(jīng)移除了表掃描：區(qū)結(jié)果Q消息r執(zhí)行計劃查詞1：〔勻該批有關(guān)的)查詞卄箱：曲sh.*FROMS曰二已已匕工dDwt■日二咋口A3adLNNjIRJCZN5alesGrdHeaderJemoASahSN蹴少索引(諾響忑5?彩:環(huán)EATZ；NGH~4U3=mREE：二卜TDE蠱工觀nil亡"M：二曰曰二門厘二nd巳叭日丫曰n已m亡[InnerJbin>開銷：出1乘隼索引掃描CClusttr^J[InnerJbin>開銷：出1乘隼索引掃描CClusttr^JslesOrdHeadierDee_o]-[idj(_galesotie...

開銷：茹先~L表掃描[Sa.1esOrd_DetaiLDeh.o][sd]

開脊so*4、上圖中可以看到創(chuàng)建了聚集索引的表已經(jīng)變成了聚集索引掃描，但是未創(chuàng)建的就還是表掃描，觀察聚集索引掃描，它只是把表掃描換成了聚集索引掃描，所以沒有很大的性能提升。5、現(xiàn)在繼續(xù)把第二個表的表掃描去掉，通過創(chuàng)建在這個表上的唯一聚集索引：CREATEUNIQUECLUSTEREDINDEXidx_SalesDetail_SalesOrderIDONSalesOrdDetailDemo(SalesOrderID,SalesOrderDetailID)GOGO1.1.5.4分析：在深入討論之前，首選需要澄清的是，掃描并不總是壞的，而查找并不總是好的，但是在絕大部分情況下，特別是在大表中返回少量數(shù)據(jù)時，查找會有更好的性能表現(xiàn)。同樣，并不總是有方法在每個查詢中移除掃描操作。如果查詢的性能問題是因為掃描，那么移除掃描操作會更好，否則，看看是否有什么改變方式去提咼性能。在第一步中，因為兩表均沒有索引，所以優(yōu)化器只能選擇掃描來查找數(shù)據(jù)。在第三步中，已經(jīng)創(chuàng)建了一個聚集索引在SalesOrdHeaderDemo表上，表掃描變成了聚集索引掃描，聚集索引查找是我們希望得到的結(jié)果，但是因為我們沒有什么謂詞在第一個表上，所以只能掃描整個聚集索引來代替掃描整個表。在第六步中，在第二個表也創(chuàng)建了聚集索引，且有一個謂詞在這個表上，所以出現(xiàn)了聚集索引查找，而不是聚集索引掃描。根據(jù)執(zhí)行計劃優(yōu)化性能——鍵值查找1.1.6.1前言：本文為本系列最后一篇，介紹鍵值查找的相關(guān)知識。鍵值查找是具有聚集索引的表上的一個書簽查找，鍵值查找用于SQLServer查詢一些非鍵值列的數(shù)據(jù)。使用非聚集索引的查詢不會有鍵值查找，但是所有鍵值查找會伴隨非聚集索引出現(xiàn)。這里特別提醒的是鍵值查找總是伴有嵌套循環(huán)關(guān)聯(lián)。1.1.6.2準(zhǔn)備工作：下面將創(chuàng)建一個表，通過執(zhí)行計劃看看鍵值查找的不同效果。為了產(chǎn)生鍵值查找，需要兩件事情：1、聚集索引2、非聚集索引當(dāng)你在非聚集索引鍵值上有謂詞時，查詢的字段又不全部包含在非聚集索引上，需要通過聚集索引去查找，此時會產(chǎn)生鍵值查找。執(zhí)行下面操作產(chǎn)生測試表：USEAdventureWorksGOIFOBJECT_ID('SalesOrdDetailDemo')ISNOTNULLBEGINDROPTABLESalesOrdDetailDemoEND

GOSELECT*INTOSalesOrdDetailDemoFROMGOSales.SalesOrderDetail1.1.6.3步驟：1、在測試表SalesOrdDetailDemo上創(chuàng)建一個聚集索引和一個非聚集索引:CREATEUNIQUECLUSTEREDINDEXidx_SalesDetail_SalesOrderIDONSalesOrdDetailDemo(SalesOrderID,SalesOrderDetailID)GOCREATENONCLUSTEREDINDEXidx_non_clust_SalesOrdDetailDemo_ModifiedDateONSalesOrdDetailDemo(ModifiedDate)GO2、執(zhí)行下面的查詢，并開啟實際執(zhí)行計劃:SELECTModifiedDateFROMSalesOrdDetailDemoWHEREModifiedDate='2004-07-3100:00:00.000'GO3、從執(zhí)行計劃的截圖中看到，使用了一個非聚集索引（執(zhí)行計劃中叫做索引）查找：|lJ滸眾|LiH；冃思6啊1」可翩查詢二：（與該批有關(guān)的）查詢卄銷：*二2匸二[Modifiedilate：：FROM:-iDetELilJerao：WHEEE[ModifiedDate：=@L索弓I查撈〔NfonCl口mtEred：[SslesQrdDetdlLDar.D]?[iclust...幵磔100t/r