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

2024-07-24 12:34:30

字体：大中小

来源：转载

供稿：网友

　　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)

（编辑：武林网）

上一篇：mysql系列之9 mmm高可用集群

下一篇：MySQL 存储过程空结果集错误Error 1329 No data 的异常处理