MySQL5.6新特性之Batched Key Access

MySQL5.6新特性之Batched Key Access

一 介紹

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的表/非驅動表必須索引可用。

本文永久更新連結地址：