一文讀懂 SQL Server 執行計劃

導讀

數據開發過程中，開發完成的 SQL 發佈到生產環境，經常會發生 SQL 執行慢甚至根本無法執行，如何避免這種情況呢？這一篇我們分析一下 SQL Server 的執行計劃是如何生成及如何閱讀評估執行計劃，本文篇幅過長，建議收藏。

基本概念

在此之前，我們先了解以下幾個概念，方便我們理解。

本文中的 SQL 語句案例以及執行計劃均使用 SQL Server 2017 版本自帶的 AdventureWorksDW 樣例數據庫。樣例數據庫可通過關注後發送關鍵字 '2017' 獲取。

索引

索引本質上就是一種幫助數據庫高效獲取數據的數據結構，比如 B 樹樹索引使用二分查找法查找，檢索一條記錄的複雜度是 O(LogN)。但是這也不意味着索引越多越好，維護索引同樣會影響寫入性能，需要根據實際情況平衡數據寫入的讀寫性能。索引是基於數據庫表創建的，包含一個表中的某些列的值以及記錄對應的地址，並且把這些值存儲在一個數據結構中。常見的就是使用哈希表，B+ 樹，SQL Server 中使用的就是 B+ 樹。SQL Server 提供瞭如下集中索引類型。

索引類型

執行計劃

執行計劃也稱爲 “查詢計劃” 或者”執行計劃“，是 SQL 語句的執行方式，由查詢優化器根據數據庫中表和索引的定義以及數據庫統計信息，爲 SQL 語句選擇的最高效的數據訪問方式，然後交給執行器去執行。

執行計劃緩存

SQL Server 提供了一個用於存儲執行計劃和數據緩衝區的內存池，內存池中中用於存儲執行計劃的部分稱爲計劃緩存。SQL Server 執行任何 TSQL 語句時，數據庫引擎首先查看計劃緩存，確認是否存在同一個 TSQL 語句的現有執行計劃。SQL Server 會重用找到的任何現有計劃，節省重新編譯 TSQL 語句的開銷。如果沒有執行計劃，則爲查詢生成新的執行計劃。

既然是緩存，執行計劃何時會從緩存中刪除呢？當存在內存不足， SQL Server 使用基於開銷的方法確定從計劃緩存中刪除哪些執行計劃。如果內存不足，SQL Server 會重複檢查執行計劃，直至刪除了足夠多的執行計劃。

重新編譯執行計劃

執行計劃會在某些情況下導致無效， SQL Server 檢測到使執行計劃無效的更改後，將執行計劃標記爲無效。此後，SQL Server 必須要下一個連接重新編譯新的計劃。

導致計劃無效的情況有：

1），查詢引用的表或者視圖進行了更改;
2），存儲過程的修改;
3），執行計劃使用的索引有更改或刪除;
4），執行計劃使用的統計信息有更新，統計信息可以通過 update statistics 顯式生成或者自動生成;
5），執行了 sp_recompile;
6），存儲過程使用了 with recompile 選項;

執行計劃重用不一定是一件好事，而編譯 / 重編譯也不一定是一件壞事。需要根據實際場景來判斷是應該重用執行計劃還是重編譯執行計劃。

如何顯示執行計劃

SSMS 提供了三種用於顯示執行計劃

1），估計執行計劃，是編譯的計劃，由查詢優化器根據估計生成。預估執行計劃不等於實際執行計劃，但是絕大多數情況下實際的執行計劃跟預估執行計劃都是一致的。統計信息變更或者執行計劃重編譯等情況下，可能會造成不同。

2），實際執行計劃，是編譯計劃及其執行上下文，在執行 SQL 語句後出現，包括實際運行時信息。

3），實時查詢統計信息，與實際執行計劃相同，包括編譯的計劃和執行上下文。可用於正在進行的查詢執行，每個一秒更新一次。開銷有可能比較大，不建議生產環境使用。下圖中

基本法則

前置條件介紹完，現在正式進入主題。首先是執行計劃的閱讀方式，我們需要遵循一個基本法則：自上向下，從右向左。同一行的執行計劃步驟，右邊的先執行。同一列的執行步驟，上邊的先執行。

基本圖形

在閱讀執行計劃之前我們有必要了解一下 SQL Server 圖形執行計劃中提供的基本圖標的意義。

1），並行執行，如果給定的圖形執行計劃圖標包含一個帶有兩個從右到左箭頭的黃色圓圈，則表示運算符並行執行。

並行執行

2），連線

運算符之間的連線表示運算符之間傳遞的方向和數據量。箭頭的粗細表示傳遞的數據量多少。如下圖中粗細箭頭表示的數據量及其他詳細信息如下所示，執行計劃從右向左的箭頭中的數據量同樣可以反映數據查詢掃描的行數與最後實際 SELECT 查詢的行數差異。

細箭頭

粗箭頭

3），運算符詳細信息

SQL Server 執行計劃支持圖形方式，文本和 XML 格式查看。本篇文章主要以圖形化方式解析執行計劃，查詢計劃由邏輯運算符組成的樹表示，查詢計劃創建後，查詢優化器會爲每個邏輯運算符選擇最有效的物理運算法，最後查詢優化器使用基於開銷的方法將邏輯運算法轉化爲物理運算符。SQL Server 使用到的邏輯運算符和物理運算符參考如下鏈接: SQL Server 圖形執行計劃圖標。

本篇內容主要介紹圖形執行計劃的閱讀方法，根據上文介紹，SQL Server 提供了兩種執行計劃 —— 估計執行計劃和實際執行計劃。兩種執行執行計劃提供的運算符統計信息不同。如下圖所示：

估計執行計劃

估計執行計劃運算符統計信息

實際執行計劃

實際執行計劃運算符統計信息

通過以上兩幅執行計劃統計信息圖可以發現，實際執行計劃除了提供估計執行計劃統計信息之外，還提供了實際執行統計信息。這些數字不代表此運算符消耗的實際資源量，它代表 SQL Server 執行期間分配的估計成本。雖然不代表實際的資源量，但是這些信息有助於瞭解執行查詢時內部發生的實際情況。大多數情況下，實際值和預估是相同的，如果出現不一致的情況，可能的原因就是數據庫表或者索引的統計信息已經過時，需要進行更新。

在上圖中比較的執行計劃統計信息圖中還有使用的索引，檢索列的列表，以及用於過濾該運算符中數據的條件信息。除了，還可以通過右鍵運算符 ——> 屬性查看更詳細的統計信息。

運算符屬性

也可以選中運算符後，單擊 F4，同樣會顯示運算符的屬性窗口。

運算符屬性詳細信息

謂詞

微軟官方給的解釋是：取值爲 TRUE、FALSE 或 UNKNOWN 的表達式。謂詞用於 WHERE 子句和 HAVING 子句的搜索條件中，還用於 FROM 子句的聯接條件以及需要布爾值的其他構造中。

謂詞常用於過濾數據。一般來說盡早地過濾數據，可以減少中間結果集的大小，減少後續計算需要處理的數據量。所以謂詞下推是一個重要優化點，謂詞下推主要作用就是儘可能下壓謂詞，提前過濾部分數據。SQL Server 2017 版本中的查詢優化器，會自動將謂詞下推，將基於表或基於視圖的條件查詢，經過查詢優化後，將 where 條件下推到基本表進行過濾再進行關聯，提高查詢性能。

表掃描 Table Scan

首先我們查詢一張沒有任何索引的事實表，如果表上面沒有創建任何索引，表肯定是堆表。

堆是不含有聚集索引的表，可以在存儲爲堆的表上創建一個或者多個非聚集索引。數據存儲在堆中並且無需指定順序，因爲是無序的，所以在向堆表中插入數據時是無法預測數據順序的。如果要確保堆返回的數據行的順序，需要指定 Order By 子句。或者通過創建聚集索引指定存儲數據的永久邏輯順序。如果某個表是堆並且不具有任何非聚集索引，則必須讀取整個表（表掃描）以便找到任何行。堆表實際是一種數據結構，用於以邏輯結構存儲表數據，後期文章會詳細講解堆表的數據結構。

select *
from FactFinance
where FinanceKey = 100;

執行計劃如下：

全表掃

聚集索引掃描

如果一個表含有聚集索引，稱爲聚集表，聚集表是 B 樹結構，表數據按照聚集索引列排序，因爲表數據只能有一種物理順序，所以一個表只能有一個聚集索引。數據量大時，可以大幅減少讀取次數。

select *
from dimcustomer
where firstname = 'Clarence'

如果選擇一個存在聚集索引的表查詢，執行計劃是如何查找記錄的的呢？此時會按照聚集索引掃描查找數據，根據執行計劃的索引缺失提示，是因爲 where 子句中數據過濾語句的字段不是聚集索引列，所以執行計劃沒有落到任何索引上，所以聚集索引掃描與表掃描相同。當表增加了聚集索引後，堆表就變成了聚集表，聚集表的數據存儲在聚集索引的葉級節點。聚集索引何時才能夠生效呢？

聚集索引掃描

聚集索引查找

如果按照聚集索引列字段查找，執行計劃則會按照聚集索引查找。

select *
from DimCustomer
where CustomerKey = 11018

聚集索引查找和聚集索引掃描兩個運算符圖標是不相同的，兩種圖形分別形象的代表了兩種數據查找方法的不同。聚集索引查找是最快的數據查找方法。開發中，能使用聚集索引查找，則使用聚集索引查找。

聚集索引查找

索引掃描

根據以上幾種數據掃描方式，假如我們創建的不是聚集索引，而是非聚集索引，執行計劃會發生什麼變化呢？

創建非聚集索引

create nonclustered index ix_firstname on dimcustomer(firstname);

在表上爲 firstname 創建索引後，此時按照如下語句查詢全表，執行計劃會按照哪種數據掃描方式查詢數據呢？

select *
from dimcustomer

執行計劃如下，依然使用的是聚集索引掃描

修改查詢語句，只查詢一列 firstname

select firstname
from dimcustomer

執行計劃會不會走索引掃描呢？非聚集索引能夠覆蓋所需要的數據，所以執行了非聚集索引掃描。

索引掃描

索引查找

再次修改上面的查詢語句，增加查詢條件，根據 firstname 查找某個人

select firstname
from dimcustomer
where firstname = 'Clarence'

因爲 firstname 是非聚集索引列，此時 firstname 作爲查詢條件，執行計劃就會按照索引查找讀取數據記錄。執行計劃如下圖：

鍵值查找運算符

我們在聚集索引表上創建了一個含有 firstname 的非聚集索引，如果需求變化了，不僅僅需要讀取 firstname，需要讀取所有字段，執行計劃又會發生什麼呢？

select *
from dimcustomer
where firstname = 'Clarence'

此時出現了連三個運算符，分別是上面的索引查找, Key Lookup（鍵值查找運算符） 和 Nested Loops (嵌套循環運算符)。

SQL Server 爲何會使用這種查找方式呢？根據執行計劃基本原則，從上向下，從右向左。SQL Server 首先對非聚集索引執行了查找，但是非聚集索引上無法獲取除 firstname 外的其餘列。因此，SQL Server 使用非聚集索引上的數據指針來獲取其餘數據，因爲該表存在聚集索引，所以該表是聚集表。所以通過鍵值查找在聚集索引上的非聚集索引不包含的列。

1），索引查找輸出列

2），鍵值查找輸出列

嵌套循環

嵌套循環（ Nested Loop ），根據如下詳細信息描述，位於上面的外部輸入，也就是第 1）部索引查找，和位於下面的內部輸入（ 第 2 步的內部輸入，也就是鍵值查找 ），外部輸入僅僅執行一次，根據外部輸入滿足關聯條件的每一行，對內部輸入進行查找，對內部輸入進行查找，這裏執行 41 此。（）

根據以上分析，外部輸入數據量較小，內部輸入數據量相對較大，而且內部輸入上已經創建了聚集索引，查詢優化器選擇了嵌套循環連接。嵌套循環連接也稱爲” 嵌套迭代 “，類似於下面的僞代碼。嵌套循環比較適用於小數據量或者小事務中使用。

for(row r1 in outer table)
    for(row r2 in inner table)
        if( r1, r2 符合匹配條件 )
            output(r1, r2);

合併連接

嘗試如下 SQL，以 CustomerKey 內連接事實表和維度表，該關聯字段在維度表上建有聚集索引，

select *
from FactInternetSales fis
inner join dimcustomer dc
on dc.CustomerKey = fis.CustomerKey

執行計劃如下：

合併連接是從每個輸入僅僅執行一次訪問，因此比嵌套循環連接要快一些，只有當兩個輸入都是大數據量的情況下，此時合併鏈接的性能與哈希連接性能相近；但是如果兩個輸入數據量差異較大，哈希連接會由於合併鏈接。合併連接要求輸入都是有序的，並且關聯條件爲等號，此時由於維度表 CutomerKey 的聚集索引爲有序的，通過該字段與事實表關聯後，SQL Server 爲另一個輸入增加了 Sort 操作符對另一個輸入進行排序，最後選擇合併排序。

哈希連接

我們執行如下 SQL 查詢事實表與維度表。

select do.OrganizationName,ff.*
from dbo.FactFinance  ff
left join DimOrganization do
on do.OrganizationKey = ff.OrganizationKey
where ff.Date > '2010-10-10';

執行計劃如下：

根據如下運算符詳細信息可以得知，查詢優化器會根據兩個輸入中較小的哪個作爲生成輸入，另一個作爲探測輸入。哈希鏈接用於多種匹配操作，（ 如下圖 logical operation ），左外鏈接，右外鏈接，完全外部🔗，左半鏈接，右半鏈接，交集，聯合和差異等。此外，哈希連接又分爲三種類型：內存中的哈希連接，Grace 哈希連接和遞歸哈希鏈接。SQL Server 會根據實際情況選擇最有的一種，有興趣的可以試一下。

哈希聯接

本文由 Readfog 進行 AMP 轉碼，版權歸原作者所有。
來源：https://mp.weixin.qq.com/s/SQnqiTvaqf6JlUcQMzNQHw