包含列索引
概述
包含列索引也是非聚集索引,索引結(jié)構(gòu)跟聚集索引結(jié)構(gòu)是一樣,有一點(diǎn)不同的地方就是包含列索引的非鍵列只存儲(chǔ)在葉子節(jié)點(diǎn);包含列索引的列分為鍵列和非鍵列,所謂的非鍵列就是INCLUDE中包含的列,至少需要有一個(gè)鍵列,且鍵列和非鍵列不允許重復(fù),非鍵列最多允許1023列(也就是表的最多列-1),由于 索引鍵列(不包括非鍵)必須遵守現(xiàn)有索引大小的限制(最大鍵列數(shù)為 16,總索引鍵大小為 900 字節(jié))的要求所以引進(jìn)了包含列索引。
正文
- 創(chuàng)建包含列索引
?
-- --創(chuàng)建表 CREATE TABLE [ dbo ] . [ Customers ] ( [ custid ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL , [ companyname ] [ nvarchar ] ( 40 ) NOT NULL , [ contactname ] [ nvarchar ] ( 30 ) NOT NULL , [ contacttitle ] [ nvarchar ] ( 400 ) NOT NULL , CONSTRAINT [ PK_Customers ] PRIMARY KEY CLUSTERED ( [ custid ] ASC ) WITH (PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] ) ON [ PRIMARY ] -- --創(chuàng)建包含列索引 CREATE NONCLUSTERED INDEX [ IX1_Customers ] ON [ dbo ] . [ Customers ] ( [ companyname ] ASC ) INCLUDE ( [ contactname ] )
WITH (STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] GO
這里的鍵列就是: companyname
非鍵列就是:contactname
非鍵列具有下列優(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 操作。(當(dāng)索引包含查詢引用的所有列時(shí),它通常稱為“覆蓋查詢”。)
- 創(chuàng)建覆蓋查詢
?覆蓋查詢就是創(chuàng)建的索引列包含查詢所引用的所有列時(shí)
- 查詢列都設(shè)為鍵列
當(dāng)我們的SELECT查詢是這樣的 SELECT [ companyname ] , [ contactname ] , [ contacttitle ] FROM [ chenmh ] . [ dbo ] . [ Customers ] where companyname = ' 好孩子 ' -- -這時(shí)我們選擇將索引列都包含在索引建列中 CREATE NONCLUSTERED INDEX [ IX2_Customers ] ON [ dbo ] . [ Customers ] ( [ companyname ] ASC , [ contactname ] ASC , [ contacttitle ] ASC ) WITH (STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] GO
將會(huì)彈出警告:警告! 最大鍵長(zhǎng)度為 900 個(gè)字節(jié)。索引 'IX2_Customers' 的最大長(zhǎng)度為 940 個(gè)字節(jié)。對(duì)于某些大值組合,插入/更新操作將失敗。
由于三個(gè)字段都是NVARCHAR字段類型,每個(gè)字符需要 2 個(gè)字節(jié),(40+30+400)*2=940個(gè)字節(jié),大于900字節(jié),這時(shí)我們可以將 [contactname] ,[contacttitle]包含在非鍵列中
2.將大數(shù)據(jù)類型設(shè)為非鍵列
?
CREATE NONCLUSTERED INDEX [ IX3_Customers ] ON [ dbo ] . [ Customers ] ( [ companyname ] ASC ) INCLUDE ( [ contactname ] , [ contacttitle ] ) WITH (STATISTICS_NORECOMPUTE = OFF , SORT_IN_TEMPDB = OFF , IGNORE_DUP_KEY = OFF , DROP_EXISTING = OFF , ONLINE = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON ) ON [ PRIMARY ] GO
這時(shí)索引鍵大小所占字符就只有40*2=80個(gè)字節(jié),同時(shí)索引也是覆蓋索引,索引的列包含查詢用到的列,當(dāng)我們查詢數(shù)據(jù)時(shí)直接在索引頁(yè)中查找數(shù)據(jù)就可以,不需要訪問數(shù)據(jù)頁(yè),減少磁盤IO,提高性能
?
帶有包含列的索引準(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ù)。
-
在 CREATE INDEX 語(yǔ)句的 INCLUDE 子句中定義非鍵列。
列大小準(zhǔn)則
-
-
必須至少定義一個(gè)鍵列。最大非鍵列數(shù)為 1023 列。也就是最大的表列數(shù)減 1。
-
索引鍵列(不包括非鍵)必須遵守現(xiàn)有索引大小的限制(最大鍵列數(shù)為 16,總索引鍵大小為 900 字節(jié))。
- 所有非鍵列的總大小只受 INCLUDE 子句中所指定列的大小限制;例如, varchar(max) 列限制為 2 GB。
-
必須至少定義一個(gè)鍵列。最大非鍵列數(shù)為 1023 列。也就是最大的表列數(shù)減 1。
列修改準(zhǔn)則
修改已定義為包含列的表列時(shí),要受下列限制:
-
- 除非先刪除索引,否則無(wú)法從表中刪除非鍵列。
-
除進(jìn)行下列更改外,不能對(duì)非鍵列進(jìn)行其他更改:
- 將列的為空性從 NOT NULL 改為 NULL。
- 增加 varchar 、 nvarchar 或 varbinary 列的長(zhǎng)度。
注意事項(xiàng)
- 鍵列的大小盡量小,有利用提高效率
- 將用于搜索和查找的列為鍵列,鍵列盡量不要包含沒必要的列。(例如上面建立的覆蓋查詢列,雖然 companyname+ contactname加起來(lái)作為鍵列也不會(huì)超過(guò)900字節(jié),但是這樣鍵大小就變大了,降低了查詢效率)
-
避免添加不必要的列。添加過(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è)上能容納的索引行將更少。這樣會(huì)使 I/O 增加并降低緩存效率。
?
總結(jié)
?
?
如果您覺得文章對(duì)你有幫助,活動(dòng)活動(dòng)你的手指麻煩給個(gè)推薦;這也是對(duì)我一種鼓勵(lì),在此表示感謝。
備注: ??? 作者: 沉寂的石頭 ??? 博客: http://www.cnblogs.com/chenmh 歡迎大家轉(zhuǎn)載,但轉(zhuǎn)載時(shí)必須注明文章來(lái)源,且在文章開頭明顯處給明鏈接,否則保留追究責(zé)任的權(quán)利。 歡迎大家拍磚 |
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號(hào)聯(lián)系: 360901061
您的支持是博主寫作最大的動(dòng)力,如果您喜歡我的文章,感覺我的文章對(duì)您有幫助,請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點(diǎn)擊下面給點(diǎn)支持吧,站長(zhǎng)非常感激您!手機(jī)微信長(zhǎng)按不能支付解決辦法:請(qǐng)將微信支付二維碼保存到相冊(cè),切換到微信,然后點(diǎn)擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
