基于PostgreSQL，如何优化多关联用户权限查询（不改数据库设计）

1. 调整连接顺序，提前缩小数据集 #

原查询从permissions表开始关联，建议改成从users表起步，先过滤指定user_id，再依次关联后续表。这样能让数据库优先处理最小的数据集，减少后续关联的数据量：

SELECT p.permission_id, p.permission_name
FROM users u
JOIN user_roles ur ON u.user_id = ur.user_id
JOIN roles r ON ur.role_id = r.role_id
JOIN role_permissions rp ON r.role_id = rp.role_id
JOIN permissions p ON rp.permission_id = p.permission_id
WHERE u.user_id = 1

注：PostgreSQL优化器可能会自动调整连接顺序，但显式指定过滤前置的表，能让优化器更精准地选择执行计划。

2. 用半连接（EXISTS/IN）替代全连接，自动去重且提升性能 #

如果用户的多个角色存在重复权限，原查询会返回重复的权限行。使用EXISTS或IN的半连接方式，既能自动去重，又能避免全连接带来的冗余数据处理，性能更优：

方式一：EXISTS子查询

SELECT p.permission_id, p.permission_name
FROM permissions p
WHERE EXISTS (
    SELECT 1
    FROM role_permissions rp
    JOIN roles r ON rp.role_id = r.role_id
    JOIN user_roles ur ON r.role_id = ur.role_id
    WHERE ur.user_id = 1
      AND rp.permission_id = p.permission_id
)

方式二：嵌套IN子查询

SELECT p.permission_id, p.permission_name
FROM permissions p
WHERE p.permission_id IN (
    SELECT rp.permission_id
    FROM role_permissions rp
    WHERE rp.role_id IN (
        SELECT ur.role_id
        FROM user_roles ur
        WHERE ur.user_id = 1
    )
)

这两种方式的核心是只检查权限是否存在于用户的角色权限集合中，不需要生成全连接的中间结果，执行效率更高。

3. 添加复合索引，加速关联查询 #

在不修改表结构的前提下，添加以下复合索引可以大幅提升查询速度：

• CREATE INDEX idx_user_roles_user_role ON user_roles(user_id, role_id);：快速定位指定用户的所有角色
• CREATE INDEX idx_role_permissions_role_perm ON role_permissions(role_id, permission_id);：快速定位指定角色的所有权限
• 确保users(user_id)、roles(role_id)、permissions(permission_id)是主键（已有主键索引），如果不是，需单独添加主键或唯一索引。

4. 保留原连接逻辑时，用DISTINCT去重 #

如果必须沿用原全连接的写法，且存在重复权限，需添加DISTINCT关键字去重，但注意这会带来额外的排序开销，优先级低于前面的半连接方案：

SELECT DISTINCT p.permission_id, p.permission_name
FROM permissions p
JOIN role_permissions rp ON p.permission_id = rp.permission_id
JOIN roles r ON r.role_id = rp.role_id
JOIN user_roles ur ON r.role_id = ur.role_id
JOIN users u ON u.user_id = ur.user_id
WHERE u.user_id = 1

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