SQL Server索引进阶 第九篇:读懂执行计划

2016-3-3 152+ 分享:

本文是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格式显示执行计划。看一下下面这个简单的查询。

 


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

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

 

SQL Server索引进阶 第九篇:读懂执行计划

用文本显示就是。

|--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索引进阶 第九篇:读懂执行计划

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

  • 查看图形化的执行计划。在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%的工作。因此,如果我们想要改进查询性能,我们保留表扫描,去掉排序,这就是为什么会提示建立索引。如果我们创建推荐的索引。

 


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

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

 

SQL Server索引进阶 第九篇:读懂执行计划

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

因为我们的where子句只是一个相等的操作,我们可以改进一下我们的索引,把Title列加入索引键,就像下面这样。

 


  1. IF EXISTS (SELECT * FROM sys.indexes 
  2. WHERE OBJECT_ID = OBJECT_ID(N'Person.Contact'
  3.  
  4. AND name = N'IX_Suffix'
  5. DROP INDEX IX_Suffix ON Person.Contact 
  6. CREATE NONCLUSTERED INDEX IX_Suffix ON Person.Contact 
  7. Suffix, Title 
  8. INCLUDE ( FirstName, MiddleName, LastName ) 

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

 

SQL Server索引进阶 第九篇:读懂执行计划

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

查看并行的数据流

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

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

 


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

执行计划是下面的样子。

 

 

SQL Server索引进阶 第九篇:读懂执行计划

 

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

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

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

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

 

SQL Server索引进阶 第九篇:读懂执行计划

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

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

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

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

 


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

新的执行计划如下所示。

 

 

 

SQL Server索引进阶 第九篇:读懂执行计划

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

排序,预排序和哈希匹配

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

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

预先排序

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

哈希匹配

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

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

 


  1. SELECT Gender, COUNT(*) 
  2. FROM NewYorkCityCensus 
  3. GROUP BY Gender 

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

 


  1. SELECT LastName, FirstName, COUNT(*) 
  2. FROM NewYorkCityCensus 
  3. 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的管理器中可用
  • 理解执行计划会帮助你评估和优化你设计的索引。