bnl在mysql 8.0.18后被hash join替代，因其在无索引等值join中存在严重i/o放大；hash join通过构建哈希表实现单次扫描大表+内存探测，大幅降低io开销，但受限于内存容量和索引可用性。
BNL为什么在MySQL 8.0.18后被Hash Join替代
因为BNL在无索引等值JOIN场景下存在不可忽视的I/O放大问题，而Hash Join能将“多次扫描内表”压缩为“单次扫描+内存哈希探测”，这是算法层面的降维优化。
BNL的实际执行开销有多高
BNL本质是带缓冲的Nested Loop：它把驱动表一批批读入join_buffer（默认256KB），再对每批中的每一行，全表扫描被驱动表找匹配。如果驱动表有10万行、join_buffer一次只能装100行，就要做1000次被驱动表全扫——哪怕被驱动表只有1万行，总I/O量也轻松突破千万级记录扫描。
- 驱动表结果集越大，BNL的循环次数越多
- 被驱动表越胖（列多/行大），每次扫描代价越高
-
join_buffer_size设得太小，分片数暴涨；设得太大，又可能触发内存不足导致磁盘临时文件
Hash Join如何绕过BNL的硬伤
Hash Join不依赖索引，也不反复扫描——它先选小表（按数据体积而非行数）构建内存哈希表，再单趟扫描大表，对每行计算哈希值并查表。只要哈希表能装进内存，整个JOIN就是两次顺序扫描+一次哈希探测，I/O量基本等于两表数据量之和。
- 哈希表构建阶段只读小表一次，且只读连接字段+主键（非整行）
- 探测阶段只读大表一次，不回表、不二次过滤
- MySQL 8.0.20起支持
<t1.c1 t2.c1></t1.c1>这类非等值条件，BNL完全无法处理
为什么不是所有场景都换Hash Join
Hash Join有隐性门槛：它需要把小表的连接字段+主键全载入内存。如果这个“小表”实际有几百万行、每行连接字段加主键占200字节，哈希表就轻松超100MB——一旦超出join_buffer_size或物理内存，就会退化成磁盘分片哈希，性能反而不如BNL。
- 被驱动表上有高选择性索引时，
Index Nested-Loop Join仍是首选（延迟低、IO少） - SQL带
LIMIT 10，BNL可能扫到第5行就出结果，Hash Join必须建完哈希表才能开始输出 -
optimizer_switch='hash_join=off'可全局禁用，但即使开启，优化器发现索引可用也会自动跳过Hash Join
真正容易被忽略的是：你看到EXPLAIN里写Using join buffer (Block Nested Loop)，不代表没走Hash Join——必须用EXPLAIN FORMAT=TREE才能确认是否命中Inner hash join。默认格式下，Hash Join会伪装成BNL，这是调试时最常踩的坑。
