explain中出现using join buffer (block nested loop)说明mysql正使用bnl算法，驱动表连接字段未走索引，属性能劣化信号，通常伴随type: all或index，且被驱动表也缺乏有效索引。
EXPLAIN里看到Using join buffer (Block Nested Loop)说明什么
这表示 MySQL 正在用 Block Nested-Loop Join（BNL）算法，且驱动表连接字段**没走索引**——不是“用了缓存就快”，恰恰相反，这是性能劣化的信号。它通常伴随 type: ALL 或 type: index，说明驱动表在全表扫描；同时被驱动表也大概率没索引，否则优化器会选 INLJ。
常见诱因包括：
– 驱动表 ON/WHERE 条件字段缺失索引
– 连接字段上写了函数或隐式转换，比如 ON DATE(t2.create_time) = '2026-04-23' 或 ON t1.id + 0 = t2.t1_id
– 统计信息过期，优化器误判驱动表行数，选错表做外层循环
怎么确认当前 JOIN 真的在走 INLJ 还是 BNL
不能只看 EXPLAIN 的 type，得交叉验证：
- 查
Extra列：出现Using where; Using join buffer (Block Nested Loop)→ 明确是 BNL - 查
key列：若为NULL，说明驱动表连接字段完全没用索引 - 查
type列：若为ref、eq_ref或const，基本可断定是 INLJ（有索引支撑） - 执行
EXPLAIN FORMAT=JSON，搜索"using_join_buffer": "block nested loop"字段 - 开 optimizer trace：
SET optimizer_trace="enabled=on"; SELECT ... JOIN ...; SELECT * FROM information_schema.OPTIMIZER_TRACE\G，搜索use_join_cache和join_buffering
为什么加了索引，join_buffer_size 调再大也没用
因为 INLJ 根本不依赖 join_buffer：它靠被驱动表的索引快速定位匹配行，每次只查 B+ 树几层，不批量缓存驱动表数据。此时调大 join_buffer_size 不仅无效，还会：
- 增加 per-connection 内存开销，高并发下易触发
OOM - 若 buffer 超过物理内存，可能引发 swap，反而拖慢整体查询
- 对
Select_full_join状态变量无影响——这个值只统计真正发生 BNL 的次数
真正该做的，是检查被驱动表连接字段是否命中索引（注意类型严格一致，比如 BIGINT 对 BIGINT，而非 VARCHAR 对 INT）。
小表驱动大表 ≠ 物理行数小，而是过滤后结果集小
优化器选驱动表依据是「预估扫描行数」，不是建表时的 SHOW TABLE STATUS 行数。比如：
- 大表
orders有 500 万行，但WHERE status IN (1,2,3)后只剩 8 万行 → 它可能成为优质驱动表 - 小表
users仅 10 万行，但没加 WHERE，或条件太宽泛（如WHERE city LIKE '%京%'）→ 实际扫描行数远超预期
所以别迷信“小表放前面”，优先用子查询固化小结果集：SELECT * FROM (SELECT id FROM orders WHERE status = 1) t JOIN users u ON t.id = u.order_id；或在明确场景下用 STRAIGHT_JOIN 强制顺序，但前提是已确认被驱动表连接列有索引——否则只是把慢操作换了个顺序执行。
