Mysql表連接方式

Mysql表連接方式

Nested-Loop Join Algorithms

A simple nested-loop join (NLJ) algorithm reads rows from the first table in a loop one at a time, passing each row to a nested loop that processes the next table in the join. This process is repeated as many times as there remain tables to be joined.

從驅動表中讀一行數據 在被驅動表中一行一行數據進行比較。

例子：

Table Join Type

t1 range

t2 ref

t3 AL

如上所示，t1表需要的數據是個範圍數據，t2表reference key t3表是個全表掃描

for each row in t1 matching range {

for each row in t2 matching reference key {

for each row in t3 {

if row satisfies join conditions, send to client

}

}

}

上面是nested join的實現方式。

Block Nested-Loop Join Algorithm

A Block Nested-Loop (BNL) join algorithm uses buffering of rows read in outer loops to reduce the number of times that tables in inner loops must be read. For example, if 10 rows are read into a buffer and the buffer is passed to the next inner loop, each row read in the inner loop can be compared against all 10 rows in the buffer. This reduces by an order of magnitude（huge） the number of times the inner table must be read.

從驅動表中讀多行數據，被驅動表中的每行讀取的數據可以跟這個多行數據進行對比。

此方式只能減少被驅動表的讀取次數。

此處有個join buffer

for each row in t1 matching range {

for each row in t2 matching reference key { ------------------------前兩步跟nlj 沒什麼區別

store used columns from t1, t2 in join buffer ---------------------這一步是關鍵區別就是緩存了需要用的欄位數據

if buffer is full { ------------只有當join buffer 滿了才會去讓t3表的每行數據去跟緩存中所有數據做比較

for each row in t3 {

for each t1, t2 combination in join buffer {

if row satisfies join conditions, send to client

}

}

empty join buffer -----------t3表的數據都跟緩存數據比較完後清空緩存。

}

}

}

if buffer is not empty { ------------當最後一步的時候 可能最後一部分數據不能使用完join buffer 空間 執行此循環

for each row in t3 {

for each t1, t2 combination in join buffer {

if row satisfies join conditions, send to client

}

}

}

The number of t3 scans decreases as the value of join_buffer_size increases, up to the point when join_buffer_size is large enough to hold all previous row combinations. At that point, no speed is gained by making it larger.

這個段話表達的就是當join_buffer_size可以容納所有的需要buffer的組合行，參數在增大 也不會提高效率。

推薦閱讀：