代码之家 › 专栏 › 技术社区 › Jon Erickson

哪个sql查询更快?按联接条件筛选还是按where子句筛选?

sql-server-2008 tsql sql-server sql

Jon Erickson · 技术社区 · 15 年前

比较这两个查询。将筛选器放在联接条件或WAE子句中是否更快?我一直觉得它在连接标准上更快,因为它会在最快的时候减少结果集,但我不确定。

我要做一些测试,但我也希望得到一些更清晰的观点。

查询1

SELECT      *
FROM        TableA a
INNER JOIN  TableXRef x
        ON  a.ID = x.TableAID
INNER JOIN  TableB b
        ON  x.TableBID = b.ID
WHERE       a.ID = 1            /* <-- Filter here? */

查询2

SELECT      *
FROM        TableA a
INNER JOIN  TableXRef x
        ON  a.ID = x.TableAID
        AND a.ID = 1            /* <-- Or filter here? */
INNER JOIN  TableB b
        ON  x.TableBID = b.ID

编辑

我做了一些测试,结果显示它实际上非常接近,但是 WHERE 子句实际上稍微快一点!=)

我完全同意在 哪里 条款,我只是好奇性能的影响。

条件为: 143016毫秒
已用时间联接条件: 143256毫秒

试验

SET NOCOUNT ON;

DECLARE @num    INT,
        @iter   INT

SELECT  @num    = 1000, -- Number of records in TableA and TableB, the cross table is populated with a CROSS JOIN from A to B
        @iter   = 1000  -- Number of select iterations to perform

DECLARE @a TABLE (
        id INT
)

DECLARE @b TABLE (
        id INT
)

DECLARE @x TABLE (
        aid INT,
        bid INT
)

DECLARE @num_curr INT
SELECT  @num_curr = 1

WHILE (@num_curr <= @num)
BEGIN
    INSERT @a (id) SELECT @num_curr
    INSERT @b (id) SELECT @num_curr

    SELECT @num_curr = @num_curr + 1
END

INSERT      @x (aid, bid)
SELECT      a.id,
            b.id
FROM        @a a
CROSS JOIN  @b b

/*
    TEST
*/
DECLARE @begin_where    DATETIME,
        @end_where      DATETIME,
        @count_where    INT,
        @begin_join     DATETIME,
        @end_join       DATETIME,
        @count_join     INT,
        @curr           INT,
        @aid            INT

DECLARE @temp TABLE (
        curr    INT,
        aid     INT,
        bid     INT
)

DELETE FROM @temp

SELECT  @curr   = 0,
        @aid    = 50

SELECT  @begin_where = CURRENT_TIMESTAMP
WHILE (@curr < @iter)
BEGIN
    INSERT      @temp (curr, aid, bid)
    SELECT      @curr,
                aid,
                bid
    FROM        @a a
    INNER JOIN  @x x
            ON  a.id = x.aid
    INNER JOIN  @b b
            ON  x.bid = b.id
    WHERE       a.id = @aid

    SELECT @curr = @curr + 1
END
SELECT  @end_where = CURRENT_TIMESTAMP

SELECT  @count_where = COUNT(1) FROM @temp
DELETE FROM @temp

SELECT  @curr = 0
SELECT  @begin_join = CURRENT_TIMESTAMP
WHILE (@curr < @iter)
BEGIN
    INSERT      @temp (curr, aid, bid)
    SELECT      @curr,
                aid,
                bid
    FROM        @a a
    INNER JOIN  @x x
            ON  a.id = x.aid
            AND a.id = @aid
    INNER JOIN  @b b
            ON  x.bid = b.id

    SELECT @curr = @curr + 1
END
SELECT  @end_join = CURRENT_TIMESTAMP

SELECT  @count_join = COUNT(1) FROM @temp
DELETE FROM @temp

SELECT  @count_where AS count_where,
        @count_join AS count_join,
        DATEDIFF(millisecond, @begin_where, @end_where) AS elapsed_where,
        DATEDIFF(millisecond, @begin_join, @end_join) AS elapsed_join

9 回复 | 直到 6 年前

Quassnoi 15 年前

就性能而言,它们是相同的(并且产生相同的计划)

从逻辑上讲,如果替换 INNER JOIN 用一个 LEFT JOIN .

在你的情况下,这将是这样的:

SELECT  *
FROM    TableA a
LEFT JOIN
        TableXRef x
ON      x.TableAID = a.ID
        AND a.ID = 1
LEFT JOIN
        TableB b
ON      x.TableBID = b.ID

或者:

SELECT  *
FROM    TableA a
LEFT JOIN
        TableXRef x
ON      x.TableAID = a.ID
LEFT JOIN
        TableB b
ON      b.id = x.TableBID
WHERE   a.id = 1

前一个查询不会返回 a.id 以外 1 ,所以后面的语法(使用 WHERE )逻辑上更加一致。

Remus Rusanu 15 年前

对于内部连接,你的标准放在哪里并不重要。SQL编译器将两者转换为一个执行计划,在该计划中,筛选发生在联接的下面(即,似乎出现的筛选表达式处于联接条件中)。

外部连接是另一回事,因为过滤器的位置改变了查询的语义。

Robin Day 15 年前

就这两种方法而言。

join/on用于连接表
筛选结果的位置

虽然你可以用不同的方法,但对我来说,这总是一种味道。

当出现问题时处理性能问题。然后你就可以研究这种“优化”。

TomTom 15 年前

对于任何查询优化器来说…它们是一样的。

Joe Mastey 15 年前

这种连接的位置不太可能成为性能的决定因素。我不太熟悉tsql的执行计划,但很可能它们会自动优化为类似的计划。

3Dave 15 年前

规则0:运行一些基准测试并查看!唯一能判断哪个更快的方法就是试试。这些类型的基准测试非常容易使用sql profiler执行。

另外,检查用join和where子句编写的查询的执行计划,看看有哪些不同。

最后,正如其他人所说,这两个应该由任何一个像样的优化器来对待,包括内置在sql server中的优化器。

Quassnoi 15 年前

快一点吗?试试看。

哪个更容易阅读?在我看来,第一个看起来更“正确”,因为移动的条件实际上与连接无关。

eKek0 15 年前

我猜是第一个,因为它对数据进行了更具体的过滤。但是你 should see the execution plan ,与任何优化一样,因为它可以在数据大小、服务器硬件等方面有很大的不同。

Peter Graham 6 年前

在PostgreSQL中它们是相同的。我们知道是因为如果你知道 explain analyze 在每个查询中,计划都是相同的。举个例子:

# explain analyze select e.* from event e join result r on e.id = r.event_id and r.team_2_score=24;

                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=27.09..38.22 rows=7 width=899) (actual time=0.045..0.047 rows=1 loops=1)
   Hash Cond: (e.id = r.event_id)
   ->  Seq Scan on event e  (cost=0.00..10.80 rows=80 width=899) (actual time=0.009..0.010 rows=2 loops=1)
   ->  Hash  (cost=27.00..27.00 rows=7 width=8) (actual time=0.017..0.017 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on result r  (cost=0.00..27.00 rows=7 width=8) (actual time=0.006..0.008 rows=1 loops=1)
               Filter: (team_2_score = 24)
               Rows Removed by Filter: 1
 Planning time: 0.182 ms
 Execution time: 0.101 ms
(10 rows)

# explain analyze select e.* from event e join result r on e.id = r.event_id where r.team_2_score=24;
                                                  QUERY PLAN                                                   
---------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=27.09..38.22 rows=7 width=899) (actual time=0.027..0.029 rows=1 loops=1)
   Hash Cond: (e.id = r.event_id)
   ->  Seq Scan on event e  (cost=0.00..10.80 rows=80 width=899) (actual time=0.010..0.011 rows=2 loops=1)
   ->  Hash  (cost=27.00..27.00 rows=7 width=8) (actual time=0.010..0.010 rows=1 loops=1)
         Buckets: 1024  Batches: 1  Memory Usage: 9kB
         ->  Seq Scan on result r  (cost=0.00..27.00 rows=7 width=8) (actual time=0.006..0.007 rows=1 loops=1)
               Filter: (team_2_score = 24)
               Rows Removed by Filter: 1
 Planning time: 0.140 ms
 Execution time: 0.058 ms
(10 rows)

它们都有相同的最小和最大成本以及相同的查询计划。另外,请注意,即使在最上面的查询中,team_score_2也会作为“过滤器”应用。