官方文档:https://dev.mysql.com/doc/refman/5.7/en/bnl-bka-optimization.html

BNL和BKA是MySQL 表关联的两种关联算法

比如t1、t2、t3关联查询且查询顺序和关联顺序一致,MySQL处理join的过程一般是

t1和t2的关联的结果集放入join buffer B1,分配到join buffer时,varchar类型字段的长度是最小的分配单元,所以varchar也指定合适长度,以免浪费内存。

B1中的结果集再和t3进行匹配,将匹配的结果集放入join buffer B2,B2是增量的buffer,因为B2中存放的是t3匹配的column和与之匹配的row的位置链接

当内表的查询类型是all、index或range的时候才会走BNL,explain输出的extra是Using join buffer (Block Nested Loop)

BAK和BNL的别在于所有的右表和左结果集匹配时走辅助索引。BAK会一次性提交B1中与t2关联的column到Multi-Range Read (MRR)接口与t2的索引进行

匹配,然后通过MRR返回匹配的按主键排序的key,从而减少回表时产生的随机I/O。join_buffer_size的大小决定BAK每次批处理提交给MRR的key的大小,

适当增大join_buffer_size 的大小可以有效的提高关联查询的性能。

内表的查询类型是ref或eq_ref时join会采用BAK,explain的extra输出为Using join buffer (Batched Key Access)

BNL默认开启,而BKA默认时关闭的,如果要开启BAK,需要一起开始MRR

mysql> SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
05-21 10:11