1122MySQL performance Optimization Nested Loop join and block Nested-loop join (BNL)

Transfer from http://blog.itpub.net/22664653/viewspace-1692317/

An introduction
I believe many development/dba in the process of using MySQL, the way that MySQL handles multi-table association or performance has been not satisfied. For the development of a submitted query with joins, it is generally more resistant, which suggests splitting the join to avoid possible performance problems associated with the join, as well as increasing the network interaction between the program and the DB.
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 introduced the BNL algorithm to optimize nested execution, and this article describes two join algorithms Nested-loop join (NLJ) and block Nested-loop join (BNL).

Two principles
2.1 Nested Loop Join algorithm
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:

1. for each row in t1 matching range {
2.    for each row in t2 Matching reference key {
3.      for  Each row in t3 {
4.       if row satisfies join conditions,
Li>      send to client
5.     }
6.   }
7.  }

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 Simplenested-loops join scans the internal table many times and performs poorly.

2.2 Block Nested-loop Join algorithm
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 outer loop The result set is 100 rows, the use of the NLJ algorithm needs to scan the internal table 100 times, if using the BNL algorithm, the outer loop table (external table) each read 10 rows of records into 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:

2. For each row in T1 matching range {
4. For each row in T2 matching reference key {
6. Store used columns from T1, T2 in join buffer
8. If buffer is full {
10. For each row in T3 {
12. For each T1, T2 combination in join buffer {
14. If row satisfies join conditions,
16. Send to Client
18. }
20. }
22. Empty buffer
24. }
26. }
28. }
34. If buffer is not empty {
36. For each row in T3 {
38. For each T1, T2 combination in join buffer {
40. If row satisfies join conditions,
42. Send to Client
44. }
46. }
48. }

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

1. (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.

2.3 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.

Three 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.

For SELECT @ @optimizer_switch \g

See http://blog.csdn.net/aoerqileng/article/details/51287563

1122MySQL performance Optimization Nested Loop join and block Nested-loop join (BNL)