如何在SQL中简单高效地查询嵌套关系？

Question

我希望编写最简单、最有效的SQL查询语句，以检索与给定用户相关的所有事件。

---

设置

这是我模式的简单表示：

需要注意的几点：

• users 通过 memberships 属于 teams。
• teams 可以拥有多个 collections、apps 和 webhooks。
• collections 也可以拥有多个 webhooks。
• webhooks 只能属于一个 team 或 collection。
• events 可以属于任何对象，但只能属于一个对象。

这看起来像大多数 SaaS 类型公司都会有的基本设置 (例如 Slack 或 Stripe)。所有内容都由团队 "拥有"，但用户属于团队并与界面进行交互。

---

问题

在这种设置下，我想创建一个SQL查询来解决...

查找所有与给定用户通过id（直接或间接）相关的事件。

我可以轻松编写查询，以通过特定手段直接或间接地找到它们。例如...

查找所有与用户通过id 直接 相关的事件。

SELECT *
FROM events
WHERE user_id = ${id}

或者...

通过用户的团队查找所有与用户间接相关的事件。

SELECT events.*
FROM events
JOIN memberships ON memberships.team_id = events.team_id
WHERE memberships.user_id = ${id}

甚至可以...

通过用户所在团队的所有集合间接地找到与其相关的所有事件。

SELECT events.*
FROM events
JOIN collections ON collections.id = events.collection_id
JOIN memberships ON memberships.team_id = collections.team_id
WHERE memberships.user_id = ${id}

Webhooks变得更加复杂，因为它们可以通过两种不同的方式相关联...

查找所有通过用户的团队或集合的任何webhook 间接 相关的事件。

SELECT *
FROM events
WHERE webhook_id IN (
  SELECT webhooks.id
  FROM webhooks
  JOIN memberships ON memberships.team_id = webhooks.team_id
  WHERE memberships.user_id = ${id}
)
OR webhook_id IN (
  SELECT webhooks.id
  FROM webhooks
  JOIN collections ON collections.id = webhooks.collection_id
  JOIN memberships ON memberships.team_id = collections.team_id
  WHERE memberships.user_id = ${id}
)

但是，如您所见，用户与发生的事件之间有很多不同的关联方式，通过所有这些路径！因此，当我尝试查询成功获取所有相关事件时，它最终看起来像...

SELECT * 
FROM events
WHERE user_id = ${id}
OR app_id IN (
  SELECT apps.id
  FROM apps
  JOIN memberships ON memberships.team_id = apps.team_id
  WHERE memberships.user_id = ${id}
)
OR collection_id IN (
  SELECT collections.id
  FROM collections
  JOIN memberships ON memberships.team_id = collections.team_id
  WHERE memberships.user_id = ${id}
)
OR memberships_id IN (
  SELECT id
  FROM memberships
  WHERE user_id = ${id}
)
OR team_id IN (
  SELECT team_id
  FROM memberships
  WHERE user_id = ${id}
)
OR webhook_id IN (
  SELECT webhooks.id
  FROM webhooks
  JOIN memberships ON memberships.team_id = webhooks.team_id
  WHERE memberships.user_id = ${id}
)
OR webhook_id IN (
  SELECT webhooks.id
  FROM webhooks
  JOIN collections ON collections.id = webhooks.collection_id
  JOIN memberships ON memberships.team_id = collections.team_id
  WHERE memberships.user_id = ${id}
)

---

问题

• 最终的“全部包括”的查询是否非常低效？
• 有更有效的编写方式吗？
• 有没有更简单、更易于阅读的编写方式？

- Ian Storm Taylor

---

您在这里标记了3个不同的数据库系统，请只使用一个。 - DavidG

这是一个写得很好的问题，我的朋友。我可以看出你已经尝试过自己解决它，表明你已经付出了努力。 - Uncle Iroh

Answer 1

与任何查询一样，最有效的方法是“它取决于”。有许多变量在起作用-表中行数，行长度，是否存在索引，服务器上的RAM等。

我能想到处理这种问题（考虑可维护性和广泛的效率方法）的最佳方式是使用CTEs，它允许您创建一个临时结果并在整个查询中重复使用该结果。 CTE使用WITH关键字，并将结果别名为表，以便您可以多次对其进行JOIN：

WITH user_memberships AS (
    SELECT *
    FROM memberships
    WHERE user_id = ${id}
), user_apps AS (
    SELECT *
    FROM apps
    INNER JOIN user_memberships
        ON user_memberships.team_id = apps.team_id
), user_collections AS (
    SELECT *
    FROM collections
    INNER JOIN user_memberships
        ON user_memberships.team_id = collections.team_id
), user_webhooks AS (
    SELECT *
    FROM webhooks
    LEFT OUTER JOIN user_collections ON user_collections.id = webhooks.collection_id
    INNER JOIN user_memberships
        ON user_memberships.team_id = webhooks.team_id
        OR user_memberships.team_id = user_collections.team_id
)

SELECT events.* 
FROM events
WHERE app_id IN (SELECT id FROM user_apps)
OR collection_id IN (SELECT id FROM user_collections)
OR membership_id IN (SELECT id FROM user_memberships)
OR team_id IN (SELECT team_id FROM user_memberships)
OR user_id = ${id}
OR webhook_id IN (SELECT id FROM user_webhooks)
;

以这种方式完成的好处包括：

1. 每个CTE都可以利用适当的JOIN谓词上的索引，更快地返回该子集的结果，而不是让执行计划尝试解决一系列复杂的谓词。
2. CTE可以单独维护，使得解决子集问题更加容易。
3. 您不会违反DRY原则（Don't Repeat Yourself）。
4. 如果CTE在查询之外有价值，您可以将其移动到存储过程中并引用它。

- e_i_pi

Answer 2

唯一能让它变快的方法，我能想到的就是使用 union。

SELECT e.* 
FROM events e
WHERE user_id = ${id}
UNION 
select e.*
  FROM apps a
  join events e on a.apps_id = e.apps_id
  JOIN memberships ON memberships.team_id = apps.team_id
  WHERE memberships.user_id = ${id}
UNION
select e.*
from 
  FROM collections c 
  join events e on e.collections_id = c.collections_id
  JOIN memberships ON memberships.team_id = collections.team_id
  WHERE memberships.user_id = ${id}
UNION
select e.*
  FROM memberships m
  join events e on e.memberships_id = e.memberships_id
  WHERE user_id = ${id}
UNION
...;

- Uncle Iroh

---

你想在这里使用UNION还是UNION ALL？UNION意味着查询不是相互独立的，但结果必须是唯一的，并且通常会导致查询中的排序操作。UNION ALL意味着你不介意有重复的结果，或者子查询保证是相互独立的（就像这个例子），因此通常不涉及额外的排序，并且速度更快。 - joshp

这是否比原始的“OR...IN”查询更快，可能很大程度上取决于具体的数据库。 - joshp

Answer 3

如果您对模式没有任何控制，那么请不要继续阅读。如果这种情况不适用于您，请不要在此处放置太多详细信息，但我认为它看起来像是一个所有权模型。

例如：

基础表

Id

IdOwner（FK到BaseTable上的Id - 非常重要）

Type（用户= 0，应用程序= 1，集合= 2等或使用枚举）

应用程序

Id（FK到BaseTable）

集合

Id（FK到BaseTable）

成员资格

Id（FK到BaseTable）

Webhooks

Id（FK到BaseTable）

团队

Id（FK到BaseTable）

事件

Id（FK到BaseTable）

成员资格

Team_Id（FK到Basetable或Team）

User_Id（FK到Basetable或Users）

用户

Id（FK到BaseTable）

然后，您的查询变成了一个递归CTE：“找到所有类型为Event的对象，这些对象由x用户拥有或最终拥有”。

这将为您提供一个ID列表，然后您必须将其连接到Events表，并获得您的对象。

这种模型有点棘手，因为要加载任何内容，您都必须将其与基本表连接起来，但对于这种嵌套所有权，它非常有效。

我想将此发布为评论，但如果我这样做，格式将会消失，因此我已将其发布为答案。如果它有帮助并且您想要更多详细信息，请随时与我联系。

如果我完全错过了重点，并且这没有帮助，请不要对我大喊大叫（以前在SO上遇到过这种情况），只需说“谢谢，Adam，但这没有帮助”，我会删除它。

顺祝商祺，

Adam。

- Adam Benson