具有包含性列的索引

浅浅的花香味﹌ 2021-06-26 16:06 274阅读 0赞

在 SQL Server 2005 中，可以通过将非键列添加到非聚集索引的叶级别来扩展非聚集索引的功能。通过包含非键列，可以创建覆盖更多查询的非聚集索引。这是因为非键列具有下列优点：

*  它们可以是不允许作为索引键列的数据类型。
 *  在计算索引键列数或索引键大小时，数据库引擎不考虑它们。

当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。这样可以实现性能提升，因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 I/O 操作。

> 注意： 当索引包含查询引用的所有列时，它通常称为“覆盖查询”。

键列存储在索引的所有级别中，而非键列仅存储在叶级别中。有关索引级别的详细信息，请参阅表组织和索引组织。

## 使用包含性列以避免大小限制 ##

可以将非键列包含在非聚集索引中，以避免超过当前索引大小的限制（最大键列数为 `16`，最大索引键大小为 `900` 字节）。数据库引擎计算索引键列数或索引键大小时，不考虑非键列。

例如，假设要为 `AdventureWorks` 示例数据库的 `Document` 表中的以下列建立索引：

Title nvarchar(50)
    
         Revision nchar(5)
    
         FileName nvarchar(400)

因为 `nchar` 和 `nvarchar` 数据类型的每个字符需要 2 个字节，所以包含这三列的索引将超出 `900` 字节的大小限制 `10` 个字节 (`455 * 2`)。使用`CREATE INDEX` 语句的 `INCLUDE` 子句，可以将索引键定义为 (`Title, Revision`)，将 `FileName` 定义为非键列。这样，索引键大小将为 `110` 个字节 (`55 * 2`)，并且索引仍将包含所需的所有列。下面的语句就创建了这样的索引。

USE AdventureWorks;
    GO
    CREATE INDEX IX_Document_Title       
    ON Production.Document (Title, Revision)       
    INCLUDE (FileName);

## 带有包含性列的索引准则 ##

设计带有包含性列的非聚集索引时，请考虑下列准则：

*  在 `CREATE INDEX` 语句的 `INCLUDE` 子句中定义非键列。
 *  只能对表或索引视图的非聚集索引定义非键列。
 *  除 `text`、`ntext` 和 `image` 之外，允许所有数据类型。
 *  精确或不精确的确定性计算列都可以是包含性列。有关详细信息，请参阅为计算列创建索引。
 *  与键列一样，只要允许将计算列数据类型作为非键索引列，从 `image`、`ntext` 和 `text`  
    数据类型派生的计算列就可以作为非键（包含性）列。
 *  不能同时在 `INCLUDE` 列表和键列列表中指定列名。
 *  `INCLUDE` 列表中的列名不能重复。

## 列大小准则 ##

*  必须至少定义一个键列。最大非键列数为 1023 列。也就是最大的表列数减 1。
 *  索引键列（不包括非键）必须遵守现有索引大小的限制（最大键列数为 16，总索引键大小为 900 字节）。
 *  所有非键列的总大小只受 INCLUDE 子句中所指定列的大小限制；例如，varchar(max) 列限制为 2 GB。

## 列修改准则 ##

修改已定义为包含性列的表列时，要受下列限制：

*  除非先删除索引，否则无法从表中删除非键列。
 *  除进行下列更改外，不能对非键列进行其他更改：
    
     *  将列的为空性从 `NOT NULL` 改为 `NULL`。
     *  增加 `varchar`、`nvarchar` 或 `varbinary` 列的长度。

> 注意：这些列修改限制也适用于索引键列。

## 设计建议 ##

重新设计索引键大小较大的非聚集索引，以便只有用于搜索和查找的列为键列。将覆盖查询的所有其他列设置为包含性非键列。这样，将具有覆盖查询所需的所有列，但索引键本身较小，而且效率高。

例如，假设要设计覆盖下列查询的索引。

USE AdventureWorks;
    GO
    SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
    FROM Person.Address
    WHERE PostalCode BETWEEN N'98000' and N'99999';

若要覆盖查询，必须在索引中定义每列。尽管可以将所有列定义为键列，但键大小为 `334` 字节。因为实际上用作搜索条件的唯一列是 `PostalCode` 列（长度为 30 字节），所以更好的索引设计应该将 `PostalCode` 定义为键列并包含作为非键列的所有其他列。

下面的语句创建了一个覆盖查询的带有包含性列的索引。

USE AdventureWorks;
    GO
    CREATE INDEX IX_Address_PostalCode       
    ON Person.Address (PostalCode)       
    INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);

性能注意事项

避免添加不必要的列。添加过多的索引列（键列或非键列）会对性能产生下列影响：

*  一页上能容纳的索引行将更少。这样会使 `I/O` 增加并降低缓存效率。
 *  需要更多的磁盘空间来存储索引。特别是，将 `varchar(max)、nvarchar(max)、varbinary(max)` 或 `xml`数据类型添加为非键索引列会显著增加磁盘空间要求。这是因为列值被复制到了索引叶级别。因此，它们既驻留在索引中，也驻留在基表中。
 *  索引维护可能会增加对基础表或索引视图执行修改、插入、更新或删除操作所需的时间。

您应该确定修改数据时在查询性能上的提升是否超过了对性能的影响，以及是否需要额外的磁盘空间要求。有关评估查询性能的详细信息，请参阅查询优化。