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

楊奇龍發表於2015-06-28
MySqlBAT
一 介紹
  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. 1 BKA使用join buffer儲存由join的第一個操作產生的符合條件的資料。
  2. 2 然後BKA演算法構建key來訪問被連線的表，並批量使用MRR介面提交keys到資料庫儲存引擎去查詢查詢。
  3. 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。
  1. 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].Block-Based Join Algorithms
[4].Join Optimizations in MySQL 5.6 and MariaDB 5.5 
[5].bacthed-key-access-speeds-up-disk-bound 
[6].Multi Range Read Optimization 
[7].On the multi range read&batch key access&block nested loop

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22664653/viewspace-1715511/，如需轉載，請註明出處，否則將追究法律責任。

相關文章