ITPub博客

首页 > 数据库 > MySQL > MySQL Batched Key Access (BKA)原理和设置使用方法举例

MySQL Batched Key Access (BKA)原理和设置使用方法举例

原创 MySQL 作者:chenfeng 时间:2019-06-20 16:57:05 0 删除 编辑

MySQL 5.6版本开始增加了提高表join性能的算法:Batched Key Access (BKA)的新特性。


BKA算法原理:将外层循环的行/结果集存入join buffer,内存循环的每一行数据与整个buffer中的记录做比较,

可以减少内层循环的扫描次数.


对于多表join语句,当MySQL使用索引访问第二个join表的时候,使用一个join buffer来收集第一个操作对象生成

的相关列值。BKA构建好key后,批量传给引擎层做索引查找。key是通过MRR接口提交给引擎的,

这样,MRR使得查询更有效率。


如果外部表扫描的是主键,那么表中的记录访问都是比较有序的,但是如果联接的列是非主键索引,那么对于表中记录

的访问可能就是非常离散的。因此对于非主键索引的联接,Batched Key Access Join算法

将能极大提高SQL的执行效率。BKA算法支持内连接,外连接和半连接操作,包括嵌套外连接。


Batched Key Access Join算法的工作步骤如下:


1) 将外部表中相关的列放入Join Buffer中。


2) 批量的将Key(索引键值)发送到Multi-Range Read(MRR)接口。


3) Multi-Range Read(MRR)通过收到的Key,根据其对应的ROWID进行排序,然后再进行数据的读取操作。


4) 返回结果集给客户端。


对于多表join语句,当MySQL使用索引访问第二个join表的时候,使用一个join buffer来收集第一个操作对象生成的相关

列值。BKA构建好key后,批量传给引擎层做索引查找。key是通过MRR接口提交给引擎 的(mrr目的是较为顺序).

这样,MRR使得查询更有效率。


大致的过程如下:


1 BKA使用join buffer保存由join的第一个操作产生的符合条件的数据


2 然后BKA算法构建key来访问被连接的表,并批量使用MRR接口提交keys到数据库存储引擎去查找查找。


3 提交keys之后,MRR使用最佳的方式来获取行并反馈给BKA


BNL(Block Nested Loop)和BKA(MySQL Batched Key Access)都是批量的提交一部分行给被join的表,从而减少访问的

次数,那么它们有什么区别呢?


 第一 BNL比BKA出现的早,BKA直到5.6才出现,而BNL至少在5.1里面就存在。


 第二 BNL主要用于当被join的表上无索引


 第三 BKA主要是指在被join表上有索引可以利用,那么就在行提交给被join的表之前,对这些行按照索引字段进行排序,

因此减少了随机IO,排序这才是两者最大的区别,但是如果被join的表没用索引呢?

 那就使用BNL了。

 

以下设置启用BKA:

要使用BKA,必须调整系统参数optimizer_switch的值,官方推荐关闭mrr_cost_based,应将其设置为off。


mysql> SET global optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';


备注:

BKA主要适用于join的表上有索引可利用,无索引只能使用BNL。

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


在EXPLAIN输出中,当Extra值包含Using join buffer(Batched Key Access),表示使用BKA。


+--------------------------------------------------------+

| Extra                                                                           |

+--------------------------------------------------------+

| NULL                                                                           |

| Using where; Using join buffer (Batched Key Access)|

+---------------------------------------------------------+


使用hint,强制走BKA的方法:

例如:

mysql> explain SELECT /*+ bka(a)*/ a.gender, b.dept_no FROM employees a, dept_emp b WHERE a.birth_date = b.from_date;

+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+

| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                   | rows   | filtered | Extra                                                                                  |

+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+

|  1 | SIMPLE      | b     | NULL       | ALL  | NULL           | NULL           | NULL    | NULL                  | 331143 |   100.00 | NULL                                                                                 |

|  1 | SIMPLE      | a     | NULL       | ref  | idx_birth_date | idx_birth_date | 3       | employees.b.from_date |     62 |   100.00 | Using join buffer (Batched Key Access)                 |

+----+-------------+-------+------------+------+----------------+----------------+---------+-----------------------+--------+----------+----------------------------------------+

2 rows in set, 1 warning (0.00 sec)


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15498/viewspace-2648252/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
交流MySQL,MongoDB和Redis技术。 微信或QQ:410294

注册时间:2015-12-07

  • 博文量
    748
  • 访问量
    1766768