专栏首页学习有记阅读查询计划:SQL Server 索引进阶 Level 9

阅读查询计划:SQL Server 索引进阶 Level 9

David Durant,2011/10/05

关于系列

本文是属于Stairway系列:Stairway to SQL Server Indexes

索引是数据库设计的基础,并告诉开发人员使用数据库关于设计者的意图。不幸的是,当性能问题出现时,索引往往被添加为事后考虑。这里最后是一个简单的系列文章,应该使他们快速地使任何数据库专业人员“快速”

在整个阶段,我们经常说某个查询以某种方式执行,我们引用生成的查询计划来支持我们的陈述。 Management Studio显示的估计和实际查询计划可以帮助您确定索引的收益或缺乏。因此,这个级别的目的是让您充分了解查询计划,您可以:

  • 验证我们的断言,当你读这个楼梯。
  • 确定您的索引是否有益于您的查询。

有许多关于阅读查询计划的文章,其中包括MSDN库中的一些文章。这里我们不打算扩大或取代它们。事实上,我们会在这个层面提供其中的许多链接/参考。显示图形执行计划(http://msdn.microsoft.com/zh-...)是一个很好的开始。其他有用的资源包括Grant Fritchey的书,SQL Server 执行计划(以电子书形式免费提供)和Fabiano Amorim关于在查询计划输出中找到的各种运算符的Simple-Talk文章系列(http://www.simple-talk .COM /作家/法比亚诺 - 阿莫林/)。

图形查询计划

查询计划是SQL Server执行查询的一组指令。 SQL Server Management Studio将以文本,图形或XML格式显示查询计划。 例如,考虑以下简单的查询:

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

这个查询的计划如图1所示。

图1 - 图形格式的实际查询计划

或者,它可以被视为文本:

 |--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文档,像这样:

查询计划的显示可以请求如下:

  • 要请求图形查询计划,请使用Management Studio的SQL编辑器工具栏,该工具栏同时包含“显示估计执行计划”和“包括实际执行计划”按钮。 “显示估计执行计划”选项立即显示所选TSQL代码的查询计划图,而不执行查询。 “包括实际执行计划”按钮是一个开关,一旦您选择了此选项,您执行的每个查询批次都将显示新查询计划图表以及结果和消息。这个选项可以在图1中看到。
  • 要请求文本查询计划,请使用SET SHOWPLAN_TEXT ON语句。打开文本版本将关闭图形版本,不会执行任何查询。
  • 要查看XML版本,请右键单击图形版本,然后从上下文菜单中选择“显示执行计划XML”。

对于这个级别的其余部分,我们将重点放在图形视图上,因为它通常提供对计划的最快理解。对于查询计划,一张图片通常胜过千言万语。

阅读图形查询计划

图形查询计划通常从右到左读取;最右边的图标表示数据收集流中的第一步。这通常是访问堆或索引。你不会看到这里使用的单词表;相反,您将看到聚簇索引扫描或堆扫描。这是首先看看哪些索引,如果有的话,正在使用。

图形查询计划中的每个图标代表一个操作。有关可能的图标的其他信息,请参阅http://msdn.microsoft.com/zh-...

连接操作的箭头表示行,从一个操作流出并进入下一个操作。

将鼠标放在图标或箭头上会导致显示其他信息。

不要把操作当作一个步骤,因为这意味着一个操作必须在下一个操作开始之前完成。这不一定是真的。例如,当WHERE子句被评估时,也就是说,当一个Filter操作被执行时,行被一次评估一个;不是一次全部。在下一行到达过滤器操作之前,行可以移动到下一个操作。另一方面,排序操作必须在第一行移动到下一个操作之前全部完成。

使用一些额外的信息

图形查询计划显示两个不属于计划本身的可能有用的信息; 建议的指标和每个操作的相对成本。

在上面的示例中,建议的索引(以绿色显示并按空间要求截断)建议在联系人表的后缀列上使用非聚簇索引; 包括标题,名字,中间名和姓氏的列。

这个计划的每个操作的相对成本告诉我们,排序操作是总成本的5%,而表扫描是95%的工作。 因此,如果我们想提高这个查询的性能,我们应该解决表扫描,而不是排序; 这就是为什么建议索引。 如果我们创建推荐的索引,像这样:

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

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

新的非聚集索引(索引键为Suffix)具有“WHERE Suffix ='Jr.”条目聚集在一起; 因此,检索数据所需IO的减少。 因此,与之前计划中的排序操作相同的排序操作现在占查询总成本的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 )

现在,所需的条目仍然聚集在索引内,并且在每个集群内,它们都是按照请求的顺序; 如新查询计划所示,如图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 - JOIN的查询计划

快速查看计划告诉我们一些事情:

  • 两个表都在同一时间被扫描。
  • 大部分的工作都花在扫描表格上。
  • 更多的行或SalesOrderHeader表比出联系人表。
  • 这两个表格不是聚集在一起的; 因此将每个SalesOrderHeader行与其联系人行进行匹配将需要额外的努力。 在这种情况下,使用哈希匹配操作。 (关于哈希的更多信息。)
  • 排序所选行所需的工作量可以忽略不计。

即使是单独的行流也可以分解成单独的较少行的流,以利用并行处理。 例如,如果我们将上述查询中的WHERE子句更改为WHERE Suffix为NULL。

更多的行将被返回,95%的Contact行有NULL后缀。 新的查询计划反映了这一点,如图4所示。

图4 - 一个并行查询计划

新的计划也向我们展示了联系人行数的增加,导致匹配和排序操作成为此查询的关键路径。如果要提高绩效,就要先攻击这两个行动。再次,包含列的索引将有所帮助。

像大多数连接一样,我们的例子通过外键/主键关系连接两个表。其中的一个表Contact(联系人)按ContactID进行排序,ContactID也恰好是其主键。在另一个表中,SaleOrderHeader,ContactID是一个外键。由于ContactID是一个外键,因此ContactID访问的SaleOrderHeader数据请求(例如我们的联接示例)可能是常见的业务需求。这些请求将受益于ContactID上的索引。

无论何时索引一个外键列,总是问自己,如果有的话,列应该作为包含列添加到索引中。在我们的例子中,我们只有一个查询,而不是一系列的查询来支持。因此,我们唯一包含的列将是OrderDate。为了支持针对SaleOrderHeader表的一系列面向ContactID的查询,我们会根据需要在索引中包含更多的SaleOrderHeader列以支持这些附加查询。

我们的CREATE INDEX语句是:

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

而执行我们的SalesOrderHeader和Contact信息连接的新计划如图5所示。

图5 - 计划在每个表上使用支持索引的JOIN查询

因为两个输入流现在都由连接谓词列ContactID排序; 查询的JOIN部分可以在不分割流的情况下完成,也不需要散列; 从而将工作负荷的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

只需要很少的内存,因为只会有两组; 女性和男性,无论输入行的数量。 另一方面:

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

会导致大量的群体,每个群体都需要自己的记忆空间; 可能消耗太多内存,哈希成为解决查询的不良技术。

有关查询计划散列的更多信息,请访问http://msdn.microsoft.com/en-...

排序

如果数据没有被预分类(索引),并且如果SQL Server认为哈希不能有效地完成,SQL Server将对数据进行排序。这通常是最不可取的选择。因此,如果在计划的早期出现“排序”图标,请检查是否可以改进索引。如果Sorticon出现在计划末尾附近,这可能意味着SQL Server将最终输出按ORDER BY子句所请求的顺序排序;并且该序列与用于解析查询的JOIN,GROUP BY和UNION的序列不同。通常情况下,你可以做些什么来避免这种情况。

结论

查询计划显示SQL Server打算使用或已经使用的方法来执行查询。它通过详细描述将要使用的操作,从操作到操作的行的流程以及涉及的并行性来实现。

  • 您可以将这些信息视为文本,图形或XML显示。
  • 图形计划显示每个操作的相对工作量。
  • 图形计划可能会建议一个索引,以提高查询的性能。
  • 了解查询计划将帮助您评估和优化索引设计。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • T-SQL进阶:超越基础 Level 2:编写子查询

    Woodson
  • SQL Server 索引内部结构:SQL Server 索引进阶 Level 10

    Woodson
  • 深入非聚集索引:SQL Server索引进阶 Level 2

    Woodson
  • Elasticsearch解决问题之道——请亮出你的DSL!

    以上的看似复杂的问题,如果转换成DSL,清楚的写出来,梳理清楚问题的来龙去脉,问题就自然解决了一大半。

    铭毅天下
  • Elasticsearch解决问题之道——请亮出你的DSL!

    以上的看似复杂的问题,如果转换成DSL,清楚的写出来,梳理清楚问题的来龙去脉,问题就自然解决了一大半。

    zhisheng
  • axios api

    copy_left
  • SAP CRM WebClient UI和Hybris Commerce里的跨组件跳转

    How to debug cross component navigation framework

    Jerry Wang
  • 使用SAP WebIDE消费API Portal里创建的API时的错误消息

    我使用API portal创建好一个新的API之后,在SAP云平台Neo环境里创建必备的Destination,使得WebIDE可以通过这些Destinatio...

    Jerry Wang
  • 一个SAP顾问在美国的这些年

    今天的文章来自我的老乡宋浩,之前作为SAP顾问在美国工作多年。如今即将加入SAP成都研究院S4CRM开发团队。我们都是大邑人。

    Jerry Wang
  • ElasticSearch(7.2.2)-为什么不⽤mysql做全⽂搜索

    版权声明:本文为博主原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接和本声明。

    cwl_java

扫码关注云+社区

领取腾讯云代金券