SQL语句执行计划分析

1、· Table Scan(表扫描)：如果看到这个信息，就说明数据表上没有聚集索引，或者查询优化器没有使用索引来查找。意即资料表的每一行都被检查到。如果资料表相对较小的话，表扫描可以非常快速，有时甚至快过使用索引。因此，当看到有执行表扫描时，第一件要做的事就是看看数据表有多少数据行。如果不是太多的话，那么表扫描可能提供了最好的总体效能。但如果数据表大的话，表扫描就极可能需要长时间来完成，查询效能就大受影响。在这种情况下，就需要仔细研究，为数据表增加一个适当的索引用于这个查询。假设你发现某查询使用了表扫描，有一个合适的非聚集索引，但它没有用到。这意味着什么呢？为什么这个索引没有用到呢？如

2、果需要获得的数据量相对数据表大小来说非常大，或者数据选择性不高(意味着同一个字段中重复的值很多)，表扫描经常会比索引扫描快。例如，如果一个数据表有10000个数据行，查询返回1000行，如果这个表没有聚集索引的话，那么表扫描将比使用一个非聚集索引更快。或者如果数据表有10000个数据行，且同一个字段(WHERE条件句有用到这个字段)上有1000笔重复的数据，表扫描也会比使用非聚集索引更快。查看图形执行计划上的数据表上的弹出式窗口时，请注意”预估的资料行数(Estimated Row Count)”。这个数字是查询优化器作出的多少个数据行会被返回的最佳推测。如果执行了表扫描且”预估的数据行数”数

3、值很高的话，就意味着返回的记录数很多，查询优化器认为执行表扫描比使用可用的非聚集索引更快。· Index Seek(索引查找)：索引查找意味着查询优化器使用了数据表上的非聚集索引来查找数据。性能通常会很快，尤其是当只有少数的数据行被返回时。· Clustered Index Seek(聚集索引查找)：这指查询优化器使用了数据表上的聚集索引来查找数据，性能很快。实际上，这是SQL Server能做的最快的索引查找类型。· Clustered Index Scan(聚集索引扫描)：聚集索引扫描与表扫描相似，不同的是聚集索引扫描是在一个建有聚集索引的数据表上执行的。和一

4、般的表扫描一样，聚集索引扫描可能表明存在效能问题。一般来说，有两种原因会引此聚集索引扫描的执行。第一个原因，相对于数据表上的整体数据行数目，可能需要获取太多的数据行。查看”预估的数据行数量(Estimated Row Count)”可以对此加以验证。第二个原因，可能是由于WHERE条件句中用到的字段选择性不高。在任何情况下，与标准的表扫描不同，聚集索引扫描并不会总是去查找数据表中的所有数据，所以聚集索引扫描一般都会比标准的表扫描要快。通常来说，要将聚集索引扫描改成聚集索引查找，你唯一能做的是重写查询语句，让语句限制性更多，从而返回更少的数据行。绝大多数情况下，查询优化器会对连接进行分析，按最有

5、效率的顺序，使用最有效率的连接类型来对数据表进行连接。但并不总是如此。在图形执行计划中你可以看到代表查询所使用到的各种不同连接类型的图标。此外，每个连接图标都有两个箭头指向它。指向连接图标的上面的箭头代表该连接的外部表，下面的箭头则代表这个连接的内部表。箭头的另一头则指向被连接的数据表名。有时在多表连接的查询中，箭头的另一头指向的并不是一个数据表，而是另一个连接。如果将鼠标移到指向外部连接与内部连接的箭头上，就可以看到一个弹出式窗口，告诉你有多少数据行被发送至这个连接来进行处理。外部表应该总是比内部表含有更少的数据行。如果不是，则说明查询优化器所选择的连接顺序可能不正确(下面是关于这个话题的更

6、多信息)。首先，让我们来看看连接类型。SQL Server可以使用三种不同的技术来连接资料表：嵌套循环(nested loop)，散列(hash)，以及合并(merge)。一般来说，嵌套循环是最快的连接类型，但如果不可能使用嵌套循环的话，则会用到散列或者合并作为合适的连接类型。两者都比嵌套循环连接慢。当连接大表时，则合并连接可能是最佳选项，而非嵌套循环连接。唯一的明确这一点的方式是对两者都进行测试以查看哪一个最有效率。如果你怀疑某个查询速度慢的原因可能是因为它所使用的连接类型不理想，那么你可以使用连接提示来复盖查询优化器的选择。在使用连接提示之前，你需要花费一些时间去了解一下每种连接类型以及它

7、们的工作方式。这是一个复杂的话题，超出了本文的讨论范围。查询优化器选择最有效率的连接类型来连接数据表。例如，嵌套循环连接的外部表应该是连接的两个表中较小的那个表。散列连接也是一样，它的外部表应该是较小的那个表。如果你觉得查询优化器选择的连接顺序是错误的，可以使用连接提示来复盖它。很多情况下，唯一的确认使用连接提示改变连接类型或连接顺序是提升还是降低了效能的方式，就是对它们进行测试，看看发生了什么。7.0, 2000, 2005 如果你的SQL Server有多个CPU，并且没有修改SQL Server的默认设置来限制SQL Server使用服务器上所有CPU的能力，那么查询优化器会考虑使用平行

8、处理(parallelism)来执行某些查询。平行处理指在多个CPU上同时运行一个查询的能力。很多情况下，一个运行在多个处理器上的查询比仅运行在单个处理器上的查询要快，但并不总是这样。查询优化器并不会总是使用平行处理，即使在它能使用的时候。这是因为查询优化器在决定使用平行处理前会考虑到各种不同的因素。例如当前SQL Server上处于活动状态的连接数量，CPU忙碌程度，是否有足够的内存来运行平行化查询，需要处理的数据行数量，以及这个查询的类型。查询优化器收集到这些真实的数据后，再决定平行处理是不是运行这个查询的最佳选择。你可能会发现，某次一个查询没有用到平行处理，但稍后某次再次运行同样的查询时

9、，却又用到了平行处理。有时，使用多个处理器所需的花费会大于使用它们能所能节省的资源。尽管查询处理器的确会衡量使用平行查询的正反两面的影响，但它的猜想并不总是正确的。如果怀疑平行处理防碍了某条查询的性能，你可以使用OPTION (MAXDOP 1)提示来关闭该查询的平行处理。决定是否使用平行处理的唯一方式是通过这两种方式对查询进行测试，看看发生了什么。7.0, 2000, 2005 *查看图形执行计划时，你可能会发现某个图标的文字用红色显示，而非通常情况下的黑色。这意味着相关的表的一些统计数据遗失，统计数据是查询优化器生成一个好的执行计划所必须的。遗失的统计数据可以通过右键这个图标，并选择”创建

10、遗失的统计资料”来创建。这时会弹出”创建遗失的统计数据”对话框，通过它可以很容易地创建遗失的统计数据。当可以选择去更新遗失的统计资料时，应该总是这样做，因为这样极有可能让你正在分析的查询语句从中获得效能上的好处。7.0, 2000, 2005 *有时你会在图形执行计划上看到标识了”Assert”的图标。这意味着查询优化器正在验证查询语句是否有违反引用完整性或者条件约束。如果没有，则没有问题。但如果有的话，查询优化器将无法为该查询建立执行计划，同时会产生一个错误。7.0, 2000, 2005 *你常常会在图形执行计划上看到标识成”书签查找(Bookmark Lookup)”的图标。书签查找相当

11、常见。书签查找的本质是告诉你查询处理器必须从数据表或者聚集索引中来查找它所需要的数据行，而不是从非聚集索引中直接读取。打比方说，如果一个查询语句的SELECT,JOIN以及WHERE子句中的所有字段，都不存在于那个用来定位符合查询条件的数据行的非聚集索引中，那么查询优化器就不得不做额外的工作在数据表或聚集索引中查找那些满足这个查询语句的字段。另一种引起书签查找的原因是使用了SELECT *。由于在绝大多情况下它会返回比你实际所需更多的数据，所以应该永不使用SELECT *.从性能方面来说，书签查找是不理想的。因为它会请求额外的I/O开销在字段中查找以返回所需的数据行。如果认为书签查找防碍了查询

12、的性能，那么有四种选择可以用来避免它：可以建立WHERE子句会用到的聚集索引，利用索引交集的优势，建立覆盖的非聚集索引，或者(如果是SQL Server 2000/2005企业版的话)可以建立索引视图。如果这些都不可能，或者使用它们中的任何一个都会耗用比书签查找更多的资源，那么书签查找就是最佳的选择了。7.0, 2000, 2005 有时查询优化器需要在tempdb数据库中建立临时工作表。如果是这样的话，就意味着图形执行计划中有标识成Index Spool, Row Count Spool或者Table Spool的图标。任何时候，使用到工作表一般都会防碍到性能，因为需要额外的I/O开销来维护

13、这个工作表。理想情况下应该不要用到工作表。不幸的是并不能总是避免用到工作表。有时当使用工作表比其它选择更有效率时，它的使用实际上会增强性能。 不论何种情况，图形执行计划中的工作表都应该引起你的警觉。应该仔细检查这样的查询语句，看看是否有办法重写查询来避免用到工作表。有可能没有办法。但如果有的话，你就朝提升这个查询的性能方面前进了一步。7.0, 2000, 2005。 * 在图形执行计划上看到流聚合(Stream Aggregate)图标就意味着有对一个单一的输入进行了聚合。当使用了DISTINCT子句，或者任何聚合函数时，如AVG, COUNT, MAX, MIN,或者SUM等，流聚合操作就相

14、当常见。 7.0, 2000, 2005 *查询分析器与Management Studio不是唯一的可以生成、显示查询执行计划的工具。SQL Server Profiler也可以显示执行计划，但格式是文本形式的。使用SQL Server Profiler来显示执行计划的一个优势是，它能为实际运行的大量查询产生执行计划。如果使用查询分析器和Management Studio，则一次只能运行一个。使用Profiler捕获、显示执行计划时，必须使用如下的配置生成一个追踪：捕获事件· Performance: Execution Plan · Performance: Show P

15、lan All · Performance: Show Plan Statistics · Performance: Show Plan Text 显示的字段· StartTime · Duration · TextData · CPU · Reads · Writes 过滤条件· Duration。你会想指定最大的查询执行时间，例如5秒钟，由此避免得到太大量的数据。当然，你可以在你的追踪中捕获更多的没有例在上面的信息，上面例出的只是一个指南而已。但必须记住不要去捕获太多的数据，否则，追踪的运行会影响服务

16、器的性能。7.0, 2000, 2005 *如果在查询中使用了OPTION FAST提示，那就必须小心执行计划的结果可能不是你所期望的。这时你所看到的执行计划基于使用了FAST提示的结果，而不是整个查询语句的实际执行计划。FAST提示用来告知果询优化器尽可能快地返回指定行数的数据行，即便这样做会防碍查询的整体性能。使用这个提示的目的在于为使用者快速返回特定行数的记录，由此让他们产生速度非常快速的错觉。当返回指定行数的数据行后，剩余的数据行按照它们通常的速度返回。因此，如果使用了FAST提示，那么生成的执行计划只是基于那些FAST返回的数据行，而非查询要返回的所有数据行。如果想看所有数据行的执行

17、计划，那么就必须移除这个FAST提示。2000,2005 *一段SQL代码写好以后，可以通过查看SQL的执行计划，初步预测该SQL在运行时的性能好坏，尤其是在发现某个SQL语句的效率较差时，我们可以通过查看执行计划，分析出该SQL代码的问题所在。 那么，作为开发人员，怎么样比较简单的利用执行计划评估SQL语句的性能呢？总结如下步骤供大家参考： 1、 打开熟悉的查看工具：PL/SQL Developer。 在PL/SQL Developer中写好一段SQL代码后，按F5，PL/SQL Developer会自动打开执行计划窗口，显示该SQL的执行计划。 2、 查看总COST，获得资源耗费的总体印象

18、 一般而言，执行计划第一行所对应的COST(即成本耗费)值，反应了运行这段SQL的总体估计成本，单看这个总成本没有实际意义，但可以拿它与相同逻辑不同执行计划的SQL的总体COST进行比较，通常COST低的执行计划要好一些。 3、 按照从左至右，从上至下的方法，了解执行计划的执行步骤 执行计划按照层次逐步缩进，从左至右看，缩进最多的那一步，最先执行，如果缩进量相同，则按照从上而下的方法判断执行顺序，可粗略认为上面的步骤优先执行。每一个执行步骤都有对应的COST,可从单步COST的高低，以及单步的估计结果集（对应ROWS/基数），来分析表的访问方式，连接顺序以及连接方式是否合理。 4、 分析表的访

19、问方式 表的访问方式主要是两种：全表扫描（TABLE ACCESS FULL）和索引扫描(INDEX SCAN)，如果表上存在选择性很好的索引，却走了全表扫描，而且是大表的全表扫描，就说明表的访问方式可能存在问题；若大表上没有合适的索引而走了全表扫描，就需要分析能否建立索引，或者是否能选择更合适的表连接方式和连接顺序以提高效率。5、 分析表的连接方式和连接顺序 表的连接顺序：就是以哪张表作为驱动表来连接其他表的先后访问顺序。 表的连接方式：简单来讲，就是两个表获得满足条件的数据时的连接过程。主要有三种表连接方式，嵌套循环（NESTED LOOPS）、哈希连接（HASH JOIN）和排序-合并连

20、接（SORT MERGE JOIN）。我们常见得是嵌套循环和哈希连接。 嵌套循环：最适用也是最简单的连接方式。类似于用两层循环处理两个游标，外层游标称作驱动表，Oracle检索驱动表的数据，一条一条的代入内层游标，查找满足WHERE条件的所有数据，因此内层游标表中可用索引的选择性越好，嵌套循环连接的性能就越高。 哈希连接：先将驱动表的数据按照条件字段以散列的方式放入内存，然后在内存中匹配满足条件的行。哈希连接需要有合适的内存，而且必须在CBO优化模式下，连接两表的WHERE条件有等号的情况下才可以使用。哈希连接在表的数据量较大，表中没有合适的索引可用时比嵌套循环的效率要高。 6、 请核心技术组

21、协助分析 以上步骤可以协助我们初步分析SQL性能问题，如果遇到连接表太多，执行计划过于复杂，可联系核心技术组共同讨论，一起寻找更合适的SQL写法或更恰当的索引建立方法 总结两点： 1、这里看到的执行计划，只是SQL运行前可能的执行方式，实际运行时可能因为软硬件环境的不同，而有所改变，而且cost高的执行计划，不一定在实际运行起来，速度就一定差，我们平时需要结合执行计划，和实际测试的运行时间，来确定一个执行计划的好坏。 2、对于表的连接顺序，多数情况下使用的是嵌套循环，尤其是在索引可用性好的情况下，使用嵌套循环式最好的，但当ORACLE发现需要访问的数据表较大，索引的成本较高或者没有合适的索引可

22、用时，会考虑使用哈希连接，以提高效率。排序合并连接的性能最差，但在存在排序需求，或者存在非等值连接无法使用哈希连接的情况下，排序合并的效率，也可能比哈希连接或嵌套循环要好。 附I：几种主要表连接的比较 *刚开始用SQL Server的时候，我没有用显示执行计划来对查询进行分析。我曾经一直认为我递交的SQL查询都是最优的，而忽略了查询性能究竟如何，从而对“执行计划”重视不够。在我职业初期，我只要能获取数据就很开心，而不去考虑数据是如何返回的，“执行计划”对我的查询作了什么工作。我以为SQL Server会自己去处理查询的性能问题的。作为一个刚进入IT行业或者刚学到新技术的软件工程师，在编写代码前

23、不太可能有时间去学习其实必须掌握的知识。也许这是因为IT行业竞争太激烈的缘故。随着时间的流逝，数据库容量慢慢变大了。终于某天，客户对应用系统的查询性能感到不满意了。他面带怒容来找我，抱怨由于查询太慢，使得他需要花更多的时间来处理公务。最初，我建议客户升级其系统资源，例如作为临时解决方案，增加硬盘容量。虽然硬盘价格现在很便宜了，但是客户还是要求我提供一个永久性的解决方案，检查和好好调试查询语句，来替代那种无休止地升级资源的临时方案。因为客户的满意度对IT行业来说是十分重要的，因此我不得不考虑他的个人建议。我答应他一定会检查和调整我的代码。如何入手呢?在刚进入IT行业时，我知道SQL Server

24、的基础只是。说实话，向客户承诺检查系统的时候，我还没有一点入手的头绪。不过我相信我可以通过GOOGL和BOL来获取相应的信息。我阅读了一些关于SQL Server的书籍，BOL，以及在网上搜索的信息。于是我知道了“显示执行计划”的概念。可以在查询管理器中将该选项的开关设置为ON。“显示执行计划”是一个图形化工具，可以帮助开发者和DBA分析，优化查询，从而改善性能。“显示执行计划”中不同的任务具有不同的图标。本文中我主要对“Table Scan”、“Index Scan”、“Index Seek”、“Cluster Index Scan”以及“Clustered Index Seek”感兴趣。也

25、许在以后，可以对别的任务进行另外介绍。时间以F1方程式的速度开始流逝，我觉得该是我全面理解“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”、和“Clustered Index Seek”如何工作的时候了。我准备开始分析并优化我的查询。在分析之前，我想到了一些问题。· MS-SQL Server什么时候使用"Table Scan"?· MS-SQL Server什么时候使用"Index Scan"?· MS-SQL Server什么时候使用"I

26、ndex Seek"?· MS-SQL Server什么时候使用"Clustered Index Scan"?· MS-SQL Server什么时候使用"Clustered Index Seek"? 我主要关注SQL Server是根据什么来使用“执行计划”分析查询的。在经过一段时间学习后，我了解了一些相关知识。这些知识应该对开发和DBA新手有帮助。于是我决定写这篇文章，共享我的知识以帮助别人来理解“执行计划”。如果你喜欢，可以慢慢读完，也可以在SQL Server上，模拟我下面做的实验。开始入手为了解释“显示执行计划”中的

27、“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”和“Clustered Index Seek”，先创建新表，并添加一些示例数据进去。下面是创建新表的脚本： Create Table PerformanceIssue( PRID UniqueIdentifier NOT NULL, PRCode Int NOT NULL, PRDesc Varchar (100) NOT NULL)ON PRIMARY 表创建后需要添加一些数据。使用下面的脚本添加100,000条记录进去。脚本执行时间可能比较长，请耐心等待其执行完毕。 D

28、eclare Loop IntDeclare PRID UniqueIdentifierDeclare PRDesc Varchar (100)Set Loop = 1Set PRDesc = ''WHILE Loop <= 100000BEGIN Set PRID = NewID() Set PRDesc = ' PerformanceIssue - ' + Convert( Varchar(10),Loop ) Insert Into PerformanceIssue Values (PRID, Loop, PRDesc) Set Loop = Loo

29、p + 1END 脚本成功执行后，数据就添加进去了。用下面语句来看一下表的内容： Select PRID, PRCode, PRDescFrom PerformanceIssueGO 由于记录较长，因此这里就不列出查询结果了。正如我前面讲到，我想解释何时会有“Table Scan”、“Index Scan”、“Index Seek”、“Clustered Index Scan”和“Clustered Index Seek”。上述哪个会改善性能呢？当SQL Server返回数据时，我们想知道SQL Server采取何种扫描机制来协助获取数据。首先看一下“Table Scan”。我们想了解什么时候

30、“Table Scan”会产生。选择“显示执行计划”或者使用热键“Alt + Q”来激活“显示执行计划”，当然也可以用快捷键“Ctrl+K”。看一下执行下面查询后的“执行计划”结果。 Select PRID, PRCode, PRDescFrom PerformanceIssueGO上面的“执行计划”中，SQL Server用到了“Table Scan”。我问自己为什么会有“Table Scan”，SQL Server是根据什么来使用该方法的。难道是因为我想获取所有100,000条记录吗？于是我换了一个角度进行思考，如果来避免查询中出现“Table Scan”呢？此时我对SQL Server的

31、扫描机制还不是很清楚，那么该如何优化查询呢？下面的SELECT查询中仅选择两列：PRID, PRCode。 Select PRID, PRCodeFrom PerformanceIssueGO查询执行后，执行计划和第一个查询一样。于是将查询改变为只检索一个字段 PRID。 Select PRIDFrom PerformanceIssueGO查询执行后，执行计划仍然和第一个查询的相同。对“Estimated row size”属性不需要太大关注。意思我立刻决定只获取一条记录，看看执行计划会如何。查询语句如下： Select PRID, PRCode, PRDescFrom Performance

32、IssueWhere PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955'- PRID GUID value might be differ in your machineGO 执行完成后，执行计划显示： 查询仍然使用了“Table Scan”方法来显示数据。那么，我需要想其它办法来避免“Table Scan”。首先我想到应该给表加上索引。于是我在PRID字段上创建非聚集索引。添加了索引后是否就能避免“Table Scan”？下面我们开始讨论关于“Index Scan”和“Index Seek”的主题。Index Scan 和 Index

33、Seek首先在PRID字段上创建非聚集索引。 CREATE UNIQUE NONCLUSTERED INDEX UNC_PRIDON PerformanceIssue (PRID)GO 本文假定读者已经知道非聚集索引如何工作的知识。了解非聚集索引更详细的信息，请阅读BOL相关主题，也可参看 http:/www.sql-server- Scan”是如何工作的。执行下面语句并查看执行计划的结果。 Select PRID, PRCode, PRDescFrom PerformanceIssueGO奇怪了，“Table Scan”仍然用到了。为什么SQL Server没有用到那个非聚集索引？于是继续优

34、化查询语句，选择检索两个字段 PRID, PRCode 。 Select PRID, PRCode From PerformanceIssueGO执行结果是和上一个查询结果一摸一样。于是修改查询为只检索一个字段 PRID 。 Select PRIDFrom PerformanceIssueGO 执行计划结果如下： “Index Scan”在查询中被用到了，这很好。很自然，接下来的问题就是“Index Scan”什么时候会被用到。字段PRID上有一个索引，查询语句中选中的字段为PRID。执行查询的时候，SQL Server扫描索引页，因此用到了“Index Scan”方法。前面的查询中选择了有索

35、引的和没有索引的字段，SQL Server无法使用“Index Scan”。当查询中只选择有索引的字段时，SQL Server就使用了“Index Scan”。我不清楚SQL Server底层到底是如何判断的，不过通过这些试验，我认为当查询中只选择有索引的字段时，SQL Server就使用“Index Scan”方法。下面看“Index Seek”方法何时产生。当我看到“Seek”这个词时，第一反应就是条件查询这个主意。我尝试三种不同的带WHERE语法的查询语句，以找出那种会用“Index Seek”。第一种语句如下： Select PRID, PRCode, PRDescFrom Perfo

36、rmanceIssueWhere PRCode = 8GO 结果显示，执行计划使用了“Table Scan”。 第二种语句如下： Select PRID, PRCode, PRDescFrom PerformanceIssueWhere PRDesc = ' PerformanceIssue - 8'GO 执行计划仍然使用“Table Scan”方法。 第三种查询语句如下： Select PRID, PRCode, PRDescFrom PerformanceIssueWhere PRID = 'D386C151-5F74-4C2A-B527-86FEF9712955&

37、#39;- PRID GUID value might be differ in your machineGO查询用到了“Index Seek”和“Bookmark Lookup”方法。用到“Index Seek”是因为WHERE后面使用带索引的字段PRID来进行过滤。“Bookmark Lookup”方法被用到是因为查询中选择了没有索引的字段。如果去掉这两个没有索引的字段，那么“Bookmark Lookup”方法就可以去掉。当然如果只返回PRID字段，那么该查询就没什么意义了，因为WHERE语句后面已经给出PRID具体取值了。我认为“Index Seek”在性能改善上比“Index Sca

38、n”和“Table Scan”要好，这主要表现在下面几个方面： 1 “Index Seek”不需要对表和索引页进行扫描；而“Table Scan”和“Index Scan”需要。2 “Index Seek”利用“WHERE”来过滤获取的数据，这样比用“Index Scan”和“Table Scan”快很多。 当我完成这些测试后，我同事问我一个很有意思的问题：SQL Server什么时候使用“Clustered Index Scan”和“Clustered Index Seek”？下面对“Clustered Index Scan”和“Clustered Index Seek”进行实验。我决定在PRCode上建一个聚集索引来测试“Clustered Index Scan”和“Clustered Index Seek”。Clustered Index Scan & Clustered Index Seek下面的脚本删除PRID