索引最多包含几个列索引包含列有什么用

转载

棉花糖 2024-05-13 21:11:01

文章标签 索引最多包含几个列非聚集索引 Sales 数据 文章分类 数据仓库大数据

【译注：此文为翻译，由于本人水平所限，疏漏在所难免，欢迎探讨指正】

之前的系列文章介绍了聚集索引和非聚集索引，着重讲解了以下几个方面的内容：

对于表中的每一行来说，索引中总是存在这一个条目与它对应（注：此例外规则将在以后予以关注）。这些条目总是按索引键排序。
在聚集索引中，索引键就是实际的表数据行。
在非聚集索引中，条目是与数据行分开的，它包含一个索引键值和一个映射到实际表数据的书签值。

此前一句话的后半部分是正确但不是完整的，在本章节我们将检查一种可选方案：给非聚集索引包含额外的列，成为“包含列（included columns）”。在第六章，我们将讲解书签操作，将会看到SQL SERVER或许会单方面的为你的索引添加一些列。

包含列

那些在非聚集索引中，但不是索引键的一部分的列称为包含列。这些列不属于索引的一部分，因此不会影响索引中条目的顺序。同样我们会看到，包含列比索引列导致更少的开销。

当创建非聚集索引时，我们将包含列与索引列分开指定，如下所示：

CREATE NONCLUSTERED INDEX FK_ProductID_ ModifiedDate
       ON Sales.SalesOrderDetail (ProductID, ModifiedDate)
       INCLUDE (OrderQty, UnitPrice, LineTotal)

ProductID 和 ModifiedDate 是索引键列，OrderQty，UnitPrice，LineTotal 是包含列。

INCLUDE 语句，那么结果索引看起来会是这个样子：

ProductID   ModifiedDate   Bookmark

Page n:

707         2004/07/25        =>  
707         2004/07/26        =>  
707         2004/07/26        =>  
707         2004/07/26        =>  
707         2004/07/27        =>  
707         2004/07/27        =>  
707         2004/07/27        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  
707         2004/07/28        =>  

Page n+1:

707         2004/07/29        =>  
707         2004/07/31        =>  
707         2004/07/31        =>  
707         2004/07/31        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>  
708         2001/07/01        =>

OrderQty，UnitPrice 和 LineTotal 这几列，索引看起来会是这个样子：

:- Search Key Columns -:      :---  Included Columns  ---:     : Bookmark :

ProductID   ModifiedDate      OrderQty    UnitPrice   LineTotal       

Page n-1:

707         2004/07/29        1           34.99       34.99       =>  
707         2004/07/31        1           34.99       34.99       =>  
707         2004/07/31        3           34.99      104.97       =>  
707         2004/07/31        1           34.99       34.99       =>  
708         2001/07/01        5           20.19      100.95       =>  

Page n:

708         2001/07/01        1           20.19       20.19       =>  
708         2001/07/01        1           20.19       20.19       =>  
708         2001/07/01        2           20.19       40.38       =>  
708         2001/07/01        1           20.19       20.19       =>  
708         2001/07/01        2           20.19       40.38       =>  

708         2001/12/01        7           20.19      141.33       =>  
708         2001/12/01        1           20.19       20.19       =>  
708         2002/01/01        1           20.19       20.19       =>  
708         2002/01/01        1           20.19       20.19       =>  
708         2002/01/01        1           20.19       20.19       =>  

Page n+1:

708         2002/01/01        2           20.19       40.38       =>  
708         2002/01/01        5           20.19      100.95       =>  
708         2002/02/01        1           20.19       20.19       =>  
708         2002/02/01        1           20.19       20.19       =>  
708         2002/02/01        2           20.19       40.38       =>

ProductID/ ModifiedDate的组合行一样。

OrderQty，UnitPrice，LineTotal 加到索引键上？”。把这些列包含进索引中但不包含进索引键中有如下几个优点：

ProductID 或者ModifiedDate 的值被更新了，那么那条数据在索引中的对应条目肯定会被重新定位，但是，如果数据行的UnitPricevalue 值被更新了，那么索引条目虽然仍然需要被更新，但是不需要移动位置。
更少的努力去定位索引条目。
索引的大小会稍微小点。
索引的数据分配统计会更容易维护。

后续几个章节，当我们了解了索引的内部结构，以及SQL SERVER 为了优化查询性能而维护的其他额外信息后，这些优点会变得更有意义。

SELECT 语句列表中但不在WHERE 字句的列最好就放置在包含索引列中（最佳实践）。

成为覆盖索引

SalesOrderID/ SalesOrderDetailID 作为SalesOrderDetail表的聚集索引这一决策表示认同，这张表的大部分查询都会请求按销售单号排序或者分组的数据。然而，也有许许多多的查询，或许来自于便利店的员工，需要以产品序列组织的信息。正是这些查询会从如上所示的索引中受益。

为了演示在那个索引中带有包含索引的潜在益处，我们将会审视SalesOrderDetail表上的两个查询，他们每一个都会执行三次，如下所示：

第一次执行：无非聚集索引。
第二次执行：使用没有包含列的非聚集索引（只有两个索引键）。
第三次执行：使用之前定义的非聚集索引（具有包含列）。

就像我们在之前章节所做的那样，我们再次使用逻辑读作为首要评判标准，但是我们也使用SSMS的“显示实际的执行计划”选项来查看各个执行的计划，这会给我们一个附加的评判标准：非读动作所占的工作量的百分比，比如在数据被读入内存后匹配关联的数据，这使得我们对于查询总消耗有一个更好的理解。

测试第一个查询：商品总数

我们的第一个查询，提供了某一个特定商品按日期分组区分的总数：

列表5.2：

SELECT  ProductID ,
        ModifiedDate ,
        SUM(OrderQty) AS 'No of Items' ,
        AVG(UnitPrice) 'Avg Price' ,
        SUM(LineTotal) 'Total Value'
FROM    Sales.SalesOrderDetail
WHERE   ProductID = 888
GROUP BY ProductID ,
        ModifiedDate ;

因为索引只会影响查询的性能，而不会影响查询的结果，在三种不同的索引架构下执行这个查询总会产出如下的行集：

ProductID   ModifiedDate    No of Rows  Avg Price         Total Value

----------- ------------    ----------- -----------------------------
888         2003-07-01      16          602.346           9637.536000
888         2003-08-01      13          602.346           7830.498000
888         2003-09-01      19          602.346           11444.574000
888        2003-10-01       2           602.346           1204.692000
888         2003-11-01      17          602.346           10239.882000
888         2003-12-01      4           602.346           2409.384000
888         2004-05-01      10          602.346           6023.460000
888         2004-06-01      2           602.346           1204.692000

ProductID = 888’ 的销售记录，而我们为每个日期的多条记录生成一个输出。进行我们的测试的基本模式在列表5.3列出。在执行任何查询之前，确保执行了 SET STATISTICS IO ON 脚本。

索引最多包含几个列索引包含列有什么用_数据

索引最多包含几个列索引包含列有什么用_Sales_02

IF EXISTS ( SELECT  1
            FROM    sys.indexes
            WHERE   name = 'FK_ProductID_ModifiedDate'
                    AND OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail') ) 
    DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate ;
GO

--RUN 1: Execute Listing 5.2 here (no non-clustered index)

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate
ON Sales.SalesOrderDetail (ProductID, ModifiedDate) ;

--RUN 2: Re-execute Listing 5.2 here (non-clustered index with no include)

IF EXISTS ( SELECT  1
            FROM    sys.indexes
            WHERE   name = 'FK_ProductID_ModifiedDate'
                    AND OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail') ) 
    DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate ;
GO

CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate
ON Sales.SalesOrderDetail (ProductID, ModifiedDate)
INCLUDE (OrderQty, UnitPrice, LineTotal) ;

--RUN 3: Re-execute Listing 5.2 here (non-clustered index with include)

View Code

列表5.3：我们要执行的查询模式

在各个索引模式下执行查询所需要的相关工作量显示在下表中：

第一次执行：没有非聚集索引	表SalesOrderDetail：扫描计数1次，逻辑读1238次，非读活动：8%
第二次执行：有非聚集索引，没有包含列	表SalesOrderDetail：扫描计数1次，逻辑读131次，非读活动：0%
第三次执行：有非聚集索引：有包含列	Table 'SalesOrderDetail'. Scan count 1, logical reads 3. Non read activity: 1%.

　　　　从这些结果你可以看到：

　　　　第一次执行需要对SalesOrderDetail 表的完整扫描，每一行需要被读并且检查以决定其是否参与到返回的结果集。

　　　　第二次执行使用了非聚集索引快速的找到了所请求的３９行数据的书签，但它仍然需要从表中返回各个数据行。

ModifiedDate／ProductID.），它快速跳转到第一个所请求的条目上，读取了39个连续的条目，当它读的同时对各个条目做了聚合计算，这样一切便完成了。

测试第二个查询：基于日期的总数

我们的第二个查询和第一个查询是相同的，只是在WHERE子句的一点小小的改变，这次仓库将请求基于日期而不是基于产品的信息。我们必须在最右边的检索键列（ModifiedDate）进行过滤，而不是最左边的列（ProductID）。新的查询展示如下：

SELECT  ModifiedDate ,
        ProductID ,
        SUM(OrderQty) 'No of Items' ,
        AVG(UnitPrice) 'Avg Price' ,
        SUM(LineTotal) 'Total Value'
FROM    Sales.SalesOrderDetail
WHERE   ModifiedDate = '2003-10-01'
GROUP BY ModifiedDate ,
        ProductID ;

部分结果行集合展示如下：

ProductID   ModifiedDate    No of Items Avg Price             Total Value
----------- ------------    ----------- --------------------- ----------------
                                   :
                                   :
782         2003-10-01      62          1430.9937             86291.624000
783         2003-10-01      72          1427.9937             100061.564000
784         2003-10-01      52          1376.994              71603.688000
792         2003-10-01      12          1466.01               17592.120000
793         2003-10-01      46          1466.01               67436.460000
794         2003-10-01      37          1466.01               54242.370000
795         2003-10-01      22          1466.01               32252.220000
                                   :
                                   :
(164 row(s) affected)

WHERE条件将表数据过滤为1492 行满足条件的数据，然后它们再分组，产生了164 行数据的输出。

在各个索引架构下执行查询所需要的工作量如下表列出所示：

第一次执行：没有非聚集索引	表SalesOrderDetail：扫描计数1次，逻辑读1238，非读活动：10%
第二次执行：有非聚集索引，但没有包含列	表SalesOrderDetail：扫描计数1次，逻辑读1238，非读活动10%
第三次执行：有非聚集索引，有包含列	表SalesOrderDetail：扫描计数1次，逻辑读761，非读活动 8%

theSalesOrderDetail 。正是因为第四章详细讲述的原因，WHERE子句没有足够的高选择性从而从非含索引中受益，除此之外，组成任何一个分组得数据行都是分散在整张表中，当表被读取时，每一行都会匹配到它的对应分组中，这些操作会消耗处理器时间和内存。第三个查询在非聚集索引中找到了它所需要的一切，但是，不像之前的查询，它发现数据行在索引中是不连续存储的，但是分组本身又延展了整个索引长度，因此，SQL SERVER扫描了整个索引。

索引扫描比起表扫描有如下两个优点：