一、 预备概念
- 覆盖索引:要返回的数据列就包含于索引的键值中,或者包含于索引的键值+聚集索引的键值中,那么就不需要回表,效率更高。
- 复合索引:包含两个或者多个字段的索引
- 非键列(包含列、include列):键列就是在索引中所包含的列,当然非键列就是该索引之外的列了
从SQL Server 2005 开始,可以通过将include列添加到非聚集索引扩展其功能。
CREATE NONCLUSTERED INDEX [Ind_USER_ID_CreateTime] ON [dbo].[WORKITEMS]
(
USER_ID ASC,
CreateTime DESC
) INCLUDE (WORK_ITEM_ID,NAME)
WITH (ONLINE = ON) ON [PRIMARY]
二、include列优点
- 当查询中的所有列都作为索引列或include列包含在索引中时,可以显著提高查询性能(不需回表)
- 它们可以是不允许作为索引键列的数据类型。
- 在计算索引键列数或索引键大小时,数据库引擎不考虑它们。
即:include列不受最大900字节、最多16个键列(2017版本开始为1700字节、32个键列)以及部分无法在大类型数据列如varchar(max)上创建索引的限制
三、include列的局限
- 只能对表或索引视图的非聚集索引定义include列
- 不支持 text、ntext 和 image 数据类型
- 对include列仅能进行以下alter操作:将列从 NOT NULL 改为 NULL;增加 varchar、nvarchar 或 varbinary 列的长度
- 如果要drop include列,必须先删除其上索引
- 索引中必须至少定义一个键列。最大include列数为 1023 列,即最大的表列数减 1
- 所有 include列的总大小只受 INCLUDE 子句中所指定列的大小限制,例如varchar(max) 列限制为 2 GB
四、性能注意事项
避免添加不必要的列,过多的索引列(键列或非键列)会对性能产生下列影响:
- 一页上能容纳的索引行将更少,这会使 I/O 增加并降低缓存效率。
- 需要更多的磁盘空间来存储索引。特别是,将 varchar(max)、nvarchar(max)、varbinary(max) 或 xml 数据类型添加为include列会显著增加磁盘空间。
- 索引维护可能会增加对基础表或索引视图执行修改、插入、更新或删除操作所需的时间。
应该确定修改数据时在查询性能上的提升是否超过了对性能的影响,以及是否需要额外的磁盘空间要求。
参考:
Create indexes with included columns - SQL Server | Microsoft Docs
https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2005/ms190806%28v%3dsql.90%29
SQL Server 索引中include的魅力(具有包含性列的索引) - 听风吹雨 - 博客园
为什么include列可以超过900字节限制