📄 44253.htm
字号:
<TD class=code bgColor=#e6e6e6><PRE><P> SELECT au_fname,au_lname<BR> FROM authors<BR> WHERE au_id IN (select au_id from titleauther ) </P><P> StmtText<BR> -------------------------------------------------------------------------------------------<BR> |---Nested Loops(Inner Join, OUTER ReFERENCES:([titleauthor].[au_id])<BR> |--Stream Aggregate(GROUP BY:([titleauthor].[au_id]))<BR> | |--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[auidind]),ORDERED FORWARD)<BR> |--ClusteredIndex Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),</P><P> SEEK:([authors].[au_id]=[titleauthor].[au_id]) ORDERED FORWARD)</P><P> Table ‘authors’. Scan count 38,logical reads 76,physical reads 0,read-ahead reads 0.</P><P> Table ‘titleauthor’. Scan count 2, logical reads 2, physical reads 1,read-ahead reads 0.</P></PRE></TD></TR></TBODY></TABLE></P>
<P>在这种情况下，查询引擎选择一个嵌套循环操作。这个查询被迫用聚集索引读取整个authors表，在处理中执行大量的逻辑页读。 </P>
<P><STRONG>注意</STRONG>：在带分支步骤的查询中，缩进行给你展示那些步骤是其它步骤的分支。 </P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P> Select distinct au_fname,au_lname<BR> From authors as a<BR> Join titleauthor as t ON a.au_id=t.au_id</P><P> StmtText<BR> ---------------------------------------------------------------------------------</P><P> |--stream Aggregate(group by: ([a].[au_lname].[a].[au_fname]))<BR> |-Nested loops(Inner Join,OUTER REFERENCES: ([a].[au_id]))<BR>|-Index scan(OBJECT:([pubs].[dbo].[authors].[authord ]as[a]),ordered forward)<BR>|-Index Seek (OBJECT: [pubs].[dbo].[titleauthor].[authord ]as [t]),</P><P> SEEK: ([t].[au_id]=[a].[au_id]) ORDER FORWARD)<BR> Table ‘titleauthors’ .Scan count 23,logical reads 23,</P><P> physical reads 0,read ahead reads 0.</P><P> Table ‘authors’ .Scan count 1,logical reads 1,physical reads 0,read-ahead read 0.</P></PRE></TD></TR></TBODY></TABLE></P>
<P>上面的这个查询中，titleauthors表逻辑读的数字上升而authors表下降。注意到，stream aggregation在查询计划中位置更高，即发生的更晚。</P>
<P><STRONG>比较查询计划</STRONG></P>
<P>可以使用查询计划比较两个不同查询的相对效果。如，你可能想知道是否一个查询比另一个查询增加了额外的开销或者选择了不同的索引策略。</P>
<P>在这个例子中，我们比较两个查询。第一个使用SUBSTRING第二个使用LIKE。</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P> Select *<BR> From authors<BR> Where substring(au_lname,1,2)=’Wh’</P><P> StmtText<BR> --------------------------------------------------------------</P><P> |--Clustered<BR> Index Scan(OBJECT: ([pubs].[dbo].[authors].[UPKCL_auidind])</P><P> Where: (substring([authors].[au_lname],1,2)=’Wh’)) </P></PRE></TD></TR></TBODY></TABLE></P>
<P>与使用LIKE的相似查询相比较：</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P> Select * <BR> From authors<BR> Where au_lname like ‘Wh%’</P><P> StmtText<BR> -------------------------------------------------------------------------<BR> |-Bookmark Lookup(BOOKMARK: ([Bmk1000]),OBJECT: ([pubs].[dbo].[authors]))<BR> |-Index Seek(OBJECT:([pubs].[dbo].[authors].[sunmind]), </P><P> SEEk:([authors].[au_lname]>=’WGp’ AND [authors].[au_lname]<’WI’),</P><P> WHERE:(like([authors].[au_lname],’Wh%’,NULL)) ORDERED FORWARD) </P></PRE></TD></TR></TBODY></TABLE></P>
<P>很明显，第二个查询使用INDEX SEEK操作，第一个查询使用了CLUSTERED INDEX SCAN操作，第二个查询计划比第一个更简单。 </P>
<P>当我们比较查询计划中那一个最好时，事实上我们更愿意用SET STATISTICS PROFILE ON或SQL Query Analyzer的Graphic Execution Plan(图形执行计划)特性而不是SET SHOWPLAN_TEXT ON。这些工具将以百分率的方式明确给你展示每一个查询计划步骤的处理消耗。这可以告诉你每种选择的或多或少的查询消耗情况。你还可以同时运行两个或更多查询来看哪个执行的最好。 </P>
<P>为了获得最全面的性能测量信息，使用SET STATISTICS IO 和SET STATISTICS TIME也是很重要的。</P>
<P>【文章相关内容】</P>
<P><STRONG>第一页</STRONG>：<A href="http://developer.51cto.com/art/200704/44253.htm"><FONT color=#0000ff>入门指南</FONT></A></P>
<P><STRONG>第二页</STRONG>：<A href="http://developer.51cto.com/art/200704/44253_1.htm"><FONT color=#0000ff>通过比较连接和子查询说明分支步骤</FONT></A> </P>
<P><STRONG>第三页</STRONG>：<A href="http://developer.51cto.com/art/200704/44253_2.htm"><FONT color=#0000ff>三种join(连接)策略</FONT></A></P>
<P>#p#</P>
<P><STRONG>理解连接的影响</STRONG></P>
<P>上文的不同查询步骤展示了SQL Server 2000是如何运用大量的操作来解析Join(连接)的。每一个Join策略都有它的长处和短处。然而，在某些罕见的情况下，查询引擎会选择效率较低的策略，如通常使用的Hash（散列）或Merge（合并）策略，而采用简单的嵌套循环就足以提供很好的性能。</P>
<P>SQL Server 使用三种join(连接)策略，这里由简单到复杂分别列出： </P>
<P><STRONG>嵌套循环</STRONG></P>
<P>对于使用简单内连接的小数据量表，嵌套循环是最佳策略。最适合两个表的记录数差别非常大，并且在连接的列上都有索引的情况。嵌套循环连接所需的I/O和比较都是最少的。 </P>
<P>嵌套循环在外表（往往是小数据量的表）中每次循环一个记录，然后在内表中查找所匹配的记录并输出。有很多关于嵌套循环策略的名字。例如，对整个表或索引进行查询，称为Naive（无知的）嵌套循环连接。使用正常索引或临时索引时，被称为索引嵌套循环连接或临时索引嵌套循环连接。 </P>
<P><STRONG>合并</STRONG></P>
<P>对于使用了排序连接列的大数据量并数据量相似的表，合并是最佳的策略。合并操作首先进行排序，然后对所有数据进行循环并产生输出。良好的合并连接性能基于在相应的列上建立索引，通常在连接谓词等式中用到的列。 </P>
<P>合并连接发挥了预先排序的优点，从每个输出中获得行数据，直接进行比较操作。例如，例如，内连接返回的是连接谓词相等的记录。如果不相等，含更低值的记录将会被丢弃，并且用下一条记录进行比较。这个过程将持续直到所有的记录都被检查完。有时合并连接被用来比较具有多对多关系的表。当这种情况发生时，SQL Server用临时表来存储这些行。 </P>
<P>如果在使用合并连接的查询中同时存在一个WHERE子句，那么这个合并连接谓词将首先被计算。然后经过合并连接谓词的每条记录将经过WHERE语句中的其他谓词再次计算。Microsoft 称之为residual predicate（剩余谓词）。</P>
<P><STRONG>Hash（散列）</STRONG> </P>
<P>对于数据量大，容量不同的表，以及连接列没有排序或索引的复杂连接需求，Hash是最佳策略。散列法被用于UNION, INTERSECT, INNER, LEFT, RIGHT和OUTER JOIN，以及集合匹配和差别等操作。Hash也用于没有有用索引的连接表。Hash操作将建立临时的Hash表并且循环所有的数据并产生输出。 </P>
<P>Hash使用一个build（已建造）输入（通常是小数据量的表）和probe(探测)输入。这个散列键（也就是在连接谓词中的列，或在GROUP BY列表中的列）被查询用来处理连接。剩余谓词是在WHERE子句中没有用于连接本身的所有其他运算。剩余谓词是在连接谓词之后计算。当构造一个Hash连接时，SQL Server可按下面的优先次序选择不同的选项： </P>
<P><STRONG>In-memory Hash(内存中散列)：</STRONG>In-memory hash连接首先将整个build输入扫描到内存中，然后在内存中创建一个临时hash表。计算出Hash值，然后将每条记录插入到Hash中。然后逐条扫描探测输入。每条探测输入将与对应的Hash相比较，如果匹配，将放在结果集中返回。 </P>
<P><STRONG>Hybrid Hash（混合散列）：</STRONG>如果散列仅比可用的内存稍大，SQL Server可能合并in-memory hash连接和grace hash连接的某些方面，称之为hybrid hash 连接。</P>
<P><STRONG>Grace Hash（优美散列）：</STRONG>当hash join太大而不能在内存中处理时，就要用到Grace hash选项。在那种情况下，整个build输入和probe输入都将被读入。然后它们被分解成多个临时的工作表，该步骤称为分区扇出。Hash键值上的Hash函数确保了所有的连接记录都在同一对分区工作表中。分区扇出将两个耗时的步骤分解为很多小步骤，这些小步骤可以被并发处理。然后Hash连接将应用于每对工作表，将所有匹配放在结果集中返回。 </P>
<P><STRONG>Recursive Hash(递归散列)</STRONG> <STRONG>：</STRONG>有时Grace Hash产生的分区扇出表仍然太大以至需要更进一步的再分区，这个就叫做递归散列。</P>
<P>注意到，散列与合并连接将每个表都处理一次。如果使用SET STATISTICS IO ON来测量这种类型的查询，会看到较低I/O的假象。然而，较低的I/O并不意味着这些连接策略一定比嵌套循环连接要快，因为还需要巨大的计算量。 </P>
<P>注意，散列连接的计算量很大。如果你发现在生产中某些查询始终用散列连接，这里要提示你应该调优你的查询或者在底层表中添加索引。 </P>
<P>在下面的例子中，我们展示标准的嵌套循环（使用默认的查询计划）和散列与合并连接（强制使用提示）。</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P> SELECT a.au_fname, a.au_lname, t.title<BR> FROM authors AS a<BR> INNER JOIN titleauthor ta<BR> ON a.au_id=ta.au_id<BR> INNER JOIN titles t<BR> ON t.title_id=ta.title_id<BR> ORDER BY au_lname ASC, au_fname ASC</P><P> StmtText<BR> -------------------------------------------------------------------------------------</P><P> |--Nested<BR> Loop(Inner Join, OUTER REFERENCES:([ta],[title_id]))<BR> |--Nested Loops(Inner Join, OUTER REFERENCES:([a],[au_id]) )<BR> | |--IndexScan(OBJECT:([pubs].[dbo].[authors].[aunmind] AS [a],ORDERED FORWAD))<BR> | |--Index Seek(OBJECT:([pubs].[dbo].[titleauthor].[auidind] AS [ta],</P><P> SEEK: ([ta].[au_id]=[a].[au_id]) ORDERED FORWAD)</P><P> |--Clustered Index Seek (OBJECT:([pubs].[dbo].[titleas].[UPKCL_titleidind] AS [t],</P><P> SEEK: ([t].[title_id]=[ta].[title_id]) ORDERED FORWAD)</P></PRE></TD></TR></TBODY></TABLE></P>
<P>上面展示计划显示的是通过SQL Server产生的标准查询计划。我们可以强制SQL Server利用提示给我们展示它是怎样处理合并和散列连接的。</P>
<P>
<TABLE cellSpacing=0 borderColorDark=#ffffff cellPadding=2 width=400 align=center borderColorLight=black border=1>
<TBODY>
<TR>
<TD class=code bgColor=#e6e6e6><PRE><P> Select a.au_fname, a.au_lname, t.title<BR> From authors AS a<BR> INNER MERGE JOIN titleauthor ta<BR> ON a.au_id=ta.au_id<BR> INNER HASH JOIN titles t<BR> ON t.title_id=ta.title_id</P><P> ORDER BY au_lname ASC, au_fname ASC <BR> Warning:The join order has been enforced because a local join hint is used.</P><P> StmtText<BR> -----------------------------------------------------------------------------------</P><P> |--Sort(ORDER BY:([a].[au_lname] ASC ,[a].[au_fname] ASC)<BR> |--Hash Match(Inner Join,HASH:([ta].[title_id])=([t].[title_id]), </P><P> RESIDUAL:([ta].[title_id]=[t].[title_id]))<BR> |--Merge Join(Inner Join, MERGE:([a] [au_id]=[ta].[au_id]), </P><P> RESIDUAL:([ta].[au_id]=[a].[au_id]))<BR> | |--Clustered index Scan(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind] AS [a],</P><P> ORDERED FORWAD)<BR> | |--Index Scan OBJECT:([pubs].[dbo].[titleauthor].[auidind] AS [ta], ORDERED FORWAD)<BR> |--Index Scan (OBJECT:([pubs].[dbo].[titles].[titleind] AS [t]))</P></PRE></TD></TR></TBODY></TABLE></P>
<P>在这个例子中，你可以很清晰的看到每一个连接将其他连接的谓词作为剩余谓词。（你也会注意到提示的使用使SQL Server发出一个警告）。这个查询还强制使用SORT操作来支持散列与合并连接。</P>
<P>【文章相关内容】</P>
<P><STRONG>第一页</STRONG>：<A href="http://developer.51cto.com/art/200704/44253.htm"><FONT color=#0000ff>入门指南</FONT></A></P>
<P><STRONG>第二页</STRONG>：<A href="http://developer.51cto.com/art/200704/44253_1.htm"><FONT color=#0000ff>通过比较连接和子查询说明分支步骤</FONT></A> </P>
<P><STRONG>第三页</STRONG>：<A href="http://developer.51cto.com/art/200704/44253_2.htm"><FONT color=#0000ff>三种join(连接)策略</FONT></A></P>
<DIV align=right>【责任编辑：<A class=ln href="mailto:sunsj@51cto.com">火凤凰</A> TEL：（010）68476606-8007】</DIV></td> </tr> <tr> <td class="d_font4"> </td> </tr> </table>⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -