包含列索引
概述
包含列索引也是非聚集索引，索引结构跟聚集索引结构是一样，有一点不同的地方就是包含列索引的非键列只存储在叶子节点；包含列索引的列分为键列和非键列，所谓的非键列就是INCLUDE中包含的列，至少需要有一个键列，且键列和非键列不允许重复，非键列最多允许1023列（也就是表的最多列-1），由于 索引键列（不包括非键）必须遵守现有索引大小的限制（最大键列数为 16，总索引键大小为 900 字节）的要求所以引进了包含列索引。
正文
- 创建包含列索引
--
--创建表
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
]
--
--创建包含列索引
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
非键列具有下列优点：
-
它们可以是不允许作为索引键列的数据类型。
-
在计算索引键列数或索引键大小时，数据库引擎不考虑它们。
当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。这样可以实现性能提升，因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 I/O 操作。（当索引包含查询引用的所有列时，它通常称为“覆盖查询”。）
- 创建覆盖查询
覆盖查询就是创建的索引列包含查询所引用的所有列时
- 查询列都设为键列
当我们的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 个字节。索引 'IX2_Customers' 的最大长度为 940 个字节。对于某些大值组合，插入/更新操作将失败。
由于三个字段都是NVARCHAR字段类型，每个字符需要 2 个字节，（40+30+400）*2=940个字节，大于900字节，这时我们可以将
[contactname] ,[contacttitle]包含在非键列中
2.将大数据类型设为非键列
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个字节，同时索引也是覆盖索引，索引的列包含查询用到的列，当我们查询数据时直接在索引页中查找数据就可以，不需要访问数据页，减少磁盘IO，提高性能
带有包含列的索引准则
设计带有包含列的非聚集索引时，请考虑下列准则：
-
-
在 CREATE INDEX 语句的 INCLUDE 子句中定义非键列。
-
只能对表或索引视图的非聚集索引定义非键列。
-
除
text
、
ntext
和
image
之外，允许所有数据类型。
-
精确或不精确的确定性计算列都可以是包含列。有关详细信息，请参阅为计算列创建索引。
-
与键列一样，只要允许将计算列数据类型作为非键索引列，从
image
、
ntext
和
text
数据类型派生的计算列就可以作为非键（包含性）列。
-
不能同时在 INCLUDE 列表和键列列表中指定列名。
- INCLUDE 列表中的列名不能重复。
-
在 CREATE INDEX 语句的 INCLUDE 子句中定义非键列。
列大小准则
-
-
必须至少定义一个键列。最大非键列数为 1023 列。也就是最大的表列数减 1。
-
索引键列（不包括非键）必须遵守现有索引大小的限制（最大键列数为 16，总索引键大小为 900 字节）。
- 所有非键列的总大小只受 INCLUDE 子句中所指定列的大小限制；例如， varchar(max) 列限制为 2 GB。
-
必须至少定义一个键列。最大非键列数为 1023 列。也就是最大的表列数减 1。
列修改准则
修改已定义为包含列的表列时，要受下列限制：
-
- 除非先删除索引，否则无法从表中删除非键列。
-
除进行下列更改外，不能对非键列进行其他更改：
- 将列的为空性从 NOT NULL 改为 NULL。
- 增加 varchar 、 nvarchar 或 varbinary 列的长度。
注意事项
- 键列的大小尽量小，有利用提高效率
- 将用于搜索和查找的列为键列，键列尽量不要包含没必要的列。（例如上面建立的覆盖查询列，虽然 companyname+ contactname加起来作为键列也不会超过900字节，但是这样键大小就变大了，降低了查询效率）
-
避免添加不必要的列。添加过多的索引列（键列或非键列）会对性能产生下列影响：
-
一页上能容纳的索引行将更少。这样会使 I/O 增加并降低缓存效率。
-
需要更多的磁盘空间来存储索引。特别是，将
varchar(max)
、
nvarchar(max)
、
varbinary(max)
或
xml
数据类型添加为非键索引列会显著增加磁盘空间要求。这是因为列值被复制到了索引叶级别。因此，它们既驻留在索引中，也驻留在基表中。
- 索引维护可能会增加对基础表或索引视图执行修改、插入、更新或删除操作所需的时间
-
一页上能容纳的索引行将更少。这样会使 I/O 增加并降低缓存效率。
总结
如果您觉得文章对你有帮助，活动活动你的手指麻烦给个推荐；这也是对我一种鼓励，在此表示感谢。
|
备注： 作者： pursuer.chen 博客： http://www.cnblogs.com/chenmh 本站点所有随笔都是原创，欢迎大家转载；但转载时必须注明文章来源，且在文章开头明显处给明链接，否则保留追究责任的权利。 《欢迎交流讨论》 |
