sql server 資料庫最佳化

剛開始用SQL Server的時候，我沒有用顯示執行計畫來對查詢進行分析。我曾經一直認為我遞交的SQL查詢都是最優的，而忽略了查詢效能究竟如何，從而對“執行計畫”重視不夠。在我職業初期，我只要能擷取資料就很開心，而不去考慮資料是如何返回的，“執行計畫”對我的查詢作了什麼工作。我以為SQL Server會自己去處理查詢的效能問題的。作為一個剛進入IT行業或者剛學到新技術的軟體工程師，在編寫代碼前不太可能有時間去學習其實必須掌握的知識。也許這是因為IT行業競爭太激烈的緣故。

隨著時間的流逝，資料庫容量慢慢變大了。終於某天，客戶對應用系統的查詢效能感到不滿意了。他面帶怒容來找我，抱怨由於查詢太慢，使得他需要花更多的時間來處理公務。最初，我建議客戶升級其系統資源，例如作為臨時解決方案，增加硬碟容量。雖然硬碟價格現在很便宜了，但是客戶還是要求我提供一個永久性的解決方案，檢查和好好調試查詢語句，來替代那種無休止地升級資源的臨時方案。因為客戶的滿意度對IT行業來說是十分重要的，因此我不得不考慮他的個人建議。我答應他一定會檢查和調整My Code。

如何入手呢?

在剛進入IT行業時，我知道SQL Server的基礎只是。說實話，向客戶承諾檢查系統的時候，我還沒有一點入手的頭緒。不過我相信我可以通過GOOGL和BOL來擷取相應的資訊。

我閱讀了一些關於SQL Server的書籍，BOL，以及在網上搜尋的資訊。於是我知道了“顯示執行計畫”的概念。可以在查詢管理器中將該選項的開關設定為ON。“顯示執行計畫”是一個圖形化工具，可以協助開發人員和DBA分析，最佳化查詢，從而改善效能。

“顯示執行計畫”中不同的任務具有不同的表徵圖。本文中我主要對“Table Scan”、“Index Scan”、“Index Seek”、“Cluster Index Scan”以及“Clustered Index Seek”感興趣。也許在以後，可以對別的任務進行另外介紹。

時間以F1方程式的速度開始流逝，我覺得該是我全面理解“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”、和“Clustered Index Seek”如何工作的時候了。

我準備開始分析並最佳化我的查詢。在分析之前，我想到了一些問題。

• MS-SQL Server什麼時候使用"Table Scan"?
• MS-SQL Server什麼時候使用"Index Scan"?
• MS-SQL Server什麼時候使用"Index Seek"?
• MS-SQL Server什麼時候使用"Clustered Index Scan"?
• MS-SQL Server什麼時候使用"Clustered Index Seek"?

我主要關注SQL Server是根據什麼來使用“執行計畫”分析查詢的。在經過一段時間學習後，我瞭解了一些相關知識。這些知識應該對開發和DBA新手有協助。於是我決定寫這篇文章，共用我的知識以協助別人來理解“執行計畫”。

如果你喜歡，可以慢慢讀完，也可以在SQL Server上，類比我下面做的實驗。

開始入手

為瞭解釋“顯示執行計畫”中的“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”和“Clustered Index Seek”，先建立新表，並添加一些樣本資料進去。下面是建立新表的指令碼：

Create Table PerformanceIssue (     PRID UniqueIdentifier NOT NULL,     PRCode Int NOT NULL,     PRDesc Varchar (100) NOT NULL ) ON [PRIMARY]

表建立後需要添加一些資料。使用下面的指令碼添加100,000條記錄進去。指令碼執行時間可能比較長，請耐心等待其執行完畢。

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

指令碼成功執行後，資料就添加進去了。

用下面語句來看一下表的內容：

Select PRID, PRCode, PRDesc From PerformanceIssue GO

由於記錄較長，因此這裡就不列出查詢結果了。

正如我前面講到，我想解釋何時會有“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”和“Clustered Index Seek”。上述哪個會改善效能呢？

當SQL Server返回資料時，我們想知道SQL Server採取何種掃描機制來協助擷取資料。首先看一下“Table Scan”。我們想瞭解什麼時候“Table Scan”會產生。

選擇“顯示執行計畫”或者使用熱鍵“Alt + Q”來啟用“顯示執行計畫”，當然也可以用快速鍵“Ctrl+K”。

看一下執行下面查詢後的“執行計畫”結果。

Select PRID, PRCode, PRDesc From PerformanceIssue GO

上面的“執行計畫”中，SQL Server用到了“Table Scan”。我問自己為什麼會有“Table Scan”，SQL Server是根據什麼來使用該方法的。難道是因為我想擷取所有100,000條記錄嗎？於是我換了一個角度進行思考，如果來避免查詢中出現“Table Scan”呢？此時我對SQL Server的掃描機制還不是很清楚，那麼該如何最佳化查詢呢？下面的SELECT查詢中僅選擇兩列：[PRID, PRCode]。

Select PRID, PRCode From PerformanceIssue GO

查詢執行後，執行計畫和第一個查詢一樣。於是將查詢改變為只檢索一個欄位 [PRID]。

Select PRID From PerformanceIssue GO

查詢執行後，執行計畫仍然和第一個查詢的相同。對“Estimated row size”屬性不需要太大關注。意思我立刻決定只擷取一條記錄，看看執行計畫會如何。查詢語句如下：

Select PRID, PRCode, PRDesc From PerformanceIssue Where PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955' -- PRID GUID value might be differ in your machine GO

執行完成後，執行計畫顯示：

查詢仍然使用了“Table Scan”方法來顯示資料。

那麼，我需要想其它辦法來避免“Table Scan”。首先我想到應該給表加上索引。於是我在PRID欄位上建立非叢集索引。添加了索引後是否就能避免“Table Scan”？下面我們開始討論關於“Index Scan”和“Index Seek”的主題。

Index Scan 和 Index Seek

首先在PRID欄位上建立非叢集索引。

CREATE UNIQUE NONCLUSTERED INDEX UNC_PRID ON PerformanceIssue (PRID) GO

本文假定讀者已經知道非叢集索引如何工作的知識。瞭解非叢集索引更詳細的資訊，請閱讀BOL相關主題，也可參看 http://www.sql-server-performance.com/gv_index_data_structures.asp。下面我們詳細講述“Index Scan”是如何工作的。

執行下面語句並查看執行計畫的結果。

Select PRID, PRCode, PRDesc From PerformanceIssue GO

奇怪了，“Table Scan”仍然用到了。為什麼SQL Server沒有用到那個非叢集索引？於是繼續最佳化查詢語句，選擇檢索兩個欄位 [PRID, PRCode] 。

Select PRID, PRCode From PerformanceIssue GO

執行結果是和上一個查詢結果一摸一樣。於是修改查詢為只檢索一個欄位 [PRID] 。

Select PRID From PerformanceIssue GO

執行計畫結果如下：

“Index Scan”在查詢中被用到了，這很好。很自然，接下來的問題就是“Index Scan”什麼時候會被用到。欄位PRID上有一個索引，查詢語句中選中的欄位為PRID。執行查詢的時候，SQL Server掃描索引頁，因此用到了“Index Scan”方法。前面的查詢中選擇了有索引的和沒有索引的欄位，SQL Server無法使用“Index Scan”。當查詢中只選擇有索引的欄位時，SQL Server就使用了“Index Scan”。我不清楚SQL Server底層到底是如何判斷的，不過通過這些實驗，我認為當查詢中只選擇有索引的欄位時，SQL Server就使用“Index Scan”方法。

下面看“Index Seek”方法何時產生。當我看到“Seek”這個詞時，第一反應就是條件查詢這個主意。

我嘗試三種不同的帶WHERE文法的查詢語句，以找出那種會用“Index Seek”。第一種語句如下：

Select PRID, PRCode, PRDesc From PerformanceIssue Where PRCode = 8 GO

結果顯示，執行計畫使用了“Table Scan”。

第二種語句如下：

Select PRID, PRCode, PRDesc From PerformanceIssue Where PRDesc = ' PerformanceIssue - 8' GO

執行計畫仍然使用“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”是因為WHERE後面使用帶索引的欄位PRID來進行過濾。“Bookmark Lookup”方法被用到是因為查詢中選擇了沒有索引的欄位。如果去掉這兩個沒有索引的欄位，那麼“Bookmark Lookup”方法就可以去掉。當然如果只返回PRID欄位，那麼該查詢就沒什麼意義了，因為WHERE語句後面已經給出PRID具體取值了。

我認為“Index Seek”在效能改善上比“Index Scan”和“Table Scan”要好，這主要表現在下面幾個方面：

1. “Index Seek”不需要對錶和索引頁進行掃描；而“Table Scan”和“Index Scan”需要。
2. “Index Seek”利用“WHERE”來過濾擷取的資料，這樣比用“Index Scan”和“Table Scan”快很多。

當我完成這些測試後，我同事問我一個很有意思的問題：SQL Server什麼時候使用“Clustered Index Scan”和“Clustered Index Seek”？下面對“Clustered Index Scan”和“Clustered Index Seek”進行實驗。

我決定在PRCode上建一個叢集索引來測試“Clustered Index Scan”和“Clustered Index Seek”。

Clustered Index Scan & Clustered Index Seek

下面的指令碼刪除PRID欄位上的索引，並在PRCode欄位上建立叢集索引。

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.

關於叢集索引的基礎知識請查閱線上說明的相關主題或者 http://www.sql-server-performance.com/gv_index_data_structures.asp。下面我們將重點放在“Clustered Index Scan”和“Clustered Index Seek”如何被使用上。

執行下面查詢語句：

Select PRID, PRCode, PRDesc From PerformanceIssue GO

查詢執行後，可以看到執行計畫中用到了“Clustered Index Scan”。

下面用三種不同的WHERE方式來實驗何時SQL Server會用到“Clustered Index Seek”。第一種形式如下：

Select PRID, PRCode, PRDesc From PerformanceIssue Where PRDesc = ' PerformanceIssue - 8' GO

查詢執行後，可以看到執行計畫中用到了“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

查詢執行後，發現執行計畫中用到的仍然是“Clustered Index Scan”。

第三種形式：

Select PRID, PRCode, PRDesc From PerformanceIssue Where PRCode = 8 GO

這次執行計畫用到了“Clustered Index Seek”。

當在WHERE後用到PRCode欄位的時候，“Clustered Index Seek”被用到。執行計畫對叢集索引表檢索的時候，因為在選取的欄位中，包括沒有索引的欄位，所以不用用到“Bookmark Lookup”方法。

我個人認為，從改善效能角度考慮，“Clustered Index Seek”比“Clustered Index Scan”和“Index Seek”要好。

1. “Clustered Index Seek”不需要掃描整個叢集索引頁。
2. 和“Index Scan”相比，對於檢索選擇的欄位包含那些沒有索引的欄位時，“Clustered Index Seek”不會有“Bookmark Lookup”方法出現。

通過這些實驗，我對執行計畫的應用積累了實際經驗。我知道哪種掃描機制可以提高效能，從而是的客戶滿意。