上面这两张图分别展示了两条 SQL 语句的 EXPLAIN 执行计划，我们来对比它们的执行效率与执行方式。
1️⃣ 第一条 SQL：JOIN 查询
SELECT *
FROM t_user u, user_role ur
WHERE u.id = ur.user_id;执行计划分析：
table | type | key | Extra |
ur | ALL | NULL | Using where |
u | eq_ref | PRIMARY | NULL |
关键点说明：
-
user_role表（ur）先被扫描，类型是ALL，说明是全表扫描。 - 然后用
u.id = ur.user_id去 t_user 表中匹配，type=eq_ref使用了主键（PRIMARY）。 - eq_ref 是最高效的连接类型之一（代表每次从 ur 表取一行，就对应 t_user 的一行）。
- 但全表扫描 user_role 可能成为瓶颈 —— 如果 user_role 表很大，成本高。
✅ 优点：
- JOIN 通常可以在 MySQL 优化器中被优化为嵌套循环，配合索引性能较好。
- 执行计划简单，数据量小的情况下效率高。
❌ 缺点：
-
ur为全表扫描（type=ALL），如果 user_role 数据量大，会拖慢执行。 - 性能依赖于 user_role 的索引情况。
2️⃣ 第二条 SQL：子查询（IN 子句）
SELECT *
FROM t_user
WHERE id IN (SELECT user_id FROM user_role);执行计划分析：
table | type | key | Extra |
user_role | index | fk_ur_user_id | Using where; Using index; LooseScan |
t_user | eq_ref | PRIMARY | NULL |
关键点说明：
- MySQL 5.6+ 会将子查询自动优化为 半连接(Semi-Join) 或 LooseScan。
-
user_role表使用了fk_ur_user_id索引，扫描类型是index，避免全表扫描。 - 子查询执行过程中，MySQL 进行了索引遍历扫描（Using index）。
- 由于
LooseScan，MySQL 能做到更高效的去重和匹配，大幅减少扫描次数。
✅ 优点：
- 使用了索引（
fk_ur_user_id），无需全表扫描。 - MySQL 会自动将 IN 子查询优化为半连接，提高性能。
- 在数据量较大而 user_role 已建索引的情况下更快。
❌ 缺点：
- 在老版本 MySQL（5.5 及以下）或未优化子查询的情况下，可能退化为嵌套子查询（性能差）。
📊 性能对比总结
对比项 | JOIN（第1条） | IN 子查询（第2条） |
表访问方式 | ur 全表扫描（ALL） | ur 使用索引扫描（index） |
连接方式 | eq_ref（高效） | 半连接（LooseScan） |
是否使用索引 | 对 user_role 无索引利用 | 对 user_role 有效利用索引 |
适合数据量 | 中小表 | 大表、有索引 |
优化器优化潜力 | 较低 | 高（可以半连接优化） |
综合效率 | 一般 | 更优（利用索引） |
✅ 结论
- 在当前 EXPLAIN 下，第二条 SQL（IN 子查询）执行效率更高，因为：
- 使用了
user_role的索引（fk_ur_user_id）； - MySQL 优化成了
LooseScan半连接，而不是重复子查询； - 避免了全表扫描。
- JOIN 版本在无索引或表数据量较小时性能差别不大，但在大数据量时性能容易受限。
🚀 优化建议
- 若采用 JOIN 方式，请确保为
user_role.user_id建立索引，减少ALL扫描； - 若采用 IN 子查询方式，保持索引并保证 MySQL 版本 ≥ 5.6，使其启用半连接优化；
- 对于大量数据操作，还可考虑使用
EXISTS代替IN，效果接近但在部分场景更高效。
