企业官网建设流程全解析

MySQL JOIN 优化：NLJ 与 BNL 的区别与实战

两个表 join 查询突然变慢，是很多后端同学都遇到过的问题。本文通过一个真实的慢查询案例，讲清楚 MySQL 两种 join 算法（NLJ 与 BNL）的原理，以及如何把一次查询从 8 秒优化到 30 毫秒。

一、问题案例

一个订单列表接口，从 100ms 飙到 8 秒，SQL 就一句 join：

SELECTo.*,u.nicknameFROMorders oJOINusers uONo.user_id=u.idWHEREo.created_at>='2026-08-01';

两个表的 join 字段都有索引，EXPLAIN的type也不是ALL，但Extra里有一行关键信息——Using join buffer (Block Nested Loop)。

二、原理：NLJ 与 BNL

MySQL 执行 join 有两种算法：

NLJ（Index Nested-Loop Join）

驱动表逐行取出，被驱动表走索引匹配。复杂度约为驱动表行数 × log(被驱动表行数)。

BNL（Block Nested-Loop Join）

当被驱动表没有可用索引时，MySQL 把驱动表的行读入 join buffer，再对被驱动表做全表扫描逐行比对。复杂度约为驱动表行数 × 被驱动表行数。

回到案例：orders几百万行，users几十万行。优化器错误地选了users当驱动表，去 join 没有索引的orders，触发了 BNL，几十万 × 几百万，8 秒由此而来。

三、优化方法

两条原则：

1. 小表驱动大表：把行数少的表放在前面作为驱动表。
2. 被驱动表的 join 字段必须有索引。

SELECTo.*,u.nicknameFROMusers u-- 小表驱动JOINorders oONo.user_id=u.id-- orders.user_id 建索引WHEREo.created_at>='2026-08-01';

如果优化器仍选错顺序，可用STRAIGHT_JOIN强制：

SELECTo.*,u.nicknameFROMusers u STRAIGHT_JOIN orders oONo.user_id=u.idWHEREo.created_at>='2026-08-01';

优化后，查询从 8 秒降到 30 毫秒。

四、为什么优化器会选错

优化器依赖统计信息估算成本，当表的统计信息（行数、区分度）过期时，估算就会失真。这也是为什么EXPLAIN里的rows只能作参考——必要时记得ANALYZE TABLE更新统计信息。

五、总结

• join 慢，先看EXPLAIN的Extra，出现Block Nested Loop就是被驱动表没走索引。
• 优化方向：小表驱动大表 + 被驱动表 join 字段建索引。
• 优化器选错顺序时，用STRAIGHT_JOIN强制。

我是无羡（小剑），全栈偏后端的独立开发者。

作品集：无羡 · 独立开发者作品集

如果对你有帮助，欢迎点赞、收藏、关注。