翻译：阅读查询计划:通往SQL Server索引级别9的阶梯。

 

阅读查询计划:通往SQL Server索引级别9的阶梯。
大卫•杜兰特2011/10/05
该系列
本文是楼梯系列的一部分:SQL Server索引的阶梯。
索引是数据库设计的基础，并且告诉开发人员使用数据库对设计人员的意图有很大的影响。不幸的是，当性能问题出现时，索引常常被添加为事后考虑。这里最后是一个简单的系列文章，应该让任何数据库专业人员迅速“跟上”他们。
在整个过程中，我们经常声明某个查询以某种方式执行;我们引用生成的查询计划来支持我们的声明。管理工作室的估计和实际查询计划的显示可以帮助您确定索引的优点或不足。因此，这个级别的目的是让您充分了解查询计划，您可以:
请在阅读这段楼梯时验证我们的断言。
确定索引是否有利于查询。
有许多关于阅读查询计划的文章，包括MSDN库中的一些文章。我们的目的不是扩大或取代他们。事实上，我们将在这一层面上为他们提供链接/参考。一个很好的起点是图形显示执行计划(http://msdn.microsoft.com/en-us/library/ms178071.aspx)。其他有用的资源包括Grant Fritchey的书，SQL Server执行计划(免费提供电子书格式)，以及Fabiano Amorim的一系列简单的关于在你的查询计划输出中发现的操作符的文章(http://www.simple-talk.com/author/fabiano-amorim/)。
图形查询计划
查询计划是SQL Server执行查询的指令集。SQL Server Management Studio将以文本、图形或XML格式显示查询计划。例如，考虑以下简单查询:
选择LastName, FirstName, MiddleName, Title。
从Person.Contact
后缀=“小”。
按标题
可以查看该查询的计划，如图1所示。

图1 -图形格式的实际查询计划。
或者，它可以被视为文本:
|——排序(按:[AdventureWorks]。[人]。(联系)。[标题]ASC))
|——聚集索引
扫描(对象:([AdventureWorks],[人]。[联系]。[PK_Contact_ContactID]),
地点:([AdventureWorks],[人]。(接触)。(后缀)= N 'Jr。'))
或者作为一个XML文档，这样开始:

查询计划的显示如下:
要请求图形化查询计划，请使用Management Studio的SQL Editor工具栏，该工具栏既有“显示估计执行计划”，也有“包含实际执行计划”按钮。“显示估计执行计划”选项将立即显示所选的TSQL代码的查询计划图，而不执行查询。“包含实际执行计划”按钮是一个开关，一旦您选择了这个选项，您所执行的每一个查询批次都将在一个新的选项卡中显示查询计划图，以及结果和消息。这个选项如图1所示。
要请求文本查询计划，请使用SET SHOWPLAN_TEXT语句。打开文本版本将会关闭图形化版本，不会执行任何查询。
要查看XML版本，右键单击图形版本，并从上下文菜单中选择“显示执行计划XML”。
对于这个级别的其余部分，我们关注图形化视图，因为它通常提供对计划的最快理解。对于查询计划，一幅图通常胜过千言万语。
阅读图形查询计划
图形查询计划通常是从右到左读取;用最右边的图标表示数据收集流中的第一步。这通常是访问堆或索引。您将不会看到这里使用的单词表;相反，您将看到聚集索引扫描或堆扫描。这是第一个查看哪些索引(如果有的话)正在使用的地方。
图形查询计划中的每个图标表示一个操作。附加信息的可能的图标,看到图形在http://msdn.microsoft.com/en-us/library/ms175913.aspx上执行计划图标
连接操作的箭头表示行，从一个操作流到下一个操作。
将鼠标放在图标或箭头上，将会显示更多信息。
不要把操作看作是一个步骤，因为这意味着一个操作必须在下一次操作开始之前完成。这并不一定是真的。例如，当对WHERE子句进行评估时，也就是说，在执行筛选操作时，每次只计算一个行;并不是所有的。在随后一行到达筛选器操作之前，行可以移动到下一个操作。另一方面，排序操作必须在第一行移动到下一个操作之前全部完成。
使用一些额外的信息
图形化查询计划显示了两个可能有用的信息，这些信息不是计划本身的一部分;建议的指标和每个操作的相对成本。
在上面所示的示例中，根据空间需求显示的绿色和截断的建议索引，建议在联系人表的后缀列上有一个非聚集索引;包含标题、FirstName、MiddleName和LastName列。
这个计划的每个操作的相对成本告诉我们排序操作是总成本的5%，而表扫描是95%的工作。因此，如果我们想要提高这个查询的性能，我们应该处理表扫描，而不是排序;这就是为什么要提出一个指数。如果我们创建推荐索引，如下所示:

CREATE NONCLUSTERED INDEX IX_Suffix ON Person.Contact
(
Suffix
)
INCLUDE ( Title, FirstName, MiddleName, LastName )

 然后再重新运行查询，我们的读数从569下降到3;下面显示的新查询计划说明了原因。

新的非聚集索引，其索引键为后缀，有“WHERE后缀= 'Jr.”。”条目聚集在一起;因此，IO的减少需要检索数据。因此，排序操作与之前的计划中的排序操作相同，现在占查询总成本的75%以上，而不是它所花费的成本的5%。因此，最初的计划需要75 / 5倍的工作量来收集与当前计划相同的信息。
由于我们的WHERE子句只包含一个相等的操作符，所以我们可以通过将Title列移动到索引键来提高索引值，例如:

IF  EXISTS (SELECT * FROM sys.indexes
WHERE OBJECT_ID = OBJECT_ID(N'Person.Contact')

AND name = N'IX_Suffix')
DROP INDEX IX_Suffix ON Person.Contact
CREATE NONCLUSTERED INDEX IX_Suffix ON Person.Contact
(
Suffix, Title
)
INCLUDE ( FirstName, MiddleName, LastName )

 现在，需要的条目仍然在索引中聚集在一起，并且在每个集群中它们处于被请求的序列中;如新的查询计划所示，如图2所示。

图2-重建非聚集索引后的查询计划。
现在的计划表明不再需要排序操作。在这一点上，我们可以放弃我们的高收益覆盖指数。这使接触表恢复到我们开始时的样子;当我们进入下一个主题时，我们希望它进入状态。
查看平行流
如果两个行流可以并行处理，它们将在图形显示中出现在彼此的上方和下方。箭头的相对宽度表示通过每个流处理的行数。
例如，下面的连接扩展了之前的查询，以包含销售信息:

SELECT C.LastName, C.FirstName, C.MiddleName, C.Title
, H.SalesOrderID, H.OrderDate
FROM Person.Contact C
JOIN Sales.SalesOrderHeader H ON H.ContactID = C.ContactID
WHERE Suffix = 'Jr.'
ORDER BY Title

 查询计划如图3所示。

图3 -连接的查询计划。
快速浏览一下这个计划可以告诉我们一些事情:
两个表同时扫描。
大部分的工作都花在了浏览表格上。
出现更多的行或SalesOrderHeader表，而不是从Contact表中取出。
这两张表不是按同一顺序排列的;因此，匹配每个SalesOrderHeader行与它的联系人行将需要额外的努力。在这种情况下，将使用散列匹配操作。(稍后将详细介绍散列)。
对所选行进行排序所需的工作是可以忽略的。
即使单独的行流也可以被分割成不同的流，每个流都可以利用并行处理。例如，如果我们将上述查询中的WHERE子句改为NULL。
将会返回更多的行，因为95%的Contact行有一个空后缀。新的查询计划反映了这一点，如图4所示。

 

图4 -一个并行查询计划。
新计划还向我们表明，越来越多的接触行导致匹配和排序操作成为该查询的关键路径。如果我们需要改进它的性能，我们必须首先攻击这两个操作。同样，包含列的索引也会有所帮助。
与大多数连接一样，我们的示例通过外键/主键关系连接两个表。其中一个表，Contact，是由ContactID排序的，它也是它的主键。在另一个表中，SaleOrderHeader, ContactID是一个外键。因为ContactID是一个外键，所以ContactID访问的SaleOrderHeader数据的请求(例如我们的连接示例)可能是一个常见的业务需求。这些请求将受益于ContactID的索引。
在索引外键列时，总是要问自己，如果有的话，应该将列添加到索引中。在我们的例子中，我们只有一个查询而不是一个查询家族来支持。因此，我们仅包含的列将是OrderDate。为了支持针对SaleOrderHeader表的面向对象的查询系列，我们将在索引中包含更多的SaleOrderHeader列，以支持这些附加查询。
我们的CREATE INDEX语句是:

CREATE NONCLUSTERED INDEX IX_ContactID ON Sales.SalesOrderHeader
(
ContactID
)
INCLUDE ( OrderDate )
执行SalesOrderHeader和联系人信息的新计划如图5所示。

图5 -每个表上有一个支持索引的连接查询计划。
因为现在两个输入流都由连接谓词列进行排序，ContactID;可以在不分割流和不散列的情况下完成查询的连接部分;因此，将26 + 5 + 3 = 34%的工作负载降低到工作负载的4%。
排序、预分类和散列
许多查询操作要求在执行操作之前将数据分组。它们包括不同的、联合的(包含不同的)、GROUP BY(及其各种聚合函数)和JOIN。通常，SQL Server将使用三种方法之一来实现这个分组，第一个方法需要您的帮助:
很高兴地发现数据已经被提交到分组序列中。
通过执行散列操作对数据进行分组。
将数据排序到分组序列中。
预分类
索引是您发布数据的方式;也就是说，在经常需要的序列中向SQL Server提供数据。这就是为什么创建非聚集索引(每个包含包含列)的原因使我们前面的示例受益。事实上，如果您将您的鼠标放在最近查询中的Merge Join图标上，这个短语将匹配两个适当排序的输入流中的行，利用它们的排序顺序。就会出现。这告诉您两个表/索引的行是使用绝对最小内存和处理器时间连接的。适当地排序输入是一个很好的短语，可以在查询计划图标的时候查看，因为它验证了您选择的索引。
哈希
如果传入的数据不是理想的序列，那么SQL Server可能会使用散列操作来对数据进行分组。哈希是一种可以使用大量内存的技术，但通常比排序更有效。在执行DISTINCT、UNION和JOIN操作时，哈希比在单独的行中进行排序更有优势，因为它可以传递到下一个操作，而不必等待所有传入的行被散列。然而，在计算分组集料时，必须在将任何聚合值传递给下一个操作之前读取所有输入行。
散列信息所需的内存数量与所需的组数直接相关。因此，哈希需要解决:

SELECT Gender, COUNT(*)
FROM NewYorkCityCensus
GROUP BY Gender
只需要很少的记忆，因为只有两组;女性和男性，不管输入行数。另一方面:

SELECT LastName, FirstName, COUNT(*)
FROM NewYorkCityCensus
GROUP BY LastName, FirstName

 

会导致大量的群体，每个群体都需要自己的记忆空间;可能消耗这么多内存，哈希成为解决查询的一种不受欢迎的技术。
更多查询计划散列,请访问http://msdn.microsoft.com/en-us/library/ms189582.aspx。
排序
如果数据没有被预发布(索引)，如果SQL Server认为不能有效地进行哈希，那么SQL Server将对数据进行排序。这通常是最不可取的选择。因此，如果在计划的早期出现了排序图标，请检查是否可以改进索引。如果Sorticon出现在计划的末尾，它可能意味着SQL Server将最终输出排序为ORDER by子句请求的序列;这个序列与用于解析查询的连接、组BYs和union的序列不同。通常，在这一点上，你几乎没有办法避免这种情况。
结论
查询计划向您展示了SQL Server打算使用或使用的方法来执行查询。它通过详细描述将要使用的操作、从操作到操作的行流以及所涉及的并行性来实现这一点。
您可以将此信息视为文本、图形或XML显示。
图形化计划显示了每个操作的相对工作量。
图形化的计划可能建议一个索引来提高查询的性能。
了解查询计划将帮助您评估和优化您的索引设计。