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

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

　　名詞解析：

　　掃描：逐行遍歷數(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萬(wàn)左右，大概類似下面這樣：

　　此表，暫時(shí)沒(méi)有任何索引。

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

　　表掃描:發(fā)生于堆表，并且沒(méi)有可用的索引可用時(shí)，會(huì)發(fā)生表掃描,表示整個(gè)表掃描一次。

　　現(xiàn)在，我們來(lái)對(duì)此表執(zhí)行一條簡(jiǎn)單的查詢語(yǔ)句：

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

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

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

　2、聚集索引掃描

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

　　下面我們?cè)贗d列來(lái)對(duì)此表加上一個(gè)聚集索引

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

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

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

　　執(zhí)行計(jì)劃如下：

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

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

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

　　表掃描：

　　聚集索引掃描：

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

　3、聚集索引查找

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

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

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

　　執(zhí)行計(jì)劃如下:

　4、索引掃描

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

　　下面我們來(lái)添加一個(gè)聚集索引，并執(zhí)行一條查詢語(yǔ)句：

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

　　SELECT?Name?FROM?Person

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

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

　　因?yàn)榇朔蔷奂饕軌蚋采w所需要的數(shù)據(jù)。如果非聚集索引不能覆蓋呢?例如，我們將SELECT改為SELECT *再來(lái)看看。

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

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

　　DROP?INDEX?Person.IX_Id

　　而此時(shí)沒(méi)有聚集索引，所以只有使用表掃描。

　5、書(shū)簽查找

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

　　下面來(lái)看一個(gè)書(shū)簽查找的示例：

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

　　執(zhí)行計(jì)劃如下：

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

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

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

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

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

　　SELECT?MAX(Age)?FROM?Person

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

　2、計(jì)算標(biāo)量

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

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

　　SELECT?COUNT(*)?FROM?Person

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

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

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

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

　　執(zhí)行計(jì)劃如下：

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

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

　4、排序

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

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

　　再來(lái)執(zhí)行同樣的查詢語(yǔ)句：

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

　　執(zhí)行計(jì)劃如下：

三、連接

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

?　　新建兩張表如下

?　　

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

　1、嵌套循環(huán)

　　先來(lái)看一個(gè)簡(jiǎn)單的Inner Join查詢語(yǔ)句

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

　　執(zhí)行計(jì)劃如下：

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

?　　

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

　2、合并連接

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

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

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

　　執(zhí)行計(jì)劃如下：

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

　3、哈希連接

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

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

　　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)建啦

　　還要?jiǎng)h除另外一個(gè)表Article的聚集索引哦。

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

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

　　執(zhí)行計(jì)劃如下：

? ? 要?jiǎng)h除掉聚集索引，否則兩個(gè)有序輸入SQL Server會(huì)選擇代價(jià)更低的合并連接。SQL Server利用兩個(gè)上面的輸入生成哈希表，下面的輸入來(lái)探測(cè)，可以在屬性窗口看到這些信息，如圖15所示。

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

四、并行

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