MySQL开启block_nested_loop(using join buffer)

开启/关闭 block_nested_loop = on/off

set @@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';

执行测试sql：

explain select * from t_doctor join t_hospital th;

开启的时候：

关闭的时候：

为啥要用join buffer？

在此我就不做赘述，大佬已经写过了，请移步暴击此处传送