MySQL block Nested Loop and batched key access Joins (block nested loops and bulk Key access connections)

Block Nested-loop and batched Key Access Joins

The batched Key access (BKA) join algorithm accesses the joined table through index and join buffer, and the BKA algorithm supports inner Join,outer join and semi join operations, including nested outer joins, The benefits of BKA include increased join performance (due to a more efficient table scan), and the addition of the Block Nested Loop (BNL) join algorithm that was previously extended to support these join types.

Block Nested-loop

MYSQL Server provides a join buffer to perform an internal link without index, an outer join, a semi-connected inner table to access the subquery, and is more efficient when accessing the inner table via index is the join buffer.

when storing "rows of interest", the join Buffer Manager is more efficient at using the join buffer space, if the row value is Null,join buffer does not allocate space for it, and minimizes the data allocated to the varchar type;

The manager supports two types of buffer, regular and incremental, E.g.join buffer B1 apply the join in table T1 and table T2, and the join result and table T3 do the join with B2;

1: The regular join buffer contains the column for each join operand (table), and if B2 is a regular join buffer, each row and table in the join buffer B1 T2 the new row data that corresponds to the row's composition (containing the columns of interest T2) are all pressed into the B2;

2: Incremental Join buffer contains only the rows of data from the inner table of the join operation, so it is incremented as the first row of a join buffer. If the B2 is an incremental join buffer, it contains the rows and columns of the inner table that are just interested in data and join

Buffer B1 A reference to the row data;

The incremental join buffers always relates to the associated increment of the join buffer for the previous join operation, so the join buffer for the previous join operation is often a regular join Buffer;join buffer B1 for table T1 and T2 connections , it must be a regular join buffer;

The incremental join buffer contains only the columns of interest for the joined table (inner table), which are specified as a row reference to the table produced by the previous join operation (and only the columns of interest), and the incremental join Some data rows in buffer refer to the same row of data stored in the previous join buffer (match success);

The incremental join buffer guarantees the small frequency of the copy data from the previous join buffer, which saves space in the join buffer, because in general, the rows produced by the previous join operation may be matched by the multiline data of the joined table of the join operation , it is not necessary to copy the same row of data from the previous join buffer multiple times, and also to reduce processing time.

batched Key Access Join

The bulk key access connection is used when using index to access the joined table of the join operation, and as with the BNL algorithm, the BKA join algorithm applies the join Buffer to hold the row values of interest in the first table of the join operation,

The BKA algorithm then creates index for all rows in the join buffer to access the second table, and submits the indexes to the database engine to find these index through the MRR (Mutil range Read) interface. ，

The MRR engine performs an index lookup, obtains the resulting data, and then executes the BKA matching algorithm to obtain matching row data (refenece previous joinbufferd row data)

 

MySQL block Nested Loop and batched key access Joins (block nested loops and bulk Key access connections)