SQL 執行計劃的了解

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

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

　　名詞解析：

　　掃描：逐行周遊資料。

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

　　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]      

　　表中的資料14萬左右，大概類似下面這樣：

　　

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

一、資料通路操作

　1、表掃描

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

　　現在，我們來對此表執行一條簡單的查詢語句：

　　SELECT * From Person WHERE Name = '公子'      

　　檢視執行計劃如下：

　　

　　表掃描，顧名思義就是整張表掃描，找到你所需要的資料了。

　2、聚集索引掃描

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

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

　　CREATE CLUSTERED INDEX IX_Id ON Person(Id)      

　　再次執行同樣的查詢語句：

　　SELECT * From Person WHERE Name = '公子'      

　　執行計劃如下：

　　

　　為什麼建的聚集索引在Id列，會對掃描有影響呢？更何況與Name條件也沒關系啊？

　　其實，你加了聚集索引之後，表就由堆表變成了聚集表。我們知道聚集表的資料存在于聚集索引的葉級節點。是以，聚集掃描與表掃描其實差别不大，要說差别大，也得看where條件裡是什麼，以後傳回的資料。就本條SQL語句而言，效率差别并不大。

　　可以看看I/O統計資訊：

　　表掃描：

　　

　　聚集索引掃描：

　　

　　此處超出本文範疇了，效率不在本文考慮範圍内，本文隻考慮的是，各種掃描的差別，以及為何會産生。

　3、聚集索引查找

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

　　看執行以下SQL語句：

　　SELECT * FROM Person WHERE Id = '73164'      

　　執行計劃如下:

　　

　4、索引掃描

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

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

　　CREATE NONCLUSTERED INDEX IX_Name ON Person(Name)    --建立非聚集索引

　　SELECT Name FROM Person      

　　檢視執行計劃如下：

　　

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

　　因為此非聚集索引能夠覆寫所需要的資料。如果非聚集索引不能覆寫呢?例如，我們将SELECT改為SELECT *再來看看。

　　

　　好明顯，傳回結果所包括的記錄太多，用非聚集索引反而不合算。是以使用了聚集索引。

　　如果此時我們删除聚集索引，再執行SELECT *看看。

　　DROP INDEX Person.IX_Id      

　　

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

　5、書簽查找

　　前面關于索引的學習我們已經知道，當在非聚集索引中并非覆寫和包含所需全部的列時，SQL Server會選擇，直接進行聚集索引掃描獲得資料，還是先去非聚集索引找到聚集索引鍵，然後利用聚集索引找到資料。

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

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

　　執行計劃如下：

　　

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

二、流聚合操作

　1、流聚合

　　流聚合：在相應排序的流中，計算多組行的彙總值。

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

　　例如執行以下語句：

　　SELECT MAX(Age) FROM Person      

　　檢視執行計劃如下：

　　

　2、計算标量

　　計算标量：根據行中的現有值計算新值。比如COUNT()函數，多一行，行數就加1咯。

　　除MIN和MAX函數之外的聚合函數都要求流聚合操作後面跟一個計算标量。

　　SELECT COUNT(*) FROM Person      

　　檢視執行計劃如下：

　　

3、散列聚合(哈希比對)

　　對于加了Group by的子句，因為需要資料按照group by 後面的列有序，就需要Sort來保證排序。注意,Sort操作是占用記憶體的操作，當記憶體不足時還會去占用tempdb。SQL Server總是會在Sort操作和散列比對中選擇成本最低的。

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

　　執行計劃如下：

　　

　　對于資料量比較大時，SQL Server選擇的是哈希比對。

　　在記憶體中建立好散清單後，會按照group by後面的值作為鍵，然後依次處理集合中的每條資料，當鍵在散清單中不存在時，向散清單添加條目，當鍵已經在散清單中存在時，按照規則（規則是聚合函數，比如Sum,avg什麼的）計算散清單中的值（Value）。

　4、排序

　　當資料量比價少時，例如執行以下語句，建立一個隻有數十條記錄的與Person一樣的表。

　　SELECT * INTO Person2 FROM Person2
　　WHERE Id < 100      

　　再來執行同樣的查詢語句：

　　SELECT Height,COUNT(Id) FROM Person2    --隻是表換成了資料量比較少的表
　　GROUP BY Height      

　　執行計劃如下：

　　

三、連接配接

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

　　建立兩張表如下

　　

　　這是一個簡單的新聞，欄目結構。

　1、嵌套循環

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

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

　　執行計劃如下：

　　

　　循環嵌套連接配接的圖示同樣十分形象，處在上面的外部輸入(Outer input)，這裡也就是聚集索引掃描。和處在下面的内部輸入(Inner Input),這裡也就是聚集索引查找。外部輸入僅僅執行一次，根據外部輸入滿足Join條件的每一行，對内部輸入進行查找。這裡由于是7行，對于内部輸入執行7次。

　　

　　根據嵌套循環的原理不難看出，由于外部輸入是掃描，内部輸入是查找，當兩個Join的表外部輸入結果集比較小，而内部輸入所查找的表非常大時，查詢優化器更傾向于選擇循環嵌套方式。

　2、合并連接配接

　　不同于循環嵌套的是，合并連接配接是從每個表僅僅執行一次通路。從這個原理來看，合并連接配接要比循環嵌套要快了不少。

　　從合并連接配接的原理不難想象，首先合并連接配接需要雙方有序.并且要求Join的條件為等于号。因為兩個輸入條件已經有序，是以從每一個輸入集合中取一行進行比較，相等的傳回，不相等的舍棄，從這裡也不難看出Merge join為什麼隻允許Join後面是等于号。從圖11的圖示中我們可以看出這個原理。

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

　　執行計劃如下：

　　

　　如果輸入資料的雙方無序，則查詢分析器不會選擇合并連接配接，我們也可以通過索引提示強制使用合并連接配接，為了達到這一目的，執行計劃必須加上一個排序步驟來實作有序。這也是上述SQL語句為什麼要加OPTION(MERGE join)的原因。上述對Article表的ColumnId列進行了排序。

　3、哈希連接配接

　　散列連接配接同樣僅僅隻需要隻通路1次雙方的資料。散列連接配接通過在記憶體中建立散清單實作。這比較消耗記憶體，如果記憶體不足還會占用tempdb。但并不像合并連接配接那樣需要雙方有序。

　　要進行下面這兩個實作，得把兩個列的聚集索引不要建在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)    --建立聚集索引
　　--這裡再設定回主鍵就可以了，有了聚集索引，就不能随主鍵預設建啦
      

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

　　然後執行以下查詢：

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

　　執行計劃如下：

　　

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

    通常來說，所求資料在其中一方或雙方沒有排序的條件達成時，會選用哈希比對。

四、并行

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

轉載一個大神的講解，原文來自：http://www.cnblogs.com/kissdodog/p/3160560.html