什么是MySQL JOIN查询的驱动表和被驱动表？

在《在连表查询场景下，MySQL隐式转换存在的坑》一文中，我们讲到，当进行连表查询时，如果关联类型使用不当会发生隐式转换，MySQL 查询优化器会根据索引可用性调整驱动表和被驱动表的选择顺序。这里涉及到驱动表和被驱动表两个概念。这篇文章我们重点讲解这两个概念的运用。

在数据库查询优化中，驱动表和被驱动表是两种重要的概念，主要与JOIN 查询 的执行顺序有关。它们描述的是在多表连接 (JOIN) 操作中，MySQL 优化器选择的查询顺序和策略：查询从哪张表开始检索数据（驱动表），以及它如何依赖另一张表来查询（被驱动表）。

1. 驱动表和被驱动表的定义

驱动表（Driving Table）：

驱动表是 MySQL 优化器选择的 优先查询的表。查询先从驱动表中检索数据，再根据驱动表的结果与被驱动表进行关联。驱动表通常记录较少、匹配数据更精准，或能利用索引高效查找的数据表。

被驱动表（Driven Table）：

被驱动表是 MySQL 优化器选择的 后查询的表，在数据关联阶段依赖驱动表的中间结果。驱动表提供关联条件后，被驱动表通过这些条件确认匹配的数据。

2. 驱动表与被驱动表的关系

驱动表与被驱动表通常由 JOIN 查询 的优化策略决定。例如以下查询：

SELECT * FROM tableA JOIN tableB ON tableA.id = tableB.id;

这里 tableA 和 tableB 之间的连接关系可能由以下因素决定哪张表是驱动表：

1. 数据量： 优化器倾向选择数据量较小的表作为驱动表，以减少查询的匹配数据范围。
2. 索引使用情况： 如果某张表的查询能够充分利用索引，通常会选择该表作为驱动表。
3. 过滤条件： 如果某张表有强过滤条件（如 WHERE 子句带有索引字段的过滤），优化器更倾向于优先查询并处理该表。

3. 驱动表和被驱动表的执行顺序

执行顺序

当 JOIN 查询两张表时：

1. MySQL 会先选一个表作为驱动表，先查询驱动表的记录。
2. 在查询驱动表的结果集后，逐条匹配被驱动表的数据。
3. 最终返回满足连接条件的数据结果。

影响更明显的场景

• 在 嵌套循环连接（Nested Loop Join） 中，驱动表是最先遍历的表，而被驱动表会根据驱动表的结果逐条进行关联查询。
• 在 HASH JOIN 或 BLOCK-NESTED JOIN 中，驱动表通常用来构建哈希表或索引结构，被驱动表依赖这个结构进行匹配。

4. 举例说明

表结构和数据：

CREATE TABLE tableA (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE tableB (
    id INT PRIMARY KEY,
    value VARCHAR(50)
);

INSERT INTO tableA VALUES (1, 'Alice'), (2, 'Bob'), (3, 'Charlie');
INSERT INTO tableB VALUES (1, 'X'), (3, 'Y');

查询：

mysql> EXPLAIN SELECT * FROM tableA JOIN tableB ON tableA.id = tableB.id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tableB
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 2
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: tableA
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: article.tableB.id
         rows: 1
     filtered: 100.00
        Extra: NULL

分析结果：

• tableB 是驱动表，查询首先从 tableB 中提取所有记录。
• 原因：tableB 数据量较少，可能更适合优化器作为驱动表。
• tableA 是被驱动表，基于 tableB.id 的结果匹配 tableA.id。

5. 如何决定驱动表？

MySQL 优化器决定驱动表的依据包括以下因素：

1. 数据量

• 数据量较少的表通常成为驱动表，因为优先从这张表中提取少量记录，有助于简化后续查询。

2. 索引

• 能够利用索引的表通常作为驱动表，由于索引可以快速定位记录，降低查询成本。

3. 过滤条件

• 如果某张表有显著的过滤条件（如 WHERE），优化器倾向把这张表作为驱动表进行筛选。

4. JOIN 类型

• 在某些 JOIN 查询中（如 INNER JOIN），优化器会选择效率最高的表作为驱动表，无论连接的逻辑顺序如何。

6. 优化驱动表的选择

1. 使用 EXPLAIN

通过 EXPLAIN 分析查询，观察优化器选择的驱动表和被驱动表：

EXPLAIN SELECT * FROM tableA INNER JOIN tableB ON tableA.id = tableB.id;

2. 强制指定驱动表

优化器通常智能地选择驱动表，但在某些场景下你可以通过子查询、提示（HINTS）等显式指定驱动表。例如：

SELECT * FROM (SELECT * FROM tableA WHERE id = 1) AS tempA
JOIN tableB ON tempA.id = tableB.id;

3. 添加索引

在表上添加适当的索引，可以提高优化器选择作为驱动表的效率。

ALTER TABLE tableA ADD INDEX idx_id(id);
ALTER TABLE tableB ADD INDEX idx_id(id);

小结

总结一下：

• 驱动表是被优先扫描的表，在提供 JOIN 条件之前被查询。
• 被驱动表依赖驱动表的结果进行匹配查询。
• 驱动表选择影响查询性能，应优先确保驱动表数据量较小、索引使用率高。
• 通过 EXPLAIN 可以检查驱动表和被驱动表的选择，并进行针对性的优化。

全文完，感谢阅读，如果喜欢请三连。