人人都会AI编程

13.3 关联查询优化：驱动表选择、ON 与 WHERE 区别

更新时间：2026-07-10

多表关联查询是业务开发中最常用的操作之一，也是性能问题的高发区。一条关联 SQL 写得不好，可能从毫秒级退化为分钟级。优化的关键往往不在语法层面，而在理解数据库到底是怎么执行这条关联的：先查哪张表，后查哪张表，条件放在哪里过滤。

13.3.1 驱动表的选择：谁先谁后，差别巨大

关联查询的底层执行算法主要有两种：嵌套循环连接（Nested-Loop Join） 和 块嵌套循环连接（Block Nested-Loop Join，MySQL 8.0.18 后逐渐被 Hash Join 取代）。无论哪种，都会先取一张表作为外层循环（驱动表），然后到另一张表（被驱动表）里去找匹配行。

驱动表的每一行，都要到被驱动表里去查找一次。这意味着：

  • 驱动表的行数如果很大，那么被驱动表会被扫描很多次。
  • 被驱动表的关联列上如果有索引，每次查找就会走索引，性能尚可；如果没有索引，每次都全表扫描，代价极高。

因此，关联查询优化的第一条原则就是：用小表驱动大表，被驱动表的关联列必须建索引。

选择驱动表时，优化器会根据统计信息估算成本。对内连接（INNER JOIN），优化器可以自由选择驱动表，它会估算哪个表做驱动表成本更低，即使 SQL 里你先写 A JOIN B，实际也可能选 B 做驱动表。对外连接（LEFT JOIN 或 RIGHT JOIN），驱动表是强制的：LEFT JOIN 的左边是驱动表，RIGHT JOIN 的右边是驱动表。这意味着外连接的驱动表选择权在你手里，你必须有意识地把小表放在驱动位。

用一个经典例子：

-- 内连接：MySQL 可能会选 orders 或 users 任意一个做驱动表
SELECT *
FROM orders o
INNER JOIN users u ON o.user_id = u.id;

-- 左连接：强制 users 为驱动表，orders 为被驱动表
SELECT *
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

假设 users 只有 1000 行，orders 有 100 万行。如果用 users 驱动 orders，外层循环 1000 次，内层通过 orders.user_id 索引每次查几十条订单，总代价很小。如果反过来用 orders 驱动 users，外层 100 万次循环，即便内层 users.id 是主键索引，上百万次的 B+ 树查找也远比前一种方案慢得多。

因此，优化关联查询时，你可以通过 EXPLAIN 查看执行计划，输出的第一行就是优化器最终选定的驱动表（id 相同的情况下，从上到下的顺序）。如果发现驱动表行数远大于被驱动表，那就需要检查一下哪里出了问题：是不是遗漏了索引，或者因为外连接写反了方向。

13.3.2 ON 与 WHERE 的核心区别：执行时机与过滤语义

很多人觉得 ON 是关联条件，WHERE 是过滤条件，这在内连接里没错，但在外连接里是完全不同的语义。

对于内连接，ON 和 WHERE 的结果等价。因为内连接只返回两表匹配成功的行，ON 里写过滤条件实际上等同于 WHERE 条件。优化器甚至会在执行前把 ON 里的条件提到 WHERE 条件里一并评估，所以你无需纠结内连接里条件放哪，保持一致即可。

但对于外连接（LEFT JOIN / RIGHT JOIN），ON 和 WHERE 的差别就非常关键了，原因在于外连接会保留驱动表的所有行。如果被驱动表找不到匹配行，会用 NULL 填充。这时：

  • ON 条件是关联匹配条件，决定哪些行能关联上，哪些行关联不上而填充 NULL。它不会把驱动表的任何行过滤掉。
  • WHERE 条件是在关联产生中间结果之后进行的全局过滤，会把不满足条件的整行（包括被 NULL 填充的行）从结果集中剔除。

用一个例子就能看明白：

-- users 表：id=1, name='张三'
-- orders 表：user_id=1, amount=100 (已支付)
-- orders 表：user_id=1, amount=200 (未支付)

-- 查询所有用户，附带已支付的订单
-- 写法一：条件放 ON，会保留所有用户，未支付订单的列填充 NULL
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id AND o.status = 'paid';

-- 结果：张三, 100
--       张三, NULL   （未支付的订单变成NULL，但用户行仍保留）

-- 写法二：条件放 WHERE，会把没有已支付订单的行全部过滤掉
SELECT u.name, o.amount
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid';

-- 结果：张三, 100   （如果张三还有其他未支付订单，但这里只要已支付的）
-- 实际上如果某个用户完全没有已支付订单，则整个用户都不会出现，LEFT JOIN 退化为内连接效果

这就是经典的“条件放 ON 还是 WHERE”差异：外连接中，ON 条件不会过滤驱动表行，WHERE 条件会。 如果你把本应放在 ON 里的条件放到了 WHERE，可能会把一些驱动表的行错误地过滤掉，导致 LEFT JOIN 变成 INNER JOIN 的效果。

对于右连接同理，只是方向相反。实际业务中，多数关联都可以用内连接搞定；如果必须用外连接，写完后一定要问自己：“这个条件是用来定义关联匹配的，还是用来过滤最终结果的？”前者放 ON，后者放 WHERE。

另外从性能角度，还有一个常被忽略的细节：在外连接中，如果你把本该放 ON 的条件错放到了 WHERE，可能会导致引擎无法使用被驱动表的索引。例如上面 WHERE o.status = 'paid' 会让 o.user_id 上的索引失效，演化成全表扫描后再过滤，这在大表上是致命的。而 AND o.status = 'paid' 在 ON 里则可以结合索引先过滤出符合条件的订单再关联，性能更好。

13.3.3 关联查询的通用优化思路

理清了驱动表和条件位置，再结合几个要点，关联查询的性能基本就能控制住：

  1. 被驱动表关联列必须有索引：这是最低要求，也是最高收益的优化。你可以通过 EXPLAIN 看第二行的 key 是否被正确使用。
  2. 尽可能用小结果集驱动大结果集：对 LEFT JOIN 是强制性的，对内连接可以加 STRAIGHT_JOIN 提示强制驱动表顺序（但不建议轻易使用，通常优化器比人估算得更准）。
  3. 避免在关联列上使用函数或运算：ON a.id = b.id + 1 会导致索引失效。
  4. 关联字段类型不一致会导致隐式转换，同样索引失效：int 和 varchar 比较时，MySQL 会把 varchar 转为 double，索引就废了。
  5. 尽量减少不必要的关联，特别是大表之间的关联：有时业务层多查一次再用代码拼接，反而比数据库算更省力，尤其是在分布式架构中。
  6. 当驱动表数据量较大时，MySQL 8.0 的 Hash Join 比 Block Nested Loop 高效得多，因为它会为被驱动表在内存建哈希表，避免大量随机 I/O。如果你还在 5.7，可以考虑在业务层分解查询。

关联查询优化不是炫技，而是实实在在地把原理用在每一行 SQL 里。你不需要记住所有特殊情况，只要每次写多表查询时，多看一眼 EXPLAIN，关注驱动表的 rows 和 Extra，就几乎不会写出系统崩掉的关联 SQL。