我们在使用 MySQL 时，常常用到JOIN 与 IN 子查询。但二者底层实现原理、执行策略、资源消耗、优化器行为及性能表现存在显著差异。

文本以下将从多个维度对两者进行详细解析。

一、基础概念回顾

1. JOIN

JOIN 是关系代数中的基本操作，用于将两个或多个表按指定条件组合成一个结果集。常见的类型包括：

• INNER JOIN
• LEFT/RIGHT JOIN
• CROSS JOIN

2. IN 子查询

IN 子查询用于判断某字段的值是否存在于子查询返回的结果聚焦，例如：

SELECT * FROM users WHERE id IN (SELECT user_id FROM orders WHERE amount > 100);

二、MySQL 执行引擎与优化器基础

MySQL 使用 查询优化器（Query Optimizer） 对 SQL 进行重写、成本估算，并选择最优执行计划。其执行依赖于：

• 存储引擎（如 InnoDB）
• 统计信息（行数、索引基数等）
• 成本模型（I/O、CPU、内存）

执行过程大致为：

1. 语法解析 → 2. 语义分析 → 3. 查询重写 → 4. 生成执行计划 → 5. 执行器执行

三、JOIN 的实现原理（深度解析）

1. JOIN 的执行算法

MySQL 主要使用以下几种 JOIN 算法（取决于版本和配置）：

(1) Nested-Loop Join (NLJ)

• 原理：对驱动表（outer table）的每一行，在被驱动表（inner table）中逐行匹配。
• 适用场景：被驱动表有索引（尤其是连接字段有索引）。
• 执行流程：for each row in t1:
  use index on t2 to find matching rows
  output matched rows

• 优点：简单、内存占用低。
• 缺点：若被驱动表无索引，则退化为全表扫描 × N，性能极差。

(2) Block Nested-Loop Join (BNL)

• 引入缘由：当被驱动表无索引时，NLJ 效率低下。
• 原理：将驱动表的多行缓存到 join buffer 中，然后一次性与被驱动表比较。
• 关键参数：join_buffer_size
• 执行流程：while not end of t1:
  load block of rows from t1 into join buffer
  for each row in t2:
  compare with all rows in join buffer

• 缺点：仍需全表扫描被驱动表，且 join buffer 有限，大表性能差。

(3) Hash Join（MySQL 8.0.18+）

• 原理：对较小表构建哈希表（in-memory hash table），然后扫描大表，通过哈希查找匹配。
• 适用条件：
• 等值连接（=）
• 无索引或索引效率低
• 内存足够
• 优势：O(N + M) 时间复杂度，远优于 BNL。
• 限制：仅支持内连接（INNER JOIN）、部分外连接；不支持非等值连接。

注意：MySQL 5.7 及更早版本不支持 Hash Join，只能使用 NLJ/BNL。

2. 驱动表选择（Table Access Order）

优化器会根据成本估算选择小表作为驱动表（即外层循环表），以减少内层循环次数。可通过 EXPLAIN 查看 table 列的顺序。

四、IN 子查询的实现原理（深度解析）

1. 子查询分类

• 非相关子查询（Uncorrelated）：子查询可独立执行，不依赖外层查询。

SELECT * FROM t1 
 WHERE id IN (SELECT id FROM t2 WHERE status = 'A');

• 相关子查询（Correlated）：子查询引用外层表字段。

 SELECT * FROM t1 
 WHERE id IN (SELECT t2.id FROM t2 WHERE t2.ref = t1.ref);

2. MySQL 对 IN 子查询的处理策略（5.6+）

从 MySQL 5.6 开始，优化器引入了 Semi-Join 优化技术，将 IN/EXISTS 子查询转换为更高效的连接操作。

Semi-Join 语义

• 只关心外层行是否在子查询中存在匹配，不要求返回子查询的所有匹配行。
• 自动去重（即使子查询返回多个一样值，外层行只返回一次）。

Semi-Join 的实现策略（优化器自动选择）

可通过 EXPLAIN FORMAT=JSON 查看是否使用了 semijoin。

3. 物化（Materialization）详解

• 优化器将子查询结果一次性执行并存储到临时表中。
• 临时表会自动创建唯一索引（用于去重和快速查找）。
• 后续对外层表每行执行 IN 判断时，实际是 在临时表中做索引查找。
• 成本：物化本身有开销（CPU + 内存/磁盘），若子查询结果大，则成本高。

4. 相关子查询的处理

• 若无法转换为 Semi-Join，则退化为 Nested-Loop 执行：for each row in t1:
  execute subquery with t1's value
  if result not empty, output row

• 这相当于 N+1 查询问题，性能极差，尤其当 t1 很大时。

五、性能对比（详细场景分析）

场景1：子查询结果集小（< 1000 行），外层表大

场景2：子查询结果集大（> 10万行）

场景3：相关子查询

场景4：存在 NULL 值

• IN 子查询若包含 NULL，整个 IN 表达式结果为 UNKNOWN（即不匹配）：SELECT 1 IN (2, NULL); — 返回 NULL（逻辑上为 false)

• NOT IN 遇到 NULL 会返回空结果（常见陷阱）。
• JOIN 对 NULL 处理明确（如 LEFT JOIN 保留 NULL）。

提议：用 EXISTS 替代 IN/NOT IN 以规避 NULL 问题。

六、EXISTS vs IN vs JOIN 对比（补充）

一般提议：存在性判断用 EXISTS，需关联字段用 JOIN，避免 IN 用于大结果集或相关查询。

七、执行计划分析（EXPLAIN 示例）

示例1：IN 子查询（非相关）

EXPLAIN SELECT * FROM users 
WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000);

• 若优化器使用 Materialization：

"query_block": {
  "select_id": 1,
  "table": { "table_name": "users", ... },
  "attached_subqueries": [
    {
      "table": {
        "table_name": "<materialized_subquery>",
        "access_type": "eq_ref",
        "key": "<auto_key>"
      }
    }
  ]
}

示例2：JOIN

EXPLAIN SELECT u.* FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.amount > 1000;

• 一般显示 type: ref 或 range，若 orders.user_id 有索引。

八、配置与调优提议

九、总结：核心结论

最终提议：

1. 优先使用 JOIN：性能可控、功能强劲、优化器支持最好。
2. 谨慎使用 IN 子查询：
3. 仅用于小结果集的非相关存在性判断。
4. 避免 NOT IN，改用 NOT EXISTS。
5. 永远避免相关 IN 子查询，重写为 JOIN 或 EXISTS。
6. 务必使用 EXPLAIN 分析执行计划，确认是否使用了物化、Semi-Join、索引等。
7. 升级到 MySQL 8.0+：享受 Hash Join、更好优化器、窗口函数等现代特性。