MySQL Batched Key Access (BKA)原理和設(shè)置使用方法舉例

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

BKA算法原理：將外層循環(huán)的行/結(jié)果集存入join buffer，內(nèi)存循環(huán)的每一行數(shù)據(jù)與整個(gè)buffer中的記錄做比較，

可以減少內(nèi)層循環(huán)的掃描次數(shù).

對(duì)于多表join語(yǔ)句，當(dāng)MySQL使用索引訪問(wèn)第二個(gè)join表的時(shí)候，使用一個(gè)join buffer來(lái)收集第一個(gè)操作對(duì)象生成

的相關(guān)列值。BKA構(gòu)建好key后，批量傳給引擎層做索引查找。key是通過(guò)MRR接口提交給引擎的，

這樣，MRR使得查詢更有效率。

如果外部表掃描的是主鍵，那么表中的記錄訪問(wèn)都是比較有序的，但是如果聯(lián)接的列是非主鍵索引，那么對(duì)于表中記錄

的訪問(wèn)可能就是非常離散的。因此對(duì)于非主鍵索引的聯(lián)接，Batched Key Access Join算法

將能極大提高SQL的執(zhí)行效率。BKA算法支持內(nèi)連接，外連接和半連接操作，包括嵌套外連接。

Batched Key Access Join算法的工作步驟如下：

1) 將外部表中相關(guān)的列放入Join Buffer中。

2) 批量的將Key（索引鍵值）發(fā)送到Multi-Range Read（MRR）接口。

3) Multi-Range Read（MRR）通過(guò)收到的Key，根據(jù)其對(duì)應(yīng)的ROWID進(jìn)行排序，然后再進(jìn)行數(shù)據(jù)的讀取操作。

4) 返回結(jié)果集給客戶端。

對(duì)于多表join語(yǔ)句，當(dāng)MySQL使用索引訪問(wèn)第二個(gè)join表的時(shí)候，使用一個(gè)join buffer來(lái)收集第一個(gè)操作對(duì)象生成的相關(guān)

列值。BKA構(gòu)建好key后，批量傳給引擎層做索引查找。key是通過(guò)MRR接口提交給引擎 的（mrr目的是較為順序）.

這樣，MRR使得查詢更有效率。

大致的過(guò)程如下:

1 BKA使用join buffer保存由join的第一個(gè)操作產(chǎn)生的符合條件的數(shù)據(jù)

2 然后BKA算法構(gòu)建key來(lái)訪問(wèn)被連接的表，并批量使用MRR接口提交keys到數(shù)據(jù)庫(kù)存儲(chǔ)引擎去查找查找。

3 提交keys之后，MRR使用最佳的方式來(lái)獲取行并反饋給BKA

BNL(Block Nested Loop)和BKA(MySQL Batched Key Access)都是批量的提交一部分行給被join的表，從而減少訪問(wèn)的

次數(shù)，那么它們有什么區(qū)別呢？

 第一 BNL比BKA出現(xiàn)的早，BKA直到5.6才出現(xiàn)，而BNL至少在5.1里面就存在。

 第二 BNL主要用于當(dāng)被join的表上無(wú)索引

 第三 BKA主要是指在被join表上有索引可以利用，那么就在行提交給被join的表之前，對(duì)這些行按照索引字段進(jìn)行排序，

因此減少了隨機(jī)IO，排序這才是兩者最大的區(qū)別，但是如果被join的表沒(méi)用索引呢？

 那就使用BNL了。

以下設(shè)置啟用BKA：

要使用BKA，必須調(diào)整系統(tǒng)參數(shù)optimizer_switch的值，官方推薦關(guān)閉mrr_cost_based，應(yīng)將其設(shè)置為off。

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

備注：

BKA主要適用于join的表上有索引可利用，無(wú)索引只能使用BNL。

多表join語(yǔ)句 ，被join的表/非驅(qū)動(dòng)表必須有索引可用。

在EXPLAIN輸出中，當(dāng)Extra值包含Using join buffer（Batched Key Access），表示使用BKA。

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

| Extra                                                                           |

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

| NULL                                                                           |

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

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

使用hint，強(qiáng)制走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)