ITPub博客

首页 > 数据库 > SQL Server > Sql Server关于create index include带有包含列的索引的最全解释

Sql Server关于create index include带有包含列的索引的最全解释

原创 SQL Server 作者:lusklusklusk 时间:2021-05-31 16:30:13 2 删除 编辑

官方文档
By including nonkey columns, you can create nonclustered indexes that cover more queries. This is because the nonkey columns have the following benefits:

  • They can be data types not allowed as index key columns.
  • They are not considered by the Database Engine when calculating the number of index key columns or index key size.



在SqlServer 2016中,当我们要给一个长度超过1700的列创建索引时,数据库会告诉我们为这个列建索引会超过限制,而include的列是不在这个限制里面的。

create table t1(hid int,hname varchar(4000),hname1 varchar(4000))
create index ind_hname on t1(hname) --警告Warning! The maximum key length 
for a nonclustered index is 1700 bytes. The index 'ind_hname' has maximum length of 
4000 bytes. For some combination of large values, the insert/update operation will fail.
create index ind_hname on t1(hid) include(hname) --不报错,正常创建,索引键列是hid,包含列是hname




官方文档的说法:通过包含非键列,可以创建覆盖更多查询的非聚集索引。 这是因为非键列具有下列优点:
1、它们可以是不允许作为索引键列的数据类型。
2、在计算索引键列数或索引键大小时,数据库引擎不考虑它们。
当查询中的所有列都作为键列或非键列包含在索引中时,带有包含性非键列的索引可以显著提高查询性能。 这样可以实现性能提升,因为查询优化器可以在索引中找到所有列值;不访问表或聚集索引数据,从而减少磁盘 I/O 操作。

以上2点的个人理解:
1、它们可以是不允许作为索引键列的数据类型。比如varchar(2000)超过1700不能做索引键列,但是varchar(2000)可以作为包含列
2、 index key size索引键大小是指索引键包含的所有字段的长度总和的限制,比如我有一个表create table table1 (col1 varchar(500), col2 varchar(500),col3 varchar(500), col4 varchar(500));因为indexed key size的限制是1700, 所以create index ind_t1 on table1(col1, col2,col3)没有问题,因为col1+col2+col3=1500<1700,但是create index ind_t2 on table1(col1, col2,col3,col4)有问题,因为col1+col2+col3+col4=2000>1700,但是我们可以这样create index ind_t3 on table1(col1, col2,col3) include (col4),即把col4放入包含列,它就不占索引键ind_t3的大小


结合以上2点的理解,得出结论
1、include包含列只能是针对非聚集索引
2、Index space<>indexed key size+include columns size, Index space是整个index段的尺寸包括include列,Index space就是整个索引占用的磁盘空间,它包括索引键和非索引键,而不是说非索引键(包含列)就不占索引段的磁盘空间
3、当查询中的所有列就包含于索引的键值中,那么就不会发生Lookup回表的操作了,因为找到索引项,就已经找到所需的数据了,没有必要再到数据行去找了,这种情况,叫做索引覆盖
4、索引定义中非键列(包含列)的顺序不会影响使用该索引的查询的性能。
5、突然多出来一个查询,而这个查询字段只是比之前的查询多一个字段的情况下,如果之前的查询已经有了索引,则可以把之前索引删除,新建一个索引,再把新增的查询字段和之前索引中include字段合并起来一并放入include
6、当查询中的所有列都出现在索引(键列或包含列)中时,这种包含列的索引才能带来效果,也就是说它唯一的好处就是直接查询索引就可以返回值,不用回表,使用的范围太窄了。一旦要通过索引再回表返回数据,那包含列太得不偿失,因为包含列也占用实际的存储空间,这样导致这个索引的index space太大了,成本太高了,特别是超大表,索引如果包含太多包含列的话,占用存储实在太可怕了。所以 include包含列是典型的空间换时间的打法,需要平衡场景使用,而不是说它一定很好

以下7、8两点结论,额外夹带的私活,帮忙理解索引扫描的原理
7、100GB的表,10个字段,每个字段平均10GB,每个字段建立一个索引,平均每个索引10GB,扫描某个索引时,不是直接扫描整个索引这10GB,而是扫描索引树根节点+索引树干节点+索引叶子节点,这样下来找到对应的索引键值,可能只需要扫描不到10MB的索引,如果要回表,那再加上回表的成本
8、大表创建索引,加上online=on不会影响业务,但是会产生巨大的事务日志,所以一般留到业务低峰比如周末操作



结论2的例子:
20210526公司发生的遇到的现象,csdb5的磁盘一下子被使用了200GB
如下语句,ATickMinutesArchive表大小254GB总计10个字段,原来的索引180GB,如下索引一创建,发现索引达到了400GB,也就是如下新建索引有200GB,这个索引大小刚好和表大小一致,说明include的8个字段和索引键的2个字段同时计算了到了索引大小中

CREATE NONCLUSTERED INDEX NIX_ATickMinutesArchive_001  
ON [dbo].[ATickMinutesArchive] ([Symbol],[DateUpdate])  
INCLUDE ([Last],[High],[Low],[TotalVol],[TimeUpdate],[Ticks],[OpenPrice],[OpenVolume])  
WITH ( ONLINE= ON )



结论3的例子1:
例如,taable1表上有col1列(主键),col2列,col3列,col4列,col5列...
现在要求作以下查询 select col2,col3 from taable1 where col2=@col2
一般来说,在col2列上加非聚集索引,就可以加速此查询。其查询步骤:检索col2列上的非聚集索引,获取对应主键col1,再由主键检索聚集索引,从聚集索引的叶级索引页上获取相关的记录。但是,如果在col2列上加非聚集索引时指定include col3列,这样col3列值便会存储在非聚集索引的叶级索引页上。其查询步骤:检索col2列上的非聚集索引,直接从非聚集索引的叶级索引页上获取相关记录。

结论3的例子2:
如遇到如下查询,则最后一个index_5都可以让下面这些所有查询语句只访问索引就返回数据,也就是说当查询中的所有列都作为键列或非键列包含在索引中时,直接查询索引就可以返回结果

select col1,col2 from table1 where col1=XX
select col1,col2,col3 from table1 where col1=XX
select col1,col2,col3,col4 from table1 where col1=XX
select col1,col2,col3,col4,col5 from table1 where col1=XX
select col1,col2,col3,col4,col5,col6  from table1 where col1=XX
CREATE INDEX [index_5] ON [dbo].[table1] ([col1]) INCLUDE ([col2], [col3], 
[col4], [col5], [col6]) WITH (DATA_COMPRESSION = PAGE, ONLINE = ON)



结论4的例子:
索引定义中非键列的顺序不会影响使用该索引的查询的性能,比如index_5和index_6效果一样, include后面的col2和col5顺序可以随意,可以col2在前,也可以col5在前

CREATE INDEX [index_5] ON [dbo].[table1] ([col1]) INCLUDE ([col2], [col3], 
[col4], [col5], [col6]) WITH (DATA_COMPRESSION = PAGE, ONLINE = ON)
CREATE INDEX [index_6] ON [dbo].[table1] ([col1]) INCLUDE ([col5], [col3], 
[col2], [col4], [col6]) WITH (DATA_COMPRESSION = PAGE, ONLINE = ON)



结论5的例子:
如遇到如下查询,且已经存在index_4索引,则可以把index_4删除,新建index_5索引,再把新增的查询col6和之前索引中include字段合并起来一并放入include

select col1,col2,col3,col4,col5,col6  from table1 where col1=XX
CREATE INDEX [index_4] ON [dbo].[table1] ([col1]) INCLUDE ([col2], 
[col3], [col4], [col5]) WITH (DATA_COMPRESSION = PAGE, ONLINE = ON)
CREATE INDEX [index_5] ON [dbo].[table1] ([col1]) INCLUDE ([col2], 
[col3], [col4], [col5], [col6]) WITH (DATA_COMPRESSION = PAGE, ONLINE = ON)

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2774731/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 Oracle OCM、Mysql OCP, 10年DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    453
  • 访问量
    881880