Using join buffer (Block Nested Loop)是BNL退化的确诊信号，表明被驱动表正反复全表扫描；根本解法是修复索引缺失或连接条件失效问题，而非调大join_buffer_size。
EXPLAIN里看到Using join buffer (Block Nested Loop)就是退化信号
这不是警告，是确诊结果。只要EXPLAIN输出中被驱动表的Extra字段明确出现Using join buffer (Block Nested Loop)，就说明当前执行路径已退化为BNLJ——即被驱动表正在被反复全表扫描，且每次只处理一小批驱动表行。
常见误判点：
- 只看
type=ALL不够：type=ALL只表示被驱动表没走索引，但未必触发BNL；必须配合Using join buffer (Block Nested Loop)才成立 -
join_buffer_size调大了但没效果？说明根本没走到BNL路径，或者已经退化到更糟的Simple Nested-Loop（此时Extra里甚至不显示buffer提示） - 在
EXPLAIN FORMAT=JSON里找"using_join_buffer": "block nested loop"字段，比传统格式更可靠
为什么明明建了索引，EXPLAIN还是显示BNL？
索引建了≠能用上。BNL常因连接条件“表面合规、实际失效”而触发，核心是优化器判定索引不可用。
典型原因：
-
ON或WHERE中对连接字段用了函数，如ON UPPER(u.name) = UPPER(o.user_name)，导致索引无法命中 - 连接字段类型不一致，比如
user.id是BIGINT，order.user_id是VARCHAR，隐式转换让索引失效 - 复合索引顺序错位：被驱动表有索引
(status, user_id)，但ON只写了o.user_id = u.id，最左前缀未覆盖 - 统计信息过期：
ANALYZE TABLE order后Using join buffer (Block Nested Loop)消失，说明之前是优化器误判
BNL退化时，EXPLAIN和实际性能的矛盾点
有时候EXPLAIN看起来“还行”，但查询就是慢——这是BNL特有的陷阱。
关键矛盾：
MySQL8 CRM Schema Design Expert
下载
面向 CRM 系统的 MySQL 8 数据库模式设计。当用户需要为客户关系管理（CRM）系统设计、评审、优化或生成数据库模式时，应使用此项技能。
-
rows预估很小，但Extra有Using join buffer (Block Nested Loop)：说明驱动表过滤后确实小，但被驱动表仍被迫全扫，IO放大效应严重 -
key非空却仍有BNL提示：可能是联合索引中部分字段被WHERE过滤，但连接字段不在最左前缀，优化器放弃使用 -
STRAIGHT_JOIN强制顺序后BNL更明显：LEFT JOIN语法锁死驱动表，若左表大且无索引，BNL必然发生，无法绕过
此时Handler_read_next会飙升，表示被驱动表正在高频随机跳转扫描，不是顺序读。
怎么确认BNL正在真实消耗资源？
别只信EXPLAIN，要看运行时证据。
实操验证方式：
- 开启
optimizer_trace：SET optimizer_trace="enabled=on"; SELECT ... JOIN ...; SELECT * FROM information_schema.OPTIMIZER_TRACE\G，搜索use_join_cache和join_buffering确认是否启用及分配大小 - 监控状态变量：
SHOW STATUS LIKE 'Select_full_join'持续上升，说明BNL在高频发生；Handler_read_rnd_next增长快于Handler_read_first，暗示大量随机回表 - 查慢日志中的
Rows_examined：若远大于rows预估值 × 驱动表行数，基本可断定BNL造成IO爆炸
真正容易被忽略的是：BNL不是“算法选错了”，而是索引缺失或SQL写法把优化器逼到了墙角——它只能靠缓冲区硬扛，而不是你该去调join_buffer_size。
