SQL Server索引进阶：第九级，读懂执行计划

原文地址：

Stairway to SQL Server Indexes: Level 9，Reading Query Plans

本文是SQL Server索引进阶系列（Stairway to SQL Server Indexes）的一部分。

在这个系列中，我们经常会以特定的方式执行特定的查询。我们引用生成的执行计划来支持我们的论调。SQL Server管理器显示的预估的和实际的查询计划，可以帮助我们确定索引的好处，以及其中的缺陷。因此，本文的主要目的是给你一些关于执行计划的充分的理解：

  • 验证你在系列中阅读到的我们的断言。
  • 确定你的索引是否对查询有好处。

在阅读执行计划方面有很多的文章，包括在MSDN上就有很多。我们今天的目的不是扩展它们，也不是代替他们。事实上，我们很多可以链接到他们的引用。 Displaying Graphical Execution Plans (SQL Server Management Studio)是一篇很好的启蒙文章，另外一个有帮助的资源是Grant Fritchey的书《SQL Server Execution Plans 》，这本书有免费的pdf下载。还有就是Fabiano Amorim的一些文章http://www.simple-talk.com/author/fabiano-amorim/。

图形化的执行计划

一个执行计划是SQL Server根据一个查询的一系列指令。SQL Server管理器可以用为文本，图形或者XML格式显示执行计划。看一下下面这个简单的查询。

SELECT LastName, FirstName, MiddleName, Title 
FROM Person.Contact 
WHERE Suffix = 'Jr.' 
ORDER BY Title

上面这个查询的执行计划如下所示。

用文本显示就是。

|--Sort(ORDER BY:([AdventureWorks].[Person].[Contact].[Title] ASC))


|--Clustered Index
Scan(OBJECT:([AdventureWorks].[Person].[Contact].[PK_Contact_ContactID]),
WHERE:([AdventureWorks].[Person].[Contact].[Suffix]=N'Jr.'))

用XML显示就是。

查看各种格式的执行计划：

  • 查看图形化的执行计划。在SQL Server管理器的工具栏，有两个按钮：“显示估计的执行计划”和“包括实际的执行计划”。“显示估计的执行计划”选项用来立即显示TSQL语句的图形化执行计划，不用执行查询。“包括实际的执行计划”是在你执行查询之后，在新tab中显示实际的执行计划。
  • 查看文本的执行计划。使用SET SHOWPLAN_TEXT ON。打开文本显示，就会关闭图形显示，也不会执行你的查询。
  • 查看XML格式的执行计划。在图形的执行计划中点击右键，从上下文菜单中选择“显示查询计划XML”。（这个菜单在我安装的中文版SQL Server 2005中没有看到，在SQL Server 2008的管理器中可以看到。）

本文以图形化的执行计划为主，通过它可以很快的理解执行计划。对于执行计划，一张图要比一千句话更好。

阅读图形化的执行计划

图形化的执行计划，通常是从右向左来阅读的，最右面图标是获取数据的第一步。正常来说，是访问堆或者索引。在这里你不会看到“表”这个词，恰恰相反，你看到的是“聚集索引扫描”和“堆扫描”。图形执行计划中的每个图标代表一个操作。关于图标的详细信息，可以参看Graphical Execution Plan Icons 。

连接图标的箭头代表一个操作到下一个操作的行数。

把你的鼠标放在图标或者箭头上，可以显示详细的信息。

不要把一个操作看做是一个步骤，意思是上一个操作必须要完成，下一个操作才可以开始。也有例外。例如，在评估where子句的时候，会执行过滤操作，每次评估一行，不是一次性评估完的。在过滤下一行的时候，上一行已经进入下一个操作了。另一方面，排序操作在进入下一个操作之前必须要全部完成。

使用一些额外的信息

图形化的执行计划除去显示计划本身的信息之外，还显示了两个有帮助的信息：建议的索引和每个操作的相对消耗。

在上面的例子中，绿色显示的就是建议的索引（在SQL Server 2008的管理器中会出现绿色的信息，就是建议的索引），推荐在Contact表的Suffix列建立非聚集索引，并且包含Title, FirstName, MiddleName和LastName列。

相关的消耗显示，排序占用了总体消耗的5%，表扫描占用了95%的工作。因此，如果我们想要改进查询性能，我们保留表扫描，去掉排序，这就是为什么会提示建立索引。如果我们创建推荐的索引。

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

再次执行查询，逻辑读从569次降到了3次，下面是新的执行计划。

在新的非聚集索引中，Suffix是索引键，where stuffix='Jr.'的记录是聚集在一起的，因此会减少获取数据所需要的读取次数。排序操作，占用了超过75%的消耗，而不是之前看到的5%。因此，原来的计划需要75/5=15倍

因为我们的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 )

现在，需要的信息任然会聚集在一起，新的执行计划如下所示。

计划显示排序操作没有了。

查看并行的数据流

如果两个数据流可以并行处理，在图形的执行计划中会一上一下的形式出现。箭头的宽度表明每个数据流处理数据的行数。

例如，下面的查询，扩展了之前的查询，同时查询一些销售信息。

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

执行计划是下面的样子。

上面的执行计划告诉我们一些事情：

  • 两张表同时被扫描。
  • 大部分的工作花费在表扫描上。
  • 大部分的数据来自于SalesOrderHeader表。
  • 两张表聚集的顺序不是一样的，因此SalesOrderHeader中满足条件的每一行，在Contact表需要额外的工作。这种情况就需要哈希匹配操作。
  • 堆排序的需要是微不足道的。

每个数据流可以分成更小的数据流来并行处理。例如，我们将上面查询中的where自己改为where suffix is NULL。

会返回更多的行，因为Contact表中95%的数据行Suffix列是NULL。新的执行计划如下所示。

新的执行计划告诉我们，增加的Contact行导致匹配和排序操作是这个查询关键。如果我们需要提高性能，我们要首先从这两个操作入手。带有包含列的索引再一次帮助了我们。

就像很多连接查询一样，我们的例子通过主外键连接了两张表。Contact表以ContactID排序，也是表的主键。SalesOrderHeader表，ContactID是外键。因为是外键，通过ContactID访问SalesOrderHeader表的数据，在业务需求中很常见。在ContactID上建立索引会有很大的帮助。

当你在外键上创建索引的时候，经常要问自己，是否需要添加一些列作为索引的包含列。在我们的例子中，只是一个查询，不需要支持一大堆查询。因此，我们只包含OrderDate列。在SalesOrderHeader表支持以ContactID为中心的查询，如果需要，就在索引中包含更多的列。

创建索引的语句如下所示。

CREATE NONCLUSTERED INDEX IX_ContactID ON Sales.SalesOrderHeader 
( 
ContactID 
) 
INCLUDE ( OrderDate )

新的执行计划如下所示。

因为所有的输入流都按照ContactID排序，没有了分组和哈希匹配，工作量从26+5+3=34%减少到4%。

排序，预排序和哈希匹配

很多操作希望在操作之前数据是分好组的。这类操作包括：distinct，union，group by和join。正常情况，SQL Server将使用下面三种方法中的一种来完成分组：

  • 很高兴的发现，数据已经预先排序进入分组序列。
  • 通过哈希匹配操作对数据分组。
  • 对即将分组的序列中的数据排序。

预先排序

索引就是你预先排序数据的方式，向SQL Server提供它通常所需的顺序。这就是创建有包含列的非聚集索引有利于查询的原因。事实上，如果你把鼠标放在图形执行计划的“合并连接”图标上的时候，将会出现“从两个已进行了相应排序的输入表中，使用其排序顺序对行进行匹配”的提示信息。这告诉我们两张表/索引在连接的时候，使用了最小的内存和处理器时间。

哈希匹配

如果输入的数据不是想要的顺序，SQL Server可能会用哈希匹配操作进行分组。哈希匹配是一种消耗大量内存的技术，但是比排序要高效。在执行distinct，union和join操作的时候，哈希匹配比排序有优势，因为处理完一行，这一行就可以进行下一个操作，而不用等所有行都哈希匹配完。但是，在计算分组聚合的时候，在进入下一个阶段之前，还是需要读取所有行才行。

哈希匹配所需要的内存，直接和分组产生的数量有关。

 

SELECT Gender, COUNT(*) 
FROM NewYorkCityCensus 
GROUP BY Gender

上面的分组消耗的内存就很少，因为只有两个分组：Female和Male。和输入数据的行数没有关系。

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

这个分组就会占用大量的内存，因为产生大量的组。这么大量的内存消耗，导致哈希匹配在查询的时候变成了一个不受欢迎的技术。

排序

如果数据没有排好序（没有索引），同时SQL Server认为哈希匹配不能高效的完成，SQL Server就会对数据进行排序。正常来说，这可能是最不想看到的。因此，如果排序图标出现在执行计划的早期，检查一下是否可以改进你的索引。如果排序图标出现在执行计划的后期，很可能意味着SQL Server因为请求中的order by子句，而对最终输出结果进行排序；这个顺序不同于join，group by和union中的顺序。这时候的排序很可能没有办法避免。

结论

执行计划告诉你SQL Server执行查询，将要使用，和已经使用的方法。通过显示每一个操作的详细信息，从一个操作到另一个操作的数据流，并行执行信息。

  • 你可以以文本，图形，或者XML格式（SQL Server 2008的管理器中可用）查询这个执行计划。
  • 图形化的执行计划显示了每个操作相关的工作量。
  • 图形化的执行计划会给出建议的索引，可以提升查询的性能。（SQL Server 2008的管理器中可用）
  • 理解执行计划会帮助你评估和优化你设计的索引。

posted on 2015-05-11 15:49  子日  阅读(536)  评论(0)  编辑  收藏  举报

导航