MySQL JOIN 查询如何优化？ - AIGC面试刷题网

简化版

JOIN 优化的核心是让小结果集驱动大表，并让被驱动表的关联字段能走索引。还要避免无条件大表 JOIN、减少返回列、提前过滤、控制 JOIN 表数量，并通过 Explain 确认连接顺序、访问类型和扫描行数。

详细版

常见优化要点：

关联字段建立索引，尤其是被驱动表的连接列。
尽量先过滤，再 JOIN，减少参与连接的数据量。
小表或小结果集作为驱动侧。
避免在 JOIN 条件中对字段做函数或类型转换。
只返回必要列，减少网络传输和临时数据。
大表 JOIN 要警惕笛卡尔积和中间结果膨胀。
用 EXPLAIN 看连接顺序、type、key、rows。

示例：

select o.id, u.name
from orders o
join user u on o.user_id = u.id
where o.status = 'PAID';

可以考虑 orders(status, user_id) 和 user(id)，让订单先过滤，再按用户主键关联。

完整版教学

一、JOIN 慢通常慢在中间结果太大

JOIN 不是简单把两张表“拼一下”。数据库要根据连接条件，从一侧取记录，再去另一侧找匹配记录。如果驱动侧结果很大，或者被驱动表没有合适索引，就会产生大量扫描。

所以 JOIN 优化第一原则是：先让参与连接的数据变少，再让查找匹配记录更快。

用嵌套循环的直觉看，驱动表每出来一行，就要到被驱动表找匹配记录。假设驱动侧过滤后有 100 行，被驱动表关联列有索引，每次查 1 到几行，成本可控；如果驱动侧有 10 万行，而被驱动表没有索引，可能变成大量重复扫描。JOIN 慢常常不是两张表本身大，而是连接前没有把候选集压下来。

Nested Loop 直觉：
for row in 驱动侧结果:
    到被驱动表按 join key 查匹配行

二、被驱动表索引很关键

假设有：

select *
from orders o
join order_item i on o.id = i.order_id
where o.user_id = ?;

如果先通过 orders(user_id) 找到某个用户的订单，再拿每个订单 ID 去 order_item 查明细，那么 order_item(order_id) 就非常关键。没有这个索引，被驱动表可能被反复扫描。

可以用数字估算：某用户有 50 个订单，每个订单平均 3 个明细。如果 order_item(order_id) 存在，50 次索引查找大约返回 150 行；如果没有索引，每个订单都可能扫描明细表，明细表 1000 万行时成本不可接受。被驱动表索引的价值，是把“每次找匹配”从扫表变成快速定位。

JOIN 场景	关键索引
`orders.user_id -> user.id`	`user(id)` 通常主键已有
`orders.id -> order_item.order_id`	`order_item(order_id)`
`user.dept_id -> dept.id`	`dept(id)`，并让用户侧先过滤
多租户 JOIN	关联索引中常带 `tenant_id`

记忆钩子：JOIN 优化先问两个问题：驱动侧能不能先变小，被驱动表能不能按关联键快速找。

三、驱动表不是永远写在前面的表

MySQL 优化器会根据统计信息选择连接顺序，内连接不一定按 SQL 书写顺序执行。你写的第一张表，不一定就是驱动表。

真正要看执行计划。EXPLAIN 里表出现的顺序、访问类型和扫描行数，能帮助判断优化器选择是否合理。统计信息不准时，优化器可能选错，此时要先考虑更新统计信息、调整索引或改写 SQL。

内连接语义允许优化器重排连接顺序，目标是降低成本。比如 orders join user，如果 orders where status='PAID' 过滤后只剩 100 行，优化器可能先访问 orders；如果 user 上有强过滤条件只剩 10 个用户，也可能先访问 user。你要从 Explain 的表顺序和 rows 看实际驱动路径，而不是从 SQL 书写顺序猜。

四、LEFT JOIN 的条件位置要小心

LEFT JOIN 保留左表记录。右表过滤条件如果放在 where 里，可能把没有匹配右表的记录过滤掉，使语义接近内连接。

select *
from user u
left join orders o on u.id = o.user_id
where o.status = 'PAID';

如果希望保留没有订单的用户，条件应该放到 on 中：

left join orders o
  on u.id = o.user_id and o.status = 'PAID'

优化 JOIN 时不能只看性能，还要保证语义正确。

这类错误很隐蔽：开发本来想查“所有用户以及他们已支付订单”，结果把 o.status='PAID' 放到 where，没有订单的用户因为右表字段为 NULL 被过滤掉。优化时如果为了让 SQL 更快随意移动条件，可能把外连接语义改坏。性能优化的第一前提是结果集语义不变。

五、大 JOIN 的工程处理

当 JOIN 表很多、数据量很大时，可以考虑：

先用子查询或临时结果缩小主表范围；
对高频报表做汇总表；
冷热数据拆分；
避免在线大范围 JOIN 导致数据库抖动；
把复杂分析交给 OLAP 系统。

在线业务库更适合短小、可预测的查询，不适合无限制复杂关联分析。

比如一个报表要把订单、用户、商品、优惠券、支付流水、退款流水全部 JOIN，并按多个维度聚合。如果直接压到在线主库，可能产生巨大临时表和排序。更合理的做法可能是同步到数仓或 OLAP，引入汇总表，或者先按时间、租户、状态把主结果集缩小到可控范围，再关联维表补充信息。

六、Explain 中怎么观察 JOIN

JOIN 优化一定要看执行计划。关注表访问顺序、每张表的 type、key、rows，以及是否出现 Using temporary、Using filesort、Block Nested Loop 或 join buffer 相关信息。被驱动表如果是 ALL 且 rows 很大，通常就是高风险信号。

JOIN 检查路径：
表顺序 -> 驱动侧 rows 是否小
      -> 被驱动表 key 是否命中
      -> JOIN 后是否产生排序/临时表
      -> 返回列是否过多

七、常见误区与追问

误区：JOIN 慢就是因为表多，应该全部拆成多次查询。 表多只是风险之一，关键看中间结果、索引和访问顺序；盲目拆查询可能带来更多网络往返和一致性问题。
误区：SQL 里写在前面的表一定是驱动表。 内连接下优化器可以重排连接顺序，要以 Explain 为准。
误区：只给驱动表过滤字段建索引就够了。 被驱动表关联列没有索引时，每次匹配都可能变成大扫描。
误区：LEFT JOIN 的右表条件放哪里都一样。 放在 where 可能过滤掉 NULL 扩展行，使外连接结果变成近似内连接。
追问：小表驱动大表一定正确吗？ 更准确是“小结果集驱动大表”，大表经过强过滤后也可能成为更合适的驱动侧。
追问：JOIN 表很多时怎么处理？ 先过滤主结果集、建立关联索引、减少返回列；复杂报表考虑汇总表、离线计算或 OLAP。

八、加强记忆

JOIN 优化抓两条主线：驱动侧结果集要小，被驱动表关联键要能快速定位。围绕这两条，再做提前过滤、减少返回列、避免函数和隐式转换、控制 JOIN 数量，并用 Explain 验证连接顺序、索引和扫描行数。别为了性能改坏语义，尤其是 LEFT JOIN 右表条件的位置；复杂分析型 JOIN 不适合硬压在线库。

简化版

详细版