mysql查询性能中的表连接优化技巧

LEFT JOIN 比 INNER JOIN 更慢且常扫全表，因其无法利用右表 WHERE 条件提前过滤，且若 ON 中未用索引字段则触发嵌套循环全表扫描；应将右表过滤条件移入 ON 子句并确保连接字段有联合索引。

为什么 LEFT JOIN 比 INNER JOIN 更慢，而且经常扫全表？

因为 LEFT JOIN 无法像 INNER JOIN 那样利用右表的条件提前过滤——即使你在 WHERE 子句里写了右表字段的非空限制，优化器也可能不把它当作驱动条件。更糟的是，如果右表没在 ON 子句中用上索引字段，mysql 就只能对右表做嵌套循环扫描（type: ALL）。

实操建议：

• 把右表的过滤条件尽量写进 ON 子句，而不是 WHERE；比如 LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid'，而非 LEFT JOIN orders o ON u.id = o.user_id WHERE o.status = 'paid'
• 确认右表连接字段（如 o.user_id）上有索引；没有就加：

  ALTER TABLE orders ADD INDEX idx_user_id_status (user_id, status);

• 用 EXPLAIN format=TREE 查看是否出现 dependent nested loop，这是性能杀手

JOIN 多张表时，谁该当驱动表？

MySQL 默认按 FROM 后的顺序决定驱动表（5.7 及以前），但 8.0+ 会基于统计信息自动重排。不过它并不总是对的——尤其当某张表有强过滤条件（比如 created_at > '2024-01-01'）却排在后面时，容易引发全表扫描。

实操建议：

• 用 STRAIGHT_JOIN 强制连接顺序，把过滤后行数最少的表放在最左：比如 select ... FROM users u STRAIGHT_JOIN orders o ON u.id = o.user_id ...
• 查 EXPLAIN 的 rows 列，选预估扫描行数最小的表作第一张表
• 避免在驱动表上用 LIKE '%xxx' 或函数（如 YEAR(created_at)），这会让索引失效，导致驱动表变“胖”

临时表和排序导致 using temporary / Using filesort 怎么办？

当 JOIN 后需要 GROUP BY、ORDER BY 或 DISTINCT，且涉及多表字段时，MySQL 常常被迫建内部临时表，甚至落盘（Using filesort）。这不是语法错，而是执行计划没走覆盖索引或合并扫描路径。

实操建议：

• 确保 ORDER BY 字段全部来自驱动表，或至少是连接后能走索引的组合；例如驱动表是 users，就别按 orders.created_at 排序
• 给常用排序字段加联合索引，比如 (user_id, created_at)，让 JOIN + ORDER BY 能直接用索引完成
• 用 SELECT ... INTO OUTFILE 或分页改用游标（WHERE id > ? ORDER BY id LIMIT 100）绕过大结果集排序

什么时候该放弃 JOIN，改用应用层拼接？

不是所有逻辑都适合 SQL JOIN。比如要查 100 个用户的最新订单，用 LEFT JOIN ... LIMIT 1 很难写出高效语句（容易触发相关子查询或派生表），而 MySQL 不支持 LATERAL（直到 8.0.14 才部分支持）。

实操建议：

• 单次查询返回主表 N 行，关联数据量小（如每个用户最多 3 条订单），优先用两次查询：

  SELECT id, name FROM users WHERE id IN (1,2,3,...);
  SELECT * FROM orders WHERE user_id IN (1,2,3,...) ORDER BY created_at DESC;

  再由代码按 user_id 归并

• 注意 IN 列表长度别超 max_allowed_packet，一般控制在 1000 以内
• 如果业务允许弱一致性，可考虑用缓存（如 redis hash）存好用户+最近订单，彻底避开 JOIN

真正卡住性能的，往往不是 JOIN 本身，而是连接字段没索引、过滤条件放错位置、或者误以为优化器总能选对顺序。动手前先 EXPLAIN，动手后看 Handlerread* 状态变量，比背技巧管用。