第 12 章：JOIN 原理与优化

zjc 于 2026-01-12 发布

这是《MySQL 零基础实战指南》的独立章节版。本章从概念、实操和生产排查三个视角展开，代码块保留了原书可直接运行的版本。 JOIN 的性能取决于三件事：驱动表扫描多少行、被驱动表如何查找匹配行、结果是否需要额外排序或临时表。本章把 JOIN 的执行方式和优化路径讲清楚。

12.1 JOIN 的基本执行模型

简单嵌套循环：

for row in 驱动表:
    for row in 被驱动表:
        if join_key 匹配:
            输出结果

如果被驱动表 Join Key 有索引，内层循环可以变成索引查找：

for row in 驱动表:
    通过索引直接查被驱动表匹配行

这是 JOIN 优化的核心：让被驱动表走 eq_ref 或 ref。

示例：

EXPLAIN
SELECT o.order_no, u.username
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.user_id = 1001;

理想计划：

orders 通过 idx_user_created 找到少量订单；
users 通过主键逐行查找，类型为 eq_ref。

12.2 驱动表选择

优化器通常倾向选择过滤后行数更小的表作为驱动表：

SELECT COUNT(*)
FROM orders
WHERE user_id = 1001;

SELECT COUNT(*)
FROM users
WHERE status = 1;

影响选择的因素：

WHERE 条件选择性；
索引可用性；
统计信息；
JOIN 缓冲区大小；
ORDER BY 是否能复用某个表顺序；
派生表和子查询成本；
数据分布倾斜。

不要固定认为小表一定是驱动表。过滤后更小、且能让另一边走索引的表才是更好的驱动表。

12.3 Block Nested-Loop

当被驱动表没有索引时，朴素嵌套循环代价很高。Block Nested-Loop 会把驱动表分批放入 Join Buffer：

读取一批驱动表行到 join_buffer；
扫描一次被驱动表；
对缓冲中的行做批量匹配；
清空缓冲，处理下一批。

查看缓冲大小：

SHOW VARIABLES LIKE 'join_buffer_size';

如果执行计划出现：

Using join buffer (Block Nested Loop)

说明关联缺少合适索引。优先补关联键索引，而不是盲目调大 join_buffer_size。

12.4 Hash Join

MySQL 8.0.18 起 InnoDB 支持 Hash Join，用于无可用索引的等值 JOIN：

Build 阶段：
  把较小的输入构建内存哈希表

Probe 阶段：
  扫描另一侧输入，探测哈希表并输出匹配结果

示例：

EXPLAIN FORMAT=TREE
SELECT COUNT(*)
FROM orders o
JOIN report_channels c
  ON o.channel_code = c.channel_code;

Hash Join 适合：

等值 JOIN；
关联列无索引；
分析型扫描；
大结果集构建代价低于反复查找；
内存能承载 Build 侧或可落盘分批处理。

不适合或需谨慎：

非等值 JOIN；
极高并发在线核心链路；
内存压力大；
有更好索引查找路径；
小表主键查找场景。

在线交易 JOIN 仍应优先设计好关联键索引。

12.5 JOIN 算法对比

算法	条件	特点
Index Nested-Loop	被驱动表有索引	在线业务首选
Block Nested-Loop	无索引时减少被驱动表扫描次数	仍需大扫描
Hash Join	等值条件，8.0.18+	分析查询常见
Straight Join	强制左表驱动	SQL 语义仍为 JOIN，但固定顺序

12.6 关联索引设计

订单表：

KEY idx_user_created (user_id, created_at)

用户表：

PRIMARY KEY (id)

订单明细表：

KEY idx_order (order_id)

查询：

SELECT o.order_no, oi.product_name
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.user_id = 1001;

好计划：

先按用户索引扫描订单；
再按明细表的 order_id 索引查找；
避免明细表全扫描。

关联字段要求：

类型一致；
字符集一致；
排序规则一致；
两边语义一致；
被驱动表索引包含 Join Key；
尽量有 NOT NULL 约束。

12.7 LEFT JOIN 优化

保留左表：

SELECT u.username, o.order_no
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
 AND o.status = 'PAID'
WHERE u.status = 1;

优化点：

左表过滤放 WHERE；
右表保留条件放 ON；
右表 Join Key 建索引；
需要 COUNT(o.id) 而不是 COUNT(*)；
小心一对多导致的汇总翻倍。

示例：

SELECT u.username, COUNT(o.id) AS paid_count
FROM users u
LEFT JOIN orders o
  ON o.user_id = u.id
 AND o.status = 'PAID'
GROUP BY u.username;

12.8 派生表 JOIN

先聚合再 JOIN：

WITH order_stats AS (
  SELECT
    user_id,
    COUNT(*) AS order_count,
    SUM(total_amount) AS amount_sum
  FROM orders
  WHERE status = 'PAID'
  GROUP BY user_id
)
SELECT u.username, s.order_count, s.amount_sum
FROM users u
JOIN order_stats s ON s.user_id = u.id;

注意：

派生表可能物化为临时表；
WHERE 能下推时优化器会改写；
大派生表 JOIN 小表可能慢；
先过滤再聚合；
外层条件不一定都能推进 CTE。

MySQL 8.0.22+ 支持 LATERAL 派生表，可按外行执行：

SELECT
  u.username,
  latest.order_no
FROM users u
JOIN LATERAL (
  SELECT o.order_no
  FROM orders o
  WHERE o.user_id = u.id
  ORDER BY o.created_at DESC
  LIMIT 1
) AS latest ON TRUE
WHERE u.status = 1;

适合每个外行取少量 Top N 的场景，前提是 orders(user_id, created_at) 有索引。

12.9 Semijoin 与 Antijoin

存在匹配即返回：

SELECT u.id, u.username
FROM users u
WHERE EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

MySQL 优化器可能将 EXISTS 改写为 Semijoin，不必扫描用户全部匹配订单，找到一条即可。

不存在匹配：

SELECT u.id, u.username
FROM users u
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.user_id = u.id
);

MySQL 8.0.17+ 对部分 Antijoin 场景有更好优化。

12.10 JOIN 查询治理

常见坏味道：

一个 SQL JOIN 十几张表；
无索引关联字段；
关联字段类型不一致；
JOIN 后再按大结果过滤；
报表 SQL 混入交易接口；
LEFT JOIN 右表条件放错位置；
一对多导致重复计数；
深分页叠加多表 JOIN。

治理策略：

问题	策略
表太多	拆接口、拆 SQL、异步组装
无索引	补关联索引
报表复杂	只读库、预聚合、OLAP
数据重复	先聚合再 JOIN
字段不一致	统一类型和字符集
大结果传输	减少字段、分页、游标
代码难维护	明确领域边界和数据归属

12.11 JOIN 优化案例

案例一：被驱动表全扫描

原 SQL：

SELECT o.order_no, m.merchant_name
FROM orders o
JOIN merchants m ON m.merchant_name = o.merchant_name
WHERE o.user_id = 1001;

问题：

用名称关联；
名称可能修改，历史订单语义不稳定。

优化：

SELECT o.order_no, m.merchant_name
FROM orders o
JOIN merchants m ON m.id = o.merchant_id
WHERE o.user_id = 1001;

案例二：一对多汇总翻倍

错误：

SELECT
  SUM(o.total_amount) AS amount
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status = 'PAID';

正确：

SELECT SUM(o.total_amount) AS amount
FROM orders o
WHERE o.status = 'PAID';

或分别聚合后在订单粒度合并。

案例三：报表大 JOIN

原 SQL 在主库 JOIN 订单、用户、商户、商品、物流和售后表。优化方向：

移到只读实例；
使用预聚合宽表；
按 T+1 或分钟级更新；
大历史扫描进入 ClickHouse；
在线接口只查最近数据和固定维度。

本章小结

JOIN 优化的第一优先级是让被驱动表通过索引查找匹配行。选择驱动表时看过滤后的行数，而不是物理表大小。无索引等值 JOIN 可以利用 Hash Join，但在线交易仍应依赖良好索引。LEFT JOIN 条件位置、一对多重复统计、关联字段一致性和报表隔离，是生产 JOIN 问题的高频来源。

思考题

Index Nested-Loop 为什么优于朴素嵌套循环？
Join Buffer 和 Hash Join 分别解决什么问题？
如何判断哪张表适合做驱动表？
LEFT JOIN 的右表条件为什么有时要放 ON？
优化一个四表 JOIN SQL，并记录执行计划变化。