当前位置:  数据库>sqlserver

教你如何看懂SQL Server查询计划

    来源: 互联网  发布时间:2014-10-14

    本文导语:  对于SQL Server的优化来说,优化查询可能是很常见的事情。由于数据库的优化,本身也是一个涉及面比较的广的话题,因此本文只谈优化查询时如何看懂SQL Server查询计划。毕竟我对SQL Server的认识有限,如有错误,也恳请您在发...

对于SQL Server的优化来说,优化查询可能是很常见的事情。由于数据库的优化,本身也是一个涉及面比较的广的话题,因此本文只谈优化查询时如何看懂SQL Server查询计划。毕竟我对SQL Server的认识有限,如有错误,也恳请您在发现后及时批评指正。

首先,打开【SQL Server Management Studio】,输入一个查询语句看看SQL Server是如何显示查询计划的吧。
说明:本文所演示的数据库,是我为一个演示程序专用准备的数据库,可以在此网页中下载。

select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v.SumMoney, v.Finished
from   OrdersView as v
where v.OrderDate >= '2010-12-1' and v.OrderDate < '2011-12-1';

其中,OrdersView是一个视图,其定义如下:

SELECT     dbo.Orders.OrderID, dbo.Orders.CustomerID, dbo.Orders.OrderDate, 
            dbo.Orders.SumMoney, dbo.Orders.Finished,
            ISNULL(dbo.Customers.CustomerName, N'') AS CustomerName
FROM         dbo.Orders LEFT OUTER JOIN
                dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

对于前一句查询,SQL Server给出的查询计划如下(点击工具栏上的【显示估计的执行计划】按钮):

从这个图中,我们至少可以得到3个有用的信息:

1. 哪些执行步骤花费的成本比较高。显然,最右边的二个步骤的成本是比较高的。
2. 哪些执行步骤产生的数据量比较多。对于每个步骤所产生的数据量,SQL Server的执行计划是用【线条粗细】来表示的,因此也很容易地从分辨出来。
3. 每一步执行了什么样的动作。

对于一个比较慢的查询来说,我们通常要知道哪些步骤的成本比较高,进而,可以尝试一些改进的方法。一般来说,如果您不能通过:提高硬件性能或者调整OS,SQL Server的设置之类的方式来解决问题,那么剩下的可选方法通常也只有以下这些了:

1. 为【scan】这类操作增加相应字段的索引。
2. 有时重建索引或许也是有效的,具体情形请参考后文。
3. 调整语句结构,引导SQL Server采用其它的查询方案去执行。
4. 调整表结构(分表或者分区)。

下面再来说说一些很重要的理论知识,这些内容对于执行计划的理解是很有帮助的。

SQL Server 查找记录的方法

说到这里,不得不说SQL Server的索引了。SQL Server有二种索引:聚集索引和非聚集索引。二者的差别在于:【聚集索引】直接决定了记录的存放位置,或者说:根据聚集索引可以直接获取到记录。【非聚集索引】保存了二个信息:1.相应索引字段的值,2.记录对应聚集索引的位置(如果表没有聚集索引则保存记录指针)。因此,如果能通过【聚集索引】来查找记录,显然也是最快的。

SQL Server 会有以下方法来查找您需要的数据记录:

1. 【Table Scan】:遍历整个表,查找所有匹配的记录行。这个操作将会一行一行的检查,当然,效率也是最差的。
2. 【Index Scan】:根据索引,从表中过滤出来一部分记录,再查找所有匹配的记录行,显然比第一种方式的查找范围要小,因此比【Table Scan】要快。
3. 【Index Seek】:根据索引,定位(获取)记录的存放位置,然后取得记录,因此,比起前二种方式会更快。
4. 【Clustered Index Scan】:和【Table Scan】一样。注意:不要以为这里有个Index,就认为不一样了。其实它的意思是说:按聚集索引来逐行扫描每一行记录,因为记录就是按聚集索引来顺序存放的。而【Table Scan】只是说:要扫描的表没有聚集索引而已,因此这二个操作本质上也是一样的。
5. 【Clustered Index Seek】:直接根据聚集索引获取记录,最快!

所以,当发现某个查询比较慢时,可以首先检查哪些操作的成本比较高,再看看那些操作在查找记录时,是不是【Table Scan】或者【Clustered Index Scan】,如果确实和这二种操作类型有关,则要考虑增加索引来解决了。不过,增加索引后,也会影响数据表的修改动作,因为修改数据表时,要更新相应字段的索引。所以索引过多,也会影响性能。还有一种情况是不适合增加索引的:某个字段用0或1表示的状态。例如可能有绝大多数是1,那么此时加索引根本就没有意义。这时只能考虑为0或者1这二种情况分开来保存了,分表或者分区都是不错的选择。

如果不能通过增加索引和调整表来解决,那么可以试试调整语句结构,引导SQL Server采用其它的查询方案去执行。这种方法要求:1.对语句所要完成的功能很清楚,2.对要查询的数据表结构很清楚,3.对相关的业务背景知识很清楚。如果能通过这种方法去解决,当然也是很好的解决方法了。不过,有时SQL Server比较智能,即使你调整语句结构,也不会影响它的执行计划。

如何比较二个相同功能的SQL语句的性能好坏呢,我建议采用二种方法:1. 直接把二个查询语句放在【SQL Server Management Studio】,然后去看它们的【执行计划】,SQL Server会以百分比的方式告诉你二个查询的【查询开销】。这种方法简单,通常也是可以参考的,不过,有时也会不准,具体原因请接着往下看(可能索引统计信息过旧)。
2. 根据真实的程序调用,写相应的测试代码去调用:这种方法就麻烦一些,但是它更能代表现实调用情况,得到的结果也是更具有参考价值的,因此也是值得的。

SQL Server Join 方式

在SQL Server中,每个join命令,都会在内部执行时采用三种更具体的方式来运行:

1. 【Nested Loops join】,如果一个联接输入很小,而另一个联接输入很大而且已在其联接列上创建了索引,则索引 Nested Loops 连接是最快的联接操作,因为它们需要的 I/O 和比较都最少。

嵌套循环联接也称为“嵌套迭代”,它将一个联接输入用作外部输入表(显示为图形执行计划中的顶端输入),将另一个联接输入用作内部(底端)输入表。外部循环逐行处理外部输入表。内部循环会针对每个外部行执行,在内部输入表中搜索匹配行。可以用下面的伪码来理解:

foreach(row r1 in outer table)
    foreach(row r2 in inner table)
        if( r1, r2 符合匹配条件 )
            output(r1, r2);

最简单的情况是,搜索时扫描整个表或索引;这称为“单纯嵌套循环联接”。如果搜索时使用索引,则称为“索引嵌套循环联接”。如果将索引生成为查询计划的一部分(并在查询完成后立即将索引破坏),则称为“临时索引嵌套循环联接”。查询优化器考虑了所有这些不同情况。

如果外部输入较小而内部输入较大且预先创建了索引,则嵌套循环联接尤其有效。在许多小事务中(如那些只影响较小的一组行的事务),索引嵌套循环联接优于合并联接和哈希联接。但在大型查询中,嵌套循环联接通常不是最佳选择。

2. 【Merge Join】,如果两个联接输入并不小但已在二者联接列上排序(例如,如果它们是通过扫描已排序的索引获得的),则合并联接是最快的联接操作。如果两个联接输入都很大,而且这两个输入的大小差不多,则预先排序的合并联接提供的性能与哈希联接相近。但是,如果这两个输入的大小相差很大,则哈希联接操作通常快得多。

合并联接要求两个输入都在合并列上排序,而合并列由联接谓词的等效 (ON) 子句定义。通常,查询优化器扫描索引(如果在适当的一组列上存在索引),或在合并联接的下面放一个排序运算符。在极少数情况下,虽然可能有多个等效子句,但只用其中一些可用的等效子句获得合并列。

由于每个输入都已排序,因此 Merge Join 运算符将从每个输入获取一行并将其进行比较。例如,对于内联接操作,如果行相等则返回。如果行不相等,则废弃值较小的行并从该输入获得另一行。这一过程将重复进行,直到处理完所有的行为止。

合并联接操作可以是常规操作,也可以是多对多操作。多对多合并联接使用临时表存储行(会影响效率)。如果每个输入中有重复值,则在处理其中一个输入中的每个重复项时,另一个输入必须重绕到重复项的开始位置。可以创建唯一索引告诉SQL Server不会有重复值。

如果存在驻留谓词,则所有满足合并谓词的行都将对该驻留谓词取值,而只返回那些满足该驻留谓词的行。

合并联接本身的速度很快,但如果需要排序操作,选择合并联接就会非常费时。然而,如果数据量很大且能够从现有 B 树索引中获得预排序的所需数据,则合并联接通常是最快的可用联接算法。

3. 【Hash Join】,哈希联接可以有效处理未排序的大型非索引输入。它们对复杂查询的中间结果很有用,因为:1. 中间结果未经索引(除非已经显式保存到磁盘上然后创建索引),而且通常不为查询计划中的下一个操作进行适当的排序。2. 查询优化器只估计中间结果的大小。由于对于复杂查询,估计可能有很大的误差,因此如果中间结果比预期的大得多,则处理中间结果的算法不仅必须有效而且必须适度弱化。

哈希联接可以减少使用非规范化。非规范化一般通过减少联接操作获得更好的性能,尽管这样做有冗余之险(如不一致的更新)。哈希联接则减少使用非规范化的需要。哈希联接使垂直分区(用单独的文件或索引代表单个表中的几组列)得以成为物理数据库设计的可行选项。

哈希联接有两种输入:生成输入和探测输入。查询优化器指派这些角色,使两个输入中较小的那个作为生成输入。

哈希联接用于多种设置匹配操作:内部联接;左外部联接、右外部联接和完全外部联接;左半联接和右半联接;交集;联合和差异。此外,哈希联接的某种变形可以进行重复删除和分组,例如 SUM(salary) GROUP BY department。这些修改对生成和探测角色只使用一个输入。

哈希联接又分为3个类型:内存中的哈希联接、Grace 哈希联接和递归哈希联接。

内存中的哈希联接:哈希联接先扫描或计算整个生成输入,然后在内存中生成哈希表。根据计算得出的哈希键的哈希值,将每行插入哈希存储桶。如果整个生成输入小于可用内存,则可以将所有行都插入哈希表中。生成阶段之后是探测阶段。一次一行地对整个探测输入进行扫描或计算,并为每个探测行计算哈希键的值,扫描相应的哈希存储桶并生成匹配项。

Grace 哈希联接:如果生成输入大于内存,哈希联接将分为几步进行。这称为“Grace 哈希联接”。每一步都分为生成阶段和探测阶段。首先,消耗整个生成和探测输入并将其分区(使用哈希键上的哈希函数)为多个文件。对哈希键使用哈希函数可以保证任意两个联接记录一定位于相同的文件对中。因此,联接两个大输入的任务简化为相同任务的多个较小的实例。然后将哈希联接应用于每对分区文件。

递归哈希联接:如果生成输入非常大,以至于标准外部合并的输入需要多个合并级别,则需要多个分区步骤和多个分区级别。如果只有某些分区较大,则只需对那些分区使用附加的分区步骤。为了使所有分区步骤尽可能快,将使用大的异步 I/O 操作以便单个线程就能使多个磁盘驱动器繁忙工作。

在优化过程中不能始终确定使用哪种哈希联接。因此,SQL Server 开始时使用内存中的哈希联接,然后根据生成输入的大小逐渐转换到 Grace 哈希联接和递归哈希联接。
如果优化器错误地预计两个输入中哪个较小并由此确定哪个作为生成输入,生成角色和探测角色将动态反转。哈希联接确保使用较小的溢出文件作为生成输入。这一技术称为“角色反转”。至少一个文件溢出到磁盘后,哈希联接中才会发生角色反转。

说明:您也可以显式的指定联接方式,SQL Server会尽量尊重您的选择。比如你可以这样写:inner loop join, left outer merge join, inner hash join
但是,我还是建议您不要这样做,因为SQL Server的选择基本上都是正确的,不信您可以试一下。

好了,说了一大堆理论东西,再来个实际的例子解释一下吧。

更具体执行过程

前面,我给出一张图片,它反映了SQL Server在执行某个查询的执行计划,但它反映的信息可能不太细致,当然,您可以把鼠标指标移动某个节点上,会有以下信息出现:

刚好,我装的是中文版的,上面都是汉字,我也不多说了。我要说的是另一种方式的执行过程,比这个包含更多的执行信息,而且是实际的执行情况。(当然,您也可以继续使用图形方式,在运行查询前点击工具栏上的【包括实际的执行计划】按钮)

让我们再次回到【SQL Server Management Studio】,输入以下语句,然后执行。

set statistics profile on 
select v.OrderID, v.CustomerID, v.CustomerName, v.OrderDate, v.SumMoney, v.Finished
from   OrdersView as v
where v.OrderDate >= '2010-12-1' and v.OrderDate < '2011-12-1';

注意:现在加了一句,【set statistics profile on 】,得到的结果如下:

可以从图片上看到,执行查询后,得到二个表格,上面的表格显示了查询的结果,下面的表格显示了查询的执行过程。相比本文的第一张图片,这张图片可能在直观上不太友好,但是,它能反映更多的信息,而且尤其在比较复杂的查询时,可能看起来更容易,因为对于复杂的查询,【执行计划】的步骤太多,图形方式会造成图形过大,不容易观察。而且这张执行过程表格能反映2个很有价值的数据(前二列)。

还是来看看这个【执行过程表格】吧。我来挑几个重要的说一下。
【Rows】:表示在一个执行步骤中,所产生的记录条数。(真实数据,非预期)
【Executes】:表示某个执行步骤被执行的次数。(真实数据,非预期)
【Stmt Text】:表示要执行的步骤的描述。
【EstimateRows】:表示要预期返回多少行数据。

在这个【执行过程表格】中,对于优化查询来说,我认为前三列是比较重要的。对于前二列,我上面也解释了,意思也很清楚。前二列的数字也大致反映了那些步骤所花的成本,对于比较慢的查询中,应该留意它们。【Stmt Text】会告诉你每个步骤做了什么事情。对于这种表格,它所要表达的其实是一种树型信息(一行就表示在图形方式下的一个节点),所以,我建议从最内层开始去读它们。做为示例,我来解释一下这张表格它所表达的执行过程。

第5行:【Clustered Index Seek(OBJECT:([MyNorthwind].[dbo].[Customers].[PK_Customers]), SEEK:([MyNorthwind].[dbo].[Customers].[CustomerID]=[MyNorthwind].[dbo].[Orders].[CustomerID]) ORDERED FORWARD)】,意思是说,SQL Server在对表Customers做Seek操作,而且是按照【Clustered Index Seek】的方式,对应的索引是【PK_Customers】,seek的值来源于[Orders].[CustomerID]

第4行:【Clustered Index Scan(OBJECT:([MyNorthwind].[dbo].[Orders].[PK_Orders]), WHERE:([MyNorthwind].[dbo].[Orders].[OrderDate]>='2010-12-01 00:00:00.000' AND [MyNorthwind].[dbo].[Orders].[OrderDate]=@1 AND [v].[OrderDate]= '2010-12-1' and OrderDate < '2011-12-1';

很明显,对于同一个视图,在不同的过滤条件下,执行计划的差别很明显。

推荐阅读-MSDN文章

索引统计信息
http://msdn.microsoft.com/zh-cn/library/ms190397(SQL.90).aspx

查询优化建议
http://msdn.microsoft.com/zh-cn/library/ms188722(SQL.90).aspx

用于对运行慢的查询进行分析的清单
http://msdn.microsoft.com/zh-cn/library/ms177500(SQL.90).aspx

逻辑运算符和物理运算符引用
http://msdn.microsoft.com/zh-cn/library/ms191158(SQL.90).aspx


    
 
 

您可能感兴趣的文章:

  • sql server 2005维护计划无法删除怎么办呢?
  • 强制SQL Server执行计划使用并行提升在复杂查询语句下的性能
  • 通过分析SQL语句的执行计划优化SQL
  • sql server维护计划自动备份数据库作业执行失败会自动生成abc82作业的解决办法
  • SQL参数化查询的另一个理由 命中执行计划
  • 关于SQL执行计划错误导致临时表空间不足的问题
  • SqlServer 执行计划及Sql查询优化初探
  • SQL Server 2008如何进行数据库分离和附加详细介绍
  • 微软网站下载的Ms SQL Server2000 JDBC Driver,必须用英文版的SQL server2000吗?
  • 请问,这是什么错误!java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][Named Pipes]??????? SQL Server?虽然分少,但一定给,只要您是前5名回复者中最好的以为!
  • 安装sql server 2008 management提示已安装 SQL Server 2005 Express的解决方法
  • SQL Server 2008 事件探查器(SQL SERVER Profiler) 列的说明
  • (X86/X64)安装sql server 2005 过程中提示“无法启动sql server的 启动”的解决方法
  • SQL Server降权运行 SQL Server 2000以GUESTS权限运行设置方法
  • MS SQL Server2014链接到MS SQL Server 2000的解决方案及问题处理
  • SQL语句实现SQL Server 2000及Sql Server 2005日志收缩(批量)
  • sql server不存在 sql server拒绝访问第1/3页
  • SQL Server误区30日谈 第5天 AWE在64位SQL SERVER中必须开启
  • 一定得帮我看看sql server2000通过sql server2000jdbc driver的代码
  • SQL Server统计SQL语句执行时间的脚本
  • SQL Server误区30日谈 第21天 数据损坏可以通过重启SQL Server来修复
  • SQL Server误区30日谈 第3天 即时文件初始化特性可以在SQL Server中开启和关闭
  • 创建 sql server 链接服务器的sql代码
  • jsp jdbc为什麽不能把数据insert到sql server 中,也不能从sql server中提取数据?!
  • 如何处理此错误:java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]没有执行可选特性
  • sql server 2005 三个常用的小sql
  • SQL Server 中查看SQL句子执行所用的时间
  • 推荐SQL Server 重新恢复自动编号列的序号的sql代码
  •  
    本站(WWW.169IT.COM)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
    本站(WWW.169IT.COM)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • mysql的SQL_NO_CACHE(在查询时不使用缓存)和sql_cache用法
  • sql中count或sum为条件的查询示例(sql查询count)
  • mysql中查询当前正在运行的SQL语句并找出mysql中运行慢的sql语句
  • SQL查询分析工具 SQL Workbench/J
  • mysqli多查询特性 实现多条sql语句查询
  • sql server简单查询的例子(sql入门)
  • 根据时间范围条件查询数据sql语句
  • 在SQL Server中使用SQL语句查询一个存储过程被其它所有的存储过程引用的存储过程名
  • shell中关于sql查询结果的判断?
  • SQL查询工具 SuperQuaiL
  • HBase上使用SQL查询 Phoniex
  • sql 查询所有数据库、表名、表字段的代码
  • 查询SQL Server中所有数据库的数据文件位置的sql脚本
  • 在SQL Server中查询资料库的TABLE数量与名称的sql语句
  • 在线等待!一个简单的SQL查询问题?
  • SQL SERVER 查询正在实行的SQL语句
  • SQLServer中用T—SQL命令查询一个数据库中有哪些表的sql语句
  • SQL查询问题
  • SQL日期查询与比较方法
  • SQL Server日志过大会影响查询结果
  • 将string数组转化为sql的in条件用sql查询
  • java命名空间java.sql接口statement的类成员方法: executeupdate定义及介绍
  • oracle导出sql语句的结果集和保存执行的sql语句(深入分析)
  • java命名空间java.sql接口connection的类成员方法: nativesql定义及介绍
  • SQL客户端软件 PKLite SQL Client
  • java命名空间java.sql接口preparedstatement的类成员方法: executeupdate定义及介绍
  • SQL客户端管理工具 SQuirreL SQL Client
  • java命名空间java.sql接口rowid的类成员方法: getbytes定义及介绍
  • sql2005 大数据量检索分页的sql代码
  • java命名空间java.sql接口ref的类成员方法: getbasetypename定义及介绍
  • 如何实现连接一次数据库,提交多个sql语句。(sql的批处理)


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2017,169IT.COM,E-mail:www_169it_com#163.com(请将#改为@)

    浙ICP备11055608号