如何优化关联用户权限表生成布尔权限列的SQL查询性能

你的原始查询性能差、可维护性低核心是三个问题：

• 存在语法错误：canDelete列定义后多了一个多余的逗号，会直接导致查询报错
• 每新增一个权限列就新增了一次关联子查询，相当于每多一个权限就要全量扫描一次permissions表做匹配，性能随权限数量增加线性下降
• 冗余了LEFT JOIN permissions和GROUP BY逻辑，和子查询的匹配逻辑重复，额外增加了运算开销

用条件聚合的方式实现，只需要关联一次permissions表，不管加多少个权限列都不会重复扫表，写法也更简洁：

SELECT 
  u.userId,
  u.username,
  u.email,
  u.name,
  MAX(CASE WHEN p.permission = 'canRead' THEN 1 ELSE 0 END) AS canRead,
  MAX(CASE WHEN p.permission = 'canWrite' THEN 1 ELSE 0 END) AS canWrite,
  MAX(CASE WHEN p.permission = 'canDelete' THEN 1 ELSE 0 END) AS canDelete
FROM users u
LEFT JOIN permissions p ON u.userId = p.userId
GROUP BY u.userId, u.username, u.email, u.name

注：GROUP BY 包含所有非聚合列是为了兼容开启ONLY_FULL_GROUP_BY的严格SQL模式，避免报错。

• 只做1次users和permissions的左关联，保证所有用户都会被保留，哪怕没有任何权限
• 用CASE WHEN匹配对应权限返回1/0，再通过MAX聚合：只要用户存在对应权限就会返回1，没有就返回0，完全符合你需要的布尔列输出要求
• 新增权限只需要加一行MAX(CASE WHEN ...)即可，性能不会随权限数量增加出现明显下降

给permissions表建立(userId, permission)的复合覆盖索引，查询时可以直接走索引匹配，不需要回表查询数据，性能还能再提升30%以上：

-- 建索引语句
CREATE INDEX idx_user_permission ON permissions(userId, permission);

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