Batched Key Access是MySQL 5.6 版本中的新特性,是一种用户提高表join性能的算法。
Batched Key Access
       对于多表join语句,当MySQL使用索引访问第二个join表的时候,使用一个join buffer来收集第一个操作对象生成的相关列值。BKA构建好key后,批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的. 这样,MRR使得查询更有效率。
大致的过程如下:

  1. BKA使用join buffer保存由join的第一个操作产生的符合条件的数据。
  2. 然后BKA算法构建key来访问被连接的表,并批量使用MRR接口提交keys到数据库存储引擎去查找。
  3. 提交keys之后,MRR使用最佳的方式来获取行并反馈给BKA .

BKA使用join buffer size来确定buffer的大小,buffer越大,访问被join的表/内部表就越顺序。
       MRR接口有2个应用场景:
       场景1:应用于传统的基于磁盘的存储引擎(innodb,myisam),对于这些引擎join buffer中keys是一次性提交到MRR,MRR通过key找到rowid,通过rowid来获取数据
       场景2:应用于远程存储引擎(NDB),来自join buffer上的部分key,从SQL NODE发送到DATA NODE,然后SQL NODE会收到通过相关关系匹配的行组合。然后使用这些行组合匹配出新行。然后在发送新key,直到发完为止。

  • BNL和BKA,MRR的关系

BNL和BKA都是批量的提交一部分结果集给下一个被join的表(标记为T),从而减少访问表T的次数,那么它们有什么区别呢?BNL和BKA的思想是类似的,详情见:《nest-loop-join官方手册》
       第一 BNL比BKA出现的早,BKA直到5.6才出现,而BNL至少在5.1里面就存在。
       第二 BNL主要用于当被join的表上无索引,Join buffering can be used when the join is of type ALL or index (in other words, when no possible keys can be used, and a full
scan is done, of either the data or index rows, respectively)
       第三 BKA主要是指在被join表上有索引可以利用,那么就在行提交给被join的表之前,对这些行按照索引字段进行排序,因此减少了随机IO,排序这才是两者最大的区别,但是如果被join的表没用索引呢?那就使用BNL了。
      BKA实现的过程中就是通过传递keys给MRR接口,本质上还是在MRR里面实现,下面这幅图则展示了它们之间的关系:

MySQL Batched Key Access-LMLPHP

  • 如何使用

通过调整系统参数optimizer_switch的值,batched_key_access设置为on,因为BKA使用了MRR,因此也要打开MRR,但是基于成本优化MRR算法不是特别准确官方文档推荐关闭
mrr_cost_based,将其设置为off。

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'

另外多表join语句 ,被join的表/非驱动表必须索引可用。

05-21 10:11