被驱动表关联字段无索引时，nlj退化为全表扫描，mysql自动降级使用bnl算法；此时explain显示type=all、extra含“using join buffer”，本质是兜底而非优化，根本解法是添加匹配索引并确保驱动表最小化。
被驱动表的关联字段没索引，NLJ就退化成全表扫描
MySQL默认倾向用Index Nested-Loop Join（NLJ），但前提是被驱动表的ON字段必须有可用索引。一旦缺失，优化器会自动降级为Block Nested-Loop Join（BNL）——这不是“升级”，而是无奈兜底。
常见错误现象：EXPLAIN里看到被驱动表的type是ALL，Extra列出现Using join buffer (Block Nested Loop)，同时rows值极大。
- 索引必须覆盖
ON条件中的字段，且类型严格一致（比如INT对INT，不能INT对VARCHAR隐式转换） - 复合索引要遵循最左前缀：若
ON a = x AND b = y，索引应建在(a, b)而非(b, a) - 如果
SELECT还查了其他字段，考虑覆盖索引避免回表，例如ALTER TABLE orders ADD INDEX idx_user_id_status(user_id, status)
BNL不是性能优化手段，而是无索引时的补救机制
BNL本身不提速，它只是把驱动表数据批量塞进join_buffer，减少被驱动表的扫描轮数。但所有匹配仍在线性比对，CPU消耗高，且无法利用索引跳过无关行。
关键参数：join_buffer_size默认仅256K，远不够缓存中等驱动表结果集。调大后可能缓解，但治标不治本。
- 执行
SHOW VARIABLES LIKE 'join_buffer_size';确认当前值 - 临时调大：运行时执行
SET SESSION join_buffer_size = 4194304;（4MB） - 永久生效需改配置文件，但注意：该Buffer是每个JOIN独立分配的，多并发下内存开销翻倍
- 真正有效的做法是让优化器走NLJ——也就是给被驱动表加索引，而不是靠调大
join_buffer_size硬扛
强制走BNL反而可能更慢，别信“开启就能加速”的说法
MySQL 5.5+默认启用BNL，但它是被动触发的：只有当被驱动表无可用索引时才启用。手动打开optimizer_switch='block_nested_loop=on'不会带来收益，反而可能干扰优化器在边界场景下的判断。
典型误操作：SET optimizer_switch='block_nested_loop=on';之后发现慢查询没改善，甚至更差。
-
BNL适合冷数据、小驱动表、无索引且无法加索引的遗留表 - 对热数据或大驱动表，BNL会导致多次全表扫描被驱动表，且
join_buffer频繁换入换出，拖垮buffer pool命中率 - 验证是否真需要BNL：先用
EXPLAIN FORMAT=JSON看used_join_buffer字段，为true才说明实际用了BNL
索引 + 驱动表控制，才是稳定高效的组合
光有索引还不够。如果驱动表选得太大，即使被驱动表有索引，NLJ的外层循环次数也爆炸。这时候STRAIGHT_JOIN和子查询预过滤就起作用了。
- 用
STRAIGHT_JOIN强制小表驱动：例如SELECT /*+ STRAIGHT_JOIN */ ... FROM small_table STRAIGHT_JOIN large_table ON ... - 用子查询限制驱动表结果集：
SELECT * FROM (SELECT id FROM users WHERE status = 1 LIMIT 1000) AS u JOIN orders ON u.id = orders.user_id - 定期执行
ANALYZE TABLE，确保优化器能准确估算过滤后行数，避免因统计信息陈旧而选错驱动表
真正卡住性能的，往往不是算法本身，而是驱动表选大了、索引建偏了、或者字段类型不一致导致索引失效——这些细节比纠结用NLJ还是BNL重要得多。
