Summary of MySQL principles: left join, right join, inner join and Hash join

This article brings you relevant knowledge about mysql. It mainly introduces the working principles of left join, right join, inner join and hash join, and analyzes the difference between subquery and join. , provide some practical skills that need to be mastered at work based on what they have learned. Let’s take a look at them together. I hope it will be helpful to everyone.

Recommended learning: mysql video tutorial

1. MySQL database JOIN connection

Create indexes on multiple tables, and multiple table JOIN and subquery statements are relatively difficult. Many developers unconsciously believe that JOIN will reduce the performance efficiency of SQL, so they split multi-table SQL into single-table queries, thinking that this will affect the efficiency of SQL execution because developers do not understand the JOIN implementation process.

Table associations between joins use indexes for matching, assuming that tables R and S are connected.

Table R is called the driving table. The data filtered by the WHERE condition in table R will be queried one by one on the index corresponding to table S. If the amount of data driving table R is not large, the above algorithm is very effective.

For the following three JOIN types, which table is the driving table:

SELECT * FROM R LEFT JOIN S ON R.x = S.x WEHRE ...
SELECT * FROM R RIGHT JOIN S ON R.x = S.x WEHRE ...
SELECT * FROM R INNER JOIN S ON R.x = S.x WEHRE ...

1, inner join

For INNER JOIN, the driving table may be table R or table S. Displays the data shared by the left and right sides.

In this scenario, whoever needs to query the smaller amount of data will drive the table. Let’s look at the following example

SELECT * FROM R INNER JOIN S  ON R.x = S.x  WHERE R.y = ? AND S.z = ?

2, LEFT JOIN

For the above Left Join, the driving table is the left table R; Right Join , the driver table is the right table S. This is the JOIN type that determines whether the data in the left table or the right table must be queried.

Returns all records in the left table and records with equal join fields in the right table. Returns all rows from the left table even if there is no match in the right table.

SELECT * FROM R LEFT JOIN S  ON R.x = S.x  WHERE R.y = ? AND S.z = ?

3. RIGHT JOIN

For the above Left Join, the driving table is the left table R; in the Right Join, the driving table is the right table Table S. This is the JOIN type that determines whether the data in the left table or the right table must be queried.

Returns all records in the right table and records that are equal to the join field in the left table. Returns all rows from the right table even if there is no match in the left table.

SELECT * FROM R RIGHT JOIN S  ON R.x = S.x  WHERE R.y = ? AND S.z = ?

2. MySQL database Hash Join

1. What is Hash Join

MySQL The second JOIN in is Hash JOIN, which is used when the join condition between two tables does not have an index.

If there is no connection, is it okay to create an index?

If some columns are indexes with low selectivity, the data must be sorted when creating the index to import the data, which will affect the import performance; the secondary index will have problems returning the table. If the amount of filtered data is large, a direct full table scan will be faster.

For OLAP business queries (OLAP is online analysis processing, is used for data analysis, which enables us to analyze information from multiple database systems at the same time), ha Greek connections are an essential feature. MySQL 8.0 begins to support the Hash Join algorithm, strengthening support for OLAP business.

Therefore, if the amount of data you query is not too large, and the response time of the query is required to be at the minute level, you can use a single instance of MySQL 8.0 to complete the query of big data.

2. How Hash JOIN works

Hash JOIN appears in the execution plan of MySQL 8.0. Hash JOIN scans the two associated tables: First, a hash table is created during the scan of the drive table; when the second table is scanned, the hash table is searched for each associated record. If found, the record will be returned.

Hash join selection driver table and nested loop join algorithm, both are basically the same. Both smaller tables are used as driver tables. If the driver table is large and the hash table created exceeds the memory size, MySQL will automatically dump the results to disk.

3. Subquery

I found that quite a few development students, including myself, prefer to write subqueries instead of traditional JOIN statements.

The logic of the subquery is very clear. Although JOIN can also meet the needs, it is not easy to understand because LEFT JOIN is an algebraic relationship and subqueries are more inclined to be understood from the perspective of human thinking.

However, in MySQL 8.0, the optimizer will automatically optimize the in subquery into a JOIN execution plan, which will significantly improve performance.

We only need to pay attention to the SQL execution plan. If the two execution plans are the same, there will be no difference in performance.

Prior to MySQL 8.0, MySQL did not fully optimize subqueries. Therefore, you will see the prompt DEPENDENT SUBQUERY in the execution plan of the subquery, which indicates that it is a dependent subquery, and the subquery needs to rely on the association of the external table. DEPENDENT SUBQUERY can be very slow to perform, and most of the time you need to manually convert it into a join between two tables.

So the blogger heretipsEveryone, if your current MySQL 8.0 version can write subqueries, because the optimization of subqueries is quite complete;

For MySQL before 8.0 version of MySQL, you need to view the SQL execution plans of all subqueries. Tips for DEPENDENT SUBQUERY must be optimized, otherwise it will have a significant performance impact on the business; the optimization of DEPENDENT SUBQUERY is usually rewritten as a derived table for table joins.

Recommended learning: mysql video tutorial

The above is the detailed content of Summary of MySQL principles: left join, right join, inner join and Hash join. For more information, please follow other related articles on the PHP Chinese website!