包含列索引
概述
包含列索引也是非聚集索引，索引結(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ì)您有幫助就好】元
