从 BNL 到 hash join：一次 join 提速 20 倍的升级

一个核心报表查询 join 两张大表，5.7 时代跑了 42 秒，迁到 8.0 之后同一个 SQL 只跑 2.1 秒，SQL 和索引都没动。团队一开始以为是新机器的功劳，EXPLAIN FORMAT=TREE 一看，执行计划里赫然写着 Inner hash join——不是硬件，是优化器换代了。

5.7 处理无索引可用的 join 靠 Block Nested Loop（BNL）：把驱动表按 join_buffer_size 分块装进内存，被驱动表全表扫，逐块比对。join_buffer_size 默认 256KB，大表场景下被驱动表要被反复扫几十上百遍，代价是乘法级的。8.0.18 引入 hash join：用驱动表在内存里建一张哈希表，被驱动表只扫一遍，逐行探测哈希表。复杂度从"多遍扫描"降为"一遍扫描加哈希探测"，这就是那 20 倍的来源。

复现一下计划的差异：

-- 两表 join，关联列上都没有索引
EXPLAIN FORMAT=TREE
SELECT COUNT(*)
FROM t_order o
JOIN t_user_ext e ON o.uid = e.uid
WHERE o.create_time >= '2026-10-01';

-- 5.7：Block nested loop，被驱动表反复扫描
-- 8.0：Inner hash join (cost=...)
--     -> Table scan on o
--     -> Hash (rows=8000000)
--         -> Table scan on e

但 hash join 不是万能护身符。建哈希表的一侧要能装进 join_buffer_size，装不下就分批做，增加开销；等值条件才支持，非等值 join 还是老路子。我们在 8.0 上实测调整 join_buffer_size 从默认 256KB 到 16MB，那张报表又快了 30%，但再往上收益递减，内存占用却线性涨，最终定在 8MB。

升级后的回归测试也值得展开：把历史 SQL 全量抓下来（pt-query-digest 的 top 1000）在 8.0 环境回放，比对执行计划变化。我们发现 97% 的语句持平或变好，3 条劣化的都和子查询改写策略有关，用 optimizer_switch 关掉对应特性解决。版本升级的工程量一大半在这些边角。

这个案例还引出一个工程判断：hash join 是"没有索引时的兜底"，有索引的等值 join 依然首选 ref/eq_ref 计划。升级 8.0 后更重要的动作是重新审视那些"因为慢而绕开 join 拆成多次查询"的老代码——优化器进步之后，有些拆分反而不如合回去。当年为绕 BNL 而设计的应用层 join，现在成了新的性能瓶颈。