sql-server - 为什么join子句的顺序会影响SQL Server中的查询计划？

Question

我正在 SQL Server 2000(和 2005)中构建一个 View ，我注意到连接语句的顺序极大地影响了查询的执行计划和速度。

select      sr.WTSASessionRangeID,
            -- bunch of other columns
from        WTSAVW_UserSessionRange us
inner join  WTSA_SessionRange sr on sr.WTSASessionRangeID = us.WTSASessionRangeID
left outer join WTSA_SessionRangeTutor srt on srt.WTSASessionRangeID = sr.WTSASessionRangeID
left outer join WTSA_SessionRangeClass src on src.WTSASessionRangeID = sr.WTSASessionRangeID
left outer join WTSA_SessionRangeStream srs on srs.WTSASessionRangeID = sr.WTSASessionRangeID
--left outer join MO_Stream ms on ms.MOStreamID = srs.MOStreamID
left outer join WTSA_SessionRangeEnrolmentPeriod srep on srep.WTSASessionRangeID = sr.WTSASessionRangeID
left outer join WTSA_SessionRangeStudent stsd on stsd.WTSASessionRangeID = sr.WTSASessionRangeID
left outer join WTSA_SessionSubrange ssr on ssr.WTSASessionRangeID = sr.WTSASessionRangeID
left outer join WTSA_SessionSubrangeRoom ssrr on ssrr.WTSASessionSubrangeID = ssr.WTSASessionSubrangeID
left outer join MO_Stream ms on ms.MOStreamID = srs.MOStreamID

在 SQL Server 2000 上，上面的查询始终生成一个成本为 946 的计划。如果我取消对查询中间的 MO_Stream 连接的注释并注释掉底部的一个，成本下降到 263。执行速度相应地下降。我一直认为查询优化器会在不考虑连接顺序的情况下适本地解释查询，但似乎顺序很重要。

因此，由于顺序似乎很重要，我是否应该遵循连接策略来编写更快的查询？

(顺便说一句，在 SQL Server 2005 上，数据几乎相同，查询计划成本分别为 0.675 和 0.631。)

编辑:在 SQL Server 2000 上，以下是分析的统计信息:

946-cost query: 9094ms CPU, 5121 reads, 0 writes, 10123ms duration

263-cost query: 172ms CPU, 7477 reads, 0 writes, 170ms duration

编辑:这是表的逻辑结构。

SessionRange ---+--- SessionRangeTutor
                |--- SessionRangeClass
                |--- SessionRangeStream --- MO_Stream
                |--- SessionRangeEnrolmentPeriod
                |--- SessionRangeStudent
                +----SessionSubrange --- SessionSubrangeRoom

编辑:感谢 Alex 和 gbn 为我指明了正确的方向。我还找到了 this question .

这是新查询:

select sr.WTSASessionRangeID    // + lots of columns

from WTSAVW_UserSessionRange us
inner join WTSA_SessionRange sr on sr.WTSASessionRangeID = us.WTSASessionRangeID
left outer join WTSA_SessionRangeTutor srt on srt.WTSASessionRangeID = sr.WTSASessionRangeID
left outer join WTSA_SessionRangeClass src on src.WTSASessionRangeID = sr.WTSASessionRangeID
left outer join WTSA_SessionRangeEnrolmentPeriod srep on srep.WTSASessionRangeID = sr.WTSASessionRangeID
left outer join WTSA_SessionRangeStudent stsd on stsd.WTSASessionRangeID = sr.WTSASessionRangeID

// SessionRangeStream is a many-to-many mapping table between SessionRange and MO_Stream
left outer join (
    WTSA_SessionRangeStream srs
    inner join MO_Stream ms on ms.MOStreamID = srs.MOStreamID
) on srs.WTSASessionRangeID = sr.WTSASessionRangeID

// SessionRanges MAY have Subranges and Subranges MAY have Rooms
left outer join (
    WTSA_SessionSubrange ssr    
    left outer join WTSA_SessionSubrangeRoom ssrr on ssrr.WTSASessionSubrangeID = ssr.WTSASessionSubrangeID
) on ssr.WTSASessionRangeID = sr.WTSASessionRangeID

SQLServer2000 成本:24.9

Answer 1

我不得不不同意以前的所有答案，原因很简单:如果您更改左连接的顺序，您的查询在逻辑上是不同的，因此它们会产生不同的结果集。自己看:

SELECT 1 AS a INTO #t1
UNION ALL SELECT 2
UNION ALL SELECT 3
UNION ALL SELECT 4;

SELECT 1 AS b INTO #t2
UNION ALL SELECT 2;

SELECT 1 AS c INTO #t3
UNION ALL SELECT 3;

SELECT a, b, c 
FROM #t1 LEFT JOIN #t2 ON #t1.a=#t2.b
  LEFT JOIN #t3 ON #t2.b=#t3.c
ORDER BY a;

SELECT a, b, c 
FROM #t1 LEFT JOIN #t3 ON #t1.a=#t3.c
  LEFT JOIN #t2 ON #t3.c=#t2.b
ORDER BY a;

a           b           c
----------- ----------- -----------
1           1           1
2           2           NULL
3           NULL        NULL
4           NULL        NULL

(4 row(s) affected)

a           b           c
----------- ----------- -----------
1           1           1
2           NULL        NULL
3           NULL        3
4           NULL        NULL