MySQL--BNL/ICP/MRR/BKA

標籤：圖片   的區別   提示   ase   bka   要求   區別   關閉   依次   

#======================================================##
MySQL關聯查詢演算法：
BNL(Block Nested-Loop)
ICP(Index Condition Pushdown)
MRR(Multi-Range Read)
BKA(Batched Key Access)

#======================================================##
BNL(Block Nested-Loop)
情境：
假設TB1和TB2進行關聯查詢，以TB1為外表迴圈掃描每行資料到TB2中尋找匹配的記錄行，但由於TB2中沒有可以使用的索引，需要掃描整個T2表的資料,因此外層TB1的資料行數決定內層TB2的掃描次數。

最佳化：
將外層表TB1的資料行進行拆分N個Block，每個Block中包含M條資料，對TB2進行N次掃描，在掃描TB2資料的每一行時將其與一個Block的資料進行匹配，將原來對TB2表的掃描次數從M*N次降低到N次。

重點：
1、內表沒有可利用的索引
2、內表和外表的順序不能對換，如LEFT JOIN操作

該演算法在MySQL 5.1版本中便已存在。

#======================================================##
ICP(Index Condition Pushdown)
情境：
假設表TB1上有索引IDX_C1_C2_C3(C1,C2,C3)，對於查詢SELECT * FROM TB1 WHERE C1=‘XXX‘ AND C3=‘XXX‘

在MySQL 5.6版本以前，由於缺少C2的過濾條件，Innodb儲存引擎層只能使用索引IDX_C1_C2_C3按照C1=‘XXX‘條件找出所有滿足條件的索引記錄，再根據這些索引記錄去叢集索引中尋找，將找到的表資料返回給MySQL Server層，然後由MySQL Server層使用C3=‘XXX‘條件進行過濾得到最終結果。

再MySQL 5.6版本中引入ICP特性，Innodb儲存引擎層只能使用索引IDX_C1_C2_C3按照C1=‘XXX‘條件去掃描所有滿足條件的索引記錄，再將這些索引記錄按照C3=‘XXX‘條件進行過濾，並按照過濾後的索引記錄去去叢集索引中尋找，將找到的表資料返回給MySQL Server層，得到最終結果。

假設滿足C1=‘XXX‘條件的資料行為100000條，而滿足C1=‘XXX‘ AND C3=‘XXX‘的資料行為100條，則：
1、在MySQL 5.5版本中，需要對TB1的叢集索引進行100000次Index Seek操作，Innodb儲存引擎層向MySQL Server層傳遞100000行資料。
2、在MySQL 5.6版本中，使用ICP僅需要對TB1的叢集索引進行100次的Index Seek操作，Innodb儲存引擎層向MySQL Server層傳遞100行資料。

ICP通過將過濾條件由MySQL Server層"下沉"到儲存引擎層，從而達到：
1、減少對叢集索引尋找的操作次數；
2、減少從儲存引擎層返回給MySQL Server層的資料量；
3、減少MySQL Server層訪問儲存引擎層的次數。

PS1: ICP僅使用於非叢集索引。
PS2: 在MySQL 5.6中僅支援普通表進行ICP操作，而MySQL 5.7中支援對分區表進行ICP操作。
#======================================================##
MRR(Multi-Range Read)
假設表TB1上有索引IDX_C1(C1)，對於查詢SELECT * FROM TB1 WHERE C1 IN(‘XXX1‘,‘XXX2‘,....,‘XXXN‘)

在MySQL 5.6版本以前，先按照C1=‘XXX1‘條件對IDX_C1進行索引尋找，再按照找到的索引記錄去TB1的叢集索引中找到對應資料記錄，再按照C1=‘XXX2‘...到C1=‘XXXN‘進行操作，將每次操作的結果集合并得到最終結果集。由於根據C1條件得到的索引記錄中的包含的聚集鍵資料時無序的，導致對叢集索引的Index seek操作造成較多的隨機IO，影響伺服器儲存效能。

在MySQL 5.6版本中引入MRR特性，先按照C1=‘XXX1‘....和C1=‘XXXM‘的條件找到滿足條件的索引記錄放到buffer中，當Buffer滿時再將buffer中的索引記錄按照聚集鍵進行排序，按照排序後的結果去叢集索引中找到相應記錄，通過排序，可以有效地將原來的隨機尋找改為順序尋找，將部分隨機IO轉換為順序IO，提示查詢效能，降低查詢對伺服器IO效能的消耗。

PS1: MRR也僅適用於非叢集索引，且根據非叢集索引得到的結果集在聚集鍵上是隨機無序的。
PS2: 假設上面TB1的叢集索引為ID，那麼IDX_C1(C1)等價於IDX_C1(C1,ID),如果僅對非叢集索引進行單個等值查詢，那麼得到的結果集對聚集鍵也是有序的,無需使用MRR特性。
PS3: MRR中涉及到的Buffer的大小取決於參數read_rnd_buffer_size的設定

#======================================================##
BKA(Batched Key Access)
情境：
假設TB1和TB2進行關聯查詢，以TB1為外表迴圈到TB2中進行關聯匹配，表TB2上有可使用的索引。

在MySQL 5.6版本前，只能迴圈TB1中的資料依次到TB2上進行索引尋找，如果TB1上的資料是無序的，則對TB2的索引尋找也是隨機的，產生大量的隨機IO操作。
在MySQL 5.6版本中，按照MRR的特性，先將TB1中的資料放入Buffer中，當Buffer滿時對Buffer中的資料按照關聯鍵進行排序，然後有序地對TB2進行索引尋找，將部分隨機IO操作轉換為順序IO操作。

PS1: BKA以來於MRR，因此要使用BKA必須開啟MRR特性，但又由於基於mrr_cost_based的成本估算不能保證MRR被使用，因此官方推薦關閉mrr_cost_based。
PS2: BKA使用的Buffer的大小取決於參數join buffer size

#======================================================##
設定開啟MRR和BKA並關閉mrr_cost_based
SET optimizer_switch=‘mrr=on,mrr_cost_based=off,batched_key_access=on‘;

#======================================================##
BKA和BNL的區別：
1、內表索引，BKA要求內表有可以使用的索引，而BNL則是因為內表沒有可使用的索引而不得已的最佳化
2、演算法目的，BKA演算法的目的是減少對內表的隨機Index Seek操作和降低隨機IO，而BNL演算法的目的是減少對內表的掃描次數和減少掃描帶來的IO開銷。

#======================================================##

MySQL--BNL/ICP/MRR/BKA