sql - SQL中如何简单高效查询嵌套关系？

Question

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

---

设置

这是我的架构的简单表示:

有几点需要注意:

• 用户通过成员资格属于团队。
• teams 可以有很多collections、apps 和webhooks。
• collections 也可以有很多 webhooks。
• webhooks 可以属于 team 或 collection，但只能属于一个。
• events 可以属于任何对象，但只能属于一个。

这似乎是大多数 SaaS 类型公司(例如 Slack 或 Stripe)都会拥有的相当基本的设置。一切都由团队“拥有”，但用户属于团队并与界面交互。

---

问题

鉴于该设置，我想创建一个 SQL 查询来解决...

Find all of the events that are related (directly or indirectly) to a given user by id.

我可以轻松地编写直接或通过特定方式间接查找的查询。例如……

Find all of the events that are directly related to a user by id.

SELECT *
FROM events
WHERE user_id = ${id}

或者...

Find all of the events that are indirectly related to a user via their teams.

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

甚至...

Find all of the events that are indirectly related to a user via any collections of their teams.

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}

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

Find all of the events that are indirectly related to a user via any webhooks of their teams or collections.

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}
)

---

问题

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

Answer 1

与任何查询一样，最有效的方法是“视情况而定”。有很多变量在起作用 - 表中的行数、行长度、索引是否存在、服务器上的 RAM 等等。

我能想到的处理此类问题的最佳方法(考虑可维护性和提高效率的方法)是使用 CTE，它允许您创建一个临时结果并在整个查询中重用该结果。 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 原则
4. 如果 CTE 在查询之外有值，您可以将它移到存储过程中并引用它