SQLServer索引調優實踐(2) – 索引覆蓋

繼續上一篇文章，繼續SQLServer索引調優實踐。這次探討一下索引覆蓋 - SQL Server主要使用索引去查詢你需要的資料，當索引包括所有的你請求查詢的欄位，SQL Server將不需要去在表中查詢。這個概念稱做“索引覆蓋”。

SQLServer2005的Non-clustered INDEX增加了一個“包含列(included column) ”選項。在 SQL Server 2005 中，可以通過將非鍵列添加到非叢集索引的分葉層級來擴充非叢集索引的功能。通過包含非鍵列，可以建立覆蓋更多查詢的非叢集索引。當查詢中的所有列都作為鍵列或非鍵列包含在索引中時，帶有包含性非鍵列的索引可以顯著提高查詢效能。這樣可以實現效能提升，因為查詢最佳化工具可以在索引中找到所有列值；不訪問表或叢集索引資料，從而減少磁碟 I/O 操作。

但應避免添加不必要的列。添加過多的索引列（鍵列或非鍵列）會對效能產生不良影響，應該合理使用。和Clustered INDEX，或者複合式索引，結合使用，擴大索引覆蓋，但不大可能所有列都有索引覆蓋，磁碟開銷和資料insert updat時索引的重新計算的時間開銷是巨大的。總之，合理的索引設計是建立在對各種查詢的分析和預測上的，只有正確地使索引與程式結合起來,才能產生最佳的最佳化方案。

繼續實踐，先建個實驗表 Table1：

建兩個索引：

1. 主鍵ID是Clustered INDEX

2. 非聚簇索引Non-Clustered INDEX建立在Age列上，包含列：Count。

CREATE NONCLUSTERED INDEX [cnt] ON [dbo].[table1]
(
    [Age] ASC
)
INCLUDE ( [Count])
ON [PRIMARY]

我們的測試SQL語句是：從10萬條記錄中取出4條記錄，兩種寫法

1. SELECT * FROM table1 WHERE age < 100;
2. SELECT count FROM table1 WHERE age < 100;

看看運行效率如何：

磁碟IO和時間：

實際執行計畫：

效能居然相差20多倍。為什嗎？

原來第二句Select Count在索引覆蓋範圍內，因為查詢最佳化工具可以在索引中找到所有列值；不訪問表或叢集索引資料，從而減少磁碟 I/O 操作。而第一句Select * 選擇了所有欄位，其中有一個欄位Name不在索引覆蓋範圍內（既不在聚簇索引列，也不在非聚簇索引覆蓋列內），SQL Server可以在同一個查詢中為一個表使用多個索引，並可以合并多個索引（使用聯結演算法），以便搜尋索引鍵共同覆蓋一個查詢。查詢分析最佳化器會自動進行選擇, 上述執行計畫就是最佳化的結果，依然比第二個index seek慢了20倍。

然後我又把sql改了一下，變成從10萬條記錄中取得大部分資料( 返回99900條)，小於符號改成大於符號：

1. SELECT * FROM table1 WHERE age > 100;
2. SELECT count FROM table1 WHERE age > 100;

看看結果：

磁碟IO和時間：

實際執行計畫：

依然是第二句索引覆蓋的快，這是毋庸置疑的。但第一句執行計畫有所不同，SQLServer查詢分析最佳化器選擇了不同的策略，改為聚簇索引掃描。上面說過了，SQL Server可以在同一個查詢中為一個表使用多個索引，並可以合并多個索引（使用聯結演算法），以便搜尋索引鍵共同覆蓋一個查詢。查詢分析最佳化器會自動進行選擇, 上述執行計畫就是最佳化的結果。

為何結果集較小和結果集較大SQLServer選擇的索引方案不同？

(From園友 高強：當 WHERE age < 100時，由於結果集記錄數較小，SQL SERVER 先INDEX SEEK（得出一個較小的結果集） 然後和clustered index seek 嵌套迴圈。而當WHERE age > 100時，由於記錄數比較多，所以SQL SERVER 認為直接根據叢集索引葉級頁面鏈表掃描頁面得出結果更快。不管怎樣，最終目的就是在相同結果集情況下，儘可能減少邏輯IO。)

看到這兒，恐怕喜歡用Select*的同學也要節制一下使用了，有時候SQLServer中Select*代價是很高的。當然類似這種SQL是沒有問題的，（where exists (select * from ...)），因為SQLServer查詢分析最佳化器會聰明的知道此Select*非彼Select*。

資料庫是一個很複雜的系統，即使你不是資料庫專家，是應用開發人員，知道一點SQLServer內部更多的東西會有好處，而合理的索引設計是建立在對各種查詢的分析和預測上的，只有正確地使索引與程式結合起來, 才能產生最佳的最佳化方案。

相關閱讀：

• SQLServer索引調優實踐

或許您對以下文章有興趣：

• 程式員辦網站創業，幾個問題你想好了嗎？
• CTO談豆瓣網和校內網技術架構變遷
• AJAX延遲非同步載入側邊欄+伺服器端緩衝AJAX輸出
• 二級下拉式功能表被遮住，css設定z-index在ie下沒作用的問題解決辦法
• 簡單JS實現走馬燈效果的文字(無需jQuery)
• jQuery和ExtJS的timeOut逾時設定和event事件處理