MySQL中的BNL优化器

在5.7以上版本中，BNL优化器默认是打开的，

show variables like 'optimizer_switch' 

optimizer_switch  index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,engine_condition_pushdown=on,index_condition_pushdown=on,mrr=on,mrr_cost_based=on,block_nested_loop=on,batched_key_access=off,materialization=on,semijoin=on,loosescan=on,firstmatch=on,duplicateweedout=on,subquery_materialization_cost_based=on,use_index_extensions=on,condition_fanout_filter=on,derived_merge=on,use_invisible_indexes=off,skip_scan=on,hash_join=on  

 

使用BNL可以较为明显的降低访问内表的次数，同时也降低回表的IO次数，以此来达到优化的目的。 

只有当join类型是all/index/range时才可以，也就是内表不使用索引或者索引效率很低时才不得已使用。

buffer的大小由join_buffer_size进行设置，buffer中保存着参与连接的所有信息，join完成后buffer释放。

对于使用到BNL特性且性能较差的SQL，建议在session级别将Join_buffer_size临时增大来提高性能。

表的join操作通常是通过效率较高的索引来做ref或者eq_ref方式连接，这种情况下，BNL是无法使用的。

对更为常见的join操作，MySQL引入了MRR和BKA，目的是减少磁盘的随机访问。