SQL Server索引中include的魅力[具有包含性列的索引]

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

【覆蓋查詢】

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

【索引覆蓋】

如果返回的數據列就包含于索引的鍵值中，或者包含于索引的鍵值+聚集索引的鍵值中，那么就不會發生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/

生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈