主页 / dba / 问题 / 321409
Accepted
Jean Louis
Asked: 2022-12-25 09:25:37 +0800 CST

如何优化此 SQL UNION 语句？

  • 772

我正在使用 PostgreSQL 14.6，我的目标是找出是否有更简单、更快的方法来实现相同的结果，但仍然使用类似的 UNION 查询。

这是我要优化的 SQL 语句：

SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, hyobjectypes, actionlltypes
WHERE hyobjects_hyobjectypes = hyobjectypes_id
  AND hyobjectypes_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 1 
UNION
SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, actionstatuses, actionlltypes
WHERE actionstatuses_id = hyobjects_actionstatuses 
  AND actionstatuses_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 1
EXCEPT
SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, actionstatuses, actionlltypes
WHERE hyobjects_actionstatuses = actionstatuses_id
  AND actionstatuses_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 2
UNION
SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects, hyobjectypes, actionstatuses, actionlltypes
WHERE hyobjects_hyobjectypes = hyobjectypes_id
  AND hyobjectypes_actionlltypes = actionlltypes_id
  AND actionlltypes_id = 1 
  AND hyobjects_actionstatuses = 1
  ORDER BY hyobjects_globalpriority DESC

表中的哪里actionlltypes有actionlltypes_id = 1什么意思ACTIVE，actionlltypes_id = 2什么意思INACTIVE。

以及列hyobjectypes_actionlltypes引用actionlltypes和列hyobjects_actionstatuses引用的位置actionlltypes。我需要的结果actionstatuses_id可能是 1 或有actionlltypes_id = 1.

postgresql

2 个回答

  1. Best Answer

    我鼓励您尽可能地在查询中学习/使用 ANSI 连接

    • 使您的查询不那么模棱两可
    • 更难创建不需要的笛卡尔积（交叉连接）

    查看列名并完全猜测事物如何使用 ANSI 连接链接在一起可能如下所示：

    SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects 
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    JOIN actionlltypes
        ON ( actionlltypes_id = hyobjectypes_actionlltypes )
    WHERE actionlltypes_id = 1 
UNION
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    JOIN actionlltypes
        ON ( actionlltypes_id = actionstatuses_actionlltypes )
    WHERE actionlltypes_id = 1
EXCEPT
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses 
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    JOIN actionlltypes
        ON ( actionlltypes_id = actionstatuses_actionlltypes )
    WHERE actionlltypes_id = 2
UNION
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    JOIN actionstatuses
        ON ( )
    JOIN actionlltypes
        ON ( actionlltypes_id = hyobjectypes_actionlltypes )
    WHERE actionlltypes_id = 1 
        AND hyobjects_actionstatuses = 1
    ORDER BY hyobjects_globalpriority DESC ;

    需要注意的一件事是在第四个查询中，似乎没有任何列将 actionstatuses 表连接到其他任何东西，从而导致交叉连接。这是故意的，还是意外？

    查看第一个查询：

    SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects 
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    JOIN actionlltypes
        ON ( actionlltypes_id = hyobjectypes_actionlltypes )
    WHERE actionlltypes_id = 1

    这相当于

    SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects 
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    JOIN actionlltypes
        ON ( actionlltypes_id = hyobjectypes_actionlltypes )
    WHERE hyobjectypes_actionlltypes = 1

    如果且仅当我对事物结构和列命名的猜测是正确的，那么结果中只有两列来自 hyobjects 表，不是吗？如果是这样，那么是否需要加入 hyobjecttypes 和 actionlltypes 表？如果是这样，那么第一个查询应该减少为：

    SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects 
    WHERE hyobjectypes_actionlltypes = 1

    第二个查询可以减少

    SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    JOIN actionlltypes
        ON ( actionlltypes_id = actionstatuses_actionlltypes )
    WHERE actionlltypes_id = 1

    到

    SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    WHERE actionstatuses_actionlltypes = 1

    查询三

    SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses 
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    JOIN actionlltypes
        ON ( actionlltypes_id = actionstatuses_actionlltypes )
    WHERE actionlltypes_id = 2

    成为

    SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses 
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    WHERE actionstatuses_actionlltypes = 2

    并查询四

    SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    JOIN actionstatuses
        ON ( )
    JOIN actionlltypes
        ON ( actionlltypes_id = hyobjectypes_actionlltypes )
    WHERE actionlltypes_id = 1 
        AND hyobjects_actionstatuses = 1

    可能成为

    SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    WHERE hyobjectypes_actionlltypes = 1
        AND hyobjectypes_actionlltypes = 1

    将它们放在一起可能会导致

    SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects 
    WHERE hyobjectypes_actionlltypes = 1
UNION
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    WHERE actionstatuses_actionlltypes = 1
EXCEPT
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN actionstatuses 
        ON ( actionstatuses_id = hyobjects_actionstatuses )
    WHERE actionstatuses_actionlltypes = 2
UNION
SELECT hyobjects_id, 
        hyobjects_globalpriority
    FROM hyobjects
    JOIN hyobjectypes
        ON ( hyobjectypes_id = hyobjects_hyobjectypes )
    WHERE hyobjectypes_actionlltypes = 1
        AND hyobjectypes_actionlltypes = 1

    但是，这纯粹是基于我对表名和列名的解释的推测。

    现在，您要实际优化的内容尚不清楚，所以我会在这里停止......

    • 2

  2. 感谢所有的好指针和参考。我一直在学习和尝试。

    我的原始语句运行良好，只是有点慢。EXPLAIN ANALYZE给我以下UNION基于陈述的结果：

     Planning Time: 0.898 ms
 Execution Time: 182.354 ms

    通过查看这里技术人员的答案，我得出了这个结果：

    SELECT hyobjects_id, hyobjects_globalpriority
  FROM hyobjects
  JOIN actionstatuses
  ON (hyobjects_actionstatuses = actionstatuses_id)
  JOIN hyobjectypes
  ON (hyobjectypes_id = hyobjects_hyobjectypes 
      AND ((hyobjectypes_actionlltypes = 1 
        AND (actionstatuses_actionlltypes = 1 OR hyobjects_actionstatuses = 1))
        OR ((hyobjectypes_actionlltypes = 2 AND (actionstatuses_actionlltypes = 1)))));

    它以以下速度给我相同的结果：

     Planning Time: 0.628 ms
 Execution Time: 57.047 ms
    • 2

相关问题