对于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]2011-12-01 00:00:00.000))】 意思是说SQL Server在对表Customers做Scan操作即最差的【表扫描】的方式原因是OrderDate列上没有索引所以只能这样了。第3行【Nested Loops(Left Outer Join, OUTER REFERENCES:([MyNorthwind].[dbo].[Orders].[CustomerID]))】 意思是说SQL Server把第5行和第4行产生的数据用【Nested Loops】的方式联接起来其中Outer表是Orders要联接的匹配操作也在第5行中指出了。第2行【Compute Scalar(DEFINE:([Expr1006]isnull([MyNorthwind].[dbo].[Customers].[CustomerName],N)))】 意思是说要执行一个isnull()函数的调用。具体原因请参考本文前部分中给出视图定义代码。第1行【SELECT [v].[OrderID],[v].[CustomerID],[v].[CustomerName],[v].[OrderDate],[v].[SumMoney],[v].[Finished] FROM [OrdersView] [v] WHERE [v].[OrderDate]1 AND [v].[OrderDate]2】 通常第1行就是整个查询表示它的返回值。索引统计信息查询计划的选择依据前面一直说到【执行计划】既然是计划就表示要在具体执行前就能确定下来的操作方案。那么SQL Server是如何选择一个执行计划的呢 SQL Server怎么知道什么时候该用索引或者用哪个索引呢 对于SQL Server来说每当要执行一个查询时都要首先检查这个查询的执行计划是否存在缓存中如果没有就要生成一个执行计划 具体在产生执行计划时并不是看有哪些索引可用随机选择而是会参考一种被称为【索引统计信息】的数据。 如果您仔细地看一下前面的执行计划或者执行过程表格会发现SQL Server能预估每个步骤所产生的数据量 正是因为SQL Server能预估这些数据量SQL Server才能选择一个它认为最合适的方法去执行查询过程 此时【索引统计信息】就能告诉SQL Server这些信息。 说到这里您是不是有点好奇呢为了让您对【索引统计信息】有个感性的认识我们来看看【索引统计信息】是个什么样子的。 请在【SQL Server Management Studio】输入以下语句然后执行。dbcc show_statistics (Products, IX_CategoryID)得到的结果如下图首先还是解释一下命令【dbcc show_statistics】这个命令可以显示我们想知道的【索引统计信息】它需要二个参数1. 表名2. 索引名再来看看命令的结果它有三个表格组成1. 第一个表格它列出了这个索引统计信息的主要信息。列名说明Name统计信息的名称。Updated上一次更新统计信息的日期和时间。Rows表中的行数。Rows Sampled统计信息的抽样行数。Steps数据可分成多少个组与第三个表对应。Density第一个索引列前缀的选择性不包括 EQ_ROWS。Average key length所有索引列的平均长度。String Index如果为“是”则统计信息中包含字符串摘要索引以支持为 LIKE 条件估算结果集大小。仅适用于char、varchar、nchar和nvarchar、varchar(max)、nvarchar(max)、text以及ntext数据类型的前导列。2. 第二个表格它列出各种字段组合的选择性数据越小表示重复越性越小当然选择性也就越高。
