性能优化（执行计划 / 慢查询 / 常见陷阱） · SQL 改写优化

JOIN 大表时驱动表选择不当导致慢，怎么优化？

How to optimize a slow JOIN on large tables caused by an incorrect driving table

答案语言

考察要点

这道题面试官想看你是否深入理解数据库 JOIN 的内部执行原理，而不仅仅是会写 JOIN 语法。考察的核心点包括：

驱动表与被驱动表的概念：是否明白 JOIN 操作中两张表的角色和执行顺序。
JOIN 执行算法：是否了解 Index Nested-Loop Join、Block Nested-Loop Join、Hash Join 等不同算法的原理、优劣和触发条件。
优化器的角色：是否知道优化器（CBO）如何基于成本估算来选择驱动表和 JOIN 算法，以及“小表驱动大表”的真正含义。

核心概念

要理解驱动表选择问题，必须先掌握以下几个核心概念。

1. 驱动表 (Driving Table) 与被驱动表 (Driven Table)

在 JOIN 查询中，数据库优化器会选择一张表作为基础，遍历其数据，然后根据 JOIN 条件去另一张表中查找匹配的数据。

驱动表：首先被访问的表，相当于循环中的外层循环（Outer Loop）。
被驱动表：根据驱动表返回的每一行数据，去其中查找匹配数据的表，相当于内层循环（Inner Loop）。

执行逻辑伪代码：

text

 1FOR each row R1 in Driving_Table:
 2    FOR each row R2 in Driven_Table:
 3        IF join_condition_is_met(R1, R2):
 4            output (R1, R2)

关键点：驱动表的选择直接决定了对被驱动表的访问次数。如果驱动表有 N 行，那么被驱动表理论上将被访问 N 次。因此，减少外层循环的次数是优化的关键。

2. `JOIN` 执行算法

MySQL 主要依赖以下几种算法来执行 JOIN。

算法	原理	触发条件	性能
Index Nested-Loop Join (INLJ)	遍历驱动表，对于驱动表的每一行，使用被驱动表上的索引来查找匹配行。	被驱动表的 `JOIN` 关联列上有索引。	最高效。访问被驱动表的成本从全表扫描 $O(M)$ 降为索引查找 $O(\log M)$ 。
Block Nested-Loop Join (BNLJ)	将驱动表的多行数据读入内存中的 `join_buffer`，然后一次性地全表扫描被驱动表，与 `join_buffer` 中的所有行进行匹配。	被驱动表的 `JOIN` 关联列上没有可用索引。	性能较差。会多次全表扫描被驱动表（取决于驱动表大小和 `join_buffer_size`）。MySQL 8.0.18 已废弃 BNLJ，默认使用 Hash Join。
Hash Join	1. Build Phase: 选择小表（驱动表），在内存中基于 `JOIN` 键构建一个哈希表。 <br> 2. Probe Phase: 遍历大表（被驱动表），用每一行的 `JOIN` 键去哈希表中探测（Probe）匹配的行。	MySQL 8.0.18+ 默认在无索引时使用。PostgreSQL/Oracle 等数据库早已是标配。	性能远好于 BNLJ。两张表都只需扫描一次，时间复杂度近似 $O(N+M)$ 。

ASCII 图示：INLJ vs BNLJ

mermaid

 1graph TD
 2    subgraph INLJ (高效)
 3        A[驱动表 t1] -- 逐行读取 --> B{row1}
 4        B -- "ON t1.id = t2.t1_id" --> C[使用 t2.t1_id 的索引]
 5        C -- O(log M) 快速定位 --> D[被驱动表 t2]
 6    end
 7
 8    subgraph BNLJ (低效, MySQL < 8.0.18)
 9        E[驱动表 t1] -- 批量读取 --> F[join_buffer]
10        F -- "ON t1.id = t2.t1_id" --> G{与 t2 中每一行比较}
11        G -- O(M) 全表扫描 --> H[被驱动表 t2]
12    end

3. 优化器选择驱动表的原则

MySQL 优化器遵循 成本估算模型（Cost-Based Optimizer, CBO）。其核心目标是选择一个总成本最低的执行计划。

“小表驱动大表”的正确理解： 这里的“小”和“大”指的不是表的总行数，而是经过 WHERE 条件过滤后，参与 JOIN 的结果集大小。

成本估算公式（简化版）： Total_Cost = Cost(Driving_Table) + Rows(Driving_Table) * Cost(Driven_Table_Per_Access)

Cost(Driving_Table): 访问驱动表的成本（全表扫描或索引扫描）。
Rows(Driving_Table): 驱动表过滤后的行数估算。
Cost(Driven_Table_Per_Access): 访问被驱动表一次的成本。
- 如果是 INLJ，成本是索引查找，非常低 ( $O(\log M)$ )。
- 如果是 BNLJ/Hash Join，成本是全表扫描或哈希探测，相对较高。

结论：优化器会优先选择过滤后结果集更小的表作为驱动表。同时，它会极力促成 INLJ，即让有索引的表成为被驱动表。如果 t1 过滤后有 100 行，t2 过滤后有 10000 行：

如果 t2 的 JOIN 键有索引，优化器会选 t1 做驱动表，t2 做被驱动表。
如果 t1 的 JOIN 键有索引，t2 没有，优化器会选 t2 做驱动表，t1 做被驱动表。

常见低分回答

“LEFT JOIN 左边的表就是驱动表，INNER JOIN 看谁写在前面”
- 错误：这是最常见的误解。对于 INNER JOIN，优化器会自由交换表的顺序。对于 LEFT JOIN，虽然语义上要求保留左表所有行，但在某些情况下（如 WHERE 条件将 LEFT JOIN 降级为 INNER JOIN），优化器也可能重排。驱动表的选择权在优化器，不由 SQL 书写顺序决定。
“行数少的表就是小表，应该做驱动表”
- 错误：如上所述，“小”指的是过滤后的结果集大小。一个有 1 亿行但 WHERE id = 1 的表（结果集 1 行）远小于一个 10 万行但无 WHERE 条件的表。
“优化 JOIN 就是让小表驱动大表”
- 不完整：这只说对了一半。更关键的是确保被驱动表的关联列上有索引，以触发高效的 INLJ 算法。如果两张表在关联列上都没有索引，即使遵循“小表驱动大表”，性能依然会很差（退化为 BNLJ 或 Hash Join）。
“直接用 STRAIGHT_JOIN 就行了”
- 危险：STRAIGHT_JOIN 是强制指定 JOIN 顺序的“核武器”，它会完全禁用优化器的重排能力。这只应在你百分百确定优化器选错了，并且通过 EXPLAIN 证实了的情况下作为最后手段。滥用 STRAIGHT_JOIN 会让数据库失去应对数据变化的自适应能力。

工程实践与优化

1. 如何发现驱动表选择不当？

使用 EXPLAIN 是不二法门。

sql

 1EXPLAIN SELECT u.name, o.order_no
 2FROM `user` u
 3JOIN `order` o ON u.id = o.user_id
 4WHERE u.city = 'Beijing';

查看 EXPLAIN 输出：

表的顺序：EXPLAIN 结果中，第一行出现的表就是驱动表。
type 列：
- 如果被驱动表的 type 是 ALL，意味着正在进行全表扫描，这是巨大的性能隐患。
- 理想情况下，被驱动表的 type 应该是 eq_ref（主键/唯一索引等值连接）或 ref（普通索引等值连接）。
key 列：如果被驱动表的 key 列为 NULL，说明没有使用索引。
Extra 列：
- 如果出现 Using join buffer (Block Nested Loop)，说明发生了低效的 BNLJ，必须优化。
- 如果出现 Using hash join，说明无索引，但使用了比 BNLJ 更优的 Hash Join。

2. 如何优化？

核心原则：创造条件让优化器选择最优计划。

为被驱动表的关联列创建索引（首选方案） 这是最根本、最有效的优化手段。
sql
```
 1-- 假设 order 表是 user_id 上的被驱动表
 2ALTER TABLE `order` ADD INDEX `idx_user_id` (`user_id`);
```
创建索引后，优化器大概率会采用 INLJ，性能会得到数量级的提升。
保证索引有效 即使有索引，以下情况也会导致索引失效，退化为 BNLJ/Hash Join：
- 类型不匹配：JOIN 的两列类型不同，如 varchar 和 int。MySQL 会进行隐式类型转换，导致索引失效。
- 字符集/校对规则不匹配：JOIN 的两列字符集或校对规则不同。
- 对被驱动表的关联列使用函数：如 ON t1.id = YEAR(t2.create_time)。

使用 STRAIGHT_JOIN 强制指定驱动表（最后手段） 当你通过 EXPLAIN 确认优化器因统计信息不准等原因选错了驱动表，可以手动干预。

sql

 1-- 假设 user 表过滤后结果集更小，但优化器误选了 order 做驱动表
 2SELECT STRAIGHT_JOIN u.name, o.order_no
 3FROM `user` u -- 强制 user 为驱动表
 4JOIN `order` o ON u.id = o.user_id
 5WHERE u.city = 'Beijing';

升级 MySQL 版本 如果还在使用低于 8.0.18 的版本，并且无法添加索引，升级到新版本可以使用 Hash Join 替代 BNLJ，也能获得显著的性能提升。

常见追问与延伸题目

追问：如果数据量增长 100 倍，这条 SQL 性能怎样？怎么优化？
- 回答要点 1 (性能分析)：性能退化程度取决于当前的 JOIN 算法。
  - INLJ：总成本约 $O(N \times \log M)$ 。如果驱动表 N 增长 100 倍，查询时间约增长 100 倍，性能退化是线性的，相对可控。
  - BNLJ/Hash Join：BNLJ 成本约 $O(N \times M)$ ，Hash Join 成本约 $O(N+M)$ 。如果两表都增长 100 倍，BNLJ 性能可能下降 10000 倍，完全不可接受。Hash Join 性能下降约 100 倍，与 INLJ 类似，但常数因子更大。
- 回答要点 2 (优化方案)：在大数据量下，除了保证 INLJ，还需要考虑：
  - 索引覆盖：在索引中包含所有查询所需字段，避免回表，进一步提升性能。
  - 分区表：如果 JOIN 键和过滤条件高度相关（如按时间、地域），可以对两张大表使用相同的分区键进行分区。这样 JOIN 可以在分区内部进行（Partition-wise Join），大大减少了需要扫描的数据量。
  - 业务拆分/SQL 改写：将一个复杂的大 JOIN 拆分成多个小查询，通过中间结果或临时表来完成，降低单次查询的复杂度和锁定的资源。
追问：STRAIGHT_JOIN 和 INNER JOIN 的 ON 条件、WHERE 条件有什么区别？
- 回答要点：
  - INNER JOIN 中，写在 ON 后的条件用于确定两表如何关联，写在 WHERE 后的条件用于对关联后的结果集进行过滤。但对于 INNER JOIN，优化器可能会为了效率重写 ON 和 WHERE 的条件，逻辑上是等价的。
  - STRAIGHT_JOIN 强制了表的连接顺序，但 ON 和 WHERE 的作用与 INNER JOIN 相同。它的主要影响是固定了驱动表和被驱动表，使得优化器无法交换它们。
追问：如果换成 PostgreSQL / TiDB，JOIN 优化有什么不同？
- 回答要点 1 (PostgreSQL)：PostgreSQL 的优化器更成熟，除了 INLJ 和 Hash Join，还支持 Merge Join。Merge Join 要求两张表在 JOIN 键上都已排序，然后像拉链一样合并。如果数据已经有序（例如通过索引扫描得到），Merge Join 效率非常高。PG 的优化器会在这三种 JOIN 策略中选择成本最低的一个，通常不需要用户过多干预。
- 回答要点 2 (TiDB/分布式数据库)：在分布式数据库中，JOIN 优化增加了数据分布的维度。
  - Index Lookup Join: 类似单机 INLJ，通过驱动表数据去远程查询被驱动表，有网络开销。
  - Hash Join: 分为 Shuffle Hash Join（将两张大表按 JOIN 键重新哈希分布到不同节点再 JOIN，网络开销大）和 Broadcast Hash Join（将小表广播到所有节点，与大表在本地 JOIN，适合“小表驱动大表”场景）。
  - TiDB Colocated Join: 如果两张表按 JOIN 键进行了分片（Placement Rules），且分片位置相同，JOIN 就可以在本地完成，无需网络传输，是最高效的方式。
  - 结论：在分布式数据库中，除了考虑索引和结果集大小，还必须考虑数据亲和性，尽量避免跨网络的数据 shuffle。