直接JOIN user和role会引发笛卡尔积，因二者无外键约束且非天然关联；必须通过user_role中间表显式建模多对多关系，并正确建立联合主键与索引。
为什么直接 JOIN user 和 role 会爆炸
因为 user 和 role 之间没有外键约束，也没有天然的 1:1 或 1:N 映射关系。你写 SELECT * FROM user, role 或 FROM user JOIN role ON 1=1，数据库就老老实实算笛卡尔积：假设有 1000 个用户、100 个角色，结果就是 10 万行——全是无效组合。
更危险的是，哪怕加了 WHERE u.id = r.id 这种明显错的条件，MySQL 也不会报错，只会返回空；但若条件漏写、或写成 ON u.id = ur.user_id 却忘了连 user_role 表，照样触发隐式笛卡尔积。
- 错误现象：
EXPLAIN显示rows列高达百万甚至十亿级，查询卡死、OOM、连接超时 - 典型误操作：用
LEFT JOIN role ON u.id = r.id（缺中间表），或把user_role当成普通业务表漏建索引 - 根本原因：关系语义缺失——数据库不理解“用户有多个角色”这件事，必须靠第三张表显式承载
中间表 user_role 必须怎么建
它不是可选配件，是多对多关系的基础设施。建错一个字段或少一个索引，后续所有 JOIN 都会慢或错。
-
user_id和role_id必须同时存在，且类型要和原表主键严格一致（比如都是BIGINT UNSIGNED） - 联合主键或唯一索引
(user_id, role_id)不可省——否则同一用户反复加同一角色不会报错，数据就脏了 - 单列索引
INDEX(user_id)或INDEX(role_id)基本没用；高频查询只走两种路径：WHERE user_id = ?或WHERE role_id = ?，所以必须建联合索引，顺序按查询倾向定（查用户角色多就(user_id, role_id)，查某角色下所有用户多就反过来） - 别在
user_role里加created_at、status等字段——那已不是中间表，而是带属性的关联实体，该叫user_role_assignment并单独设计生命周期
LEFT JOIN 时 WHERE 和 ON 的位置决定是否丢数据
想查“所有用户及其是否为 admin 角色”，结果里必须包含没角色的用户。这时候如果把过滤写在 WHERE，就全丢了：
SELECT u.name, r.name AS role_name FROM user u LEFT JOIN user_role ur ON u.id = ur.user_id LEFT JOIN role r ON ur.role_id = r.id AND r.name = 'admin'; -- ✅ 正确：条件放 ON
下面这句会筛掉所有没角色的用户，因为 r.name = 'admin' 在 WHERE 里会让 r.name 为 NULL 的行被排除：
SELECT u.name, r.name AS role_name FROM user u LEFT JOIN user_role ur ON u.id = ur.user_id LEFT JOIN role r ON ur.role_id = r.id WHERE r.name = 'admin'; -- ❌ 错：等价于 INNER JOIN，丢失空角色用户
- 原则：涉及右表（
role）的过滤，若想保留左表（user）的 NULL 行，必须写进对应ON - 性能影响：
ON r.name = 'admin'不影响role表索引使用；但WHERE UPPER(r.name) = 'ADMIN'会让索引失效 - 别依赖
DISTINCT拯救——它不解决笛卡尔积，只掩盖问题，且在大数据量下开销巨大
查“每个用户的所有角色名拼成字符串”该怎么写
这不是要多行结果，而是每用户一行、角色名用逗号拼起来。强行 GROUP BY u.id + LEFT JOIN 会出错，因为 user_role 和 role 是一对多，GROUP BY 前必须先聚合角色。
正确做法是分两层：先用子查询或 CTE 把用户和角色关系聚合成字符串，再和 user 表关联：
SELECT u.name, COALESCE(ur.role_list, '') AS roles FROM user u LEFT JOIN ( SELECT ur.user_id, GROUP_CONCAT(r.name ORDER BY r.name SEPARATOR ', ') AS role_list FROM user_role ur JOIN role r ON ur.role_id = r.id GROUP BY ur.user_id ) ur ON u.id = ur.user_id;
-
GROUP_CONCAT是 MySQL 特有，PostgreSQL 用STRING_AGG(r.name, ', ')，SQL Server 用STRING_AGG（2017+）或FOR XML - 别在最外层
GROUP BY u.id——那样会强制把所有用户字段都塞进GROUP BY，冗余且易错 - 如果还要支持分页，这个子查询必须提前物化或加覆盖索引，否则
GROUP_CONCAT+ 大表JOIN容易拖垮性能
中间表本身不复杂，但它的索引结构、JOIN 位置、聚合时机这三个点，任一出错都会让查询从秒级变成分钟级，或者结果错得离谱。尤其当业务方开始要求“查某角色下所有用户+用户最近登录时间”时，user_role 就不再是纯关联表，而成了需要单独维护生命周期的数据实体。
