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

MySQL JOIN 优化NLJ 与 BNL 的区别与实战两个表 join 查询突然变慢是很多后端同学都遇到过的问题。本文通过一个真实的慢查询案例讲清楚 MySQL 两种 join 算法NLJ 与 BNL的原理以及如何把一次查询从 8 秒优化到 30 毫秒。一、问题案例一个订单列表接口从 100ms 飙到 8 秒SQL 就一句 joinSELECTo.*,u.nicknameFROMorders oJOINusers uONo.user_idu.idWHEREo.created_at2026-08-01;两个表的 join 字段都有索引EXPLAIN的type也不是ALL但Extra里有一行关键信息——Using join buffer (Block Nested Loop)。二、原理NLJ 与 BNLMySQL 执行 join 有两种算法NLJIndex Nested-Loop Join驱动表逐行取出被驱动表走索引匹配。复杂度约为驱动表行数 × log(被驱动表行数)。BNLBlock Nested-Loop Join当被驱动表没有可用索引时MySQL 把驱动表的行读入 join buffer再对被驱动表做全表扫描逐行比对。复杂度约为驱动表行数 × 被驱动表行数。回到案例orders几百万行users几十万行。优化器错误地选了users当驱动表去 join 没有索引的orders触发了 BNL几十万 × 几百万8 秒由此而来。三、优化方法两条原则小表驱动大表把行数少的表放在前面作为驱动表。被驱动表的 join 字段必须有索引。SELECTo.*,u.nicknameFROMusers u-- 小表驱动JOINorders oONo.user_idu.id-- orders.user_id 建索引WHEREo.created_at2026-08-01;如果优化器仍选错顺序可用STRAIGHT_JOIN强制SELECTo.*,u.nicknameFROMusers u STRAIGHT_JOIN orders oONo.user_idu.idWHEREo.created_at2026-08-01;优化后查询从 8 秒降到 30 毫秒。四、为什么优化器会选错优化器依赖统计信息估算成本当表的统计信息行数、区分度过期时估算就会失真。这也是为什么EXPLAIN里的rows只能作参考——必要时记得ANALYZE TABLE更新统计信息。五、总结join 慢先看EXPLAIN的Extra出现Block Nested Loop就是被驱动表没走索引。优化方向小表驱动大表 被驱动表 join 字段建索引。优化器选错顺序时用STRAIGHT_JOIN强制。我是无羡小剑全栈偏后端的独立开发者。作品集无羡 · 独立开发者作品集如果对你有帮助欢迎点赞、收藏、关注。