The MySQL join table has three matching modes. A left join B is used as an example. A left join B uses table A as the driver and table B as the matching table to join

A simple Nested Loop Join Algorithm (NLJ) in which A Loop reads each row from table A and matches each row to each row in table B, essentially A two-layer for Loop.

For each A_row in A {for each B_row in B {if (a_row. fid == b_row.fid) // Matched data}}Copy the code

A Block Nested Loop Join Algorithm (BNL) that loops multiple rows (buffer_size/row_size rows) from table A and then matches each row in table B. Compared with the first method, cache is used to reduce The Times of reading the matching table

If buffer is full {for each A_row in A {store used columns from A join buffer If (buffer_row.fid == b_row.fid) // Match empty join If buffer is not empty {//}Copy the code

This is the way mysql uses it by default, Run the Show variables like ‘optimizer_switc%’ command to check whether block_nested_loop=on is enabled for cache blocks. By default, block_nested_loop is enabled ‘%join_buffer%’ Displays the cache fast size. Default is 256KB

Therefore, do not query for redundant fields during join operation, so that the cache block can cache more rows.

A nested-loop Join reduces the I/O and number of matches in a matching table by using an Index, provided that the associated column must have an Index in the matching table

For each A_row in A {if(A_row. Fid exist B_fid_index) {// Match data successfully}}Copy the code