sql - 这里最佳的 JOIN 方法是什么？

Question

这里有两个查询返回相同的结果集，但哪个是最优语句或者无关紧要？

SELECT A.id, B.somefield FROM (
   SELECT id from table1
   UNION
   SELECT id from table2
) A LEFT JOIN table3 B on A.id = B.id

或

SELECT A.id, B.somefield FROM table1 A LEFT JOIN table3 B on A.id = B.id
UNION
SELECT A.id, B.somefield FROM table2 B LEFT JOIN table3 B on A.id = B.id

我意识到我可以向它们注入(inject)大量数据并运行一些测试，但我对“为什么”更快感兴趣？ (我正在使用 postgresql，以防它影响事物)。

谢谢。

Answer 1

使用 UNION 的执行计划首先显示了更少的步骤，不幸的是执行计划并不是一切，还有表扫描、逻辑读取、CPU 使用率，所以这不是全部所有这一切在很大程度上取决于您的数据和指标。

对于重复项，第一个查询应该执行得更好，因为 UNION 删除重复项发生在连接之前，导致表 3 上的表扫描次数较少。如果表 1 中没有重复项和 table2 那么应该没有区别。

这可以用一些样本数据来证明。我所有的示例都使用以下 5 个表(T4 和 T5 只是将输出转储到其中，因此您不必在 SQL fiddle 上向下滚动页面以查看执行计划)

CREATE TABLE T1 (ID INT NOT NULL);
CREATE TABLE T2 (ID INT NOT NULL);
CREATE TABLE T3 (FK INT NOT NULL, SomeValue VARCHAR(10) NOT NULL);
CREATE TABLE T4 (ID INT NOT NULL, SomeValue VARCHAR(10) NULL);
CREATE TABLE T5 (ID INT NOT NULL, SomeValue VARCHAR(10) NULL);

并且全部使用以下进行测试(也反向完成以消除任何查询计划缓存):

INSERT INTO T4
SELECT  ID, SomeValue
FROM    T1
        LEFT JOIN T3
            ON ID = FK
UNION 
SELECT  ID, SomeValue
FROM    T2
        LEFT JOIN T3
            ON ID = FK;

INSERT INTO T5
SELECT  ID, SomeValue
FROM    (   SELECT  ID
            FROM    T1
            UNION
            SELECT  ID
            FROM    T2
        ) T
        LEFT JOIN T3
            ON ID = FK;

示例 1 - T1 包含也在 T2 中的行

INSERT INTO T1 (ID)
SELECT  *
FROM    GENERATE_SERIES(0, 40000);

INSERT INTO T2 (ID)
SELECT  *
FROM    GENERATE_SERIES(20000, 60000);

INSERT INTO T3 (FK, SomeValue)
SELECT  *, 'VALUE'
FROM    GENERATE_SERIES(10000, 50000);

Example on SQL Fiddle显示插入到 T4(UNION before JOIN)执行得更好。我已经运行了 25 次，其中 22 次插入到 T4 的速度更快。没有足够的数据来消除等式中的服务器负载，因此如预期的那样存在一些异常。在 this example 中插入的顺序是相反的, 再次看到类似的结果。

示例 2 - table1 和 table2 中没有重复项

INSERT INTO T1 (ID)
SELECT  *
FROM    GENERATE_SERIES(0, 30000);

INSERT INTO T2 (ID)
SELECT  *
FROM    GENERATE_SERIES(30001, 60000);

INSERT INTO T3 (FK, SomeValue)
SELECT  *, 'VALUE'
FROM    GENERATE_SERIES(10000, 50000);

在此示例中，执行时间彼此更接近，并且经常在执行速度更快的方法之间切换。

Sample Data

Sample Data 2

最后，重申一下已经提出的观点，如果您不期望欺骗/不关心欺骗，那么 UNION ALL 将提高性能，但由于没有欺骗，性能应该与这两种方法在很大程度上相似，这应该在同等程度上改进这两种方法。我没有对此进行测试，但更改我用来检查它的测试数据应该不是一项艰巨的任务。

编辑

我刚刚在 SQL Fiddle 上尝试了查询，它们显示出比在我的本地机器上更多的差异，所以对这些例子持保留态度，并在你自己的服务器上进行测试，这样更容易创造公平的考试环境!