MySQL Block nested-loop Join (BNL)

Prior to version 5.5, MySQL itself supported only one form-by-table association, the nested Loop (Nested loop). If the data volume of the associated table is large, the execution time of the join association is very long. In a later version of 5.5, MySQL optimizes nested execution by introducing the BNL algorithm
"Nested Loop Join"
NLJ algorithm: The result set of the driver table/external table is used as the loop base data, and then the result set is cycled from one fetch of data to the next, and then the results are merged. If there is a multi-table join, the result set of the preceding table is used as the looping data, taken to each row and then to the next table in the join, to get the result set back to the client.
The pseudo-algorithm of Nested-loop is as follows:

 for inch T1 matching Range {  for in  T2 Matching reference key {     forin
     T3 {      if  row satisfies join conditions,      Send to client   }}

Because normal Nested-loop only pass one line into the inner loop at a time, the outer loop (the result set) has how many rows, and how many times the memory loop executes. The scan cost is O (Rn) If there is an index on the connection of the internal table, and if there is no index, the scan cost is O (RN*SN). If the internal table S has many records, the Simpl Enested-loops join scans the internal table many times and performs poorly.

"Block nested-loop Join"
BNL algorithm: The row/result set of the outer loop is stored in the join buffer, each row of the inner loop is compared with the record in the entire buffer, thus reducing the number of inner loops.
For example, the result set of the outer loop is 100 rows, using the NLJ algorithm to scan the internal table 100 times, if using the BNL algorithm, the outer loop table (external table) each read 10 rows of records are placed in the join buffer, and then in the Innerloop table (internal table) Directly matches the 10 rows of data, the memory loop can be compared to the 10 rows at a time, which only needs to be compared 10 times and the internal table scan is reduced by 9/10. Therefore, the BNL algorithm can significantly reduce the number of internal loop-table scans.
The query described earlier, if you use join buffer, the actual join is shown below:

 forEach rowinchT1 matching Range { forEach rowinchT2 Matching reference key {store used columns fromT1, T2inchJoin BufferifBuffer isFull { forEach rowinchT3 { forEach T1, T2 combinationinchJoin Buffer {ifrow satisfies join conditions, send to client}} Empty buffer }}}ifBuffer isNot empty { forEach rowinchT3 { forEach T1, T2 combinationinchJoin Buffer {ifrow satisfies join conditions, send to client }}

If T1, T2 participates in the Join column length only and is S, C is the combination of the two, then the number of times the T3 table is scanned
(S * C)/join_buffer_size + 1
The number of scan T3 decreases with the increase of join_buffer_size, until the join buffer can accommodate all T1, T2 combinations, and then increase the join buffer size, the query speed will not be changed any faster.

• MySQL uses join buffer to have the following points:

1. The join_buffer_size variable determines the buffer size.
2. Join buffer can only be used when the join type is all, index, range.
3. Each join that can be buffer is assigned a buffer, which means that a query may end up using multiple join buffer.
4. The first Nonconst table does not assign a join buffer, even if its scan type is all or index.
5. The join buffer is assigned before the join and is freed when query executes.
6. Join buffer only saves the column participating in the join, not the entire data row.

• How to use

Version 5.6 and later, the Block_nested_loop parameter in the optimizer management parameter Optimizer_switch controls whether the BNL is used for the optimizer. The default is on, if set to off, the optimizer chooses the NLJ algorithm when it chooses the join mode.

MySQL Block nested-loop Join (BNL)