剛開始用SQL Server的時(shí)候，我沒有用顯示執(zhí)行計(jì)劃來對(duì)查詢進(jìn)行分析。我曾經(jīng)一直認(rèn)為我遞交的SQL查詢都是最優(yōu)的，而忽略了查詢性能究竟如何，從而對(duì)“執(zhí)行計(jì)劃”重視不夠。在我職業(yè)初期，我只要能獲取數(shù)據(jù)就很開心，而不去考慮數(shù)據(jù)是如何返回的，“執(zhí)行計(jì)劃”對(duì)我的查詢作了什么工作。我以為SQL Server會(huì)自己去處理查詢的性能問題的。作為一個(gè)剛進(jìn)入IT行業(yè)或者剛學(xué)到新技術(shù)的軟件工程師，在編寫代碼前不太可能有時(shí)間去學(xué)習(xí)其實(shí)必須掌握的知識(shí)。也許這是因?yàn)镮T行業(yè)競(jìng)爭(zhēng)太激烈的緣故。
隨著時(shí)間的流逝，數(shù)據(jù)庫(kù)容量慢慢變大了。終于某天，客戶對(duì)應(yīng)用系統(tǒng)的查詢性能感到不滿意了。他面帶怒容來找我，抱怨由于查詢太慢，使得他需要花更多的時(shí)間來處理公務(wù)。最初，我建議客戶升級(jí)其系統(tǒng)資源，例如作為臨時(shí)解決方案，增加硬盤容量。雖然硬盤價(jià)格現(xiàn)在很便宜了，但是客戶還是要求我提供一個(gè)永久性的解決方案，檢查和好好調(diào)試查詢語句，來替代那種無休止地升級(jí)資源的臨時(shí)方案。因?yàn)榭蛻舻臐M意度對(duì)IT行業(yè)來說是十分重要的，因此我不得不考慮他的個(gè)人建議。我答應(yīng)他一定會(huì)檢查和調(diào)整我的代碼。
在剛進(jìn)入IT行業(yè)時(shí)，我知道SQL Server的基礎(chǔ)只是。說實(shí)話，向客戶承諾檢查系統(tǒng)的時(shí)候，我還沒有一點(diǎn)入手的頭緒。不過我相信我可以通過GOOGL和BOL來獲取相應(yīng)的信息。
我閱讀了一些關(guān)于SQL Server的書籍，BOL，以及在網(wǎng)上搜索的信息。于是我知道了“顯示執(zhí)行計(jì)劃”的概念。可以在查詢管理器中將該選項(xiàng)的開關(guān)設(shè)置為ON。“顯示執(zhí)行計(jì)劃”是一個(gè)圖形化工具，可以幫助開發(fā)者和DBA分析，優(yōu)化查詢，從而改善性能。
“顯示執(zhí)行計(jì)劃”中不同的任務(wù)具有不同的圖標(biāo)。本文中我主要對(duì)“Table Scan”、“Index Scan”、“Index Seek”、“Cluster Index Scan”以及“Clustered Index Seek”感興趣。也許在以后，可以對(duì)別的任務(wù)進(jìn)行另外介紹。
時(shí)間以F1方程式的速度開始流逝，我覺得該是我全面理解“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”、和“Clustered Index Seek”如何工作的時(shí)候了。
我準(zhǔn)備開始分析并優(yōu)化我的查詢。在分析之前，我想到了一些問題。
-
MS-SQL Server什么時(shí)候使用"Table Scan"?
-
MS-SQL Server什么時(shí)候使用"Index Scan"?
-
MS-SQL Server什么時(shí)候使用"Index Seek"?
-
MS-SQL Server什么時(shí)候使用"Clustered Index Scan"?
-
MS-SQL Server什么時(shí)候使用"Clustered Index Seek"?
?
我主要關(guān)注SQL Server是根據(jù)什么來使用“執(zhí)行計(jì)劃”分析查詢的。在經(jīng)過一段時(shí)間學(xué)習(xí)后，我了解了一些相關(guān)知識(shí)。這些知識(shí)應(yīng)該對(duì)開發(fā)和DBA新手有幫助。于是我決定寫這篇文章，共享我的知識(shí)以幫助別人來理解“執(zhí)行計(jì)劃”。
如果你喜歡，可以慢慢讀完，也可以在SQL Server上，模擬我下面做的實(shí)驗(yàn)。
為了解釋“顯示執(zhí)行計(jì)劃”中的“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”和“Clustered Index Seek”，先創(chuàng)建新表，并添加一些示例數(shù)據(jù)進(jìn)去。下面是創(chuàng)建新表的腳本：
?
|
Create Table PerformanceIssue
(
????PRID UniqueIdentifier NOT NULL,
????PRCode Int NOT NULL,
????PRDesc Varchar (100) NOT NULL
)
ON [PRIMARY]
|
?
表創(chuàng)建后需要添加一些數(shù)據(jù)。使用下面的腳本添加100,000條記錄進(jìn)去。腳本執(zhí)行時(shí)間可能比較長(zhǎng)，請(qǐng)耐心等待其執(zhí)行完畢。
?
|
Declare @Loop Int
Declare @PRID UniqueIdentifier
Declare @ PRDesc Varchar (100)
Set @Loop = 1
Set @ PRDesc = ''
WHILE @Loop <= 100000
BEGIN
?? Set @PRID = NewID()
?? Set @PRDesc = ' PerformanceIssue - ' + Convert( Varchar(10),@Loop )
?? Insert Into PerformanceIssue Values (@PRID, @Loop, @PRDesc)
?? Set @Loop = @Loop + 1
END
|
?
腳本成功執(zhí)行后，數(shù)據(jù)就添加進(jìn)去了。
用下面語句來看一下表的內(nèi)容：
?
|
Select PRID, PRCode, PRDesc
From PerformanceIssue
GO
|
?
由于記錄較長(zhǎng)，因此這里就不列出查詢結(jié)果了。
正如我前面講到，我想解釋何時(shí)會(huì)有“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”和“Clustered Index Seek”。上述哪個(gè)會(huì)改善性能呢？
當(dāng)SQL Server返回?cái)?shù)據(jù)時(shí)，我們想知道SQL Server采取何種掃描機(jī)制來協(xié)助獲取數(shù)據(jù)。首先看一下“Table Scan”。我們想了解什么時(shí)候“Table Scan”會(huì)產(chǎn)生。
選擇“顯示執(zhí)行計(jì)劃”或者使用熱鍵“Alt + Q”來激活“顯示執(zhí)行計(jì)劃”，當(dāng)然也可以用快捷鍵“Ctrl+K”。
看一下執(zhí)行下面查詢后的“執(zhí)行計(jì)劃”結(jié)果。
?
|
Select PRID, PRCode, PRDesc
From PerformanceIssue
GO
|
上面的“執(zhí)行計(jì)劃”中，SQL Server用到了“Table Scan”。我問自己為什么會(huì)有“Table Scan”，SQL Server是根據(jù)什么來使用該方法的。難道是因?yàn)槲蚁氆@取所有100,000條記錄嗎？于是我換了一個(gè)角度進(jìn)行思考，如果來避免查詢中出現(xiàn)“Table Scan”呢？此時(shí)我對(duì)SQL Server的掃描機(jī)制還不是很清楚，那么該如何優(yōu)化查詢呢？下面的SELECT查詢中僅選擇兩列：[PRID, PRCode]。
?
|
Select PRID, PRCode
From PerformanceIssue
GO
|
查詢執(zhí)行后，執(zhí)行計(jì)劃和第一個(gè)查詢一樣。于是將查詢改變?yōu)橹粰z索一個(gè)字段 [PRID]。
?
|
Select PRID
From PerformanceIssue
GO
|
查詢執(zhí)行后，執(zhí)行計(jì)劃仍然和第一個(gè)查詢的相同。對(duì)“Estimated row size”屬性不需要太大關(guān)注。意思我立刻決定只獲取一條記錄，看看執(zhí)行計(jì)劃會(huì)如何。查詢語句如下：
?
|
Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955'
-- PRID GUID value might be differ in your machine
GO
|
?
執(zhí)行完成后，執(zhí)行計(jì)劃顯示： 
查詢?nèi)匀皇褂昧恕癟able Scan”方法來顯示數(shù)據(jù)。
那么，我需要想其它辦法來避免“Table Scan”。首先我想到應(yīng)該給表加上索引。于是我在PRID字段上創(chuàng)建非聚集索引。添加了索引后是否就能避免“Table Scan”？下面我們開始討論關(guān)于“Index Scan”和“Index Seek”的主題。
首先在PRID字段上創(chuàng)建非聚集索引。
?
|
CREATE UNIQUE NONCLUSTERED INDEX UNC_PRID
ON PerformanceIssue (PRID)
GO
|
?
本文假定讀者已經(jīng)知道非聚集索引如何工作的知識(shí)。了解非聚集索引更詳細(xì)的信息，請(qǐng)閱讀BOL相關(guān)主題，也可參看 http://www.sql-server-performance.com/gv_index_data_structures.asp。下面我們?cè)敿?xì)講述“Index Scan”是如何工作的。
執(zhí)行下面語句并查看執(zhí)行計(jì)劃的結(jié)果。
?
|
Select PRID, PRCode, PRDesc
From PerformanceIssue
GO
|
奇怪了，“Table Scan”仍然用到了。為什么SQL Server沒有用到那個(gè)非聚集索引？于是繼續(xù)優(yōu)化查詢語句，選擇檢索兩個(gè)字段 [PRID, PRCode] 。
?
|
Select PRID, PRCode From PerformanceIssue
GO
|
執(zhí)行結(jié)果是和上一個(gè)查詢結(jié)果一摸一樣。于是修改查詢?yōu)橹粰z索一個(gè)字段 [PRID] 。
?
|
Select PRID
From PerformanceIssue
GO
|
?
執(zhí)行計(jì)劃結(jié)果如下： 
“Index Scan”在查詢中被用到了，這很好。很自然，接下來的問題就是“Index Scan”什么時(shí)候會(huì)被用到。字段PRID上有一個(gè)索引，查詢語句中選中的字段為PRID。執(zhí)行查詢的時(shí)候，SQL Server掃描索引頁(yè)，因此用到了“Index Scan”方法。前面的查詢中選擇了有索引的和沒有索引的字段，SQL Server無法使用“Index Scan”。當(dāng)查詢中只選擇有索引的字段時(shí)，SQL Server就使用了“Index Scan”。我不清楚SQL Server底層到底是如何判斷的，不過通過這些試驗(yàn)，我認(rèn)為當(dāng)查詢中只選擇有索引的字段時(shí)，SQL Server就使用“Index Scan”方法。
下面看“Index Seek”方法何時(shí)產(chǎn)生。當(dāng)我看到“Seek”這個(gè)詞時(shí)，第一反應(yīng)就是條件查詢這個(gè)主意。
我嘗試三種不同的帶WHERE語法的查詢語句，以找出那種會(huì)用“Index Seek”。第一種語句如下：
?
|
Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRCode = 8
GO
|
?
結(jié)果顯示，執(zhí)行計(jì)劃使用了“Table Scan”。 
第二種語句如下：
?
|
Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRDesc = ' PerformanceIssue - 8'
GO
|
?
執(zhí)行計(jì)劃仍然使用“Table Scan”方法。 
第三種查詢語句如下：
?
|
Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955'
-- PRID GUID value might be differ in your machine
GO
|
查詢用到了“Index Seek”和“Bookmark Lookup”方法。用到“Index Seek”是因?yàn)閃HERE后面使用帶索引的字段PRID來進(jìn)行過濾。“Bookmark Lookup”方法被用到是因?yàn)椴樵冎羞x擇了沒有索引的字段。如果去掉這兩個(gè)沒有索引的字段，那么“Bookmark Lookup”方法就可以去掉。當(dāng)然如果只返回PRID字段，那么該查詢就沒什么意義了，因?yàn)閃HERE語句后面已經(jīng)給出PRID具體取值了。
我認(rèn)為“Index Seek”在性能改善上比“Index Scan”和“Table Scan”要好，這主要表現(xiàn)在下面幾個(gè)方面：
?
- “Index Seek”不需要對(duì)表和索引頁(yè)進(jìn)行掃描；而“Table Scan”和“Index Scan”需要。
- “Index Seek”利用“WHERE”來過濾獲取的數(shù)據(jù)，這樣比用“Index Scan”和“Table Scan”快很多。
?
當(dāng)我完成這些測(cè)試后，我同事問我一個(gè)很有意思的問題：SQL Server什么時(shí)候使用“Clustered Index Scan”和“Clustered Index Seek”？下面對(duì)“Clustered Index Scan”和“Clustered Index Seek”進(jìn)行實(shí)驗(yàn)。
我決定在PRCode上建一個(gè)聚集索引來測(cè)試“Clustered Index Scan”和“Clustered Index Seek”。
Clustered Index Scan & Clustered Index Seek
下面的腳本刪除PRID字段上的索引，并在PRCode字段上創(chuàng)建聚集索引。
?
|
Drop Index PerformanceIssue.UNC_PRID
GO
CREATE UNIQUE CLUSTERED INDEX UC_PRCode
ON PerformanceIssue( PRCode)
GO
-------------
Clustered index has been created successfully.
Index has been created.
|
?
關(guān)于聚集索引的基礎(chǔ)知識(shí)請(qǐng)查閱聯(lián)機(jī)幫助的相關(guān)主題或者 http://www.sql-server-performance.com/gv_index_data_structures.asp。下面我們將重點(diǎn)放在“Clustered Index Scan”和“Clustered Index Seek”如何被使用上。
執(zhí)行下面查詢語句：
?
|
Select PRID, PRCode, PRDesc
From PerformanceIssue
GO
|
?
查詢執(zhí)行后，可以看到執(zhí)行計(jì)劃中用到了“Clustered Index Scan”。 
下面用三種不同的WHERE方式來試驗(yàn)何時(shí)SQL Server會(huì)用到“Clustered Index Seek”。第一種形式如下：
?
|
Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRDesc = ' PerformanceIssue - 8'
GO
|
?
查詢執(zhí)行后，可以看到執(zhí)行計(jì)劃中用到了“Clustered Index Scan”。 
第二種形式如下：
?
|
Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955'
-- PRID GUID value might be differ in your machine
GO
|
?
查詢執(zhí)行后，發(fā)現(xiàn)執(zhí)行計(jì)劃中用到的仍然是“Clustered Index Scan”。 
第三種形式：
?
|
Select PRID, PRCode, PRDesc
From PerformanceIssue
Where PRCode = 8
GO
|
?
這次執(zhí)行計(jì)劃用到了“Clustered Index Seek”。 
當(dāng)在WHERE后用到PRCode字段的時(shí)候，“Clustered Index Seek”被用到。執(zhí)行計(jì)劃對(duì)聚集索引表檢索的時(shí)候，因?yàn)樵谶x取的字段中，包括沒有索引的字段，所以不用用到“Bookmark Lookup”方法。
我個(gè)人認(rèn)為，從改善性能角度考慮，“Clustered Index Seek”比“Clustered Index Scan”和“Index Seek”要好。
?
- “Clustered Index Seek”不需要掃描整個(gè)聚集索引頁(yè)。
- 和“Index Scan”相比，對(duì)于檢索選擇的字段包含那些沒有索引的字段時(shí)，“Clustered Index Seek”不會(huì)有“Bookmark Lookup”方法出現(xiàn)。
?
通過這些試驗(yàn)，我對(duì)執(zhí)行計(jì)劃的應(yīng)用積累了實(shí)際經(jīng)驗(yàn)。我知道哪種掃描機(jī)制可以提高性能，從而是的客戶滿意。
?