mysql - 这个查询可以在没有 ONION 的情况下重写吗，它是否可扩展？

Question

我有几张表:

• 在 product 表中，我有一个产品列表。
• 在 user 表中，我有一个用户列表。
• 在 group 表中，我有用户组。
• 在 group_member 表中，我链接了 group 和 member(多对多)
• 在 user_product 表中，我链接了 user 和 product(多对多)
• 在 group_product 表中，我链接了 group 和 product(多对多)

所以一个用户可以有很多产品，一个产品可以有很多用户。一个用户可以是多个组的成员，一个组可以有多个成员。一个组可以有很多产品，一个产品可以有很多组。换句话说，一个产品可以同时有组和用户。

我想问数据库的是:“列出给定 user 可以访问的所有产品，或者通过 user_product 表中的直接关系，或通过用户所属的组。我想要产品名称和用户名称。”

这是我提出的查询:

# First get all the products the user has access to via a group.
SELECT product.name,
       user.first_name
FROM product
       INNER JOIN group_product
               ON group_product.product_id = product.product_id
       INNER JOIN group
               ON group.group_id = group_product.group_id
       INNER JOIN group_member
               ON group_member.group_id = group.group_id
       INNER JOIN user
               ON user.user_id = group_member.user_id
WHERE user.user_id = 1

UNION 

# Now get all the products via direct access from user_product.
SELECT product.name,
       user.first_name
FROM product
       INNER JOIN user_product
               ON user_product.product_id = product.product_id
       INNER JOIN user
               ON user.user_id = user_product.user_id
WHERE user.user_id = 1

这是一个很好的查询，还是将其重写为仅 JOIN 查询更好/可能？如果有 100,000 个用户、10,000 个组和 100 个产品，这会是一个快速查询吗？这是一个好的数据库设计，还是以其他方式存储此逻辑更好？

(这是我的第一个更复杂的查询。)

Answer 1

您的查询具有适合您的数据模型的正确方法。您的数据模型的“正确性”实际上取决于更改的数量和频率——您可以选择在用户添加到组或从组中删除时始终存储明确的用户-产品关系。这是一种非规范化策略，将开销从查询转移到更新 - 通常最好不要考虑这些移动，除非性能经过测试并且有缺陷。

一个非常小的优化可能是避免在并集之前加入用户和产品。目前您只选择产品名称和用户名字，但如果您选择许多列，排序/区分将涉及比严格必要的更多工作，所以像:-

select product.name, user.first_name
from
(
select 
group_product.product_id
from  
group_product
inner join group on group.group_id = group_product.group_id
inner join group_member on group_member.group_id = group.group_id
where group_member.user_id = 1
union
select product_id product.name,
from user_product
where user_product.user_id = 1
) as d
inner join product on product.product_id = d.product_id
inner join user on user.user_id = 1