Mysql Nested-loop Join Algorithms

MySQL uses a nested-loop algorithm or its variants when performing joins between multiple tables (nested loops)

nested-loop Join Algorithm

a simple nested loop join (NLJ) algorithm reads a row from the first table and then passes it to the other tables in the join, reading sequentially, and the process repeats the remaining tables are joined.

Suppose that when you make a connection in three table t1,t2,t3:

Table   Join Typet1      ranget2      reft3      all

As follows:

 for inch  range {  for inreference key  '    {for '  T3 {      ifjoin   conditions,      to client    }   }}

Because a nested loop connection passes one row to the inner table at a time, it is usually handled several times inside the table.

Block nested-loop Join Algorithm (block nested loop join algorithm)

The Block Nested-loop (BNL) connection algorithm reduces the number of reads in the table by using data rows that are read by the cached external loop. e.g. if the appearance reads and caches 10 rows of data, the cache is passed to the following inner table, and the inner table can compare the 10 rows of data directly in the cache without re-reading the 10 rows of data, reducing the number of internal table reads in order of magnitude.

MYSQL uses join buffer for a few conditions:

The 1:join buffer size system variable determines the sizes of each join buffer.

2:join buffer can be used for all, Index,range table access types.

3: Each join is assigned a buffer, so one query can use multiple join buffer.

4: If the first table is Noconst table, the join buffer will not be assigned, even if his access type is all or index.

5:join buffer is allocated at execution, and is released after the query is complete.

6:join buffer caches only the columns of interest (which need to be returned to the client), not the entire row of data.

Compare the previous NLJ algorithm (without buffer):

 forEach rowinchT1 matching Range { forEach rowinchT2 Matching ReferenceKey{store used columns fromT1, T2inch JoinBufferifBuffer is  Full {       forEach rowinchT3 { forEach T1, T2 combinationinch JoinBuffer {ifRow satisfiesJoinconditions, send toClient}} Empty buffer }}}ifBuffer is  notEmpty { forEach rowinchT3 { forEach T1, T2 combinationinch JoinBuffer {ifRow satisfiesJoinconditions, send toclient} }}

If T1,t2 is saved in the combined size of the connection cache as s, the number of combinations is C, then the number of T3 scans is:

* C)/+1

The number of scans of the T3 will decrease as the join_buffer_si increases, and when the connection cache is increased to include all records, the number of scans can not be reduced at large.

Mysql Nested-loop Join Algorithms