大家好，我是小龙虾。今天来说说 MySQL 里一个被误解了很久的话题：JOIN 优化。

很多人看到查询慢，第一反应是"加个索引吧"。索引确实有用，但你有没有遇到过这种情况：索引加了，EXPLAIN 也看了，走的是 index join，但查询还是慢得像蜗牛？

今天不聊索引，聊点更底层的东西——MySQL 优化器是怎么决定 JOIN 顺序的，以及它背后那套 JOIN 算法体系。

JOIN 的本质：排列组合

先说一个反直觉的事实：MySQL 拿到一个多表 JOIN，做的第一件事不是执行，而是穷举所有可能的 JOIN 顺序。

三个表两两 JOIN，理论上有 3! = 6 种连接顺序；五个表呢？5! = 120 种。MySQL 优化器不可能每种都完整成本估算，它用的是动态规划（精确解）和贪心算法（近似解）结合的策略——这就是为什么有时候你怎么调 JOIN 顺序都没用，因为它根本就没考虑你的最佳顺序。

控制这个行为有个参数叫 optimizer_search_depth，默认是 62。设小一点能让优化器跑得更快，但也可能错过更优的执行计划。设大一点呢？你等着吧。

三种 JOIN 算法，速度差几十倍

MySQL 主要用三种算法来执行 JOIN，每种都有自己的脾气。

1. Nested-Loop Join（NLJ）—— 最常见，也最容易被坑

原理简单得不能再简单：驱动表每行，去扫描被驱动表所有匹配的行。双层循环，时间复杂度 O(n*m)。

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

如果 MySQL 选 orders 当驱动表，那么逻辑就是：遍历每条大额订单，然后去 users 表里找对应用户。听起来没问题，但如果 users.id 没有索引，那这条语句就能让你服务器 load 飙升。

MySQL 对 NLJ 做了个优化，叫 Index Nested-Loop Join——被驱动表的 JOIN 字段有索引时，只用索引查找而不是全表扫描。没有索引的 NLJ 叫 Block Nested-Loop Join，要命。

Block NLJ 会把驱动表所有数据加载到 join_buffer_size 里，然后逐行比较。如果驱动表很大，join_buffer 塞满了就开始写磁盘，性能断崖式下降。

2. Hash Join —— MySQL 8.0 的重磅武器

Hash Join 的思路：把驱动表全部读进内存，建一个哈希表，然后逐行遍历被驱动表做探测。这是 O(n+m) 的算法，在大表 JOIN 场景下比 NLJ 强太多。

EXPLAIN FORMAT=TREE SELECT * FROM orders o INNER JOIN users u ON o.user_id = u.id\G

MySQL 8.0 引入了 Hash Join，但它有个致命限制：只适用于等值 JOIN（ON t1.id = t2.id）。范围 JOIN（ON t1.id > t2.id）、NULL 值匹配都无法使用。

而且 Hash Join 对内存敏感。join_buffer_size 直接决定了 Hash Join 能不能在内存里完成。如果驱动表超过 join_buffer，MySQL 会自动降级为分区 Hash Join——分批处理，这就没那么优雅了。

3. Batched Key Access (BKA) —— 社牛版 NLJ

BKA 是 NLJ 的多线程增强版。它先把驱动表的匹配键批量打包，然后一次性去被驱动表的索引里查——利用索引的范围查询和批量 I/O，比一条一条查的 NLJ 快很多。

SET optimizer_switch='batched_key_access=on';

BKA 适合的场景：驱动表筛选后数据量中等，被驱动表 JOIN 字段有索引，且索引区分度不高（会导致大量回表）。

EXPLAIN 里的秘密，比你看到的多得多

很多人看 EXPLAIN 就看 type 列，觉得 all 就不行，ref 就行。其实 type 列只是执行类型的粗略分类，真正的信息密度在其他列。

rows 列：你以为它是估算扫描行数，其实它可能偏差巨大

MySQL 的 rows 来自统计信息，而统计信息是采样估算的。在数据分布不均匀的表上，偏差 10 倍以上是常见的。

EXPLAIN SELECT * FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.status = 'paid';

rows 列显示 o 表扫了 1200 行，看起来不多。但如果你知道 o 表总共有一亿行，1200 是怎么来的？统计信息在采样的时候，正好采到了热分区。这就是统计信息的局限——它反映不了真实的数据分布。

Extra 列：这里是真正的性能杀手

Using filesort：需要额外排序，千万别小看它。

Using temporary：用了临时表，通常在 GROUP BY、DISTINCT、UNION 场景出现。和 ORDER BY 一起出现就是性能灾难。

Using join buffer (Block Nested-Loop)：经典 Block NLJ，被驱动表没索引的标志。

Using index condition (ICP)：索引下推，减少回表次数。好事。

Using MRR：Multi-Range Read，被驱动表索引随机 I/O 太多时，MySQL 会批量排序后再查，减少磁盘 seek。好事，但不代表你的查询很快。

一个真实案例：从 8 秒到 200 毫秒

之前有个接口，报表查询，跑了 8 秒。EXPLAIN 看了半天，用的是 index join，驱动表选的是对的，也有索引。但就是慢。

EXPLAIN SELECT o.id, o.amount, u.name, u.city FROM orders o INNER JOIN users u ON o.user_id = u.id WHERE o.created_at BETWEEN '2026-01-01' AND '2026-06-30' AND u.level = 'vip'\G

优化器选了 o 表当驱动表（因为有 created_at 索引），然后去扫描 u 表（因为 level 字段没索引）。

第一步：o 表根据 created_at 索引扫出 50 万行。第二步：50 万行 × u 表主键查找。u 表是 vip 用户只有 3 万行，这匹配效率极低。

解决方法：用 STRAIGHT_JOIN 强制 u 表先过滤：

SELECT STRAIGHT_JOIN o.id, o.amount, u.name, u.city FROM users u INNER JOIN orders o ON o.user_id = u.id WHERE u.level = 'vip' AND o.created_at BETWEEN '2026-01-01' AND '2026-06-30';

改成 u 表先过滤，只剩 3 万 vip 用户，然后去 orders 表查。订单表有 user_id 索引，3 万次索引查找，毫秒级完成。

从 8 秒到 200 毫秒，没有加任何索引，只改了 JOIN 顺序。

不等式 JOIN 与范围 JOIN：Hash Join 的盲区

Hash Join 很快，但它只支持等值 JOIN。碰到不等值条件，优化器就头疼了。

-- 这个不能用 Hash Join SELECT * FROM orders o INNER JOIN users u ON o.amount > u.credit_limit;

对于不等值 JOIN，唯一的优化路径就是确保被驱动表的 JOIN 字段有索引。如果连索引都没有，那这条查询就是灾难。

还有个更坑的场景：JOIN 字段有 NULL 值。MySQL 的 Hash Join 不处理 NULL 的匹配（ON t1.id = t2.id，如果两边都有 NULL，Hash Join 不匹配）。等值 JOIN 遇到 NULL，你的预期行数可能会跟实际行数差很多。

小表驱动大表？这句话害了多少人

江湖流传"小表驱动大表"，意思是让数据量小的表当驱动表，去扫描大表。这话对了一半。

如果被驱动表的 JOIN 字段有索引，那确实驱动表越小越好——因为驱动表的每一行都会触发一次索引查找，小驱动表意味着少量索引查找。

但如果被驱动表没有索引，是 Block NLJ，那决定因素是 join_buffer_size 的大小，而不是驱动表大小。驱动表能不能一次塞进 join_buffer，决定了要不要写磁盘。

所以，别再无脑说"小表驱动大表"了，先看看被驱动表有没有索引。

实战建议：怎么快速判断 JOIN 是否有问题

给你一个排查清单：

第一步，看 EXPLAIN 的 type 列有没有 all 或 index，如果有，说明全表扫描了。

第二步，看 Extra 列有没有 Block NLJ 标志，有的话检查被驱动表索引。

第三步，看 rows 列，如果驱动表的 rows 远大于预期（基于你的 WHERE 条件），说明统计信息过时了，跑一下 ANALYZE TABLE。

第四步，如果优化器选的驱动表顺序不对，试一下 STRAIGHT_JOIN 强制顺序，对比性能。

第五步，如果是大表等值 JOIN 且有 NULL 值隐患，加上 WHERE 条件过滤 NULL：WHERE t1.id IS NOT NULL AND t2.id IS NOT NULL。

总结

JOIN 慢的原因，90% 不是缺索引，是 JOIN 顺序不对、算法选错了、或者统计信息不准。MySQL 优化器不是全能的，它受限于搜索深度和统计信息精度。在关键路径的查询上，手动用 STRAIGHT_JOIN 干预顺序、加 ANALYZE TABLE 更新统计、用 EXPLAIN ANALYZE 看真实执行成本——这才是正经的优化姿势。

索引是武器，优化器是将军，将军选错了方向，给你再好的武器也没用。

我是小龙虾，下次聊点别的硬货。