【翻译】SQL Server索引进阶:第五级,包含列

本文涉及的产品
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
云数据库 RDS SQL Server,基础系列 2核4GB
简介:
  • 表中的每一行在索引中总是有一个入口(这条规则有一个意外,在后面的级别中我们会讲到)。这些入口总是用索引键排序。
  • 在聚集索引中,索引的入口就是表的实际行。
  • 在非聚集索引中,入口和数据行是分开的,索引由索引键列和标签组成,标签是索引键列到表数据行的映射。

第三句的后半部分是正确的,但是不完整。今天我们将测试在非聚集索引中包括额外列的情况,这些额外列叫做“包含列”。在第六级中,将会测试标签的操作,我们将会看到SQL Server可能会单方面的给你的索引添加一些列。

包含列

在非聚集索引中有一些列,不是索引键的一部分,被叫做“包含列”。这些列不是键的一部分,不影响索引入口的排序。同样,我们将会看到,相比较键列,它们带来的消耗也较小。

在创建非聚集索引的时候,在索引列之外,需要单独的指定包含列,就像下面的一样。

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



在上面的例子中,ProductID和ModifiedDate是索引键的列,OrderQty,UnitPrice和LineTotal是包含列。

如果我们不指定包含列,索引可能是下面的样子。

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        =>  

但是,如果我们指定了包含列,索引就是下面的样子。

:- 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       =>  

你可能会问:“为什么要有包含列?为什么不简单的把OrderQty,UnitPrice和LineTotal加入索引键?”。把这些列加入索引,但是不作为索引键,有些面的好处:
 

  • 这些列不是索引键的一部分,它们不会影响索引入口的排序。反过来,减少它们在索引中的消耗。举个例子,如果需要修改一行数据的ProductID或者ModifiedDate的值,这行在索引中对应的入口就会被重新分配。但是如果修改UnitPrice的值,只会更新索引的入口,但是不需要移动。
  • 分配索引的入口带来的消耗会更小。
  • 索引占用的空间会更小。
  • 索引的分布统计的维护会更容易。

大部分的有点在后面的级别中会更有意义,后面我们会看到索引的内部结构,SQL Server为了优化查询而维护的一些额外信息。

决定一个索引列是否是索引键的一部分,还是只是一个包含列,在你做索引的决定的时候不是最重要的。也就是说,那些经常出现在select中的,而不是where子句中的列,最好是放在包含列中。

在第四级中,我们对于设计者在SalesOrderDetail表建立SalesOrderID/SalesOrderDetailID的聚集索引的决定表示支持。对于这张表的大部分查询都是有序的,或者是以订单分组的。但是,也有一部分的查询,可能是从仓库的工作人员发出的,会需要产品序列的信息。这些查询将会从本文开头创建的索引中受益。
 

为了说明包含列的带来的好处,我们看一下在SalesOrderDetail表执行的两个查询,每个查询会执行三次:

  • 第一次,没有非聚集索引。
  • 第二次,有非聚集索引,但是没有包含列,只有两个键列。
  • 第三次,使用文章开头定义的非聚集索引,既有索引键,也有包含列。

和我们之前的文章一样,再次使用IO读取的次数作为主要的衡量指标,但是我们也是用SQL Server管理器的“显示实际的执行计划”选项来查看每次执行的执行计划。这给我们增加了一个衡量指标:消耗在非读取活动上的工作量所占的百分比,例如,在读取到内存之后,进行数据匹配的工作。这给我们一个,关于查询总共的消耗,更好的理解。
 

测试第一个查询:从产品角度产生的全部活动

查询语句如下

 

 
  1. SELECT  ProductID , 
  2.         ModifiedDate , 
  3.         SUM(OrderQty) AS 'No of Items' , 
  4.         AVG(UnitPrice) 'Avg Price' , 
  5.         SUM(LineTotal) 'Total Value' 
  6. FROM    Sales.SalesOrderDetail 
  7. WHERE   ProductID = 888 
  8. GROUP BY ProductID , 
  9.         ModifiedDate ; 


因为索引影响的是查询的性能,而不是查询的结果。在三个不同索引下查询的结果都是下面的内容。

上面的8行结果,是在39行ProductID=888的基础上聚合而成的。在每次查询之前都需要做一些事前工作,还需要打开IO统计,SET STATISTICS IO ON.

 

 
  1. IF EXISTS ( SELECT  1 
  2.             FROM    sys.indexes 
  3.             WHERE   name = 'FK_ProductID_ModifiedDate' 
  4.                     AND OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail') )  
  5.     DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate ; 
  6. GO 
  7.  
  8. --RUN 1: Execute Listing 5.2 here (no non-clustered index) 
  9.  
  10. CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate 
  11. ON Sales.SalesOrderDetail (ProductID, ModifiedDate) ; 
  12.  
  13. --RUN 2: Re-execute Listing 5.2 here (non-clustered index with no include) 
  14.  
  15. IF EXISTS ( SELECT  1 
  16.             FROM    sys.indexes 
  17.             WHERE   name = 'FK_ProductID_ModifiedDate' 
  18.                     AND OBJECT_ID = OBJECT_ID('Sales.SalesOrderDetail') )  
  19.     DROP INDEX Sales.SalesOrderDetail.FK_ProductID_ModifiedDate ; 
  20. GO 
  21.  
  22. CREATE NONCLUSTERED INDEX FK_ProductID_ModifiedDate 
  23. ON Sales.SalesOrderDetail (ProductID, ModifiedDate) 
  24. INCLUDE (OrderQty, UnitPrice, LineTotal) ; 
  25.  
  26. --RUN 3: Re-execute Listing 5.2 here (non-clustered index with include) 


三次查询的统计结果如下:

Run 1:

No Nonclustered Index

Table 'SalesOrderDetail'. Scan count 1, logical reads 1238.

Non read activity:  8%.

Run 2:

Index – No Included Columns

Table 'SalesOrderDetail'. Scan count 1, logical reads 131.

Non read activity:  0%.

Run 3:

With Included Columns

Table 'SalesOrderDetail'. Scan count 1, logical reads 3.

Non read activity:  1%.

从上面的结果可以看出:

  • 第一次,需要全表扫描,每一行都会被读取,来判断是否满足查询的条件。
  • 第二次,通过非聚集索引快速的定位,只有39次请求,但是还是要从表中获取其他列的信息。
  • 第三次,非聚集索引包括了请求的全部信息,是一个最优的排序。直接跳到第一个入口,然后连续的读取39个入口,进行聚合计算,然后返回结果就行了。

 

测试第二个查询:从日期角度产生的全部活动

查询语句如下

 
  1. SELECT  ModifiedDate , 
  2.         ProductID , 
  3.         SUM(OrderQty) 'No of Items' , 
  4.         AVG(UnitPrice) 'Avg Price' , 
  5.         SUM(LineTotal) 'Total Value' 
  6. FROM    Sales.SalesOrderDetail 
  7. WHERE   ModifiedDate = '2003-10-01' 
  8. GROUP BY ModifiedDate , 
  9.         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行结果。

查询的统计如下

Run 1:

No Nonclustered Index

Table 'SalesOrderDetail'. Scan count 1, logical reads 1238.

Non read activity:  10%.

Run 2:

With Index – No Included Columns

Table 'SalesOrderDetail'. Scan count 1, logical reads 1238.

Non read activity:  10%.

Run 3:

With Included Columns

Table 'SalesOrderDetail'. Scan count 1, logical reads 761.

Non read activity:  8%.

第一次和第二次查询的执行计划是相同的,都是全表扫描。具体原因在第四级中已经介绍过,where子句没有从非聚集索引中受益。同样的,每一组在表中都很分散,读取表的时候,需要读取每一行来查看匹配的组,这些操作会消耗处理器时间和内存。

第三次查询在索引中发现了所需要的全部信息,但是不想第一个查询,发现在索引中的行,不是连续的。

扫描索引,而不是扫描表,有两个好处:

  • 索引占用的空间比表小,需要的读取更少。
  • 行已经被分组,需要的非读取活动更少。

非读取活动,就是开启执行计划之后,执行完查询之后,在执行计划tab中显示的,除表扫描,索引扫描之外的活动,例如:计算标量,流聚合等等。

读取活动,就是执行计划中显示的扫描,表扫描,以及索引扫描。

 

结论

包含列使得非聚集索引可以覆盖各种查询,提高这些查询的性能,有时候是很吸引人的。包含列增加了索引的大小,增加了一些维护工作。在你创建非聚集索引的时候,尤其是包含外键的时候,问一问自己:“我应该在索引中增加哪些额外的列呢?”。




本文转自 virusswb 51CTO博客,原文链接:http://blog.51cto.com/virusswb/986284,如需转载请自行联系原作者
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情: https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
2月前
|
SQL 存储 关系型数据库
如何巧用索引优化SQL语句性能?
本文从索引角度探讨了如何优化MySQL中的SQL语句性能。首先介绍了如何通过查看执行时间和执行计划定位慢SQL,并详细解析了EXPLAIN命令的各个字段含义。接着讲解了索引优化的关键点,包括聚簇索引、索引覆盖、联合索引及最左前缀原则等。最后,通过具体示例展示了索引如何提升查询速度,并提供了三层B+树的存储容量计算方法。通过这些技巧,可以帮助开发者有效提升数据库查询效率。
172 2
|
2月前
|
SQL Oracle 关系型数据库
SQL优化-使用联合索引和函数索引
在一次例行巡检中,发现一条使用 `to_char` 函数将日期转换为字符串的 SQL 语句 CPU 利用率很高。为了优化该语句,首先分析了 where 条件中各列的选择性,并创建了不同类型的索引,包括普通索引、函数索引和虚拟列索引。通过对比不同索引的执行计划,最终确定了使用复合索引(包含函数表达式)能够显著降低查询成本,提高执行效率。
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引:详细技巧与方法
在数据库管理中,索引是提高SQL查询性能的重要手段
|
3月前
|
SQL 存储 索引
SQL Server的Descending Indexes降序索引
【9月更文挑战第21天】在SQL Server中,降序索引允许指定列的排序顺序为降序,可显著优化涉及降序排序的查询性能,特别是在复合索引中。通过创建降序索引,可以更高效地满足特定业务需求,如按交易时间降序获取最新记录。然而,使用时需考虑查询频率、数据分布及维护成本,以确保最佳性能。
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么:深入解析与技巧
在SQL数据库中,索引是一种用于提高查询性能的重要数据结构
|
2月前
|
SQL 存储 关系型数据库
SQL默认索引是什么
在SQL数据库中,索引是一种用于提高查询性能的数据结构
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL用了索引
在数据库管理和优化过程中,确认SQL查询是否使用了索引是一个至关重要的步骤
|
2月前
|
SQL 关系型数据库 MySQL
如何确认SQL查询是否使用了索引:详细步骤与技巧
在数据库管理和优化中,确认SQL查询是否有效利用了索引是提升性能的关键步骤
|
2月前
|
索引
SQL_创建和管理索引
SQL_创建和管理索引
28 0
|
3月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")