翻译：与包括索引列:5级阶梯SQL Server索引

与包括索引列:5级阶梯SQL Server索引

通过大卫·杜兰特,2011/07/13

该系列

本文是楼梯系列的一部分:SQL Server的阶梯索引

索引数据库设计的基础,告诉开发人员使用数据库设计者的意图。不幸的是索引时往往是后加上的性能问题出现。终于在这里是一个简单的系列文章,应该让任何数据库专业迅速“加速”

前水平介绍聚集和非聚集索引,每个突出以下方面:

总有一个入口在索引表中的每一行(我们指出一个例外规则将在稍后的水平)。这些条目总是在索引键序列。
在聚集索引中,表的索引条目是实际的行。
非聚集索引的条目是独立于数据行; 包括索引键列和书签索引键列映射到实际的价值表的行。

最后的一半以前的句子是正确的,但是不完整。在这个层次,我们检查选项包括附加列集群指数,包括列。在6级检查书签操作,我们将看到,SQL Server可能单方面将一些列添加到您的索引。

包括列

非聚集索引的列,但不是索引键的一部分,被称为包括列。这些列不是关键的一部分,所以不影响索引条目的顺序。同时,正如我们将看到的,它们会引起较少的开销比键列。

当创建一个非聚集索引,我们指定包含分开列键列; 如清单5.1所示。

创建非聚集索引FK_ProductID_ ModifiedDate在Sales.SalesOrderDetail(ProductID,ModifiedDate)包括(OrderQty,UnitPrice,LineTotal)

清单5.1:创建一个非聚集索引,包括列

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

如果我们不指定了呢包括条款在上面的SQL语句,生成的指数会看起来像这样:

ProductID ModifiedDate书签

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

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

然而,包括对SQL ServerOrderQty,UnitPrice和LineTotal列,索引是这样的:

:-搜索键列::- - - - - - - - - - - -包括列:书签:

ProductID ModifiedDate OrderQty UnitPrice LineTotal

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

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

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

检查这个指数显示的内容,很明显,索引键列的行命令。五行产品与修改日期708年1月1日2002(以粗体突出显示),例如,在索引中是连续的,其他的行ProductID/ModifiedDate组合。

你可能会问“为什么甚至包括列? 为什么不简单地添加OrderQty,UnitPrice和LineTotal索引键? “有几个优势在这些列索引中而不是在索引键,如:

列不属于索引键的位置不影响在索引条目。反过来,这降低了他们的开销在索引中。例如,如果ProductID或ModifiedDate价值行修改,那么这一行在索引的条目必须搬迁。但是,如果UnitPricevalue行修改,索引条目仍然需要更新,但它不需要感动。
所需的努力找到一个条目(s)指数更少。
指数的大小将会略小。
索引的数据分布统计信息将更容易维护。

大部分这些优势将更有意义的晚年的水平,当我们观察内部结构的索引和一些额外的信息由SQL Server维护优化查询性能。

决定是否一个索引列索引键的一部分,或只是一个包含列,索引不是最重要的决定你会做。也就是说,经常出现在列选择但不是在列表在哪里子句的查询最好放置在包含的列索引的一部分。

在成为一个覆盖指数

在四级,我们表示赞同的设计者AdventureWorksdatabase关于他们的决定SalesOrderID/SalesOrderDetailID的聚集索引SalesOrderDetail表。大多数查询该表将请求数据命令或按销售订单分组号。然而,一些查询,也许从仓库人员,需要在产品序列的信息。这些查询将受益于该指数如清单5.1所示。

说明的潜在好处包括列索引,我们将着眼于对SalesOrderDetailtable两个查询,我们将执行三次,如下:

1:运行没有非聚集索引
运行2:使用非聚集索引不包含包含列(只有两个键列)
运行3:使用非聚集索引如清单5.1中定义的

当我们在先前的水平,我们再次使用读取数作为主要的指标,但我们也使用SQL Server Management Studio的“显示实际执行计划”选项来查看每个执行的计划。这将给我们一个额外的度量:工作量的百分比是花在non-read活动,如匹配相关数据后,读取到内存中。这给了我们更好的理解查询的总成本。

测试第一个查询:活动产品的总数

我们的第一个查询,如清单5.2所示,是一个提供活动总数按日期为一个特定的产品。

选择ProductID,ModifiedDate,总和(OrderQty)作为没有的物品,AVG(UnitPrice)“Avg价格”,总和(LineTotal)“总价”
从Sales.SalesOrderDetail
在哪里ProductID=888年
集团ProductID,ModifiedDate;

清单5.2:“产品”活动总数查询

因为索引可以影响一个查询的性能,而不是结果; 对三种不同的索引方案执行这个查询总收益率以下行设置:

ProductID ModifiedDate Avg的行没有价格总额

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
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 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“行每个日期的表给一个输出行,一个或者更多的ProductID = 888的销售。基本的计划进行我们的测试清单5.3所示。在运行任何查询之前,确保你运行设置数据输入输出。

如果存在(选择1从sys.indexes在哪里的名字=“FK_ProductID_ModifiedDate”和OBJECT_ID=OBJECT_ID(“Sales.SalesOrderDetail”))指数下降Sales.SalesOrderDetail.FK_ProductID_ModifiedDate;
去
这里,运行1:执行清单5.2(没有集群指数)

创建非聚集索引FK_ProductID_ModifiedDate
在Sales.SalesOrderDetail(ProductID,ModifiedDate);

——运行2:重新执行清单5.2(非集群指数没有包括)

如果存在(选择1从sys.indexes在哪里的名字=“FK_ProductID_ModifiedDate”和OBJECT_ID=OBJECT_ID(“Sales.SalesOrderDetail”))指数下降Sales.SalesOrderDetail.FK_ProductID_ModifiedDate;
去

创建非聚集索引FK_ProductID_ModifiedDate
在Sales.SalesOrderDetail(ProductID,ModifiedDate)
包括(OrderQty,UnitPrice,LineTotal);

——运行3:重新执行清单5.2以下(包括集群指数)

清单5.3:测试产品”活动总数”查询

所需的相关工作执行查询每个索引方案如表5.1所示。

1:运行

没有非聚集索引

表“SalesOrderDetail”。扫描数1,逻辑读1238。

非阅读活动:8%。

运行2:

——不包括列建立索引

表“SalesOrderDetail”。扫描数1,逻辑读131。

非阅读活动:0%。

运行3:

包括列

表“SalesOrderDetail”。扫描数1,逻辑读3。

非阅读活动:1%。

表5.1:运行第一个查询结果三次不同的非聚集索引可用

从这些结果可以看出:

运行1需要一个完整的扫描SalesOrderDetail表; 每一行必须阅读和检查,以确定是否应该参与的结果。
运行2使用非聚集索引快速找到请求的书签仅为39行,但是它必须从表中检索每个单独的行。
跑3在非聚集索引,找到了所需的一切,最有利的序列ModifiedDate在ProductID。它迅速跃升至第一个请求条目,连续读了39项,做总计算每个条目阅读,并完成。

测试第二个查询:根据日期活动总数

第一个第二个查询是相同的,除了改变在哪里条款。这次仓库请求基于日期的信息,而不是产品。我们必须过滤最右边搜索键列,ModifiedDate; 而不是最左边的列,ProductID。新的查询清单5.4所示。

选择ModifiedDate,ProductID,总和(OrderQty)没有的物品,AVG(UnitPrice)“Avg价格”,总和(LineTotal)“总价”
从Sales.SalesOrderDetail
在哪里ModifiedDate=“2003-10-01”
集团ModifiedDate,ProductID;

清单5.4:“活动总数按日期”查询

结果行集,部分是:

ProductID ModifiedDate没有Avg的物品价格总额
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
:
:
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 1466.01 - 67436.460000
794 2003-10-01 37 1466.01 - 54242.370000
795 2003-10-01 22 1466.01 - 32252.220000
:
:
(164行受影响)

的在哪里条款过滤表1492预选赛行; 分组时,产生164行输出。

运行测试,遵循相同的模式如清单5.3所述,但使用新的查询清单5.4。结果所需的相关工作执行查询每个索引方案如表5.2所示。

1:运行

没有非聚集索引

表“SalesOrderDetail”。扫描数1,逻辑读1238。

非阅读活动:10%。

运行2:

——不包括列建立索引

表“SalesOrderDetail”。扫描数1,逻辑读1238。

非阅读活动:10%。

运行3:

包括列

表“SalesOrderDetail”。扫描数1,逻辑读761。

非阅读活动:8%。

表2:运行第二个查询结果三次不同的非聚集索引可用

第一次和第二次测试导致相同的计划; 一个完整的扫描theSalesOrderDetail表。原因在四级,详细介绍在哪里条款不足够选择性受益于non-covering指数。行包括任何一组也分散在桌子上。表被读,每一行必须匹配组; 和操作处理器时间和内存消耗。

第三个测试发现在非聚集索引所需的一切; 但不像前面的查询,它没有发现内的连续行位于索引。他行组成每个组内是连续的指数; 但组织本身是分散在索引的长度。因此,SQL Server扫描索引。

扫描索引的表有两个优点:

该指数小于表,需要更少的读取。
行已经分组,需要更少的非阅读活动。

结论

包括列使非聚集索引成为各种覆盖索引查询,提高这些查询的性能; 有时会很显著。包括列增加一个索引的大小,但添加其他小的开销。任何时候你正在创建一个非聚集索引,特别是在一个外键列,问问自己——“我该什么附加列包含在这个索引吗?”

posted @ 2018-01-10 10:49 奔跑吧菜鸟阅读(143) 评论(0) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

奔跑吧菜鸟

翻译：与包括索引列:5级阶梯SQL Server索引

与包括索引列:5级阶梯SQL Server索引

该系列

包括列

在成为一个覆盖指数

测试第一个查询:活动产品的总数

测试第二个查询:根据日期活动总数

结论

公告