执行计划进阶：JOIN 顺序与驱动表选择

一条三表 JOIN 的慢查询，加索引之后可能依然很慢。原因往往不在索引本身，而在优化器挑选了错误的驱动表或者 JOIN 顺序——一旦外层产生的结果集远大于预期，内层的索引再好，也只是被执行了更多次。

优化器是怎么挑 JOIN 顺序的

对于一张 join 涉及到的表，可选的执行顺序数量随表数呈阶乘增长。优化器不会穷举所有组合，通常采用基于成本的贪心搜索（MySQL 的实现带有深度限制），对每个候选顺序估算成本，取最小者。

成本估算依赖两个东西：每张表的行数与每个条件过滤后的行数比例。这两项都来自统计信息：

-- 查看某张表的统计信息
SHOW INDEX FROM orders;

*************************** 1. row ***************************
        Table: orders
   Non_unique: 0
     Key_name: PRIMARY
  Cardinality: 982344          <-- 优化器认为的基数
     Sub_part: NULL

-- InnoDB 的持久化统计信息表
SELECT table_name, index_name, stat_value AS rows, 
       (SELECT n_rows FROM mysql.innodb_table_stats t 
        WHERE t.table_name='orders') AS table_rows
FROM mysql.innodb_index_stats 
WHERE table_name = 'orders' AND stat_name = 'n_diff_pfx01';

Cardinality 是估算值，InnoDB 通过随机采样若干索引页来推算，采样页数很小。当数据分布倾斜或表刚经历大批量写入时，这个估算与实际值可能相差一个数量级。估算错 → 成本算错 → JOIN 顺序选错。

三种 JOIN 算法的适用边界

Nested Loop Join

最朴素的双重循环：外层取一行，拿 join key 去内层查找。

for row_o in outer_table:          # 扫描 N 次
    for row_i in inner_index(row_o.join_key):   # 每次 O(log M) 或常数级
        if match: emit

当内层表在 join 列上有索引时，单次探测代价接近常数，总代价约为 N × 单点查找成本。这决定了它的适用条件：外层结果集要小，内层要有可用索引。如果内层没有索引，退化成全表扫描，总代价变成 N × M，灾难由此产生。

MySQL 有 Block Nested Loop 的变体：把外层的一批行放进 join buffer，再去内层做一次批量比较，减少内层扫描次数。

Hash Join

把较小的那张表在内存里建成哈希表，扫描较大的表并对每一行做哈希探测。

构建阶段：扫描 build 表，按 join key 建哈希表      O(N_build)
探测阶段：扫描 probe 表，每行算哈希，命中则输出     O(N_probe)

总代价约为 O(N_build + N_probe)，与 Nested Loop 相比不需要内层索引，在全表关联大结果集时有明显优势。代价是不能利用索引的有序性，且哈希表超过内存额度时需要落盘分区。

Merge Join

两侧都按 join key 有序时，用两个游标交替前推进：

while not exhausted(outer) and not exhausted(inner):
    if outer.key == inner.key: emit; advance both
    elif outer.key < inner.key: advance outer
    else: advance inner

代价约 O(N log N + M log M)（含排序）。如果两侧本来就有索引可以提供有序性，排序成本为零，效率很高。

驱动表怎么选

经验法则是：让结果集更小的表做驱动表（外层），让被驱动表上有索引。

原因是 Nested Loop 的总开销正比于外层行数。把外层从 100 万行换成 1000 行，即使每次探测成本相同，总开销也降低三个数量级。

优化器会自己算这件事，但它算的前提是有可靠的统计信息。可以用下面这条 SQL 实际对比两个方向的成本：

EXPLAIN FORMAT=JSON
SELECT u.name, o.amount
FROM orders o
JOIN users u ON u.id = o.user_id
WHERE o.status = 1 AND u.city = 3G

需要人工干预时，MySQL 提供了几个手段：

-- 强制 JOIN 顺序（先扫 o 再扫 u）
SELECT /*+ JOIN_ORDER(o, u) */ u.name, o.amount FROM orders o JOIN users u ON ...;

-- 强制某张表使用某个索引
SELECT u.name FROM users u FORCE INDEX (idx_city) WHERE u.city = 3;

-- 提示派生表/子查询优先合并还是物化
SELECT /*+ MERGE(dt) */ ... 
SELECT /*+ NO_MERGE(dt) */ ...

SELECT * FROM t STRAIGHT_JOIN t2 ON t.a = t2.a;   -- 强制按书写顺序 JOIN

hint 是把双刃剑。它的有效期只到数据分布下一次显著变化为止，之后很可能变成负优化。写 hint 时应留下注释说明当时的数据特征，否则半年后没人敢删也没人敢留。

逐列读一份真实的 EXPLAIN

EXPLAIN SELECT u.name, o.amount, p.title
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
WHERE o.created_at >= '2026-01-01'
  AND u.city = 7
ORDER BY o.created_at DESC LIMIT 50G

输出（节选）：

*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: u
         type: ref
possible_keys: PRIMARY, idx_city
          key: idx_city
      key_len: 4
          ref: const
         rows: 1200
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
        table: o
         type: ref
          key: idx_user_created
      key_len: 8
          ref: test.u.id
         rows: 45
     filtered: 33.33
        Extra: Using index condition
*************************** 3. row ***************************
        table: p
         type: eq_ref
          key: PRIMARY
      key_len: 8
          ref: test.o.product_id
         rows: 1
     filtered: 100.00

逐列含义：

• id：都是 1，说明这是一条简单查询，没有子查询或 UNION 需要分层执行。出现不同 id 时，值越大越先执行。
• select_type：SIMPLE 表示非子查询。DEPENDENT SUBQUERY 意味着子查询依赖外层每一行执行一次，通常是需要改写的信号。
• table 的出现顺序就是 JOIN 顺序。这里优化器选了 u -> o -> p，即 users 作为驱动表。
• type：从差到好依次为 ALL（全表扫描）< index（全索引扫描）< range（范围）< ref（非唯一索引等值）< eq_ref（唯一索引，最多命中一行）< const（主键/唯一索引常量比较）。这里的 u 是 ref，p 是 eq_ref，o 是 ref——没有 ALL，说明所有表都走了索引。
• key_len：实际用到的索引字节数。idx_city 若为两字段复合索引且 key_len 只显示一个字段的长度，说明第二个字段没被用上。key_len = 8 且列是 BIGINT 则刚好用满一个字段。
• ref：const 表示与常量比较；test.u.id 表示用了上一行结果里的列做比较——这是驱动关系的直接证据。
• rows：优化器估算的检查行数，不是返回行数。这是最关键的一列。第二行 45 表示针对 users 表的每一行预估要检查 orders 表里 45 行。
• filtered：存储引擎返回的行中，满足条件剩余比例的估算。第二行 33.33 表示这 45 行里预计只有三分之一能通过 created_at 条件。实际参与下一次 join 的行数估算是 rows × filtered%。
• Extra：Using index condition 表示启用了索引条件下推；Using temporary; Using filesort 出现在第一行，说明外层结果需要临时表加排序。

这份计划里有一点值得深究：LIMIT 50 加 ORDER BY o.created_at DESC，但排序字段来自第二张表。优化器必须先把 u × o 的 join 结果物化出来再排序，无法利用 idx_user_created 的有序性提前终止扫描。如果 users 表的 city=7 过滤出 1200 行，每行对应 45 行 orders，中间结果就是 5 万多行，全部要写出来参与排序。这种情况下改写成先从 orders 侧取够 50 行往往更快：

SELECT u.name, o.amount, p.title
FROM (
  SELECT id, user_id, product_id, amount, created_at
  FROM orders
  WHERE created_at >= '2026-01-01'
  ORDER BY created_at DESC LIMIT 200     -- 先按有序索引截断
) o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
WHERE u.city = 7
LIMIT 50;

内层利用 (created_at) 索引的有序性直接跳过排序，只处理 200 行候选。代价是结果可能不足 50 行需要二次处理，业务侧需要接受这个语义变化。

统计信息过期与处理

ANALYZE TABLE orders;       -- 重新采样统计信息

SHOW VARIABLES LIKE 'innodb_stats_persistent_sample_pages';   -- 采样页数
SET GLOBAL innodb_stats_persistent_sample_pages = 40;         -- 提高采样精度

采样页数提高会让 ANALYZE 变慢，但估算更准。对于数据分布严重倾斜的列，更有效的手段是直方图：

ANALYZE TABLE orders UPDATE HISTOGRAM ON status WITH 10 BUCKETS;
SELECT histogram FROM information_schema.column_statistics 
WHERE table_name = 'orders' AND column_name = 'status';

直方图存储的值分布能让优化器知道 status=9 只占 0.1%，而不是按均匀分布猜测。

子查询：物化与半连接

IN (SELECT ...) 这类子查询有几条不同的执行路径：

• MATERIALIZATION：先执行子查询把结果写进临时表去重建索引，再 join。子查询不相关时收益明显。
• 半连接（FirstMatch / LooseScan / DuplicateWeedout）：找到第一个匹配就停止，不需要穷举所有匹配。EXPLAIN 的 Extra 里可能出现 Using first match。
• DEPENDENT SUBQUERY：correlated subquery，外层每一行都要重跑一遍子查询。这是最糟的情况，通常应该改写成 JOIN。

下面这类相关子查询外层每行触发一次：

SELECT name FROM users u 
WHERE EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id AND o.amount > 100);

改写为半连接形式：

SELECT u.name FROM users u JOIN orders o ON o.user_id = u.id 
WHERE o.amount > 100;

两者的去重代价类似，优化器经常规划成同一个计划。真正决定性能的是外层行数与内层能否走索引，而不是写法本身。用 EXPLAIN FORMAT=JSON 的 query_plan 能确认优化器选了哪种策略。

统计信息准确、索引可用、驱动关系合理，这三者决定了绝大多数 JOIN 查询的性能上限。剩下的部分交给优化器，避免在它算得对的地方用 hint 强行接管。