理解SQL Server的SQL查询计划

核心提示： 通过比较连接和子查询说明分支步骤一条正确的老规则是：在结果集相同的情况下，连接比子查询具有更好的性能，理解SQL Server的SQL查询计划(4)，SELECT au_fname,au_lnameFROM authorsWHERE au_id IN (select au_id from

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

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

SELECT au_fname,au_lname
FROM authors
WHERE au_id IN (select au_id from titleauther )
StmtText
-------------------------------------------------------------------------------------------
|---Nested Loops(Inner Join, OUTER ReFERENCES:([titleauthor].[au_id])
|--Stream Aggregate(GROUP BY:([titleauthor].[au_id]))
| |--Index Scan(OBJECT:([pubs].[dbo].[titleauthor].[auidind]),ORDERED FORWARD)
|--ClusteredIndex Seek(OBJECT:([pubs].[dbo].[authors].[UPKCL_auidind]),
SEEK:([authors].[au_id]=[titleauthor].[au_id]) ORDERED FORWARD)
Table ‘authors’. Scan count 38,logical reads 76,physical reads 0,read-ahead reads 0.
Table　‘titleauthor’. Scan count 2, logical reads 2, physical reads 1,read-ahead reads 0.

在这种情况下，查询引擎选择一个嵌套循环操作。这个查询被迫用聚集索引读取整个authors表，在处理中执行大量的逻辑页读。

注意：在带分支步骤的查询中，缩进行给你展示那些步骤是其它步骤的分支。

Select distinct au_fname,au_lname
From authors as a
Join titleauthor as t ON a.au_id=t.au_id
StmtText
---------------------------------------------------------------------------------
|--stream Aggregate(group by: ([a].[au_lname].[a].[au_fname]))
|-Nested loops(Inner Join,OUTER REFERENCES: ([a].[au_id]))
|-Index scan(OBJECT:([pubs].[dbo].[authors].[authord ]as[a]),ordered forward)
|-Index Seek (OBJECT: [pubs].[dbo].[titleauthor].[authord ]as [t]),
SEEK: ([t].[au_id]=[a].[au_id]) ORDER FORWARD)
Table ‘titleauthors’ .Scan count 23,logical reads 23,
physical reads 0,read ahead reads 0.
Table ‘authors’ .Scan count 1,logical reads 1,physical reads 0,read-ahead read 0.

上面的这个查询中，titleauthors表逻辑读的数字上升而authors表下降。注意到，stream aggregation在查询计划中位置更高，即发生的更晚。