关闭 x
    ITJS.cn - 技术改变世界
    IT采购网

    理解SQL Server的SQL查询计划(1)

    出处：ITJS

    入门指南

    让我们以一个简单的例子帮助你理解如何阅读查询计划，可以通过发出SET SHOWPLAN_TEXT On命令，或者在SQL Query Analyzer 的配置属性中设置同样的选项等方式得到查询计划。

    注意：这个例子使用了表pubs.big_sales，该表与pubs..sales表完全相同，除了多了80000行的记录，以当作简单explain plan例子的主要数据。

    如下所示，这个最简单的查询将扫描整个聚集索引，假如该索引存在。注意聚集键值是物理次序，数据按该次序存放。所以，假如聚集键值存在，你将可能避免对整个表进行扫描。即使你所选的列不在聚集键值中，例如ord_date，这个查询引擎将用索引扫描并返回结果集。

    SELECT *
    

    通过比较连接和子查询说明分支步骤
    

    一条正确的老规则是：在结果集相同的情况下，连接比子查询具有更好的性能。 
    

    


    

    SELECT au_fname,au_lname
    

    理解连接的影响
    

    上文的不同查询步骤展示了SQL Server 2000是如何运用大量的操作来解析Join(连接)的。每一个Join策略都有它的长处和短处。然而，在某些罕见的情况下，查询引擎会选择效率较低的策略，如通常使用的Hash（散列）或Merge（合并）策略，而采用简单的嵌套循环就足以提供很好的性能。
    

    SQL Server 使用三种join(连接)策略，这里由简单到复杂分别列出： 
    

    嵌套循环
    

    对于使用简单内连接的小数据量表，嵌套循环是最佳策略。最适合两个表的记录数差别非常大，并且在连接的列上都有索引的情况。嵌套循环连接所需的I/O和比较都是最少的。 
    

    嵌套循环在外表（往往是小数据量的表）中每次循环一个记录，然后在内表中查找所匹配的记录并输出。有很多关于嵌套循环策略的名字。例如，对整个表或索引进行查询，称为Naive（无知的）嵌套循环连接。使用正常索引或临时索引时，被称为索引嵌套循环连接或临时索引嵌套循环连接。 
    

    合并
    

    对于使用了排序连接列的大数据量并数据量相似的表，合并是最佳的策略。合并操作首先进行排序，然后对所有数据进行循环并产生输出。良好的合并连接性能基于在相应的列上建立索引，通常在连接谓词等式中用到的列。 
    

    合并连接发挥了预先排序的优点，从每个输出中获得行数据，直接进行比较操作。例如，例如，内连接返回的是连接谓词相等的记录。假如不相等，含更低值的记录将会被丢弃，并且用下一条记录进行比较。这个过程将持续直到所有的记录都被检查完。有时合并连接被用来比较具有多对多关系的表。当这种情况发生时，SQL Server用临时表来存储这些行。 
    

    假如在使用合并连接的查询中同时存在一个WHERE子句，那么这个合并连接谓词将首先被计算。然后经过合并连接谓词的每条记录将经过WHERE语句中的其他谓词再次计算。Microsoft 称之为residual predicate（剩余谓词）。
    

    Hash（散列） 
    

    对于数据量大，容量不同的表，以及连接列没有排序或索引的复杂连接需求，Hash是最佳策略。散列法被用于UNION, INTERSECT, INNER, LEFT, RIGHT和OUTER JOIN，以及集合匹配和差别等操作。Hash也用于没有有用索引的连接表。Hash操作将建立临时的Hash表并且循环所有的数据并产生输出。 
    

    Hash使用一个build（已建造）输入（通常是小数据量的表）和probe(探测)输入。这个散列键（也就是在连接谓词中的列，或在GROUP BY列表中的列）被查询用来处理连接。剩余谓词是在WHERE子句中没有用于连接本身的所有其他运算。剩余谓词是在连接谓词之后计算。当构造一个Hash连接时，SQL Server可按下面的优先次序选择不同的选项： 
    

    In-memory Hash(内存中散列)：In-memory hash连接首先将整个build输入扫描到内存中，然后在内存中创建一个临时hash表。计算出Hash值，然后将每条记录插入到Hash中。然后逐条扫描探测输入。每条探测输入将与对应的Hash相比较，假如匹配，将放在结果集中返回。 
    

    Hybrid Hash（混合散列）：假如散列仅比可用的内存稍大，SQL Server可能合并in-memory hash连接和grace hash连接的某些方面，称之为hybrid hash 连接。
    

    Grace Hash（优美散列）：当hash join太大而不能在内存中处理时，就要用到Grace hash选项。在那种情况下，整个build输入和probe输入都将被读入。然后它们被分解成多个临时的工作表，该步骤称为分区扇出。Hash键值上的Hash函数确保了所有的连接记录都在同一对分区工作表中。分区扇出将两个耗时的步骤分解为很多小步骤，这些小步骤可以被并发处理。然后Hash连接将应用于每对工作表，将所有匹配放在结果集中返回。 
    

    Recursive Hash(递归散列) ：有时Grace Hash产生的分区扇出表仍然太大以至需要更进一步的再分区，这个就叫做递归散列。
    

    注意到，散列与合并连接将每个表都处理一次。假如使用SET STATISTICS IO ON来测量这种类型的查询，会看到较低I/O的假象。然而，较低的I/O并不意味着这些连接策略一定比嵌套循环连接要快，因为还需要巨大的计算量。 
    

    注意，散列连接的计算量很大。假如你发现在生产中某些查询始终用散列连接，这里要提示你应该调优你的查询或者在底层表中添加索引。 
    

    在下面的例子中，我们展示标准的嵌套循环（使用默认的查询计划）和散列与合并连接（强制使用提示）。
    

    


    

    SELECT a.au_fname, a.au_lname, t.title
     




    入门指南
    

    让我们以一个简单的例子帮助你理解如何阅读查询计划，可以通过发出SET SHOWPLAN_TEXT On命令，或者在SQL Query Analyzer 的配置属性中设置同样的选项等方式得到查询计划。 
    

    注意：这个例子使用了表pubs.big_sales，该表与pubs..sales表完全相同，除了多了80000行的记录，以当作简单explain plan例子的主要数据。
    

    如下所示，这个最简单的查询将扫描整个聚集索引，假如该索引存在。注意聚集键值是物理次序，数据按该次序存放。所以，假如聚集键值存在，你将可能避免对整个表进行扫描。即使你所选的列不在聚集键值中，例如ord_date，这个查询引擎将用索引扫描并返回结果集。
    

    


    

    SELECT *
    FROM big_sales
    SELECT ord_date
    FROM big_sales
    StmtText
    -------------------------------------------------------------------------
    |--ClusteredIndexScan(OBJECT:([pubs].[dbo].[big_sales].[UPKCL_big_sales]))
    

    上面的查询展示返回的数据量非常不同，所以小结果集(ord_date)的查询比其它查询运行更快，这只是因为存在大量底层的I/O。然而，这两个查询计划实际上是一样的。你可以通过使用其它索引提高性能。例如，在title_id列上有一个非聚集索引存在：
    

    


    

    SELECT title_id
    FROM big_sales
    StmtText
    ------------------------------------------------------------------
    |--Index Scan(OBJECT:([pubs].[dbo].[big_sales].[ndx_sales_ttlID]))
    

    上面的查询的执行时间与SELECT *查询相比非常小，这是因为可以从非聚集索引即可得到所有结果。该类查询被称为covering query（覆盖查询），因为全部结果集被一个非聚集索引所覆盖。
    

    SEEK与SCAN
    

    第一件事是你需要在查询计划中区别SEEK和SCAN操作的不同。 
    

    注意：一个简单但非常有用的规则是SEEK操作是有效率的，而SCAN操作即使不是非常差，其效率也不是很好。SEEK操作是直接的，或者至少是快速的，而SCAN操作需要对整个对象进行读取（表，聚集索引或非聚集索引）。因此，SCAN操作通常比SEEK要消耗更多的资源。假如你的查询计划仅是扫描操作，你就应该考虑调整你的查询了。 
    

    where子句在查询性能中能产生巨大的差异，如下面展示的： 
    

    


    

    Select *
    From big_sales
    Where stor_id=’6380’
    StmtText
    -----------------------------------------------------------------------------|--Clustered
    Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales])),
    SEEK: ([big_sales].[stor_id]={@1} ORDERED FORWARD)
    

    上面的查询是在聚集索引上执行SEEK而不是SCAN操作。这个SHOWPLAN确切的描述SEEK操作是基于stor_id并且结果是按照在索引中存储的顺序排序的。因为SQL Server支持索引的向前和向后滚动的性能是相同的，所以你可以在查询计划中看到ORDERED FORWARD 或ORDERED BACKWARD。这只是告诉你表或索引读取的方向。你甚至可以在ORDER BY子句中通过用ASC和DESC关键字操作这些行为。范围查询返回的查询计划，与前面的直接查询的查询计划很相似。下面两个范围查询可提供一些信息：
    

    


    

    Select *
    From big_sales
    Where stor_id>=’7131’
    StmtText
    ------------------------------------------------------------------------------|-Clustered
    Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ),
    SEEK: ([big_sales].[stor_id]>=’7131’) ORDER FORWARD 
    

    上面的查询看起来很象以前的例子，除了SEEK谓词有点不同。
    

    


    

    Select *
    From big_sales
    Where stor_id between ‘7066’ and ‘7131’
    StmtText
    ------------------------------------------------------------------------------|-Clustered
    Index Seek(OBJECT: ([pubs].[dbo].[big_sales].[UPKCL_big_sales] ),
    SEEK:([big_sales].[stor_id]>=’7066’ and ([big_sales].[stor_id]<=’7131’) ORDER FORWARD)
    

    这个看起来也一样。只是查找谓词改变了。因为查找是非常快的，所以这个查询是相当好的。
    

    SEEK和SCAN也可包含Where谓词。在这种情况下，这个谓词告诉你Where子句从结果集中过滤出哪些记录。因为它是作为SEEK或SCAN的一个组件执行的， Where子句通常既不损害也不提高这个操作本身的性能。Where子句会帮助查询优化器找到可能有最佳性能的索引。
    

    查询优化的一个重要部分是要确定是否在某个索引上执行SEEK操作，假如是这样，就找到了具有最佳性能的索引。大部分情况下，查询引擎能出色地查找到存在的索引。但是，目前有三种涉及到索引的常见问题： 
    

    ◆数据库设计师，通常是应用开发者，在表中没有建立任何索引。 
    ◆数据库设计师通常猜测不到常用的查询或事务类型，所以建立在表上的索引或主键往往效率不高。 
    ◆当索引表被创建时，即使数据库设计师猜测较准，但事务负载随着时间将发生改变，使得这些索引效率变差。
    

    假如你在你的查询计划中看到大量的SCAN而不是SEEK，你应该从新评估你的索引。例如，看看下面的查询： 
    

    


    

    Select ord_num
    From sales
    Where ord_date IS NOT NUL
    And ord_date>’Jan 01,2002 12:00:00 AM’
    StemtText
    ----------------------------------------------------------------------------------|--
    Clustered Index Scan(OBJECT: ([pubs].[dbo].[sales].[UPKCL_sales] ),
    WHERE : ([sales].[ord_date]>’Jan 1,2002 12:00:00 AM ’))
    

    现在这个查询在我们刚创建的sales_ord_date索引上执行SEEK INDEX操作。
    
	
	

	
	
    
 上一篇返回首页 下一篇
    
			
	
    

    声明：   此文观点不代表本站立场；转载务必保留本文链接；版权疑问请联系我们。
    
	
    
	
    
				
    
					
    别人在看