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

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)