包含列索引
概述
包含列索引也是非聚集索引，索引結(jié)構(gòu)跟聚集索引結(jié)構(gòu)是一樣，有一點不同的地方就是包含列索引的非鍵列只存儲在葉子節(jié)點；包含列索引的列分為鍵列和非鍵列，所謂的非鍵列就是INCLUDE中包含的列，至少需要有一個鍵列，且鍵列和非鍵列不允許重復(fù)，非鍵列最多允許1023列（也就是表的最多列-1），由于 索引鍵列（不包括非鍵）必須遵守現(xiàn)有索引大小的限制（最大鍵列數(shù)為 16，總索引鍵大小為 900 字節(jié)）的要求所以引進了包含列索引。
正文
- 創(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)點：
-
它們可以是不允許作為索引鍵列的數(shù)據(jù)類型。
-
在計算索引鍵列數(shù)或索引鍵大小時，數(shù)據(jù)庫引擎不考慮它們。
?
當(dāng)查詢中的所有列都作為鍵列或非鍵列包含在索引中時，帶有包含性非鍵列的索引可以顯著提高查詢性能。這樣可以實現(xiàn)性能提升，因為查詢優(yōu)化器可以在索引中找到所有列值；不訪問表或聚集索引數(shù)據(jù)，從而減少磁盤 I/O 操作。（當(dāng)索引包含查詢引用的所有列時，它通常稱為“覆蓋查詢”。）
- 創(chuàng)建覆蓋查詢
?覆蓋查詢就是創(chuàng)建的索引列包含查詢所引用的所有列時
- 查詢列都設(shè)為鍵列
當(dāng)我們的SELECT查詢是這樣的
SELECT
[
companyname
]
,
[
contactname
]
,
[
contacttitle
]
FROM
[
chenmh
]
.
[
dbo
]
.
[
Customers
]
where
companyname
=
'
好孩子
'
--
-這時我們選擇將索引列都包含在索引建列中
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
將會彈出警告：警告! 最大鍵長度為 900 個字節(jié)。索引 'IX2_Customers' 的最大長度為 940 個字節(jié)。對于某些大值組合，插入/更新操作將失敗。
由于三個字段都是NVARCHAR字段類型，每個字符需要 2 個字節(jié)，（40+30+400）*2=940個字節(jié)，大于900字節(jié)，這時我們可以將
[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
這時索引鍵大小所占字符就只有40*2=80個字節(jié)，同時索引也是覆蓋索引，索引的列包含查詢用到的列，當(dāng)我們查詢數(shù)據(jù)時直接在索引頁中查找數(shù)據(jù)就可以，不需要訪問數(shù)據(jù)頁，減少磁盤IO，提高性能
?
帶有包含列的索引準(zhǔn)則
設(shè)計帶有包含列的非聚集索引時，請考慮下列準(zhǔn)則：
-
-
在 CREATE INDEX 語句的 INCLUDE 子句中定義非鍵列。
-
只能對表或索引視圖的非聚集索引定義非鍵列。
-
除
text
、
ntext
和
image
之外，允許所有數(shù)據(jù)類型。
-
精確或不精確的確定性計算列都可以是包含列。有關(guān)詳細信息，請參閱為計算列創(chuàng)建索引。
-
與鍵列一樣，只要允許將計算列數(shù)據(jù)類型作為非鍵索引列，從
image
、
ntext
和
text
數(shù)據(jù)類型派生的計算列就可以作為非鍵（包含性）列。
-
不能同時在 INCLUDE 列表和鍵列列表中指定列名。
- INCLUDE 列表中的列名不能重復(fù)。
-
在 CREATE INDEX 語句的 INCLUDE 子句中定義非鍵列。
列大小準(zhǔn)則
-
-
必須至少定義一個鍵列。最大非鍵列數(shù)為 1023 列。也就是最大的表列數(shù)減 1。
-
索引鍵列（不包括非鍵）必須遵守現(xiàn)有索引大小的限制（最大鍵列數(shù)為 16，總索引鍵大小為 900 字節(jié)）。
- 所有非鍵列的總大小只受 INCLUDE 子句中所指定列的大小限制；例如， varchar(max) 列限制為 2 GB。
-
必須至少定義一個鍵列。最大非鍵列數(shù)為 1023 列。也就是最大的表列數(shù)減 1。
列修改準(zhǔn)則
修改已定義為包含列的表列時，要受下列限制：
-
- 除非先刪除索引，否則無法從表中刪除非鍵列。
-
除進行下列更改外，不能對非鍵列進行其他更改：
- 將列的為空性從 NOT NULL 改為 NULL。
- 增加 varchar 、 nvarchar 或 varbinary 列的長度。
注意事項
- 鍵列的大小盡量小，有利用提高效率
- 將用于搜索和查找的列為鍵列，鍵列盡量不要包含沒必要的列。（例如上面建立的覆蓋查詢列，雖然 companyname+ contactname加起來作為鍵列也不會超過900字節(jié)，但是這樣鍵大小就變大了，降低了查詢效率）
-
避免添加不必要的列。添加過多的索引列（鍵列或非鍵列）會對性能產(chǎn)生下列影響：
-
一頁上能容納的索引行將更少。這樣會使 I/O 增加并降低緩存效率。
-
需要更多的磁盤空間來存儲索引。特別是，將
varchar(max)
、
nvarchar(max)
、
varbinary(max)
或
xml
數(shù)據(jù)類型添加為非鍵索引列會顯著增加磁盤空間要求。這是因為列值被復(fù)制到了索引葉級別。因此，它們既駐留在索引中，也駐留在基表中。
- 索引維護可能會增加對基礎(chǔ)表或索引視圖執(zhí)行修改、插入、更新或刪除操作所需的時間
-
一頁上能容納的索引行將更少。這樣會使 I/O 增加并降低緩存效率。
?
總結(jié)
?
?
如果您覺得文章對你有幫助，活動活動你的手指麻煩給個推薦；這也是對我一種鼓勵，在此表示感謝。
|
備注： ??? 作者： 沉寂的石頭 ??? 博客： http://www.cnblogs.com/chenmh 歡迎大家轉(zhuǎn)載，但轉(zhuǎn)載時必須注明文章來源，且在文章開頭明顯處給明鏈接，否則保留追究責(zé)任的權(quán)利。 歡迎大家拍磚 |
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
