Demo of join in MySQL database

This article mainly describes the practical application of join in the MySQL database. We all know that there is only one join in the MySQL database and the nested loop. Nested loop connection is a good choice when the connected data subset is small.

The nested loop is used to scan a table. Every time a record is read, it is searched in another table based on the index. Without an index, it is generally not nested loops.

Generally, in the nested loop, the result set of the driver table meeting the condition is not large, and the connected fields of the driver table must be indexed, so that nstedloop is used. If the driver table returns too many records, it is not suitable for nested loops. If the join field does not have an index, hash join is applicable because no index is required.

This section describes the oracle nestedloop. In MySQL, only nestedloop is supported.

Nested loop process: select the table with the smallest row resource as the External table of the driving table). The internal table requires a highly selective index. From the External table driver table), a row is taken at a time to compare each row of the internal table, that is, the External table has several distinct rows, and there are several nested loops.

So the cost of the nested loop is:

 
 

  
  
cost = outer access cost + (inner access cost * outer cardinality) 
 
 

Outer access cost should be used to read the driver table to the memory, while the internal table requires a valid index to reduce the inner access cost, which is easy to understand if the driver table requires a small value.

My opinion: When the connected fields of the driven table are not indexed, the effect of using a small result set to drive a large result set and a large result set to drive a small result set should be the same, all tables need to be scanned, which is nothing more than the relationship between m * n and n * m.

In his book, Jian Chaoyang also has a saying: using small result sets to drive big result sets forever ...... The precondition for this optimization is that the resource consumption difference for each table access through the join condition is not too large.

If there is a big difference in resource consumption (generally because of the differences in indexes), we cannot simply judge the driving sequence of the join statement by the size of the result set, instead, the optimization scheme should be determined by comparing the number of cycles and the product size required for each loop.

 

Original article title: MySQL join

Connection: http://www.cnblogs.com/arlen/articles/1752456.html

The above content is an introduction to join in the MySQL database. I hope you will have some gains.