SQL Server 執(zhí)行計劃的理解

　　要理解執(zhí)行計劃，怎么也得先理解，那各種各樣的名詞吧。鑒于自己還不是很了解。本文打算作為只寫懂的，不懂的懂了才寫。

　　在開頭要先說明，第一次看執(zhí)行計劃要注意，SQL Server的執(zhí)行計劃是從右向左看的。

　　名詞解析：

　　掃描：逐行遍歷數(shù)據(jù)。

　　先建立一張表，并給大家看看大概是什么樣子的。

　　CREATE?TABLE?Person(
????　　Id?int?IDENTITY(1,1)?NOT?NULL,
????　　Name?nvarchar(50)?NULL,
????　　Age?int?NULL,
????　　Height?int?NULL,
????　　Area?nvarchar(50)?NULL,
????　　MarryHistory?nvarchar(10)?NULL,
????　　EducationalBackground?nvarchar(10)?NULL,
????　　Address?nvarchar(50)?NULL,
????　　InSiteId?int?NULL
　　)?ON?[PRIMARY]

　　表中的數(shù)據(jù)14萬左右，大概類似下面這樣：

　　此表，暫時沒有任何索引。

一、數(shù)據(jù)訪問操作 　1、表掃描

　　表掃描:發(fā)生于堆表，并且沒有可用的索引可用時，會發(fā)生表掃描,表示整個表掃描一次。

　　現(xiàn)在，我們來對此表執(zhí)行一條簡單的查詢語句：

　　SELECT?*?From?Person?WHERE?Name?=?'公子'

　　查看執(zhí)行計劃如下：

　　表掃描，顧名思義就是整張表掃描，找到你所需要的數(shù)據(jù)了。

　2、聚集索引掃描

　　聚集索引掃描：發(fā)生于聚集表，也相當(dāng)于全表掃描操作，但在針對聚集列的條件如(WHERE Id > 10)等操作時，效率會較好。

　　下面我們在Id列來對此表加上一個聚集索引

　　CREATE?CLUSTERED?INDEX?IX_Id?ON?Person(Id)

　　再次執(zhí)行同樣的查詢語句：

　　SELECT?*?From?Person?WHERE?Name?=?'公子'

　　執(zhí)行計劃如下：

　　為什么建的聚集索引在Id列，會對掃描有影響呢？更何況與Name條件也沒關(guān)系??？

　　其實，你加了聚集索引之后，表就由堆表變成了聚集表。我們知道聚集表的數(shù)據(jù)存在于聚集索引的葉級節(jié)點。因此，聚集掃描與表掃描其實差別不大，要說差別大，也得看where條件里是什么，以后返回的數(shù)據(jù)。就本條SQL語句而言，效率差別并不大。

　　可以看看I/O統(tǒng)計信息：

　　表掃描：

　　聚集索引掃描：

　　此處超出本文范疇了，效率不在本文考慮范圍內(nèi)，本文只考慮的是，各種掃描的區(qū)別，以及為何會產(chǎn)生。

　3、聚集索引查找

　　聚集索引查找：掃描聚集索引中特定范圍的行。

　　看執(zhí)行以下SQL語句：

　　SELECT?*?FROM?Person?WHERE?Id?=?'73164'

　　執(zhí)行計劃如下:

　4、索引掃描

　　索引掃描：整體掃描非聚集索引。

　　下面我們來添加一個聚集索引，并執(zhí)行一條查詢語句：

　　CREATE?NONCLUSTERED?INDEX?IX_Name?ON?Person(Name)????--創(chuàng)建非聚集索引

　　SELECT?Name?FROM?Person

　　查看執(zhí)行計劃如下：

　　為什么此處會選擇索引掃描(非聚集索引)呢？

　　因為此非聚集索引能夠覆蓋所需要的數(shù)據(jù)。如果非聚集索引不能覆蓋呢?例如，我們將SELECT改為SELECT *再來看看。

?　　好明顯，返回結(jié)果所包括的記錄太多，用非聚集索引反而不合算。因此使用了聚集索引。

　　如果此時我們刪除聚集索引，再執(zhí)行SELECT *看看。

　　DROP?INDEX?Person.IX_Id

　　而此時沒有聚集索引，所以只有使用表掃描。

　5、書簽查找

　　前面關(guān)于索引的學(xué)習(xí)我們已經(jīng)知道，當(dāng)在非聚集索引中并非覆蓋和包含所需全部的列時，SQL Server會選擇，直接進(jìn)行聚集索引掃描獲得數(shù)據(jù)，還是先去非聚集索引找到聚集索引鍵，然后利用聚集索引找到數(shù)據(jù)。

　　下面來看一個書簽查找的示例：

　　SELECT?*?FROM?Person?WHERE?Name?=?'胖胖'　　--Name列有非聚集索引

　　執(zhí)行計劃如下：

　　上面的過程可以理解為：首先通過非聚集索引找到所求的行，但這個索引并不包含所有的列，因此還要額外去基本表中找到這些列，因此要進(jìn)行鍵查找，如果基本表是以堆進(jìn)行組織的，那么這個鍵查找(Key Lookup)就會變成RID查找(RID Lookup),鍵查找和RID查找統(tǒng)稱為書簽查找。不過有時當(dāng)非聚集索引返回的行數(shù)過多時，SQL Server可能會選擇直接進(jìn)行聚集索引掃描了。

二、流聚合操作 　1、流聚合

　　流聚合：在相應(yīng)排序的流中，計算多組行的匯總值。

　　所有的聚合函數(shù)(如COUNT(),MAX())都會有流聚合的出現(xiàn)，但是其不會消耗IO，只有消耗CPU。

?　　例如執(zhí)行以下語句：

　　SELECT?MAX(Age)?FROM?Person

　　查看執(zhí)行計劃如下：

　2、計算標(biāo)量

　　計算標(biāo)量：根據(jù)行中的現(xiàn)有值計算新值。比如COUNT()函數(shù)，多一行，行數(shù)就加1咯。

　　除MIN和MAX函數(shù)之外的聚合函數(shù)都要求流聚合操作后面跟一個計算標(biāo)量。

　　SELECT?COUNT(*)?FROM?Person

　　查看執(zhí)行計劃如下：

3、散列聚合(哈希匹配)

　　對于加了Group by的子句，因為需要數(shù)據(jù)按照group by 后面的列有序，就需要Sort來保證排序。注意,Sort操作是占用內(nèi)存的操作，當(dāng)內(nèi)存不足時還會去占用tempdb。SQL Server總是會在Sort操作和散列匹配中選擇成本最低的。

　　SELECT?Height,COUNT(Id)?FROM?Person????--查出各身高的認(rèn)輸
　　GROUP?BY?Height

　　執(zhí)行計劃如下：

　　對于數(shù)據(jù)量比較大時，SQL Server選擇的是哈希匹配。

　　在內(nèi)存中建立好散列表后，會按照group by后面的值作為鍵，然后依次處理集合中的每條數(shù)據(jù)，當(dāng)鍵在散列表中不存在時，向散列表添加條目，當(dāng)鍵已經(jīng)在散列表中存在時，按照規(guī)則（規(guī)則是聚合函數(shù)，比如Sum,avg什么的）計算散列表中的值（Value）。

　4、排序

?　　當(dāng)數(shù)據(jù)量比價少時，例如執(zhí)行以下語句，新建一個只有數(shù)十條記錄的與Person一樣的表。

　　SELECT?*?INTO?Person2?FROM?Person2
　　WHERE?Id?<?100

　　再來執(zhí)行同樣的查詢語句：

　　SELECT?Height,COUNT(Id)?FROM?Person2????--只是表換成了數(shù)據(jù)量比較少的表
　　GROUP?BY?Height

　　執(zhí)行計劃如下：

三、連接

　　當(dāng)多表連接時(包括書簽查找，索引之間的連接)，SQL Server會采用三類不同的連接方式：循環(huán)嵌套連接，合并連接，散列連接。這幾種連接格式有適合自己的場景，不存在哪個更好的說法。

?　　新建兩張表如下

?　　

?　　這是一個簡單的新聞，欄目結(jié)構(gòu)。

　1、嵌套循環(huán)

　　先來看一個簡單的Inner Join查詢語句

　　SELECT?*?FROM?Nx_Column?AS?C
　　INNER?JOIN?Nx_Article?AS?A
　　ON?A.ColumnId?=?C.ColumnId

　　執(zhí)行計劃如下：

　　循環(huán)嵌套連接的圖標(biāo)同樣十分形象，處在上面的外部輸入(Outer input)，這里也就是聚集索引掃描。和處在下面的內(nèi)部輸入(Inner Input),這里也就是聚集索引查找。外部輸入僅僅執(zhí)行一次，根據(jù)外部輸入滿足Join條件的每一行，對內(nèi)部輸入進(jìn)行查找。這里由于是7行，對于內(nèi)部輸入執(zhí)行7次。

?　　

　　根據(jù)嵌套循環(huán)的原理不難看出，由于外部輸入是掃描，內(nèi)部輸入是查找，當(dāng)兩個Join的表外部輸入結(jié)果集比較小，而內(nèi)部輸入所查找的表非常大時，查詢優(yōu)化器更傾向于選擇循環(huán)嵌套方式。

　2、合并連接

　　不同于循環(huán)嵌套的是，合并連接是從每個表僅僅執(zhí)行一次訪問。從這個原理來看，合并連接要比循環(huán)嵌套要快了不少。

　　從合并連接的原理不難想象，首先合并連接需要雙方有序.并且要求Join的條件為等于號。因為兩個輸入條件已經(jīng)有序，所以從每一個輸入集合中取一行進(jìn)行比較，相等的返回，不相等的舍棄，從這里也不難看出Merge join為什么只允許Join后面是等于號。從圖11的圖標(biāo)中我們可以看出這個原理。

　　SELECT?*?FROM?Nx_Column?AS?C
　　INNER?JOIN????Nx_Article?AS?A
　　ON?A.ColumnId?=?C.ColumnId
　　OPTION(MERGE?join)

　　執(zhí)行計劃如下：

　　如果輸入數(shù)據(jù)的雙方無序，則查詢分析器不會選擇合并連接，我們也可以通過索引提示強(qiáng)制使用合并連接，為了達(dá)到這一目的，執(zhí)行計劃必須加上一個排序步驟來實現(xiàn)有序。這也是上述SQL語句為什么要加OPTION(MERGE join)的原因。上述對Article表的ColumnId列進(jìn)行了排序。

　3、哈希連接

　　散列連接同樣僅僅只需要只訪問1次雙方的數(shù)據(jù)。散列連接通過在內(nèi)存中建立散列表實現(xiàn)。這比較消耗內(nèi)存，如果內(nèi)存不足還會占用tempdb。但并不像合并連接那樣需要雙方有序。

　　要進(jìn)行下面這兩個實現(xiàn)，得把兩個列的聚集索引不要建在ColumnId列，否則不會采用哈希連接。

　　ALTER?TABLE?PK_Nx_Column?DROP?CONSTRAINT?PK_Nx_Column????--刪除主鍵
　　DROP?INDEX?Nx_Column.PK_Nx_Column????--刪除聚集索引
　　CREATE?CLUSTERED?INDEX?IX_ColumnName?ON?Nx_Column(ColumnName)????--創(chuàng)建聚集索引
　　--這里再設(shè)置回主鍵就可以了，有了聚集索引，就不能隨主鍵默認(rèn)建啦

　　還要刪除另外一個表Article的聚集索引哦。

　　然后執(zhí)行以下查詢：

　　SELECT?*?FROM?Nx_Column?AS?C
　　INNER?JOIN????Nx_Article?AS?A
　　ON?A.ColumnId?=?C.ColumnId

　　執(zhí)行計劃如下：

? ? 要刪除掉聚集索引，否則兩個有序輸入SQL Server會選擇代價更低的合并連接。SQL Server利用兩個上面的輸入生成哈希表，下面的輸入來探測，可以在屬性窗口看到這些信息，如圖15所示。

??? 通常來說，所求數(shù)據(jù)在其中一方或雙方?jīng)]有排序的條件達(dá)成時，會選用哈希匹配。

四、并行

　　當(dāng)多個表連接時，SQL Server還允許在多CPU或多核的情況下允許查詢并行，這樣無疑提高了效率。