深入分析SqlServer查詢計(jì)劃

更新時(shí)間：2014年09月06日 09:51:26 投稿：hebedich

最近使用到sqlserver數(shù)據(jù)庫(kù)并要對(duì)查詢語(yǔ)句進(jìn)行調(diào)優(yōu)，之前接觸的不多，搜索網(wǎng)上一些帖子和查閱微軟msdn資料對(duì)sqlserver的查詢計(jì)劃才大致了解了一些，用這篇文章做個(gè)總結(jié)。本文主要側(cè)重對(duì)查詢計(jì)劃的理解，尤其是對(duì)微軟復(fù)雜的運(yùn)算函數(shù)的理解，如理解有誤請(qǐng)指出。

對(duì)于SQL Server的優(yōu)化來(lái)說(shuō)，優(yōu)化查詢可能是很常見(jiàn)的事情。由于數(shù)據(jù)庫(kù)的優(yōu)化，本身也是一個(gè)涉及面比較的廣的話題，因此本文只談優(yōu)化查詢時(shí)如何看懂SQL Server查詢計(jì)劃。畢竟我對(duì)SQL Server的認(rèn)識(shí)有限，如有錯(cuò)誤，也懇請(qǐng)您在發(fā)現(xiàn)后及時(shí)批評(píng)指正。

首先，打開(kāi)【SQL Server Management Studio】，輸入一個(gè)查詢語(yǔ)句看看SQL Server是如何顯示查詢計(jì)劃的吧。
說(shuō)明：本文所演示的數(shù)據(jù)庫(kù)，是我為一個(gè)演示程序?qū)Ｓ脺?zhǔn)備的數(shù)據(jù)庫(kù)，可以在此網(wǎng)頁(yè)中下載。

select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v.SumMoney, v.Finished
from  OrdersView as v
where v.OrderDate >= '2010-12-1' and v.OrderDate < '2011-12-1';

其中，OrdersView是一個(gè)視圖，其定義如下：

SELECT   dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate, 
      dbo.Orders.SumMoney, dbo.Orders.Finished, 
      ISNULL(dbo.Customers.CustomerName, N'') AS CustomerName
FROM     dbo.Orders LEFT OUTER JOIN
        dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

對(duì)于前一句查詢，SQL Server給出的查詢計(jì)劃如下（點(diǎn)擊工具欄上的【顯示估計(jì)的執(zhí)行計(jì)劃】按鈕）：

從這個(gè)圖中，我們至少可以得到3個(gè)有用的信息：

1. 哪些執(zhí)行步驟花費(fèi)的成本比較高。顯然，最右邊的二個(gè)步驟的成本是比較高的。
2. 哪些執(zhí)行步驟產(chǎn)生的數(shù)據(jù)量比較多。對(duì)于每個(gè)步驟所產(chǎn)生的數(shù)據(jù)量， SQL Server的執(zhí)行計(jì)劃是用【線條粗細(xì)】來(lái)表示的，因此也很容易地從分辨出來(lái)。
3. 每一步執(zhí)行了什么樣的動(dòng)作。

對(duì)于一個(gè)比較慢的查詢來(lái)說(shuō)，我們通常要知道哪些步驟的成本比較高，進(jìn)而，可以嘗試一些改進(jìn)的方法。一般來(lái)說(shuō)，如果您不能通過(guò)：提高硬件性能或者調(diào)整OS,SQL Server的設(shè)置之類的方式來(lái)解決問(wèn)題，那么剩下的可選方法通常也只有以下這些了：

1. 為【scan】這類操作增加相應(yīng)字段的索引。
2. 有時(shí)重建索引或許也是有效的，具體情形請(qǐng)參考后文。
3. 調(diào)整語(yǔ)句結(jié)構(gòu)，引導(dǎo)SQL Server采用其它的查詢方案去執(zhí)行。
4. 調(diào)整表結(jié)構(gòu)（分表或者分區(qū)）。

下面再來(lái)說(shuō)說(shuō)一些很重要的理論知識(shí)，這些內(nèi)容對(duì)于執(zhí)行計(jì)劃的理解是很有幫助的。

回到頂部SQL Server 查找記錄的方法

說(shuō)到這里，不得不說(shuō)SQL Server的索引了。SQL Server有二種索引：聚集索引和非聚集索引。二者的差別在于：【聚集索引】直接決定了記錄的存放位置，或者說(shuō)：根據(jù)聚集索引可以直接獲取到記錄。【非聚集索引】保存了二個(gè)信息：1.相應(yīng)索引字段的值，2.記錄對(duì)應(yīng)聚集索引的位置（如果表沒(méi)有聚集索引則保存記錄指針）。因此，如果能通過(guò)【聚集索引】來(lái)查找記錄，顯然也是最快的。

SQL Server 會(huì)有以下方法來(lái)查找您需要的數(shù)據(jù)記錄：

1. 【Table Scan】：遍歷整個(gè)表，查找所有匹配的記錄行。這個(gè)操作將會(huì)一行一行的檢查，當(dāng)然，效率也是最差的。
2. 【Index Scan】：根據(jù)索引，從表中過(guò)濾出來(lái)一部分記錄，再查找所有匹配的記錄行，顯然比第一種方式的查找范圍要小，因此比【Table Scan】要快。
3. 【Index Seek】：根據(jù)索引，定位（獲?。┯涗浀拇娣盼恢?，然后取得記錄，因此，比起前二種方式會(huì)更快。
4. 【Clustered Index Scan】：和【Table Scan】一樣。注意：不要以為這里有個(gè)Index，就認(rèn)為不一樣了。其實(shí)它的意思是說(shuō)：按聚集索引來(lái)逐行掃描每一行記錄，因?yàn)橛涗浘褪前淳奂饕齺?lái)順序存放的。而【Table Scan】只是說(shuō)：要掃描的表沒(méi)有聚集索引而已，因此這二個(gè)操作本質(zhì)上也是一樣的。
5. 【Clustered Index Seek】：直接根據(jù)聚集索引獲取記錄，最快！

所以，當(dāng)發(fā)現(xiàn)某個(gè)查詢比較慢時(shí)，可以首先檢查哪些操作的成本比較高，再看看那些操作在查找記錄時(shí)，是不是【Table Scan】或者【Clustered Index Scan】，如果確實(shí)和這二種操作類型有關(guān)，則要考慮增加索引來(lái)解決了。不過(guò)，增加索引后，也會(huì)影響數(shù)據(jù)表的修改動(dòng)作，因?yàn)樾薷臄?shù)據(jù)表時(shí)，要更新相應(yīng)字段的索引。所以索引過(guò)多，也會(huì)影響性能。還有一種情況是不適合增加索引的：某個(gè)字段用0或1表示的狀態(tài)。例如可能有絕大多數(shù)是1，那么此時(shí)加索引根本就沒(méi)有意義。這時(shí)只能考慮為0或者1這二種情況分開(kāi)來(lái)保存了，分表或者分區(qū)都是不錯(cuò)的選擇。

如果不能通過(guò)增加索引和調(diào)整表來(lái)解決，那么可以試試調(diào)整語(yǔ)句結(jié)構(gòu)，引導(dǎo)SQL Server采用其它的查詢方案去執(zhí)行。這種方法要求： 1.對(duì)語(yǔ)句所要完成的功能很清楚， 2.對(duì)要查詢的數(shù)據(jù)表結(jié)構(gòu)很清楚， 3.對(duì)相關(guān)的業(yè)務(wù)背景知識(shí)很清楚。如果能通過(guò)這種方法去解決，當(dāng)然也是很好的解決方法了。不過(guò)，有時(shí)SQL Server比較智能，即使你調(diào)整語(yǔ)句結(jié)構(gòu)，也不會(huì)影響它的執(zhí)行計(jì)劃。

如何比較二個(gè)相同功能的SQL語(yǔ)句的性能好壞呢，我建議采用二種方法： 1. 直接把二個(gè)查詢語(yǔ)句放在【SQL Server Management Studio】，然后去看它們的【執(zhí)行計(jì)劃】，SQL Server會(huì)以百分比的方式告訴你二個(gè)查詢的【查詢開(kāi)銷】。這種方法簡(jiǎn)單，通常也是可以參考的，不過(guò)，有時(shí)也會(huì)不準(zhǔn)，具體原因請(qǐng)接著往下看(可能索引統(tǒng)計(jì)信息過(guò)舊)。
2. 根據(jù)真實(shí)的程序調(diào)用，寫相應(yīng)的測(cè)試代碼去調(diào)用：這種方法就麻煩一些，但是它更能代表現(xiàn)實(shí)調(diào)用情況，得到的結(jié)果也是更具有參考價(jià)值的，因此也是值得的。

回到頂部SQL Server Join 方式

在SQL Server中，每個(gè)join命令，都會(huì)在內(nèi)部執(zhí)行時(shí)采用三種更具體的方式來(lái)運(yùn)行：

1. 【Nested Loops join】，如果一個(gè)聯(lián)接輸入很小，而另一個(gè)聯(lián)接輸入很大而且已在其聯(lián)接列上創(chuàng)建了索引，則索引 Nested Loops 連接是最快的聯(lián)接操作，因?yàn)樗鼈冃枰?I/O 和比較都最少。

嵌套循環(huán)聯(lián)接也稱為“嵌套迭代”，它將一個(gè)聯(lián)接輸入用作外部輸入表（顯示為圖形執(zhí)行計(jì)劃中的頂端輸入），將另一個(gè)聯(lián)接輸入用作內(nèi)部（底端）輸入表。外部循環(huán)逐行處理外部輸入表。內(nèi)部循環(huán)會(huì)針對(duì)每個(gè)外部行執(zhí)行，在內(nèi)部輸入表中搜索匹配行?？梢杂孟旅娴膫未a來(lái)理解：

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

最簡(jiǎn)單的情況是，搜索時(shí)掃描整個(gè)表或索引；這稱為“單純嵌套循環(huán)聯(lián)接”。如果搜索時(shí)使用索引，則稱為“索引嵌套循環(huán)聯(lián)接”。如果將索引生成為查詢計(jì)劃的一部分（并在查詢完成后立即將索引破壞），則稱為“臨時(shí)索引嵌套循環(huán)聯(lián)接”。查詢優(yōu)化器考慮了所有這些不同情況。

如果外部輸入較小而內(nèi)部輸入較大且預(yù)先創(chuàng)建了索引，則嵌套循環(huán)聯(lián)接尤其有效。在許多小事務(wù)中（如那些只影響較小的一組行的事務(wù)），索引嵌套循環(huán)聯(lián)接優(yōu)于合并聯(lián)接和哈希聯(lián)接。但在大型查詢中，嵌套循環(huán)聯(lián)接通常不是最佳選擇。

2. 【Merge Join】，如果兩個(gè)聯(lián)接輸入并不小但已在二者聯(lián)接列上排序（例如，如果它們是通過(guò)掃描已排序的索引獲得的），則合并聯(lián)接是最快的聯(lián)接操作。如果兩個(gè)聯(lián)接輸入都很大，而且這兩個(gè)輸入的大小差不多，則預(yù)先排序的合并聯(lián)接提供的性能與哈希聯(lián)接相近。但是，如果這兩個(gè)輸入的大小相差很大，則哈希聯(lián)接操作通?？斓枚唷?/p>

合并聯(lián)接要求兩個(gè)輸入都在合并列上排序，而合并列由聯(lián)接謂詞的等效 (ON) 子句定義。通常，查詢優(yōu)化器掃描索引（如果在適當(dāng)?shù)囊唤M列上存在索引），或在合并聯(lián)接的下面放一個(gè)排序運(yùn)算符。在極少數(shù)情況下，雖然可能有多個(gè)等效子句，但只用其中一些可用的等效子句獲得合并列。

由于每個(gè)輸入都已排序，因此 Merge Join 運(yùn)算符將從每個(gè)輸入獲取一行并將其進(jìn)行比較。例如，對(duì)于內(nèi)聯(lián)接操作，如果行相等則返回。如果行不相等，則廢棄值較小的行并從該輸入獲得另一行。這一過(guò)程將重復(fù)進(jìn)行，直到處理完所有的行為止。

合并聯(lián)接操作可以是常規(guī)操作，也可以是多對(duì)多操作。多對(duì)多合并聯(lián)接使用臨時(shí)表存儲(chǔ)行（會(huì)影響效率）。如果每個(gè)輸入中有重復(fù)值，則在處理其中一個(gè)輸入中的每個(gè)重復(fù)項(xiàng)時(shí)，另一個(gè)輸入必須重繞到重復(fù)項(xiàng)的開(kāi)始位置。可以創(chuàng)建唯一索引告訴SQL Server不會(huì)有重復(fù)值。

如果存在駐留謂詞，則所有滿足合并謂詞的行都將對(duì)該駐留謂詞取值，而只返回那些滿足該駐留謂詞的行。

合并聯(lián)接本身的速度很快，但如果需要排序操作，選擇合并聯(lián)接就會(huì)非常費(fèi)時(shí)。然而，如果數(shù)據(jù)量很大且能夠從現(xiàn)有 B 樹(shù)索引中獲得預(yù)排序的所需數(shù)據(jù)，則合并聯(lián)接通常是最快的可用聯(lián)接算法。

3. 【Hash Join】，哈希聯(lián)接可以有效處理未排序的大型非索引輸入。它們對(duì)復(fù)雜查詢的中間結(jié)果很有用，因?yàn)椋?1. 中間結(jié)果未經(jīng)索引（除非已經(jīng)顯式保存到磁盤上然后創(chuàng)建索引），而且通常不為查詢計(jì)劃中的下一個(gè)操作進(jìn)行適當(dāng)?shù)呐判颉?2. 查詢優(yōu)化器只估計(jì)中間結(jié)果的大小。由于對(duì)于復(fù)雜查詢，估計(jì)可能有很大的誤差，因此如果中間結(jié)果比預(yù)期的大得多，則處理中間結(jié)果的算法不僅必須有效而且必須適度弱化。

哈希聯(lián)接可以減少使用非規(guī)范化。非規(guī)范化一般通過(guò)減少聯(lián)接操作獲得更好的性能，盡管這樣做有冗余之險(xiǎn)（如不一致的更新）。哈希聯(lián)接則減少使用非規(guī)范化的需要。哈希聯(lián)接使垂直分區(qū)（用單獨(dú)的文件或索引代表單個(gè)表中的幾組列）得以成為物理數(shù)據(jù)庫(kù)設(shè)計(jì)的可行選項(xiàng)。

哈希聯(lián)接有兩種輸入：生成輸入和探測(cè)輸入。查詢優(yōu)化器指派這些角色，使兩個(gè)輸入中較小的那個(gè)作為生成輸入。

哈希聯(lián)接用于多種設(shè)置匹配操作：內(nèi)部聯(lián)接；左外部聯(lián)接、右外部聯(lián)接和完全外部聯(lián)接；左半聯(lián)接和右半聯(lián)接；交集；聯(lián)合和差異。此外，哈希聯(lián)接的某種變形可以進(jìn)行重復(fù)刪除和分組，例如 SUM(salary) GROUP BY department。這些修改對(duì)生成和探測(cè)角色只使用一個(gè)輸入。

哈希聯(lián)接又分為3個(gè)類型：內(nèi)存中的哈希聯(lián)接、Grace 哈希聯(lián)接和遞歸哈希聯(lián)接。

內(nèi)存中的哈希聯(lián)接：哈希聯(lián)接先掃描或計(jì)算整個(gè)生成輸入，然后在內(nèi)存中生成哈希表。根據(jù)計(jì)算得出的哈希鍵的哈希值，將每行插入哈希存儲(chǔ)桶。如果整個(gè)生成輸入小于可用內(nèi)存，則可以將所有行都插入哈希表中。生成階段之后是探測(cè)階段。一次一行地對(duì)整個(gè)探測(cè)輸入進(jìn)行掃描或計(jì)算，并為每個(gè)探測(cè)行計(jì)算哈希鍵的值，掃描相應(yīng)的哈希存儲(chǔ)桶并生成匹配項(xiàng)。

Grace 哈希聯(lián)接：如果生成輸入大于內(nèi)存，哈希聯(lián)接將分為幾步進(jìn)行。這稱為“Grace 哈希聯(lián)接”。每一步都分為生成階段和探測(cè)階段。首先，消耗整個(gè)生成和探測(cè)輸入并將其分區(qū)（使用哈希鍵上的哈希函數(shù)）為多個(gè)文件。對(duì)哈希鍵使用哈希函數(shù)可以保證任意兩個(gè)聯(lián)接記錄一定位于相同的文件對(duì)中。因此，聯(lián)接兩個(gè)大輸入的任務(wù)簡(jiǎn)化為相同任務(wù)的多個(gè)較小的實(shí)例。然后將哈希聯(lián)接應(yīng)用于每對(duì)分區(qū)文件。

遞歸哈希聯(lián)接：如果生成輸入非常大，以至于標(biāo)準(zhǔn)外部合并的輸入需要多個(gè)合并級(jí)別，則需要多個(gè)分區(qū)步驟和多個(gè)分區(qū)級(jí)別。如果只有某些分區(qū)較大，則只需對(duì)那些分區(qū)使用附加的分區(qū)步驟。為了使所有分區(qū)步驟盡可能快，將使用大的異步 I/O 操作以便單個(gè)線程就能使多個(gè)磁盤驅(qū)動(dòng)器繁忙工作。

在優(yōu)化過(guò)程中不能始終確定使用哪種哈希聯(lián)接。因此，SQL Server 開(kāi)始時(shí)使用內(nèi)存中的哈希聯(lián)接，然后根據(jù)生成輸入的大小逐漸轉(zhuǎn)換到 Grace 哈希聯(lián)接和遞歸哈希聯(lián)接。
如果優(yōu)化器錯(cuò)誤地預(yù)計(jì)兩個(gè)輸入中哪個(gè)較小并由此確定哪個(gè)作為生成輸入，生成角色和探測(cè)角色將動(dòng)態(tài)反轉(zhuǎn)。哈希聯(lián)接確保使用較小的溢出文件作為生成輸入。這一技術(shù)稱為“角色反轉(zhuǎn)”。至少一個(gè)文件溢出到磁盤后，哈希聯(lián)接中才會(huì)發(fā)生角色反轉(zhuǎn)。

說(shuō)明：您也可以顯式的指定聯(lián)接方式，SQL Server會(huì)盡量尊重您的選擇。比如你可以這樣寫：inner loop join, left outer merge join, inner hash join
但是，我還是建議您不要這樣做，因?yàn)镾QL Server的選擇基本上都是正確的，不信您可以試一下。

好了，說(shuō)了一大堆理論東西，再來(lái)個(gè)實(shí)際的例子解釋一下吧。

回到頂部更具體執(zhí)行過(guò)程

前面，我給出一張圖片，它反映了SQL Server在執(zhí)行某個(gè)查詢的執(zhí)行計(jì)劃，但它反映的信息可能不太細(xì)致，當(dāng)然，您可以把鼠標(biāo)指標(biāo)移動(dòng)某個(gè)節(jié)點(diǎn)上，會(huì)有以下信息出現(xiàn)：

剛好，我裝的是中文版的，上面都是漢字，我也不多說(shuō)了。我要說(shuō)的是另一種方式的執(zhí)行過(guò)程，比這個(gè)包含更多的執(zhí)行信息，而且是實(shí)際的執(zhí)行情況。（當(dāng)然，您也可以繼續(xù)使用圖形方式，在運(yùn)行查詢前點(diǎn)擊工具欄上的【包括實(shí)際的執(zhí)行計(jì)劃】按鈕）

讓我們?cè)俅位氐健維QL Server Management Studio】，輸入以下語(yǔ)句，然后執(zhí)行。

set statistics profile on 

select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v.SumMoney, v.Finished
from  OrdersView as v
where v.OrderDate >= '2010-12-1' and v.OrderDate < '2011-12-1';

注意：現(xiàn)在加了一句，【set statistics profile on 】，得到的結(jié)果如下：

可以從圖片上看到，執(zhí)行查詢后，得到二個(gè)表格，上面的表格顯示了查詢的結(jié)果，下面的表格顯示了查詢的執(zhí)行過(guò)程。相比本文的第一張圖片，這張圖片可能在直觀上不太友好，但是，它能反映更多的信息，而且尤其在比較復(fù)雜的查詢時(shí)，可能看起來(lái)更容易，因?yàn)閷?duì)于復(fù)雜的查詢，【執(zhí)行計(jì)劃】的步驟太多，圖形方式會(huì)造成圖形過(guò)大，不容易觀察。而且這張執(zhí)行過(guò)程表格能反映2個(gè)很有價(jià)值的數(shù)據(jù)（前二列）。

還是來(lái)看看這個(gè)【執(zhí)行過(guò)程表格】吧。我來(lái)挑幾個(gè)重要的說(shuō)一下。
【Rows】：表示在一個(gè)執(zhí)行步驟中，所產(chǎn)生的記錄條數(shù)。（真實(shí)數(shù)據(jù)，非預(yù)期）
【Executes】：表示某個(gè)執(zhí)行步驟被執(zhí)行的次數(shù)。（真實(shí)數(shù)據(jù)，非預(yù)期）
【Stmt Text】：表示要執(zhí)行的步驟的描述。
【EstimateRows】：表示要預(yù)期返回多少行數(shù)據(jù)。

在這個(gè)【執(zhí)行過(guò)程表格】中，對(duì)于優(yōu)化查詢來(lái)說(shuō)，我認(rèn)為前三列是比較重要的。對(duì)于前二列，我上面也解釋了，意思也很清楚。前二列的數(shù)字也大致反映了那些步驟所花的成本，對(duì)于比較慢的查詢中，應(yīng)該留意它們。【Stmt Text】會(huì)告訴你每個(gè)步驟做了什么事情。對(duì)于這種表格，它所要表達(dá)的其實(shí)是一種樹(shù)型信息（一行就表示在圖形方式下的一個(gè)節(jié)點(diǎn)），所以，我建議從最內(nèi)層開(kāi)始去讀它們。做為示例，我來(lái)解釋一下這張表格它所表達(dá)的執(zhí)行過(guò)程。

第5行：【Clustered Index Seek(OBJECT:([MyNorthwind].[dbo].[Customers].[PK_Customers]), SEEK:([MyNorthwind].[dbo].[Customers].[CustomerID]=[MyNorthwind].[dbo].[Orders].[CustomerID]) ORDERED FORWARD)】，意思是說(shuō)，SQL Server在對(duì)表Customers做Seek操作，而且是按照【Clustered Index Seek】的方式，對(duì)應(yīng)的索引是【PK_Customers】，seek的值來(lái)源于[Orders].[CustomerID]

第4行：【Clustered Index Scan(OBJECT:([MyNorthwind].[dbo].[Orders].[PK_Orders]), WHERE:([MyNorthwind].[dbo].[Orders].[OrderDate]>='2010-12-01 00:00:00.000' AND [MyNorthwind].[dbo].[Orders].[OrderDate]<'2011-12-01 00:00:00.000'))】，意思是說(shuō)，SQL Server在對(duì)表Customers做Scan操作，即：最差的【表掃描】的方式，原因是，OrderDate列上沒(méi)有索引，所以只能這樣了。

第3行：【Nested Loops(Left Outer Join, OUTER REFERENCES:([MyNorthwind].[dbo].[Orders].[CustomerID]))】，意思是說(shuō)，SQL Server把第5行和第4行產(chǎn)生的數(shù)據(jù)用【Nested Loops】的方式聯(lián)接起來(lái)，其中Outer表是Orders，要聯(lián)接的匹配操作也在第5行中指出了。

第2行：【Compute Scalar(DEFINE:([Expr1006]=isnull([MyNorthwind].[dbo].[Customers].[CustomerName],N'')))】，意思是說(shuō)，要執(zhí)行一個(gè)isnull()函數(shù)的調(diào)用。具體原因請(qǐng)參考本文前部分中給出視圖定義代碼。

第1行：【SELECT [v].[OrderID],[v].[CustomerID],[v].[CustomerName],[v].[OrderDate],[v].[SumMoney],[v].[Finished] FROM [OrdersView] [v] WHERE [v].[OrderDate]>=@1 AND [v].[OrderDate]<@2】，通常第1行就是整個(gè)查詢，表示它的返回值。

回到頂部索引統(tǒng)計(jì)信息：查詢計(jì)劃的選擇依據(jù)

前面一直說(shuō)到【執(zhí)行計(jì)劃】，既然是計(jì)劃，就表示要在具體執(zhí)行前就能確定下來(lái)的操作方案。那么SQL Server是如何選擇一個(gè)執(zhí)行計(jì)劃的呢？ SQL Server怎么知道什么時(shí)候該用索引或者用哪個(gè)索引呢？對(duì)于SQL Server來(lái)說(shuō)，每當(dāng)要執(zhí)行一個(gè)查詢時(shí)，都要首先檢查這個(gè)查詢的執(zhí)行計(jì)劃是否存在緩存中，如果沒(méi)有，就要生成一個(gè)執(zhí)行計(jì)劃，具體在產(chǎn)生執(zhí)行計(jì)劃時(shí)，并不是看有哪些索引可用（隨機(jī)選擇），而是會(huì)參考一種被稱為【索引統(tǒng)計(jì)信息】的數(shù)據(jù)。如果您仔細(xì)地看一下前面的執(zhí)行計(jì)劃或者執(zhí)行過(guò)程表格，會(huì)發(fā)現(xiàn)SQL Server能預(yù)估每個(gè)步驟所產(chǎn)生的數(shù)據(jù)量，正是因?yàn)镾QL Server能預(yù)估這些數(shù)據(jù)量，SQL Server才能選擇一個(gè)它認(rèn)為最合適的方法去執(zhí)行查詢過(guò)程，此時(shí)【索引統(tǒng)計(jì)信息】就能告訴SQL Server這些信息。說(shuō)到這里，您是不是有點(diǎn)好奇呢，為了讓您對(duì)【索引統(tǒng)計(jì)信息】有個(gè)感性的認(rèn)識(shí)，我們來(lái)看看【索引統(tǒng)計(jì)信息】是個(gè)什么樣子的。請(qǐng)?jiān)凇維QL Server Management Studio】，輸入以下語(yǔ)句，然后執(zhí)行。

dbcc show_statistics (Products, IX_CategoryID)

得到的結(jié)果如下圖：

首先，還是解釋一下命令：【dbcc show_statistics】這個(gè)命令可以顯示我們想知道的【索引統(tǒng)計(jì)信息】，它需要二個(gè)參數(shù)，1. 表名，2. 索引名

再來(lái)看看命令的結(jié)果，它有三個(gè)表格組成：
1. 第一個(gè)表格，它列出了這個(gè)索引統(tǒng)計(jì)信息的主要信息。

列名說(shuō)明Name統(tǒng)計(jì)信息的名稱。Updated上一次更新統(tǒng)計(jì)信息的日期和時(shí)間。Rows表中的行數(shù)。Rows Sampled統(tǒng)計(jì)信息的抽樣行數(shù)。Steps數(shù)據(jù)可分成多少個(gè)組，與第三個(gè)表對(duì)應(yīng)。Density第一個(gè)索引列前綴的選擇性（不包括 EQ_ROWS）。Average key length所有索引列的平均長(zhǎng)度。String Index如果為“是”，則統(tǒng)計(jì)信息中包含字符串摘要索引，以支持為 LIKE 條件估算結(jié)果集大小。僅適用于char、varchar、nchar和nvarchar、varchar(max)、nvarchar(max)、text以及ntext數(shù)據(jù)類型的前導(dǎo)列。

2. 第二個(gè)表格，它列出各種字段組合的選擇性，數(shù)據(jù)越小表示重復(fù)越性越小，當(dāng)然選擇性也就越高。

列名說(shuō)明All density索引列前綴集的選擇性（包括 EQ_ROWS）。注意：這個(gè)值越小就表示選擇性越高。
如果這個(gè)值小于0.1，這個(gè)索引的選擇性就比較高，反之，則表示選擇性就不高了。Average length索引列前綴集的平均長(zhǎng)度。Columns為其顯示All density和Average length的索引列前綴的名稱。

3. 第三個(gè)表格，數(shù)據(jù)分布的直方圖，SQL Server就是靠它預(yù)估一些執(zhí)行步驟的數(shù)據(jù)量。

列名說(shuō)明RANGE_HI_KEY每個(gè)組中的最大值。RANGE_ROWS每組數(shù)據(jù)組的估算行數(shù)，不包含最大值。EQ_ROWS每組數(shù)據(jù)組中與最大值相等的行的估算數(shù)目。DISTINCT_RANGE_ROWS每組數(shù)據(jù)組中的非重復(fù)值的估算數(shù)目，不包含最大值。AVG_RANGE_ROWS每組數(shù)據(jù)組中的重復(fù)值的平均數(shù)目，不包含最大值，計(jì)算公式：RANGE_ROWS / DISTINCT_RANGE_ROWS for DISTINCT_RANGE_ROWS > 0

為了能讓您更好的理解這些數(shù)據(jù)，尤其是第三組，請(qǐng)看下圖：

當(dāng)時(shí)我在填充測(cè)試數(shù)據(jù)時(shí)，故意把CategoryId分為1到8（10是后來(lái)臨時(shí)加的），每組填充了78條數(shù)據(jù)。所以【索引統(tǒng)計(jì)信息】的第三個(gè)表格的數(shù)據(jù)也都是正確的，也正是根據(jù)這些統(tǒng)計(jì)信息，SQL Server才能對(duì)每個(gè)執(zhí)行步驟預(yù)估相應(yīng)的數(shù)據(jù)量，從而影響Join之類的選擇。當(dāng)然了，在選擇Join方式時(shí)，也要參考第二個(gè)表格中字段的選擇性。SQL Server在為查詢生成執(zhí)行計(jì)劃時(shí)，查詢優(yōu)化器將使用這些統(tǒng)計(jì)信息并結(jié)合相關(guān)的索引來(lái)評(píng)估每種方案的開(kāi)銷來(lái)選擇最佳的查詢計(jì)劃。

再來(lái)個(gè)例子說(shuō)明一下統(tǒng)計(jì)信息對(duì)于查詢計(jì)劃的重要性。首先多加點(diǎn)數(shù)據(jù)，請(qǐng)看以下代碼：

declare @newCategoryId int;
insert into dbo.Categories (CategoryName) values(N'Test statistics');
set @newCategoryId = scope_identity();

declare @count int;
set @count = 0;

while( @count < 100000 )
begin
  insert into Products (ProductName, CategoryID, Unit, UnitPrice, Quantity, Remark) 
  values( cast(newid() as nvarchar(50)), @newCategoryId, N'個(gè)', 100, @count +1, N'');

  set @count = @count + 1;
end
go

update statistics Products;
go

再來(lái)看看索引統(tǒng)計(jì)信息：

再來(lái)看看同一個(gè)查詢，但因?yàn)椴樵儏?shù)值不同時(shí)，SQL Server選擇的執(zhí)行計(jì)劃：

select p.ProductId, t.Quantity 
from Products as p left outer join [Order Details] as t on p.ProductId = t.ProductId 
where p.CategoryId = 26;  -- 26 就是最新產(chǎn)生的CategoryId，因此這個(gè)查詢會(huì)返回10W條記錄

select p.ProductId, t.Quantity 
from Products as p left outer join [Order Details] as t on p.ProductId = t.ProductId 
where p.CategoryId = 6;  -- 這個(gè)查詢會(huì)返回95條記錄

從上圖可以看出，由于CategoryId的參數(shù)值不同，SQL Server會(huì)選擇完全不同的執(zhí)行計(jì)劃。統(tǒng)計(jì)信息重要性在這里體現(xiàn)的很清楚吧。

創(chuàng)建統(tǒng)計(jì)信息后，數(shù)據(jù)庫(kù)引擎對(duì)列值（根據(jù)這些值創(chuàng)建統(tǒng)計(jì)信息）進(jìn)行排序，并根據(jù)這些值（最多 200 個(gè)，按間隔分隔開(kāi)）創(chuàng)建一個(gè)“直方圖”。直方圖指定有多少行精確匹配每個(gè)間隔值，有多少行在間隔范圍內(nèi)，以及間隔中值的密度大小或重復(fù)值的發(fā)生率。

SQL Server 2005 引入了對(duì) char、varchar、varchar(max)、nchar、nvarchar、nvarchar(max)、text 和 ntext 列創(chuàng)建的統(tǒng)計(jì)信息收集的其他信息。這些信息稱為“字符串摘要”，可以幫助查詢優(yōu)化器估計(jì)字符串模式中查詢謂詞的選擇性。查詢中有 LIKE 條件時(shí)，使用字符串摘要可以更準(zhǔn)確地估計(jì)結(jié)果集大小，并不斷優(yōu)化查詢計(jì)劃。這些條件包括諸如 WHERE ProductName LIKE '%Bike' 和 WHERE Name LIKE '[CS]heryl' 之類的條件。

既然【索引統(tǒng)計(jì)信息】這么重要，那么它會(huì)在什么時(shí)候生成或者更新呢？事實(shí)上，【索引統(tǒng)計(jì)信息】是不用我們手工去維護(hù)的， SQL Server會(huì)自動(dòng)去維護(hù)它們。而且在SQL Server中也有個(gè)參數(shù)來(lái)控制這個(gè)更新方式：

統(tǒng)計(jì)信息自動(dòng)功能工作方式

創(chuàng)建索引時(shí)，查詢優(yōu)化器自動(dòng)存儲(chǔ)有關(guān)索引列的統(tǒng)計(jì)信息。另外，當(dāng) AUTO_CREATE_STATISTICS 數(shù)據(jù)庫(kù)選項(xiàng)設(shè)置為 ON（默認(rèn)值）時(shí)，數(shù)據(jù)庫(kù)引擎自動(dòng)為沒(méi)有用于謂詞的索引的列創(chuàng)建統(tǒng)計(jì)信息。

隨著列中數(shù)據(jù)發(fā)生變化，索引和列的統(tǒng)計(jì)信息可能會(huì)過(guò)時(shí)，從而導(dǎo)致查詢優(yōu)化器選擇的查詢處理方法不是最佳的。例如，如果創(chuàng)建一個(gè)包含一個(gè)索引列和 1,000 行數(shù)據(jù)的表，每一行在索引列中的值都是唯一的，則查詢優(yōu)化器將把該索引列視為收集查詢數(shù)據(jù)的好方法。如果更新列中的數(shù)據(jù)后存在許多重復(fù)值，則該列不再是用于查詢的理想候選列。但是，查詢優(yōu)化器仍然根據(jù)索引的過(guò)時(shí)分布統(tǒng)計(jì)信息（基于更新前的數(shù)據(jù)），將其視為好的候選列。

當(dāng) AUTO_UPDATE_STATISTICS 數(shù)據(jù)庫(kù)選項(xiàng)設(shè)置為 ON（默認(rèn)值）時(shí)，查詢優(yōu)化器會(huì)在表中的數(shù)據(jù)發(fā)生變化時(shí)自動(dòng)定期更新這些統(tǒng)計(jì)信息。每當(dāng)查詢執(zhí)行計(jì)劃中使用的統(tǒng)計(jì)信息沒(méi)有通過(guò)針對(duì)當(dāng)前統(tǒng)計(jì)信息的測(cè)試時(shí)就會(huì)啟動(dòng)統(tǒng)計(jì)信息更新。采樣是在各個(gè)數(shù)據(jù)頁(yè)上隨機(jī)進(jìn)行的，取自表或統(tǒng)計(jì)信息所需列的最小非聚集索引。從磁盤讀取一個(gè)數(shù)據(jù)頁(yè)后，該數(shù)據(jù)頁(yè)上的所有行都被用來(lái)更新統(tǒng)計(jì)信息。常規(guī)情況是：在大約有 20% 的數(shù)據(jù)行發(fā)生變化時(shí)更新統(tǒng)計(jì)信息。但是，查詢優(yōu)化器始終確保采樣的行數(shù)盡量少。對(duì)于小于 8 MB 的表，則始終進(jìn)行完整掃描來(lái)收集統(tǒng)計(jì)信息。

采樣數(shù)據(jù)（而不是分析所有數(shù)據(jù)）可以將統(tǒng)計(jì)信息自動(dòng)更新的開(kāi)銷降至最低。在某些情況下，統(tǒng)計(jì)采樣無(wú)法獲得表中數(shù)據(jù)的精確特征。可以使用 UPDATE STATISTICS 語(yǔ)句的 SAMPLE 子句和 FULLSCAN 子句，控制按逐個(gè)表的方式手動(dòng)更新統(tǒng)計(jì)信息時(shí)采樣的數(shù)據(jù)量。FULLSCAN 子句指定掃描表中的所有數(shù)據(jù)來(lái)收集統(tǒng)計(jì)信息，而 SAMPLE 子句用來(lái)指定采樣的行數(shù)百分比或采樣的行數(shù)

在 SQL Server 2005 中，數(shù)據(jù)庫(kù)選項(xiàng) AUTO_UPDATE_STATISTICS_ASYNC 提供了統(tǒng)計(jì)信息異步更新功能。當(dāng)此選項(xiàng)設(shè)置為 ON 時(shí)，查詢不等待統(tǒng)計(jì)信息更新，即可進(jìn)行編譯。而過(guò)期的統(tǒng)計(jì)信息置于隊(duì)列中，由后臺(tái)進(jìn)程中的工作線程來(lái)更新。查詢和任何其他并發(fā)查詢都通過(guò)使用現(xiàn)有的過(guò)期統(tǒng)計(jì)信息立即編譯。由于不存在等待更新后的統(tǒng)計(jì)信息的延遲，因此查詢響應(yīng)時(shí)間可預(yù)測(cè)；但是過(guò)期的統(tǒng)計(jì)信息可能導(dǎo)致查詢優(yōu)化器選擇低效的查詢計(jì)劃。在更新后的統(tǒng)計(jì)信息就緒后啟動(dòng)的查詢將使用那些統(tǒng)計(jì)信息。這可能會(huì)導(dǎo)致重新編譯緩存的計(jì)劃（取決于較舊的統(tǒng)計(jì)信息版本）。如果在同一個(gè)顯式用戶事務(wù)中出現(xiàn)某些數(shù)據(jù)定義語(yǔ)言 (DDL) 語(yǔ)句（例如，CREATE、ALTER 和 DROP 語(yǔ)句），則無(wú)法更新異步統(tǒng)計(jì)信息。

AUTO_UPDATE_STATISTICS_ASYNC 選項(xiàng)設(shè)置于數(shù)據(jù)庫(kù)級(jí)別，并確定用于數(shù)據(jù)庫(kù)中所有統(tǒng)計(jì)信息的更新方法。它只適用于統(tǒng)計(jì)信息更新，而無(wú)法用于以異步方式創(chuàng)建統(tǒng)計(jì)信息。只有將 AUTO_UPDATE_STATISTICS 設(shè)置為 ON 時(shí)，將此選項(xiàng)設(shè)置為 ON 才有效。默認(rèn)情況下，AUTO_UPDATE_STATISTICS_ASYNC 選項(xiàng)設(shè)置為 OFF。

從以上說(shuō)明中，我們可以看出，對(duì)于大表，還是有可能存在統(tǒng)計(jì)信息更新不及時(shí)的時(shí)候，這時(shí)，就可能會(huì)影響查詢優(yōu)化器的判斷了。
有些人可能有個(gè)經(jīng)驗(yàn)：對(duì)于一些慢的查詢，他們會(huì)想到重建索引來(lái)嘗試解決。其實(shí)這樣做是有道理的。因?yàn)?，在某些時(shí)候一個(gè)查詢突然變慢了，可能和統(tǒng)計(jì)信息更新不及時(shí)有關(guān)，進(jìn)而會(huì)影響查詢優(yōu)化器的判斷。如果此時(shí)重建索引，就可以讓查詢優(yōu)化器知道最新的數(shù)據(jù)分布，自然就可以避開(kāi)這個(gè)問(wèn)題。還記得我前面用【set statistics profile on】顯示的執(zhí)行過(guò)程表格嗎？注意哦，那個(gè)表格就顯示每個(gè)步驟的實(shí)際數(shù)據(jù)量和預(yù)估的數(shù)據(jù)量。要不要重建索引，其實(shí)我們可以用【set statistics profile on】來(lái)看一下，如果實(shí)際數(shù)據(jù)量和預(yù)估的數(shù)據(jù)量的差值比較大，那么我們可以考慮手工去更新統(tǒng)計(jì)信息，然后再去試試。

回到頂部?jī)?yōu)化視圖查詢

再來(lái)說(shuō)說(shuō)優(yōu)化視圖查詢，雖然視圖也是由一個(gè)查詢語(yǔ)句定義的，本質(zhì)上也是一個(gè)查詢，但它和一般的查詢語(yǔ)句在優(yōu)化時(shí)，還是有所區(qū)別的。這里主要的區(qū)別在于，視圖雖然是由一個(gè)查詢語(yǔ)句定義的，但如果只去分析這個(gè)查詢定義，可能得到的意義不大，因?yàn)橐晥D多數(shù)時(shí)候就不是直接使用，而是在使用前，會(huì)加上where語(yǔ)句，或者放在其它語(yǔ)句中供from子句所使用。下面還是舉個(gè)例子吧，在我的演示數(shù)據(jù)庫(kù)中有個(gè)視圖OrdersView，定義代碼前面有。我們來(lái)看看，如果直接使用這個(gè)視圖，會(huì)有什么樣的執(zhí)行計(jì)劃出來(lái)：

從這個(gè)視圖可以看出，SQL Server會(huì)對(duì)表Orders做全表掃描，應(yīng)該是很低效的。再來(lái)看看下面這個(gè)查詢：

從這個(gè)執(zhí)行計(jì)劃可以看出，與上面那個(gè)就不一樣了。前一個(gè)查詢中對(duì)Orders表的查找是使用【Clustered Index Scan】的方式，而現(xiàn)在在使用【Clustered Index Seek】的方式了，最右邊二個(gè)步驟的成本的百分比也發(fā)生了改變。這樣就足以說(shuō)明，優(yōu)化視圖時(shí)，最好能根據(jù)實(shí)際需求，應(yīng)用不同的過(guò)濾條件，再來(lái)決定如何去優(yōu)化。

再來(lái)一個(gè)由三個(gè)查詢組成的情況來(lái)看看這個(gè)視圖的執(zhí)行計(jì)劃。

select * from dbo.OrdersView where OrderId = 1;
select * from dbo.OrdersView where CustomerId = 1;
select * from dbo.OrdersView where OrderDate >= '2010-12-1' and OrderDate < '2011-12-1';