从单表查询到多表 JOIN：MySQL 执行计划背后的秘密

摘要：DBA 丢给你一个慢查询，你除了加索引还能做什么？本文从const到all逐层拆解 MySQL 的 6 种单表访问方法，再深入到连接查询的底层——笛卡尔积、嵌套循环连接、Join Buffer，以及ON与WHERE的微妙差异。读完之后，你不仅能看懂EXPLAIN，更能预判一条 SQL 到底会怎么跑。

一、一条 SQL 的旅程：从语法树到执行计划

写 SQL 时，我们总以为数据库"应该"这么执行：先查 A 表，再查 B 表，最后拼在一起。但实际上，你看到的 SQL 只是声明式语法——你告诉 MySQL “我想要什么”，MySQL 自己决定"怎么去拿"。

用户 SQL 语句 | v 查询解析器 -> 语法树 | v 查询优化器 -> 生成执行计划（Execution Plan） | 决定：用哪个索引？访问方法是什么？连接顺序是什么？ v 执行引擎 -> 调用存储引擎接口 | v 返回结果集

执行计划的核心就是两个问题：

1. 单表怎么查？-> 访问方法（access method）
2. 多表怎么连？-> 连接算法（join algorithm）

本文先回答第一个问题，再回答第二个。

二、单表访问的六种"交通工具"

设计 MySQL 的大叔给单表查询的访问方法起了 6 个名字，我们用一个更通俗的类比来理解：把查询比作从西安钟楼到大雁塔，不同的访问方法就像不同的交通工具。

实战技巧：执行EXPLAIN SELECT ...时，type列就显示访问方法。优化 SQL 的首要目标就是尽量把ALL变成RANGE，把RANGE变成REF或CONST。

2.1 const：坐火箭

通过主键或唯一二级索引与常数进行等值比较，最多只匹配一条记录。

-- 主键等值查询SELECT*FROMsingle_tableWHEREid=1438;-- 唯一二级索引等值查询SELECT*FROMsingle_tableWHEREkey2=3841;

const 执行过程： B+ 树根节点 | v 逐层二分查找 +--------------+ | id = 1438 | <- 叶子节点（聚簇索引） | 完整记录 | +--------------+ | v 直接返回记录（最多 1 条）

B+ 树矮胖的结构决定了查找只需 3~4 次页面内比较，代价极小。

例外：唯一索引列值为NULL时，因为NULL可以有多个，退化为ref：

SELECT*FROMsingle_tableWHEREkey2ISNULL;-- type = ref，不是 const

2.2 ref：坐高铁

通过普通二级索引与常数进行等值比较，可能匹配多条记录，然后回表。

SELECT*FROMsingle_tableWHEREkey1='abc';

ref 执行过程： idx_key1 B+ 树 聚簇索引 B+ 树 +--------------+ +--------------+ | key1='abc' | -- 回表 --> | id = x | | id = x | | 完整记录 | +--------------+ +--------------+ | key1='abc' | -- 回表 --> | id = y | | id = y | | 完整记录 | +--------------+ +--------------+

联合索引的 ref 用法：只要最左边连续的列是等值匹配，就能用ref。

-- 能用 ref（最左连续等值）WHEREkey_part1='god like';WHEREkey_part1='a'ANDkey_part2='b';-- 不能用 ref（key_part2 不是最左列，或不是全部等值）WHEREkey_part1='a'ANDkey_part2>'b';

2.3 ref_or_null：高铁 Plus

在ref的基础上还要找出值为NULL的记录。

SELECT*FROMsingle_tableWHEREkey1='abc'ORkey1ISNULL;

ref_or_null 执行过程： 在 idx_key1 B+ 树中找两个范围： +--------------------------------------+ | ① 先找 key1 IS NULL 的连续记录 | | （NULL 值在 B+ 树最左侧） | +--------------------------------------+ | ② 再找 key1 = 'abc' 的连续记录 | | （等值匹配） | +--------------------------------------+ | v 合并两个结果集的主键，统一回表

2.4 range：开车兜风

索引列匹配一个或多个范围区间。

SELECT*FROMsingle_tableWHEREkey2IN(1438,6328)OR(key2>=38ANDkey2<=79);

数轴上的区间示意： key2 的取值范围： <----+----+----+----+----+----+----+----+---> 38 79 1438 6328 | | | | +--------+ +---------+ [38, 79] 两个单点 连续区间 结果：取并集 -> key2 ∈ {1438, 6328} ∪ [38, 79]

能触发 range 的操作符：=,<=>,IN,IS NULL,IS NOT NULL,>,<,>=,<=,BETWEEN,!=,LIKE 'prefix%'。

复杂条件的区间提取：当 WHERE 中有 AND/OR 组合时，MySQL 会取区间交集（AND）或并集（OR）。

-- AND -> 取交集WHEREkey2>100ANDkey2>200-- 合并为 key2 > 200-- OR -> 取并集WHEREkey2>100ORkey2>200-- 合并为 key2 > 100

关键规则：如果用不到索引的条件和用到索引的条件用OR连接，整条都会退化为全表扫描：

WHEREkey2>100ORcommon_field='abc'-- common_field 没有索引，优化器会把 key2 > 100 也-- 当成需要全表扫描的条件，最终 type = ALL

2.5 index：坐公交

当查询列表和 WHERE 条件中的列全部包含在某个二级索引中时，MySQL 可以直接遍历二级索引的全部记录，而不需要回表。

SELECTkey_part1,key_part2,key_part3FROMsingle_tableWHEREkey_part2='abc';

index 执行过程： 直接遍历 idx_key_part B+ 树的叶子节点： +--------------+ +--------------+ +--------------+ | key1, k2, k3 | --> | key1, k2, k3 | --> | key1, k2, k3 | | a, abc, x | | b, abc, y | | c, def, z | +--------------+ +--------------+ +--------------+ 匹配 匹配 不匹配 保留 保留 跳过

为什么遍历二级索引比全表扫描快？因为二级索引记录只包含索引列和主键，比聚簇索引的完整记录小得多，同样 16KB 的页能装更多记录，遍历的页数更少。

2.6 all：步行

最朴素的执行方式——直接扫描聚簇索引的全部叶子节点，逐条对比 WHERE 条件。当没有任何索引可以使用时，MySQL 就会选择这种方式。

SELECT*FROMsingle_tableWHEREcommon_field='abc';-- common_field 没有索引，只能全表扫描

all 执行过程： 从聚簇索引的第一个叶子节点开始： 页1: [R1][R2][R3][R4][R5] --> 页2: [R6][R7][R8][R9][R10] --> ... 逐条判断 common_field = 'abc'？

三、索引合并：多条索引齐上阵

前面说"一般情况下只能利用单个二级索引"，但 MySQL 还留了一手：索引合并（Index Merge），在特定场景下可以同时使用多个索引。

3.1 Intersection 合并：取交集

当 WHERE 条件用AND连接多个索引列时，可以从多个二级索引分别查出主键集合，取交集后再回表。

SELECT*FROMsingle_tableWHEREkey1='a'ANDkey3='b';

Intersection 索引合并过程： idx_key1 B+ 树 idx_key3 B+ 树 +----------+ +----------+ | key1='a' | | key3='b' | | id=1 | | id=3 | | id=3 | | id=5 | | id=5 | | id=7 | +----------+ +----------+ | | | 主键取交集：{1,3,5} ∩ {3,5,7} = {3,5} | | +-----------+ +-----------+ | | v v 回表查 id=3, id=5

什么情况下能用 Intersection 合并？

1. 二级索引列都是等值匹配（联合索引的每个列也必须等值匹配）
2. 主键列可以是范围匹配

为什么这么苛刻？因为只有等值匹配时，从每个二级索引查出的主键值集合是按主键排好序的，这样取交集只需 O(n) 的"双指针"算法。如果顺序乱了，就得先排序，代价太高。

按有序主键值回表取记录有个专业名词：Rowid Ordered Retrieval（ROR）。

3.2 Union 合并：取并集

当 WHERE 条件用OR连接多个索引列时，可以从多个二级索引分别查出主键集合，取并集后再回表。

SELECT*FROMsingle_tableWHEREkey1='a'ORkey3='b';

Union 索引合并过程： idx_key1: {id=1, id=3} idx_key3: {id=3, id=5} | | | 主键取并集: {1,3} ∪ {3,5} = {1,3,5} | | +-----------+ +-------+ | | v v 回表查 id=1,3,5

使用条件与 Intersection 类似：二级索引列必须等值匹配，主键可范围匹配。或者某部分用 Intersection 合并后再与其他结果集取 Union。

3.3 Sort-Union 合并：先排序再合并

如果OR两边的条件涉及范围匹配，查出的主键值集合就不是有序的了：

SELECT*FROMsingle_tableWHEREkey1<'a'ORkey3>'z';

这时 MySQL 会先分别对两个主键集合排序，然后再做 Union 合并。这就是 Sort-Union。

为什么没有 Sort-Intersection？因为 Intersection 的目的是减少回表记录数，如果还要先对大量记录排序，排序成本可能比回表还高，得不偿失。

索引合并的替代方案

如果你发现 SQL 频繁触发 Intersection 索引合并，不妨考虑建一个联合索引：

-- 原来有两个独立索引 idx_key1 和 idx_key3WHEREkey1='a'ANDkey3='b';-- 优化：建联合索引，直接命中，无需合并ALTERTABLEsingle_tableADDINDEXidx_key1_key3(key1,key3);

四、连接的本质：笛卡尔积与过滤

4.1 连接的本质

连接（JOIN）的本质非常简单：把各个连接表中的记录都取出来依次匹配，符合条件的组合加入结果集。

假设有 t1 和 t2 两张表：

SELECT*FROMt1,t2;

t1 表: t2 表: +----+----+ +----+----+ | m1 | n1 | | m2 | n2 | +----+----+ +----+----+ | 1 | a | | 2 | b | | 2 | b | | 3 | c | | 3 | c | | 4 | d | +----+----+ +----+----+ 笛卡尔积结果（3 × 3 = 9 行）： +----+----+----+----+ | m1 | n1 | m2 | n2 | +----+----+----+----+ | 1 | a | 2 | b | | 2 | b | 2 | b | | 3 | c | 2 | b | | 1 | a | 3 | c | | 2 | b | 3 | c | | 3 | c | 3 | c | | 1 | a | 4 | d | | 2 | b | 4 | d | | 3 | c | 4 | d | +----+----+----+----+

没有任何过滤条件时，这就是笛卡尔积。3 个 100 行的表连接会产生 100 万行记录！所以连接时必须有过滤条件。

4.2 内连接 vs 外连接

内连接（t1 INNER JOIN t2 ON t1.m1 = t2.m2）： t1 t2 结果 1,a 2,b 2,b 匹配 2,b ✅ 2,b ----> 3,c 3,c 匹配 3,c ✅ 3,c 4,d 无匹配 ❌ 左外连接（t1 LEFT JOIN t2 ON t1.m1 = t2.m2）： t1 t2 结果 1,a 2,b 无匹配，但保留 1,a + NULL,NULL ✅ 2,b ----> 3,c 2,b 匹配 2,b ✅ 3,c 4,d 3,c 匹配 3,c ✅

4.3 ON 与 WHERE 的分工

这是连接查询中最容易搞混的知识点：

SELECT*FROMt1LEFTJOINt2ONt1.m1=t2.m2WHEREt2.n2<'d';

LEFT JOIN 的执行逻辑： 1. 用 ON 条件匹配 t1 和 t2 的记录 - 匹配成功：组合成一行 - 匹配失败：t1 记录保留，t2 字段填 NULL 2. 用 WHERE 条件过滤上一步的结果 - 包括过滤掉 t2.n2 IS NULL 的行（如果需要的话）

推荐写法：

• 只涉及单表的过滤条件 → 放在WHERE中
• 涉及两表的连接条件 → 放在ON中

五、连接算法：嵌套循环与 Join Buffer

5.1 嵌套循环连接（NLJ）

MySQL 执行连接查询的核心算法是嵌套循环连接（Nested-Loop Join）：

伪代码： for each row in 驱动表 { -- 驱动表只扫描 1 次 for each row in 被驱动表 { -- 被驱动表扫描 N 次（N = 驱动表结果行数） if 满足连接条件: 加入结果集 } }

实际执行示例： SELECT * FROM t1, t2 WHERE t1.m1 > 1 AND t1.m1 = t2.m2 AND t2.n2 < 'd'; 步骤1：扫描驱动表 t1 t1 中满足 t1.m1 > 1 的记录： +----+----+ | 2 | b | -- 第1条驱动记录 | 3 | c | -- 第2条驱动记录 +----+----+ 步骤2：用每条驱动记录去被驱动表 t2 中查找匹配 当 t1.m1 = 2 时，t2 的查询变为： SELECT * FROM t2 WHERE t2.m2 = 2 AND t2.n2 < 'd'; -- 结果：m2=2, n2='b' ✅ 当 t1.m1 = 3 时，t2 的查询变为： SELECT * FROM t2 WHERE t2.m2 = 3 AND t2.n2 < 'd'; -- 结果：m2=3, n2='c' ✅

关键结论：

• 驱动表只访问1 次
• 被驱动表访问N 次（N = 驱动表过滤后的记录数）
• 所以被驱动表的查询效率直接决定整个连接查询的效率

5.2 被驱动表的索引加速

既然被驱动表要被访问多次，为它建立索引就至关重要。

SELECT*FROMt1,t2WHEREt1.m1=t2.m2;

如果t2.m2是主键或唯一索引，那么每次访问被驱动表都是const级别。MySQL 把这种在连接中对被驱动表使用主键/唯一索引等值查找的访问方法称为：eq_ref。

EXPLAIN 输出中的 type 列： +----+-------------+-------+--------+ | id | select_type | table | type | +----+-------------+-------+--------+ | 1 | SIMPLE | t1 | ALL | <- 驱动表全表扫描 | 1 | SIMPLE | t2 | eq_ref | <- 被驱动表用主键等值匹配 +----+-------------+-------+--------+

如果t2.m2是普通二级索引，则 type 为ref。

5.3 基于块的嵌套循环连接（BNL）

当被驱动表没有索引且数据量很大时，每次访问被驱动表都是全表扫描，I/O 代价极高。MySQL 引入了Join Buffer来优化：

Join Buffer 工作机制： 驱动表结果集（假设 1000 条） | v +---------------------------+ | Join Buffer（内存块） | | 容量 = join_buffer_size | | 默认 256KB | | | | 装入一批驱动表记录（比如100条）| +---------------------------+ | v 扫描被驱动表（只扫描 1 次） | v 被驱动表的每条记录与 Buffer 中所有记录做匹配 （全部在内存中完成，无需重复读盘）

效果对比：

Join Buffer 配置：

-- 查看当前大小（默认 256KB）SHOWVARIABLESLIKE'join_buffer_size';-- 临时调大（会话级）SETSESSIONjoin_buffer_size=1048576;-- 1MB

注意：Join Buffer 中只放查询列表和过滤条件涉及的列，所以再次强调——不要用SELECT *，只选需要的列，可以让 Buffer 装下更多记录。

六、连接优化实战

6.1 小表驱动大表

由于被驱动表会被扫描多次，选择结果集更小的表作为驱动表能显著减少整体扫描量。

-- 假设 orders 表有 100 万条，users 表有 1 万条-- 如果 WHERE 过滤后 orders 只剩 100 条，users 剩 5000 条-- 优化前（大表驱动小表）SELECT*FROMusers uLEFTJOINorders oONu.id=o.user_id;-- users 结果 5000 条 → orders 被扫描 5000 次-- 优化后（小表驱动大表）SELECT*FROMorders oLEFTJOINusers uONo.user_id=u.id;-- orders 结果 100 条 → users 被扫描 100 次

对于内连接，MySQL 优化器会自动选择小表作为驱动表。但对于外连接，驱动表是固定的（LEFT JOIN 左边就是驱动表），写 SQL 时要特别注意。

6.2 为被驱动表建索引

连接查询中，被驱动表的连接列（ON 条件中的列）上必须有索引，否则每次访问都是全表扫描。

-- 慢查询SELECT*FROMorders oJOINusers uONo.user_id=u.id;-- 如果 orders.user_id 没有索引，orders 作为被驱动表时就是灾难-- 优化ALTERTABLEordersADDINDEXidx_user_id(user_id);

6.3 调大 join_buffer_size

当被驱动表确实无法建索引时（比如临时表、复杂条件），可以调大 Join Buffer：

-- 查看当前值SHOWVARIABLESLIKE'join_buffer_size';-- 建议：根据内存情况适当调大，但不宜超过 1~4MB-- 过大的 Join Buffer 可能导致内存碎片和线程切换开销

七、总结：EXPLAIN 速查手册

+--------+----------+-------------------------------------------+ | type | 速度 | 含义 | +--------+----------+-------------------------------------------+ | const | 最快 | 主键/唯一索引等值匹配，最多1条 | | eq_ref | 最快 | 连接中被驱动表用主键/唯一索引等值匹配 | | ref | 很快 | 普通二级索引等值匹配 | | ref_or_null | 很快 | 二级索引等值匹配 + NULL | | range | 中等 | 索引范围扫描 | | index | 较慢 | 遍历整个二级索引（覆盖索引） | | ALL | 最慢 | 全表扫描 | +--------+----------+-------------------------------------------+

优化优先级：

1. 消灭 ALL：给 WHERE 条件列加索引
2. 消灭 index：检查是否 SELECT * 导致无法覆盖索引
3. 争取 const/ref：确保等值查询命中主键或普通索引
4. 连接优化：确保被驱动表的连接列有索引，小表驱动大表
5. 最后的手段：调大 join_buffer_size

MySQL 查询执行全景图： 单表查询 多表连接 | | v v +--------+ +-----------+ | const | 等值+主键/唯一 | eq_ref | | ref | 等值+普通索引 | ref | <- 被驱动表有索引 | range | 范围匹配 | range | | index | 遍历二级索引 | index | | ALL | 全表扫描 | ALL/BNL | <- 被驱动表无索引 +--------+ +-----------+ | | +-------------+---------------+ | 索引合并（Index Merge） - Intersection（AND） - Union（OR） - Sort-Union（范围+OR）

延伸阅读：

• MySQL 官方文档：EXPLAIN Output Format
• 《高性能 MySQL》第 5 章：创建高性能的索引