SQL Server 索引中include的魅力（具有包含性列的索引）

http://www.cnblogs.com/gaizai/archive/2010/01/11/1644358.html

 

開文之前首先要講講幾個概念

　　【覆蓋查詢】

　　　　當索引包含查詢引用的所有列時，它通常稱為“覆蓋查詢”。

　　【索引覆蓋】

　　 　　如果返回的資料列就包含於索引的索引值中，或者包含於索引的索引值+叢集索引的索引值中，那麼就不會發生Bookup Lookup，因為找到索引項目，就已經找到所需的資料了，沒有必要再到資料行去找了。這種情況，叫做索引覆蓋；

　　【複合索引】

　　　　和複合索引相對的就是單一索引了，就是索引只包含一個欄位，所以複合索引就是包含兩個或者多個欄位的索引；

　　

　　【非鍵列】

　　　　鍵列就是在索引中所包含的列，當然非鍵列就是該索引之外的列了；

下面就開始今天的主題

　　【摘要1】

　　在 SQL Server 2005 中，可以通過將非鍵列添加到非叢集索引的分葉層級來擴充非叢集索引的功能。通過包含非鍵列，可以建立覆蓋更多查詢的非叢集索引。這是因為非鍵列具有下列優點：
    * 它們可以是不允許作為索引鍵列的資料類型。
    * 在計算索引鍵列數或索引鍵大小時，資料庫引擎不考慮它們。
　　當查詢中的所有列都作為鍵列或非鍵列包含在索引中時，帶有包含性非鍵列的索引可以顯著提高查詢效能。這樣可以實現效能提升，因為查詢最佳化工具可以在索引中找到所有列值；不訪問表或叢集索引資料，從而減少磁碟 I/O 操作。複製代碼

　　說明：第一：只能是針對非叢集索引；第二：比起複合索引是有效能上的提升的，因為索引的大小變小了；

　　【摘要2】

　　鍵列儲存在索引的所有層級中，而非鍵列僅儲存在分葉層級中。

　　說明：這就表現為包含與不包含的關係了。有關索引層級的詳細資料，請參閱表組織和索引組織。

　　【摘要3】

　　使用包含性列以避免大小限制
　　可以將非鍵列包含在非叢集索引中，以避免超過當前索引大小的限制（最大鍵列數為
16，最大索引鍵大小為
900 位元組）。資料庫引擎計算索引鍵列數或索引鍵大小時，不考慮非鍵列。
　　例如，假設要為 AdventureWorks 樣本資料庫的 Document 表中的以下列建立索引：
     Title nvarchar(50)
     Revision nchar(5)
     FileName nvarchar(400)
　　因為 nchar 和 nvarchar 資料類型的每個字元需要 2 個位元組，所以包含這三列的索引將超出
900 位元組的大小限制
10 個位元組 (455
* 2)。使用 CREATE INDEX 語句的 INCLUDE 子句，可以將索引鍵定義為 (Title, Revision)，將 FileName 定義為非鍵列。這樣，索引鍵大小將為
110 個位元組 (55
* 2)，並且索引仍將包含所需的所有列。下面的語句就建立了這樣的索引。複製代碼

　　說明：當你把一個nvarchar(500)的欄位設定為主鍵的時候，你就可以看到不能超出900位元組的提示了。一般來說我們是不太會做這些操作的，所以那個錯誤提示也是不常見的，也許你可能還見過。

　　一個資料頁的大小才8k，所以我們合理的設定每個欄位的大小，不要浪費太多的空間，這樣對查詢也是有好處的，這個include就比較好的的解決了索引和空間的問題，雖然那些include的資料也會佔用空間。

　　雖然可以設定include，但是也盡量不要使用太多的欄位作為索引包含的非鍵列。

　　【摘要4】

　　帶有包含性列的索引準則
　　設計帶有包含性列的非叢集索引時，請考慮下列準則：
    * 在 CREATE INDEX 語句的 INCLUDE 子句中定義非鍵列。
    * 只能對錶或索引檢視表的非叢集索引定義非鍵列。
    * 除 text、ntext 和 image 之外，允許所有資料類型。
    * 精確或不精確的確定性計算資料行都可以是包含性列。有關詳細資料，請參閱為計算資料行建立索引。
    * 與鍵列一樣，只要允許將計算資料行資料類型作為非索引鍵之索引資料行，從 image、ntext 和 text 資料類型派生的計算資料行就可以作為非鍵（包含性）列。
    * 不能同時在 INCLUDE 列表和鍵列列表中指定列名。
    * INCLUDE 列表中的列名不能重複。複製代碼

　　說明：include不能使用在叢集索引中。後面的兩點，這個在實際中很難想象會有這樣的需求要把重複列放到一個索引中。如果有朋友遇到過這樣的需求可以告知一些，不勝感激。那如果有是否可以通過不同的列名（其實儲存是同樣的值）來解決這個問題呢？？

　　【摘要5】

　　列大小準則
    * 必須至少定義一個鍵列。最大非鍵列數為
1023 列。也就是最大的表列數減
1。
    * 索引鍵列（不包括非鍵）必須遵守現有索引大小的限制（最大鍵列數為
16，總索引鍵大小為
900 位元組）。
    * 所有非鍵列的總大小隻受 INCLUDE 子句中所指定列的大小限制；例如，varchar(max) 列限制為
2 GB。複製代碼

　　說明：varchar(max)這樣的定義是在2005之後才有的，所以這些數值也是對2005後的版本才生效的。

　　最大的表列數為：1024

　　最大非鍵列數為：1023

　　【摘要6】

　　修改已定義為包含性列的表列時，要受下列限制：
    * 除非先刪除索引，否則無法從表中刪除非鍵列。
    * 除進行下列更改外，不能對非鍵列進行其他更改：
          o 將列的為空白性從 NOT NULL 改為 NULL。
          o 增加 varchar、nvarchar 或 varbinary 列的長度。
    * 這些列修改限制也適用於索引鍵列。複製代碼

　　說明：這些細小的東西一直沒有注意過。所以要記錄下來，用來“防身”，呵呵。

　　【摘要7】

　　設計建議
　　重新設計索引鍵大小較大的非叢集索引，以便只有用於搜尋和尋找的列為鍵列。將覆蓋查詢的所有其他列設定為包含性非鍵列。這樣，將具有覆蓋查詢所需的所有列，但索引鍵本身較小，而且效率高。複製代碼

　　說明：也就是說把常用的where後面的條件查詢的欄位作為索引的鍵列，而需要返回的欄位就作為索引包含的非鍵列。

　　如果where的是兩個或兩個以上的謂詞的話，這個索引就可以建立為複合索引了。以前天真的認為要返回的欄位只能通過在複合索引中入這些欄位，不管它是否會用來做謂詞。看到這篇文章，才有了豁然開朗的感覺。

　　【摘要8】

USE AdventureWorks;
GO
CREATE INDEX IX_Address_PostalCode      
ON Person.Address (PostalCode)      
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID); 複製代碼

　　說明：這個是使用include的文法，在表的設計中的索引設計中是沒有辦法選擇的；

　　【摘要9】

　　效能注意事項
　　避免添加不必要的列。添加過多的索引列（鍵列或非鍵列）會對效能產生下列影響：
    * 一頁上能容納的索引行將更少。這樣會使 I/O 增加並降低緩衝效率。
    * 需要更多的磁碟空間來儲存索引。特別是，將 varchar(max)、nvarchar(max)、varbinary(max) 或 xml 資料類型添加為非索引鍵之索引資料行會顯著增加磁碟空間要求。這是因為列值被複製到了索引分葉層級。因此，它們既駐留在索引中，也駐留在基表中。
    * 索引維護可能會增加對基礎資料表或索引檢視表執行修改、插入、更新或刪除操作所需的時間。
　　您應該確定修改資料時在查詢效能上的提升是否超過了對效能的影響，以及是否需要額外的磁碟空間要求。有關評估查詢效能的詳細資料，請參閱查詢最佳化。
複製代碼

　　說明：“這是因為列值被複製到了索引分葉層級”這句很好的說明了物理上的儲存結構和原理。

　　【圖片解析】

　　也說明了為什麼不能在叢集索引中建立具有包含性列的索引，因為非叢集索引的葉層是由索引頁而不是由資料頁組成，這就得說到聚集和非叢集索引的的實體儲存體了，叢集索引的順序排序和儲存就是基表的順序和儲存結構。

　

　　【一個例子】

SELECT UserName,Password,RealName,Mobile,Age FROM bw_Users WHERE UserName = XXX AND Age = XX

說明：

1. 這是一個我們很常見的查詢語句，我們如何提高查詢效率呢？
2. 首先我們來看看謂詞，這條語句是通過UserName = XXX AND Age = XX作為條件的，那麼我們就應該建立一個複合式索引，也稱為複合索引，注意索引中的鍵列的位置，先UserName後Age；
3. 其實上面那個是一個非叢集索引，那我們就可以把Password,RealName,Mobile這三列作為索引包含列；
4. 所以，最終就是建立一個以UserName 和 Age做為鍵列、Password,RealName,Mobile作為非鍵列的非叢集索引；
5. 通常來說我們系統的使用者表並不是很大，所以這樣的最佳化起不了很明顯的效果，如果有興趣的可以使用大表進行效能測試；

　　【其它】

1. 有一點我很奇怪，那就是為什麼在修改表的時候，為什麼【包含的列】是停用？只能通過命令來編寫該類索引？
2. 另外一點我想說，微軟的MSDN的確是最好的學習工具，在網路上搜尋出來的東西很多都是重複的，而且說的不全，不過能講的比較簡單、通俗而已。所以有空還是多看看MSDN吧。這句話是對自己說的。呵呵。
   

官方解釋：具有包含性列的索引

-------------------華麗分割線-------------------

作者：聽風吹雨

出處：http://gaizai.cnblogs.com/

著作權：本文著作權歸作者和部落格園共有

轉載：歡迎轉載，不過記得留下買路錢

郵箱：gaizai@126.com

格言：不喜歡是因為你不會 && 因為會所以喜歡