數據庫

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的方法:

例如:

我還沒有學會寫個人說明!

PostgreSQL DBA(31) - Backup&Recovery#4(搭建流復制)

上一篇

中國銀聯跨中心,異構數據同步技術與實踐

下一篇

你也可能喜歡

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

長按儲存圖像,分享給朋友

ITPUB 每周精要將以郵件的形式發放至您的郵箱


微信掃一掃

微信掃一掃
重庆百变王牌开奖结果