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 的異常處理