Block Nested-loop and batched Key Access

Official Document: Https://dev.mysql.com/doc/refman/5.7/en/bnl-bka-optimization.html

BNL and BKA are two relational algorithms associated with MySQL tables

For example, T1, T2, T3 associated queries and query Order and association order consistent, MySQL processing join process is generally

T1 and T2 's associated result set are placed in the join buffer B1, when assigned to the join buffer, the varchar type field is the smallest allocation unit, so varchar also specifies the appropriate length to avoid wasting memory.

The result set in the B1 is then matched to T3, and the matching result set is placed in the join buffer b2,b2 is the delta buffer, because B2 stores the T3 matching column and the location link of the row that matches it

The Bnl,explain output extra is a using join buffer (Block Nested Loop) when the query type of the inner table is all, index, or range

Bak and BNL do not lie in the secondary index when all the right and left result sets match. Bak commits the T2-to-multi-range Read (MRR) interface associated with the B1 in one-time to the index of the T2

Matches, and then returns the matching key sorted by primary key by MRR, thereby reducing the random I/O generated when the table is returned. The size of the join_buffer_size determines the size of the key that Bak submits to MRR each time the batch is processed,

Appropriately increasing the size of join_buffer_size can effectively improve the performance of correlated queries.

When the query type of the inner table is ref or EQ_REF, the join takes bak,explain extra output as a using join buffer (batched Key Access)

BNL is turned on by default, and Bka is turned off by default, if you want to turn Bak on, you need to start together MRR

MySQL>SET optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_ Access=on';

Block Nested-loop and batched Key Access