一介紹
MySQL 5.6版本提供了很多效能最佳化的特性，其中之一是關於提高表join效能的演算法 --- Batched Key Access (BKA) ,本文將結合之前寫過MRR,BNL最佳化特性一起來詳細介紹該演算法。這篇文章是
我拖延時間最久的，之前一直沒有搞清楚MRR,BKA之間的關聯，BKA,BNL的區別，本週花了一天時間收集資料，算是搞懂了，裡面有基於文件翻譯的，可能不準確，請大家指正。
二原理
對於多表join語句，當MySQL使用索引訪問第二個join表的時候，使用一個join buffer來收集第一個操作物件生成的相關列值。BKA構建好key後，批次傳給引擎層做索引查詢。key是透過MRR介面
提交給引擎的. 這樣，MRR使得查詢更有效率。
大致的過程如下:

1 BKA使用join buffer儲存由join的第一個操作產生的符合條件的資料。
2 然後BKA演算法構建key來訪問被連線的表，並批次使用MRR介面提交keys到資料庫儲存引擎去查詢查詢。
3 提交keys之後，MRR使用最佳的方式來獲取行並反饋給BKA .

BKA使用join buffer size來確定buffer的大小，buffer越大，訪問被join的表/內部表就越順序。
MRR介面有2個應用場景：
場景1：應用於傳統的基於磁碟的儲存引擎(innodb,myisam)，對於這些引擎join buffer中keys是一次性提交到MRR，MRR透過key找到rowid，透過rowid來獲取資料
場景2：應用於遠端儲存引擎(NDB)，來自join buffer上的部分key，從SQL NODE傳送到DATA NODE，然後SQL NODE會收到透過相關關係匹配的行組合。然後使用這些行組合匹配出新行。然後在傳送新
key，直到發完為止。

三 BNL和BKA,MRR的關係
BNL和BKA都是批次的提交一部分結果集給下一個被join的表(標記為T)，從而減少訪問表T的次數，那麼它們有什麼區別呢？NBL和BKA的思想是類似的,詳情見:《nest-loop-join官方手冊》
第一 NBL比BKA出現的早，BKA直到5.6才出現，而NBL至少在5.1裡面就存在。
第二 NBL主要用於當被join的表上無索引，Join buffering can be used when the join is of type ALL or index (in other words, when no possible keys can be used, and a full
scan is done, of either the data or index rows, respectively)
第三 BKA主要是指在被join表上有索引可以利用，那麼就在行提交給被join的表之前，對這些行按照索引欄位進行排序，因此減少了隨機IO，排序這才是兩者最大的區別，但是如果被join的表沒用
索引呢？那就使用NBL了。
上面原理環境提到講了在BKA實現的過程中就是透過傳遞keys給MRR介面，本質上還是在MRR裡面實現，下面這幅圖則展示了它們之間的關係:

四如何使用
要使用BKA，必須調整系統引數optimizer_switch的值，batched_key_access設定為on，因為BKA使用了MRR，因此也要開啟MRR,但是基於成本最佳化MRR演算法不是特別準確官方文件推薦關閉
mrr_cost_based，將其設定為off。

set optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on'

另外多表join語句，被join的表/非驅動表必須索引可用。

五參考資料
[1].Block Nested-Loop and Batched Key Access Joins
[3].
[4].Join Optimizations in MySQL 5.6 and MariaDB 5.5
[5].bacthed-key-access-speeds-up-disk-bound
[6].
[7]

【MySQL】MySQL5.6新特性之Batched Key Access

相關文章