SQL Server索引中include的魅力
開文之前首先要講講幾個(gè)概念
什么是具有包含性列的索引？請(qǐng)看官方解釋：http://msdn.microsoft.com/zh-cn/library/ms190806%28SQL.90%29.aspx
【覆蓋查詢】
當(dāng)索引包含查詢引用的所有列時(shí)，它通常稱為“覆蓋查詢”。
【索引覆蓋】
如果返回的數(shù)據(jù)列就包含于索引的鍵值中，或者包含于索引的鍵值+聚集索引的鍵值中，那么就不會(huì)發(fā)生Bookup Lookup，因?yàn)檎业剿饕?xiàng)，就已經(jīng)找到所需的數(shù)據(jù)了，沒有必要再到數(shù)據(jù)行去找了。這種情況，叫做索引覆蓋;
【復(fù)合索引】
和復(fù)合索引相對(duì)的就是單一索引了，就是索引只包含一個(gè)字段，所以復(fù)合索引就是包含兩個(gè)或者多個(gè)字段的索引;
【非鍵列】
鍵列就是在索引中所包含的列，當(dāng)然非鍵列就是該索引之外的列了;
下面就開始今天的主題
【摘要1】
在 SQL Server 2005 中，可以通過(guò)將非鍵列添加到非聚集索引的葉級(jí)別來(lái)擴(kuò)展非聚集索引的功能。通過(guò)包含非鍵列，可以創(chuàng)建覆蓋更多查詢的非聚集索引。這是因?yàn)榉擎I列具有下列優(yōu)點(diǎn)：
* 它們可以是不允許作為索引鍵列的數(shù)據(jù)類型。
* 在計(jì)算索引鍵列數(shù)或索引鍵大小時(shí)，數(shù)據(jù)庫(kù)引擎不考慮它們。
當(dāng)查詢中的所有列都作為鍵列或非鍵列包含在索引中時(shí)，帶有包含性非鍵列的索引可以顯著提高查詢性能。這樣可以實(shí)現(xiàn)性能提升，因?yàn)椴樵儍?yōu)化器可以在索引中找到所有列值;不訪問表或聚集索引數(shù)據(jù)，從而減少磁盤 I/O 操作。
說(shuō)明：***：只能是針對(duì)非聚集索引;第二：比起復(fù)合索引是有性能上的提升的，因?yàn)樗饕拇笮∽冃×?
【摘要2】
鍵列存儲(chǔ)在索引的所有級(jí)別中，而非鍵列僅存儲(chǔ)在葉級(jí)別中。
說(shuō)明：這就表現(xiàn)為包含與不包含的關(guān)系了。有關(guān)索引級(jí)別的詳細(xì)信息，請(qǐng)參閱表組織和索引組織。
【摘要3】
使用包含性列以避免大小限制
可以將非鍵列包含在非聚集索引中，以避免超過(guò)當(dāng)前索引大小的限制(***鍵列數(shù)為 16，***索引鍵大小為 900 字節(jié))。數(shù)據(jù)庫(kù)引擎計(jì)算索引鍵列數(shù)或索引鍵大小時(shí)，不考慮非鍵列。
例如，假設(shè)要為 AdventureWorks 示例數(shù)據(jù)庫(kù)的 Document 表中的以下列建立索引：
Title nvarchar(50)
Revision nchar(5)
FileName nvarchar(400)
因?yàn)?nchar 和 nvarchar 數(shù)據(jù)類型的每個(gè)字符需要 2 個(gè)字節(jié)，所以包含這三列的索引將超出 900 字節(jié)的大小限制 10 個(gè)字節(jié) (455 * 2)。使用 CREATE INDEX 語(yǔ)句的 INCLUDE 子句，可以將索引鍵定義為 (Title, Revision)，將 FileName 定義為非鍵列。這樣，索引鍵大小將為 110 個(gè)字節(jié) (55 * 2)，并且索引仍將包含所需的所有列。下面的語(yǔ)句就創(chuàng)建了這樣的索引。
說(shuō)明：當(dāng)你把一個(gè)nvarchar(500)的字段設(shè)置為主鍵的時(shí)候，你就可以看到不能超出900字節(jié)的提示了。一般來(lái)說(shuō)我們是不太會(huì)做這些操作的，所以那個(gè)錯(cuò)誤提示也是不常見的，也許你可能還見過(guò)。
一個(gè)數(shù)據(jù)頁(yè)的大小才8k，所以我們合理的設(shè)置每個(gè)字段的大小，不要浪費(fèi)太多的空間，這樣對(duì)查詢也是有好處的，這個(gè)include就比較好的的解決了索引和空間的問題，雖然那些include的數(shù)據(jù)也會(huì)占用空間。
雖然可以設(shè)置include，但是也盡量不要使用太多的字段作為索引包含的非鍵列。
【摘要4】
帶有包含性列的索引準(zhǔn)則
設(shè)計(jì)帶有包含性列的非聚集索引時(shí)，請(qǐng)考慮下列準(zhǔn)則：
* 在 CREATE INDEX 語(yǔ)句的 INCLUDE 子句中定義非鍵列。
* 只能對(duì)表或索引視圖的非聚集索引定義非鍵列。
* 除 text、ntext 和 image 之外，允許所有數(shù)據(jù)類型。
* 精確或不精確的確定性計(jì)算列都可以是包含性列。有關(guān)詳細(xì)信息，請(qǐng)參閱為計(jì)算列創(chuàng)建索引。
* 與鍵列一樣，只要允許將計(jì)算列數(shù)據(jù)類型作為非鍵索引列，從 image、ntext 和 text 數(shù)據(jù)類型派生的計(jì)算列就可以作為非鍵(包含性)列。
* 不能同時(shí)在 INCLUDE 列表和鍵列列表中指定列名。
* INCLUDE 列表中的列名不能重復(fù)。
說(shuō)明：include不能使用在聚集索引中。后面的兩點(diǎn)，這個(gè)在實(shí)際中很難想象會(huì)有這樣的需求要把重復(fù)列放到一個(gè)索引中。如果有朋友遇到過(guò)這樣的需求可以告知一些，不勝感激。那如果有是否可以通過(guò)不同的列名(其實(shí)保存是同樣的值)來(lái)解決這個(gè)問題呢??
#p#
【摘要5】
列大小準(zhǔn)則
* 必須至少定義一個(gè)鍵列。***非鍵列數(shù)為 1023 列。也就是***的表列數(shù)減 1。
* 索引鍵列(不包括非鍵)必須遵守現(xiàn)有索引大小的限制(***鍵列數(shù)為 16，總索引鍵大小為 900 字節(jié))。
* 所有非鍵列的總大小只受 INCLUDE 子句中所指定列的大小限制;例如，varchar(max) 列限制為 2 GB。
說(shuō)明：varchar(max)這樣的定義是在2005之后才有的，所以這些數(shù)值也是對(duì)2005后的版本才生效的。
***的表列數(shù)為：1024
***非鍵列數(shù)為：1023
【摘要6】
修改已定義為包含性列的表列時(shí)，要受下列限制：
* 除非先刪除索引，否則無(wú)法從表中刪除非鍵列。
* 除進(jìn)行下列更改外，不能對(duì)非鍵列進(jìn)行其他更改：
o 將列的為空性從 NOT NULL 改為 NULL。
o 增加 varchar、nvarchar 或 varbinary 列的長(zhǎng)度。
* 這些列修改限制也適用于索引鍵列。
說(shuō)明：這些細(xì)小的東西一直沒有注意過(guò)。所以要記錄下來(lái)，用來(lái)“防身”，呵呵。
【摘要7】
設(shè)計(jì)建議
重新設(shè)計(jì)索引鍵大小較大的非聚集索引，以便只有用于搜索和查找的列為鍵列。將覆蓋查詢的所有其他列設(shè)置為包含性非鍵列。這樣，將具有覆蓋查詢所需的所有列，但索引鍵本身較小，而且效率高。
說(shuō)明：也就是說(shuō)把常用的where后面的條件查詢的字段作為索引的鍵列，而需要返回的字段就作為索引包含的非鍵列。
如果where的是兩個(gè)或兩個(gè)以上的謂詞的話，這個(gè)索引就可以創(chuàng)建為復(fù)合索引了。以前天真的認(rèn)為要返回的字段只能通過(guò)在復(fù)合索引中入這些字段，不管它是否會(huì)用來(lái)做謂詞?？吹竭@篇文章，才有了豁然開朗的感覺。
【摘要8】
USE AdventureWorks;
GO
CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
說(shuō)明：這個(gè)是使用include的語(yǔ)法，在表的設(shè)計(jì)中的索引設(shè)計(jì)中是沒有辦法選擇的;
【摘要9】
性能注意事項(xiàng)
避免添加不必要的列。添加過(guò)多的索引列(鍵列或非鍵列)會(huì)對(duì)性能產(chǎn)生下列影響：
* 一頁(yè)上能容納的索引行將更少。這樣會(huì)使 I/O 增加并降低緩存效率。
* 需要更多的磁盤空間來(lái)存儲(chǔ)索引。特別是，將 varchar(max)、nvarchar(max)、varbinary(max) 或 xml 數(shù)據(jù)類型添加為非鍵索引列會(huì)顯著增加磁盤空間要求。這是因?yàn)榱兄当粡?fù)制到了索引葉級(jí)別。因此，它們既駐留在索引中，也駐留在基表中。
* 索引維護(hù)可能會(huì)增加對(duì)基礎(chǔ)表或索引視圖執(zhí)行修改、插入、更新或刪除操作所需的時(shí)間。
您應(yīng)該確定修改數(shù)據(jù)時(shí)在查詢性能上的提升是否超過(guò)了對(duì)性能的影響，以及是否需要額外的磁盤空間要求。有關(guān)評(píng)估查詢性能的詳細(xì)信息，請(qǐng)參閱查詢優(yōu)化。
說(shuō)明：“這是因?yàn)榱兄当粡?fù)制到了索引葉級(jí)別”這句很好的說(shuō)明了物理上的存儲(chǔ)結(jié)構(gòu)和原理。
【圖片解析】
上圖也說(shuō)明了為什么不能在聚集索引中建立具有包含性列的索引，因?yàn)榉蔷奂饕娜~層是由索引頁(yè)而不是由數(shù)據(jù)頁(yè)組成，這就得說(shuō)到聚集和非聚集索引的的物理存儲(chǔ)了，聚集索引的順序排序和存儲(chǔ)就是基表的順序和存儲(chǔ)結(jié)構(gòu)。
【一個(gè)例子】
SELECT UserName,Password,RealName,Mobile,Age FROM bw_Users WHERE UserName = XXX AND Age = XX
說(shuō)明：
- 這是一個(gè)我們很常見的查詢語(yǔ)句，我們?nèi)绾翁岣卟樵冃誓?
- 首先我們來(lái)看看謂詞，這條語(yǔ)句是通過(guò)UserName = XXX AND Age = XX作為條件的，那么我們就應(yīng)該建立一個(gè)組合索引，也稱為復(fù)合索引，注意索引中的鍵列的位置，先UserName后Age;
- 其實(shí)上面那個(gè)是一個(gè)非聚集索引，那我們就可以把Password,RealName,Mobile這三列作為索引包含列;
- 所以，最終就是建立一個(gè)以UserName 和 Age做為鍵列、Password,RealName,Mobile作為非鍵列的非聚集索引;
- 通常來(lái)說(shuō)我們系統(tǒng)的用戶表并不是很大，所以這樣的優(yōu)化起不了很明顯的效果，如果有興趣的可以使用大表進(jìn)行性能測(cè)試;
【其它】
有一點(diǎn)我很奇怪，那就是為什么在修改表的時(shí)候，為什么【包含的列】是不可用的?只能通過(guò)命令來(lái)編寫該類索引?
另外一點(diǎn)我想說(shuō)，微軟的MSDN的確是***的學(xué)習(xí)工具，在網(wǎng)絡(luò)上搜索出來(lái)的東西很多都是重復(fù)的，而且說(shuō)的不全，不過(guò)能講的比較簡(jiǎn)單、通俗而已。所以有空還是多看看MSDN吧。這句話是對(duì)自己說(shuō)的。呵呵。
原文鏈接：http://www.cnblogs.com/gaizai/archive/2010/01/11/1644358.html
【編輯推薦】
