查询基础（SELECT / JOIN / 聚合 / 排序） · JOIN 多表连接

什么是驱动表？如何判断哪个表应该作为驱动表？STRAIGHT_JOIN 的作用？

What is a driving table? How to determine which table should be the driving table? What is the

答案语言

考察要点

这道题面试官想看你是否真正理解了 SQL 的执行过程，而不只是停留在声明式的语法层面。核心考察点包括：

JOIN 的执行原理：特别是 MySQL 中主流的 Nested-Loop Join（嵌套循环连接）算法。
查询优化器（Optimizer）的成本估算：理解优化器是如何基于成本（Cost-Based Optimization）来决定表连接顺序的。
性能分析与干预能力：当优化器做出错误选择时，你是否知道如何诊断（EXPLAIN）并强制干预（STRAIGHT_JOIN）。

核心概念

1. 驱动表（Driving Table）与被驱动表（Driven Table）

在多表 JOIN 查询中，数据库执行引擎会选择一个表作为基础，遍历其中的每一行数据，然后根据 JOIN 条件去另一个表中查找匹配的行。

驱动表：被首先选择，作为外层循环的表。
被驱动表：根据驱动表的结果，进行匹配查找的表，作为内层循环的表。

这个过程可以用伪代码清晰地表示（以 Nested-Loop Join 为例）：

text

 1// SELECT * FROM t1 JOIN t2 ON t1.id = t2.t1_id;
 2// 假设 t1 是驱动表，t2 是被驱动表
 3
 4FOR each row r1 IN t1 {
 5  // 在被驱动表 t2 中查找匹配的行
 6  FOR each row r2 IN t2 {
 7    IF r1.id == r2.t1_id {
 8      // 找到匹配，返回组合行
 9      return (r1, r2);
10    }
11  }
12}

2. 优化器如何选择驱动表

MySQL 的查询优化器遵循成本最优原则，通常会选择**“小表驱动大表”**来最小化总查询成本（主要是 I/O 和 CPU 消耗）。

这里的“小”和“大”不是指表的总行数，而是指经过 WHERE 条件过滤后，参与 JOIN 操作的中间结果集的大小。

判断原则：优化器会估算不同连接顺序的成本，选择成本最低的方案。 总成本 ≈ 驱动表扫描成本 + (驱动表行数 * 被驱动表单次查询成本)

示例： 假设有两张表：

orders (订单表, 1 亿行)
users (用户表, 100 万行)

场景一：查询北京地区用户的订单

sql

 1SELECT *
 2FROM orders o
 3JOIN users u ON o.user_id = u.id
 4WHERE u.city = '北京';

Plan A (users 驱动 orders)：
1. users 表经过 WHERE u.city = '北京' 过滤，假设结果集很小（如 1 万行）。
2. 对这 1 万行 users 记录，每次都去 orders 表中查找（假设 o.user_id 有索引）。
3. 成本 ≈ Cost(scan users where city='北京') + 10000 * Cost(lookup orders by user_id)。
Plan B (orders 驱动 users)：
1. 扫描 orders 表（1 亿行）。
2. 对这 1 亿行 orders 记录，每次都去 users 表中查找（假设 u.id 是主键）。
3. 成本 ≈ Cost(scan orders) + 100000000 * Cost(lookup users by id)。

显然，Plan A 的成本远低于 Plan B。因此，优化器会选择 users 表作为驱动表。

3. `JOIN` 的实现算法

MySQL 主要使用 Nested-Loop Join 的变体：

算法	全称	何时使用	核心思想
INLJ	Index Nested-Loop Join	被驱动表的 `JOIN` 关联列上有索引。	`For each row in Driving Table, use index to lookup in Driven Table.` 这是最高效的方式。
BNLJ	Block Nested-Loop Join	被驱动表的 `JOIN` 关联列上没有索引。 (MySQL 8.0.18 前)	`Load a block of rows from Driving Table into join_buffer, then full scan Driven Table to find matches.` 性能较差。
Hash Join	Hash Join	MySQL 8.0.18+ 引入，用于替代 BNLJ。	`Build a hash table on the smaller (driven) table in memory, then stream the larger (driving) table and probe the hash table for matches.` 在无索引时远胜 BNLJ。

关键： 保证被驱动表的 JOIN 列上有索引，是 JOIN 优化的第一要务，因为它能让执行计划走上最高效的 INLJ。

4. STRAIGHT_JOIN 的作用

STRAIGHT_JOIN 是一个 SQL 提示（Hint），用于强制指定 JOIN 的连接顺序。

语法：FROM t1 STRAIGHT_JOIN t2 ON ...

作用：告诉优化器，必须以 t1 作为驱动表，t2 作为被驱动表。它剥夺了优化器自主选择连接顺序的权力。

使用场景：当优化器因为统计信息不准或查询过于复杂而做出了错误的判断时，可以使用 STRAIGHT_JOIN 人为干预，强制使用你认为更优的执行计划。但这通常是最后的手段。

常见低分回答

“小表驱动大表就是行数少的表驱动行数多的表。”
- 错误：忽略了 WHERE 条件的过滤效果。“小”指的是过滤后的结果集大小，而非原始表大小。这是最常见的误解。
“写在 JOIN 左边的就是驱动表。”
- 错误：对于 INNER JOIN，表的书写顺序不决定执行顺序。优化器会根据成本自由选择。只有 LEFT JOIN 和 RIGHT JOIN 会因为语义限制而固定驱动表（LEFT JOIN 的左表一定是驱动表）。
“STRAIGHT_JOIN 可以让查询变快。”
- 不准确：STRAIGHT_JOIN 只是一个强制工具。如果你的判断比优化器更准，它能让查询变快；如果你的判断是错的，它会让查询变得更慢。它本身不是一个优化器。
混淆 LEFT JOIN 和 INNER JOIN 的驱动表选择机制。
- 错误：INNER JOIN 的连接顺序由优化器决定。而 LEFT JOIN 为了保证左表数据全部存在，必须将左表作为驱动表。RIGHT JOIN 则必须将右表作为驱动表。

工程实践与优化

使用 EXPLAIN 分析执行计划 EXPLAIN 输出结果中，排在第一行的表就是驱动表。通过观察表的顺序，可以判断优化器的选择是否符合预期。
sql
```
 1EXPLAIN SELECT * FROM orders o JOIN users u ON o.user_id = u.id WHERE u.city = '北京';
```
如果 users 表出现在第一行，说明优化器选择了 users 作为驱动表。
确保 JOIN 列有索引 这是最重要的优化手段。必须为被驱动表的 JOIN 关联列创建索引，以启用高效的 INLJ 算法，避免低效的 BNLJ 或 Hash Join。对于外键关系，数据库通常会自动创建索引。
更新统计信息 如果发现优化器选择了错误的驱动表，首先应考虑是不是表的统计信息过时了。可以执行 ANALYZE TABLE <table_name>; 来更新统计信息，让优化器能做出更准确的成本估算。
谨慎使用 STRAIGHT_JOIN STRAIGHT_JOIN 是一种“硬编码”，会使 SQL 语句对数据分布的变化非常敏感。今天你强制的顺序是优的，明天数据量变化后可能就变成了最差的。
- 优先：通过加索引、改写 SQL、更新统计信息来引导优化器。
- 最后：在上述方法都无效，且你明确知道当前最优执行计划的情况下，才考虑使用 STRAIGHT_JOIN 作为临时或最终解决方案。

常见追问与延伸题目

追问：LEFT JOIN 的驱动表是固定的吗？为什么？
- 回答要点：是的，LEFT JOIN 的驱动表一定是左边的表。这是由 LEFT JOIN 的语义决定的。LEFT JOIN 的目的是返回左表的所有行，即使在右表中没有匹配的行。为了实现这个目的，执行器必须遍历左表的每一行，然后尝试去右表查找匹配。如果以右表为驱动表，就无法保证左表的行在右表无匹配时也能被包含在结果集中。RIGHT JOIN 同理，右表必须是驱动表。
追问：如果我有一个 INNER JOIN，优化器选错了驱动表，除了 STRAIGHT_JOIN 还有什么办法“诱导”它？
- 回答要点：
  - 加索引/删索引：最直接的方式。给期望的被驱动表的 join key 加上索引，或者去掉干扰优化器判断的“坏”索引。
  - 改写 SQL：有时复杂的子查询或 OR 条件会让优化器估算不准。可以尝试将 IN 子查询改写为 JOIN，或者用 UNION ALL 替代 OR。
  - 使用优化器提示（Optimizer Hints）：MySQL 8.0 提供了更丰富的 Hint 语法，如 JOIN_ORDER、NO_JOIN_ORDER，可以更精细地控制连接顺序，比 STRAIGHT_JOIN 更灵活。
  - ANALYZE TABLE：如前所述，更新统计信息，给优化器最新的“情报”。
追问：MySQL 8.0 引入了 Hash Join，它对驱动表的选择有什么影响？
- 回答要点：Hash Join 主要是在被驱动表上没有可用索引时替代 BNLJ。它的执行过程分为两个阶段：
  1. Build Phase：选择两个表中较小（过滤后）的一个作为“构建表”（Build Table），在内存中基于其 JOIN key 创建一个哈希表。
  2. Probe Phase：扫描另一个“探测表”（Probe Table），用每一行的 JOIN key 去哈希表中探测匹配项。
- 对驱动表选择的影响：这里的“构建表”和“探测表”的角色，类似于“被驱动表”和“驱动表”。优化器会选择过滤后结果集更小的表作为构建表（Build Table），因为它占用的内存更少。所以，选择逻辑依然是“小表驱动大表”，只不过这里的“驱动”变成了“探测”（Probe），“被驱动”变成了“构建”（Build）。Hash Join 的引入使得在无索引场景下的 JOIN 性能大幅提升，但优化器选择小结果集作为内层处理逻辑的核心思想没有改变。