SQL实现账号全量关联权限（含组权限）查询的性能优化问题

关联表命名建议 #

针对权限组绑定权限的关联表，采用group_permission命名是更合理的选择：

• 现有命名permission_group易产生语义歧义，容易和“拥有某类权限的业务分组”概念混淆
• 通用多对多关联表的命名规则为「关联双方实体名_关联实体名」，对应“权限组-权限”的映射关系，group_permission可以直接体现两个表的关联逻辑，降低后续开发、维护的理解成本。

原查询实现的现存问题 #

你当前编写的可运行SQL存在两个明确的缺陷：

1. 性能损耗：用OR跨表拼接查询条件时，数据库优化器无法有效命中已有索引，且从权限主表出发分别关联两个独立的关联表时，会生成大量无意义的中间结果集，数据量越大查询耗时越高
2. 结果重复：如果某条权限同时被账号直接绑定、且通过所属权限组关联到账号，原查询会返回重复的权限记录，需要额外叠加去重逻辑才能得到正确结果。

优化后的查询实现 #

可以把两类授权路径拆为独立的、可命中索引的子查询，通过集合合并的方式拿到全量权限，执行效率远优于原OR写法：

-- 替换为需要查询的账号ID
WITH query_param AS (
  SELECT 'a518f2fb-7e46-4c0c-9428-c87eac6674e8' AS account_id
)
-- 取账号直接绑定的权限
SELECT p.*
FROM permission p
INNER JOIN account_permission ap
  ON p.id = ap.permission_id
CROSS JOIN query_param qp
WHERE ap.account_id = qp.account_id

UNION

-- 取账号所属权限组绑定的权限（对应优化命名后的group_permission表）
SELECT p.*
FROM permission p
INNER JOIN group_permission gp
  ON p.id = gp.permission_id
INNER JOIN account_group ag
  ON gp.group_id = ag.group_id
CROSS JOIN query_param qp
WHERE ag.account_id = qp.account_id;

优化方案的优势 #

• 每个子查询都可以通过关联表上account_id、group_id字段的联合索引快速定位关联数据，无需对权限主表做全量扫描
• UNION会自动对返回的权限ID做去重，从根源上解决重复权限记录的问题
• 若业务上不存在“直接授权+组授权重复绑定同一权限”的场景，可将UNION替换为UNION ALL，进一步减少去重环节的性能开销。

内容的提问来源于stack exchange，提问作者Altaula