25 KiB
tags, create time
| tags | create time | ||||
|---|---|---|---|---|---|
|
2026-05-16 00:00 |
JOIN 原理与优化
概述
JOIN 是关系型数据库的核心能力，也是性能问题的主要来源。理解 MySQL 的 JOIN 执行算法才能写出高效的关联查询。
什么是 JOIN
在关系型数据库中，数据通常分散在多张表中——用户信息一张表，订单记录另一张表。JOIN 就是把这些分散的表按某种规则"拼"在一起，形成一张完整的视图。
[!QUESTION] 💡 思考一下 想象你在 Excel 里有两张表：左边是学生名单（含学号），右边是成绩表（也有学号）。你想看到"每个学生对应的成绩"——你会怎么操作？ 答：你会通过"学号"这一列，把两行的数据对应起来。SQL 中的 JOIN 就是这个操作的自动化版本。
一个具体的例子
假设有两张表：
| users 表 | orders 表 | ||
|---|---|---|---|
| id | name | order_id | user_id |
| 1 | Alice | 101 | 1 |
| 2 | Bob | 103 | 2 |
| 102 | 3 |
我们想查 "每个订单对应用户的名字" ——单张表做不到，必须同时看 users 和 orders：
SELECT u.name, o.order_id, o.amount
FROM users u JOIN orders o ON u.id = o.user_id;
结果：
| name | order_id | amount |
|---|---|---|
| Alice | 101 | ¥200 |
| Bob | 103 | ¥80 |
⚠️ 注意：order_id=102（user_id=3）没有出现在结果中，因为 users 表里没有 id=3 的记录。这就是 JOIN 的匹配逻辑——只有两边都能对上的行才会被选中。
flowchart LR
subgraph users 表
A1["id=1 / Alice"]
A2["id=2 / Bob"]
A3["id=3（不存在）"]
end
subgraph orders 表
B1["order_id=101<br/>user_id=1 ¥200"]
B2["order_id=102<br/>user_id=3 ¥150"]
B3["order_id=103<br/>user_id=2 ¥80"]
end
A1 -->|"ON id = user_id"| B1
A2 -->|"ON id = user_id"| B3
A3 -.->|"users 表中无 id=3"| B2
style A3 fill:#EE5A24,color:#fff
style B2 fill:#EE5A24,color:#fff
[!NOTE] 为什么要拆成多张表？ 你可能会问：为什么不把所有数据存在一张大表里？这涉及数据库设计的核心原则——避免冗余。
- 如果订单表直接存用户名，用户改名时要更新几万条订单记录
- 分表后只改 users 表一条记录，订单表通过 user_id 引用即可
- 这就是「范式」（Normal Form）的思想——详见 hhs/MySQL/05-表设计/21-表结构设计三范式
一句话理解 JOIN 的本质
JOIN 就是"按条件逐行匹配两张表的数据"。有索引时能跳过大部分不匹配的行（快），没索引时只能一行一行比对（慢）——后续所有优化都是围绕这一点展开的。
驱动表与被驱动表
[!TIP] 为什么先讲这个？ 在深入 JOIN 的执行算法之前，必须先理解驱动表和被驱动表的概念——它们是所有 JOIN 优化决策的基础。
当一个 JOIN 语句被执行时，MySQL 会将两张表区分角色：
| 角色 | 职责 | 通俗理解 |
|---|---|---|
| 驱动表（Driving Table） | 先读取数据，提供"查找的关键词" | 手持"点名册"的人 |
| 被驱动表（Driven Table） | 根据驱动表提供的每行数据去匹配 | 拿着点名册逐一核对的人 |
上面的例子中：
-- users 是驱动表，orders 是被驱动表
SELECT u.name, o.order_id
FROM users u -- 👈 驱动表：先读它
JOIN orders o ON u.id = o.user_id; -- 👆 被驱动表：每次拿 u.id 来匹配
谁当驱动表重要吗？
非常重要。 核心原则：用小表驱动大表——驱动表行数越少，被驱动表被扫描的次数就越少。
flowchart TD
S["小表：100 行"] -->|"驱动"| L["大表：100 万行<br/>匹配 100 次 ✅"]
L2["大表：100 万行"] -->|"驱动"| S2["小表：100 行<br/>匹配 100 万次 ❌"]
style L fill:#00D866,color:#fff
style S2 fill:#EE5A24,color:#fff
MySQL 的 Optimizer（优化器）会尝试自动选择最优顺序——它会统计每张表的行数，评估成本后决定谁做驱动表。但当统计信息不准确或数据分布特殊时，优化器可能选错，这时就需要手动干预。
[!TIP] 黄金法则 驱动表可以全表扫描，但被驱动表必须走索引。
任何 JOIN 优化的目标都是确保被驱动表的访问方式足够高效。我们会在后面的「驱动表选择」深入学习如何判断和优化。
JOIN 类型速览
-- Inner JOIN：只返回两边都匹配的行（最常用）
SELECT * FROM orders o INNER JOIN users u ON o.user_id = u.id;
-- LEFT OUTER JOIN：左表全保留，右表不匹配则为 NULL
SELECT o.id, u.username
FROM orders o LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL; -- 找孤儿订单（用户已被删除）
-- RIGHT OUTER JOIN：等价于交换左右表的 LEFT JOIN
-- 实践中几乎不用 RIGHT JOIN，改成 LEFT JOIN 更易读
-- CROSS JOIN：笛卡尔积（慎用！）
SELECT a.name, b.name FROM table_a CROSS JOIN table_b;
-- 结果 = |a| × |b| 行
SELF JOIN（自连接）
自连接是同一张表与自身进行 JOIN——表没有变，只是用两个不同的别名把它"当成两张表"来用。这是处理层次结构和行间比较的经典手法。
场景一：组织架构树（上下级关系）
-- 找出每个员工及其直属上级的名字
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;
-- 结果：
-- | employee | manager |
-- | Alice | NULL | ← CEO，无上级
-- | Bob | Alice |
-- | Carol | Alice |
-- | David | Bob |
[!NOTE] SELF JOIN vs 递归 CTE 的选择
- 只查一层关系（直接上级/下级）：SELF JOIN 简洁高效
- 需要遍历整棵树（所有层级的汇报线）：必须用递归 CTE——详见 hhs/MySQL/02-SQL核心/10-子查询与派生表
场景二：查找同一组内的相邻行
-- 找出连续两天都有订单的用户（日环比分析）
SELECT DISTINCT a.user_id
FROM orders a
INNER JOIN orders b
ON a.user_id = b.user_id
AND DATEDIFF(b.created_at, a.created_at) = 1
WHERE DATE(a.created_at) = '2026-05-20';
场景三：去重——保留每组最新的一条
-- 删除同一用户的历史记录，只保留最新一条
DELETE old FROM orders old
INNER JOIN orders new
ON old.user_id = new.user_id
AND old.created_at < new.created_at;
-- old 是"要删的旧记录"，new 是"用来比较的新记录"
flowchart LR
subgraph "orders 表（别名 old）"
O1["user=1, 05-18"]
O2["user=1, 05-19"]
O3["user=1, 05-20"]
end
subgraph "orders 表（别名 new）"
N1["user=1, 05-18"]
N2["user=1, 05-19"]
N3["user=1, 05-20"]
end
O1 -->|"old.created_at < new.created_at"| N2
O1 --> N3
O2 --> N3
O3 -.->|"无更早匹配"| N3
style O1 fill:#EE5A24,color:#fff
style O2 fill:#EE5A24,color:#fff
style O3 fill:#00D866,color:#fff
[!TIP] 自连接的性能注意事项
- 自连接本质是同一张表扫描两次，如果表很大且没有索引，代价等同于两张大表的 BNLJ
- 务必确保 JOIN 条件列有索引（如
manager_id、user_id + created_at）- 如果只是做"每组取最新一条"，
ROW_NUMBER()窗口函数通常比自连接 DELETE 更安全（详见 hhs/MySQL/02-SQL核心/08-DQL SELECT 全解析）
JOIN 执行算法
MySQL InnoDB 在执行 JOIN 时，本质上只有两种核心策略：被驱动表有索引时用 INLJ（Index Nested-Loop Join），没有索引时退化到 BNLJ（Block Nested-Loop Join）。而 INLJ 内部又根据索引是否为唯一键进一步细分。
| 算法缩写 | 全称 | 触发条件 | 关键特征 |
|---|---|---|---|
| INLJ | Index Nested-Loop Join | 被驱动表 JOIN 列有索引 | 每行做一次索引查找，O(log m) |
| BNLJ | Block Nested-Loop Join | 被驱动表无可用索引 | 多行拼成块写入 Join Buffer，逐表扫描 |
[!NOTE] 为什么没有"Simple NLJ"？ Simple NLJ 是学术上的概念——外层每行内层逐行比较。MySQL 从未使用这种实现：有索引就走 INLJ（直接 SEEK），没索引就直接上 BNLJ（分块缓冲）。下文不再讨论 Simple NLJ。
决策对照表
当你写了一个 JOIN 后，Optimizer 的选择逻辑如下：
flowchart TD
J["Optimizer 开始评估"] --> D{"被驱动表 JOIN 列<br/>是否有索引？"}
D -->|有| INLJ["Index Nested-Loop Join"]
D -->|无| BNLJ["Block Nested-Loop Join"]
INLJ --> I{"该索引是否为<br/>唯一索引 / 主键？"}
I -->|是| UNIJ["Unique NLJ<br/>一次查找即返回"]
I -->|否| INDEXJ["Non-Unique NLJ<br/>一次查找可能多行"]
BNLJ --> BUFF["读取 N 行 → Join Buffer<br/>被驱动表全扫 1 次"]
style UNIJ fill:#00D866,color:#fff
style INDEXJ fill:#FF9F43,color:#000
style BUFF fill:#EE5A24,color:#fff
[!TIP] 一眼判断好坏
- 走 Unique NLJ (eq_ref) → ✅ 最优
- 走 INLJ (ref) → ✅ 良好
- 走 BNLJ (ALL) → ⚠️ 需要加索引
1. Block Nested-Loop Join（BNL，块嵌套循环）
当被驱动表没有可用索引时启用。MySQL 会将多行驱动表缓存到 Join Buffer 中，一次性与被驱动表比较。
-- 假设 user.tag 和 order.tag 上都没有索引
-- Optimizer 会选择 BNLJ
SELECT * FROM users u JOIN orders o ON u.tag = o.tag;
[!QUESTION] 💡 思考 如果被驱动表有百万行数据，每读一行都要跟驱动表的所有行做比较——这比数据库慢的原因更接近"人脑"的逻辑。你觉得 MySQL 在这种极端情况下会怎么优化？ 答：不要逐行带进来比较！先把驱动表的 N 行拼成一大块放到内存缓冲区里，然后对被驱动表只扫一遍就完事。这就是 Block Nested-Loop Join 的核心思想。
flowchart LR
S["从驱动表读 N 行"] --> B["写入 Join Buffer<br/>默认 256KB"]
B --> P{"Buffer 满了？"}
P -->|否| R["扫描被驱动表<br/>与 Buffer 中每行比较"]
P -->|是| O["输出已匹配结果"]
O --> C["清空 Buffer"]
C --> R
R --> S
-- BNLJ 成本估算公式
-- 总操作数 ≈ ceil(驱动表行数 / 每Buffer能存行数) × 被驱动表行数
-- 每Buffer行数 = floor(join_buffer_size / 单行字节数)
[!NOTE] Join Buffer 大小
SHOW VARIABLES LIKE 'join_buffer_size'; -- 默认 256KB，可调至最大 4MB -- 每个连接独立分配，退出时释放 -- 注意：它不参与排序也不去重，纯粹做行数据缓存
[!WARNING] BNLJ 是最后的手段 被驱动表无论多大都必须全扫一次。两张百万级大表无索引 JOIN 的成本可达 百亿级行比较。
2. Index Nested-Loop Join（INLJ，索引嵌套循环）
最理想的 JOIN 方式——被驱动表可以通过索引快速定位。
-- orders.user_id 上有索引 → Optimizer 选 INLJ
SELECT * FROM users u JOIN orders o ON u.id = o.user_id;
-- 提示优化器使用特定 JOIN 顺序（非强制）
SELECT * FROM users u USE INDEX FOR JOIN (idx_user_id)
JOIN orders o ON u.id = o.user_id;
sequenceDiagram
participant Driver as 驱动表(users)
participant IDX as 二级索引(idx_user_id)
participant Target as 被驱动表(orders)
loop 每行驱动数据
Driver->>IDX: Seek key=user_id
IDX-->>Driver: 找到匹配的 ROWID
Driver->>Target: Read by ROWID (回表取完整行)
Target-->>Driver: 返回完整行
end
[!NOTE] INLJ 的成本拆解
- 单次查找代价 = log₂(索引页数)，通常 ≈ 3~4 次磁盘随机读
- 若驱动表 1000 行、被驱动表 100 万行：1000 × log₂(1M) ≈ 20,000 次索引查找
- 对比 BNLJ（Buffer 每批存 50 行）：ceil(1000/50) × 1,000,000 = 20,000,000 次行比较
- 差距达 三个数量级
INLJ 的两个子分类：
| 子类 | 索引类型 | 每次查找返回 | Extra 标识 |
|---|---|---|---|
| Unique NLJ | 主键 / 唯一索引 | 恰好 0 或 1 行 | Using index condition |
| Non-Unique NLJ | 普通二级索引 | 可能 0…N 行 | Using index condition |
成本对比：加一个索引能带来什么？
假设场景：驱动表 1,000 行，被驱动表 100 万行
quadrantChart
title JOIN 算法性能分布
x-axis "低效 ← → 高效"
y-axis "高成本 ← → 低成本"
"Block NLJ (无索引)": [0.08, 0.1]
"Non-Unique NLJ": [0.25, 0.75]
"Unique NLJ (主键)": [0.95, 0.95]
-- 量化对比 (操作次数)
-- Unique NLJ (主键): 1000 × log2(1000000) ≈ 20,000 次查找
-- Non-Unique NLJ (二级): 1000 × log2(1M) × 10 ≈ 200,000 次查找 (假设每 key 平均 10 匹配)
-- Block NLJ (无索引): ceil(1000/50) × 1,000,000 = 20,000,000 次行比较
[!SUMMARY] 💡 一句话总结 同一个 JOIN，有索引和无索引相差三个数量级——这就是 MySQL 优化的核心杠杆。
驱动表选择
MySQL 在解析 SQL 时，默认从左到右确定驱动表——左边第一张表就是驱动表。但 Optimizer 会在评估成本后决定是否交换表的顺序（以最小化被驱动表的扫描行数）。
-- 经验法则：用小表驱动大表
-- Optimizer 通常会根据统计信息自动决定最优顺序
SELECT * FROM small_table t1 JOIN large_table t2 ON t1.id = t2.small_id;
[!TIP] 黄金法则 驱动表可以是全表扫描，但被驱动表必须走索引。 任何 JOIN 优化都要回到这个原则：检查 EXPLAIN 中被驱动表的 type 是否为 ref / eq_ref / range，如果是 ALL 就说明优化失败了。
STRAIGHT_JOIN：强制驱动表顺序
当 Optimizer 因统计信息过期或数据分布不均而选错顺序时，可以用 STRAIGHT_JOIN 强制指定：
-- 告诉 MySQL：不要用你的优化器，按我写的顺序执行
SELECT * FROM large_table t1 STRAIGHT_JOIN small_table t2
ON t1.id = t2.small_id;
适用场景：
- EXPLAIN 显示被驱动表走了全表扫描（type = ALL）
- 大表作为驱动表且小表能走索引（
small_id有索引）时，比反过来的成本低得多 - 临时排查问题——固定顺序便于复现和优化
[!WARNING] 谨慎使用 STRAIGHT_JOIN 它绕过了 Optimizer 的成本模型，仅在确认优化器做出错误选择时才用。数据分布变化后可能反而变慢。优先选择修复统计信息（
ANALYZE TABLE）或调整索引。
驱动表 vs 被驱动表的判断标准
| 角色 | 访问方式 | 理想 type | 可接受 type |
|---|---|---|---|
| 驱动表 | 全表扫描 / 索引扫描 | ALL, index |
— |
| 被驱动表 | 每行索引查找 | eq_ref, ref |
range, fulltext |
| 两者都差 | ⚠️ 性能灾难 | — | ALL × 2 |
JOIN 优化 Checklist
流程概览
flowchart TD
A["写好 JOIN 查询"] --> B{"EXPLAIN 分析"}
B --> C{"被驱动表 type"}
C -->|"eq_ref / ref"<| OK["✅ 走索引，优秀"]
C -->|"range"<| WARN["⚠️ 范围扫描，可接受"]
C -->|"ALL / index"<| BAD["❌ 全表/全索引扫描"]
BAD --> D{"Checklist 逐项排查"}
D --> E["被驱动表的 JOIN 条件列有索引吗？"]
D --> F["JOIN 条件的数据类型一致吗？<br/>VARCHAR vs INT 会导致索引失效"]
D --> G["有没有函数包裹 JOIN 列？"]
D --> H["能不能把 JOIN 拆成多次单表查询？"]
style OK fill:#00D866,color:#fff
style BAD fill:#EE5A24,color:#fff
实战：EXPLAIN 输出解读
看一个具体的例子：
EXPLAIN SELECT * FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;
期望的 EXPLAIN 输出：
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------+
| id | select_type | table | type | key | extra | rows | filtered | ref | |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------+
| 1 | SIMPLE | u | ALL | NULL | | 1000 | 100.00 | NULL | |
| 1 | SIMPLE | o | ref | idx_user_id | | 50 | 100.00 | u.id | |
| 1 | SIMPLE | p | eq_ref | PRIMARY | | 1 | 100.00 | o.product_id | |
+----+-------------+-------+--------+---------------+---------+---------+-------------------+------+-------+
逐字段说明：
| 字段 | 含义 | 本例中的解读 |
|---|---|---|
| table | 当前行涉及的表 | 三表 JOIN 有三行输出 |
| type | 访问类型（关键指标） | ALL → 驱动表全扫（正常）；ref → 被驱动表走普通索引；eq_ref → 被驱动表走主键/唯一索引 |
| key | 实际使用的索引 | idx_user_id 和 PRIMARY 均命中 |
| rows | 预估扫描行数 | 1000 × 50 × 1 = 50000 次索引查找，可接受 |
| filtered | WHERE 过滤后的比例 | 100% 表示 WHERE 还没起作用（无额外过滤条件） |
| Extra | 额外信息 | 无 Using filesort / Using temporary，说明执行计划健康 |
[!NOTE] Extra 中需要警惕的关键字
Using filesort→ 需要额外排序，考虑加联合索引Using temporary→ 用了临时表，常出现在 DISTINCT / GROUP BY / UNION 中Using index condition→ 下推索引条件，部分过滤在存储引擎层完成，是好事
Multi-Join 处理策略（3+ 表）
生产中最常见的是 3 表以上 JOIN。MySQL 采用 链式驱动——前一张表的输出结果作为下一张被驱动表的输入。
sequenceDiagram
participant T1 as 表A (驱动)
participant T2 as 表B (被驱动)
participant T3 as 表C (被驱动)
participant T4 as 表D (被驱动)
Note over T1: 第一步：全扫 A<br/>得到 N1 行中间结果
loop N1 行
T1->>T2: B ON A.x = B.x (走索引)
T2-->>T1: M1 行匹配
end
Note over T1,T2: 第二步：得到 M1 行中间结果
loop M1 行
T1->>T3: C ON B.y = C.y (走索引)
T3-->>T1: M2 行匹配
end
Note over T1,T3: 第三步：得到 M2 行中间结果
loop M2 行
T1->>T4: D ON C.z = D.z (走索引)
T4-->>T1: 最终结果
end
[!QUESTION] 💡 思考 如果有 4 张表按顺序 JOIN，每张表都有合适的索引——那中间结果的行数会逐级递减还是递增？为什么？ 答：取决于 JOIN 条件的选择性。WHERE 过滤和精确匹配会让行数逐层减少；而一对多关联则可能逐层膨胀。关键是要确保每一层的被驱动表都走索引。
优化思路升级：
- 确保第 2 张及之后的每张表都有索引支撑 JOIN 条件——只有第 1 张表可以全表扫描
- 用小表驱动大表：EXPLAIN 输出从上到下依次是被驱动表，上面的驱动下面的
- 利用覆盖索引减少回表：如果 SELECT 的列都在索引里，InnoDB 可以直接从索引树返回结果
- 拆分解耦：超复杂的多表 JOIN 可以考虑拆成两步——先拿 ID 集合再批量查详情
-- 覆盖索引示例：索引已包含所有需要的列，无需回表
CREATE INDEX idx_order_cover ON orders(user_id, product_id, amount);
-- 此时下面这个查询可以直接走覆盖索引扫描
SELECT user_id, product_id, amount FROM orders WHERE user_id = 42;
USING vs ON
-- 推荐：用 USING 更简洁（要求两列同名）
SELECT * FROM users u JOIN orders o USING (user_id);
-- 等价的 ON 写法
SELECT * FROM users u JOIN orders o ON u.user_id = o.user_id;
-- 进阶技巧：USING 的结果集中 user_id 只出现一列，避免列名冲突
常见 JOIN 陷阱
| 陷阱 | 示例 | 修复 |
|---|---|---|
| 类型不匹配 | u.code VARCHAR JOIN o.code INT |
统一类型 |
| 函数包裹 | ON YEAR(u.created) = YEAR(o.created) |
改用范围比较 |
| NULL 值 | ON t1.col = t2.col 中一列为 NULL 不匹配 |
检查业务逻辑 |
| 隐式转换 | WHERE varchar_col = 123 |
显式字符串比较 |
| 缺少复合索引 | 多条件 JOIN 只用单列索引 | 创建覆盖联合索引 |
进阶优化技巧
1. Index Condition Pushdown（ICP，索引条件下推）
传统扫描方式：二级索引找到 ROWID → 回表取完整行 → WHERE 过滤。ICP 将部分 WHERE 条件的过滤下沉到存储引擎层，在二级索引上就直接完成，减少回表次数。
-- 假设 (category, status) 上有联合索引
SELECT * FROM products WHERE category = 'electronics' AND status = 'active';
-- ICP 开启时 (SHOW STATUS LIKE 'Last_query_cost...')：
-- 普通模式: 二级索引扫描所有 category='electronics' 的 ROWID → 全部回表 → 再过滤 status
-- ICP 模式: 二级索引上同时匹配 category + status → 只回表符合两条件的行
[!TIP] 如何确认 ICP 生效？ EXPLAIN 的 Extra 列出现
Using index condition——这表示 MySQL 5.6+ 的 ICP 已启用。
2. Loose Scan（松散扫描）
当聚合函数（COUNT/DISTINCT/MAX/MIN）配合有序索引使用时，Optimizer 可以跳过中间重复值，直接"跳跃"到每组第一个记录。
-- 假设 (category, subcategory) 上有联合索引
-- 传统 GROUP BY: 扫描所有 10 万行 → 分组 → 每组选第一条
SELECT category, COUNT(DISTINCT subcategory) FROM products GROUP BY category;
-- Loose Scan: 直接从二级索引树中抽取每组的唯一子键
-- 代价从全表扫描降到仅遍历索引的第一层 ≈ O(unique_categories)
[!NOTE] 触发条件
- 聚合函数必须是
MIN()/MAX()或COUNT(DISTINCT)- GROUP BY 的列必须紧跟联合索引的最左前缀
- 查询结果中不能有其他需要额外处理的列
3. Semi Join（半连接）
将子查询转化为 JOIN 的一种优化策略。MySQL 会对 IN / EXISTS 子查询自动做半连接转换。
-- 原始子查询
SELECT * FROM orders WHERE user_id IN (SELECT id FROM users WHERE level = 'vip');
-- 等价于半连接：orders 只需要知道"是否存在匹配"，不需要返回 users 的所有匹配行
-- MySQL 内部可能选择以下策略之一：
-- DuplicateWeedout: 先做 JOIN 再去重
-- FirstMatch: 被驱动表找到第一行匹配就停止
-- Loosescan: 对被驱动表做 Loose Scan
[!WARNING] 注意去重开销 半连接需要额外的去重步骤（DuplicateWeedout）。如果驱动表本身 SELECT DISTINCT，可以考虑先对驱动表去重再做 JOIN。
4. Derived Table Materialization（派生表物化）
复杂子查询作为 FROM 子句时，MySQL 会将其物化为临时表。过度物化会带来额外开销。
-- 派生表会被物化为临时表
SELECT * FROM (SELECT user_id, SUM(amount) FROM orders GROUP BY user_id) t
JOIN users u ON t.user_id = u.id;
-- 如果 optimizer_switch='derived_merge=on'（MySQL 5.7+ 默认），
-- 优化器会尝试将子查询合并到外层查询中，避免物化开销
[!TIP] 调优建议
-- 查看当前优化器开关状态 SHOW VARIABLES LIKE 'optimizer_switch'; -- derived_merge=on 是推荐的默认配置，能让 MySQL 自动展开简单派生表
性能对比速查表
| 算法 | 最优场景 | 最坏场景 | 典型代价公式 |
|---|---|---|---|
| Unique NLJ | 被驱动表是主键 / 唯一索引 | 驱动表大量行无匹配（返回 NULL） | 驱动表行数 × log(被驱动表) |
| Non-Unique NLJ | 被驱动表有普通二级索引 | 索引选择性差，大量重复值 | 驱动表行数 × log(被驱动表) × 平均匹配数 |
| Block NLJ | 被驱动表无索引 + 驱动表较小 | 大表 × 大表无索引 | ceil(驱动表行数 / Buffer行数) × 被驱动表行数 |
[!WARNING] 数量级示意 假设驱动表 1000 行、被驱动表 100 万行：
- Unique NLJ：≈ 1000 × 20 = 2 万次索引查找
- Block NLJ（Buffer 存 50 行）：≈ ceil(1000/50) × 1,000,000 = 2000 万次行比较
- 差距达 三个数量级，这就是为什么被驱动表索引如此重要。
关联笔记
- hhs/MySQL/02-SQL核心/10-子查询与派生表 — EXISTS / IN 子查询与 JOIN 的替代关系
- hhs/MySQL/03-索引与查询优化/12-B+Tree 索引原理 — JOIN 如何利用二级索引加速
- hhs/MySQL/03-索引与查询优化/15-EXPLAIN 完全指南 — 通过 type 字段识别 JOIN 质量问题