What is the usage principle of MySQL Join?

Join type

• left join, with the left table as the driving table and the left table as the basis of the result set, join the right The data of the table is added to the result set

• right join, using the right table as the driving table and the right table as the result Set basis, connect the data from the left table to the result set

• inner join, the result set takes the results of the two tables Intersection

• ##full join, the result set is the union of the two tables

• mysql does not have full join, union replaces
  The difference between union and union all is that union will remove duplicates

• cross join Cartesian product

• If the where condition is not used, the result set is the product of the two associated table rows
  The difference between and is,

  When cross join creates a result set, the result set will be filtered according to the on condition

• straight_join

• Specify the driver table strictly according to the SQL order ,

  The left table is the driver

Join principle

This sentence can be rewritten like this: its essence can be understood as the operation of a nested loop , where the outer for loop drives the table, and the inner for loop is the driven table. It can be divided into three algorithms according to the strategy of connecting the composed data.

Simpe Nested-Loop Join

• For example, if there are table A and table B, if the two tables are JOIN, the connection table conditions of table A will be added one by one to table B. Table loop, matching the same ID in table A and table B is put into the result set. This kind of efficiency is the lowest.

Index Nested-Loop Join

• Execution process (disk scan)

• From table t1 Read a row of data R in;
  From the data row R, take out the a field to table t2 for

  tree search ;

  Take out the fields that meet the conditions in table t2 Row, form a row with R as part of the result set;
  Repeat steps 1 to 3 until the loop ends at the end of table t1.

• For each row of R, search in table t2 based on the a field, using a tree search process.

Block Nested-Loop Join

• mysql uses a buffer called

  join bufferr to reduce the number of loops , this buffer is 256KB by default. You can view it through the command show variables like 'join_%'

• The specific method is to query the qualified columns in the first table at one time buffer, then traverse the second table once, compare it with all values ​​in the buffer one by one, and add the comparison results to the result set

• Only when the JOIN type is ALL, The join buffer is only used when index, rang or index_merge is used. You can check the SQL query type through explain.

Join optimization

• In order to optimize the join algorithm, the Index nested-loop join algorithm is used to create an index field on the connection field

• Use a table with a small amount of data to drive a table with a large amount of data

• Increase the size of the join buffer size (the more data is cached at one time, the Then the number of loops in the outer table will be fewer)

• Pay attention to the implicit conversion and character encoding of the connection field to avoid index failure

The above is the detailed content of What is the usage principle of MySQL Join?. For more information, please follow other related articles on the PHP Chinese website!