Shulou(Shulou.com)05/31 Report--

Editor to share with you the SQL statement optimization of JOIN and LEFT JOIN and RIGHT JOIN statement example analysis, I hope you will learn something after reading this article, let's discuss it together!

In the application of database, we often need to do multi-table query on the database, but when the amount of data is very large, multi-table query will have a great impact on the execution efficiency, so we should pay special attention to using JOIN, LEFT JOIN and RIGHT JOIN statements.

The join principle of the SQL statement:

The join operation in the database is actually an association between one table and another table, and many errors are understood as: first take a Dekal product of these two tables, then throw them into memory, and filter them slowly with where and having conditions. In fact, the database is not so stupid, it will take up a lot of memory, and it is inefficient, for example, we only need some rows of one table and some rows of another table. If the whole table does Dekar product, it is too expensive. The real practice is to traverse one table while traversing other tables according to the conditions on each table. After finding that the final condition is met, it will be sent to the client, until all the final data has been checked, which is called nested loop query.

1. LEFT JOIN and RIGHT JOIN optimization

In MySQL, the implementation is as follows:

1. Table B depends on Table An and all tables on which A depends

2. Table A depends on all tables except LEFT JOIN's table (B)

3. Join_condition decides how to read the table. The where condition is of no use to B.

4. Standard where and LEFT JOIN will be jointly optimized.

5. If the line in A satisfies the where and having conditions, but not in B, it will be populated with null

RIGHT JOIN is similar to LEFT JOIN in that this position is interchangeable

In principle, the conversion between LEFT JOIN and normal JOIN can be directly converted to normal join when the where condition always returns false for the generated null rows.

Such as:

SELECT * FROM T1 LEFT JOIN T2 ON (column1) WHERE t2.column2=5

Will be converted to:

SELECT * FROM T1, T2 WHERE t2.column2=5 AND t1.column1=t2.column1

Note: because the condition t2.column2 = 5 is set, it is not true for all generated rows whose T2 is null.

Such optimization will be very fast, because it is equivalent to converting an external connection to an equivalent join, reducing the scanning and judgment of a lot of lines.

Nested loop JOIN algorithm-Nested-Loop Join

The simple nested loop algorithm starts with a table, finds a row through the conditions of the table, then finds the data of the next table, and then goes back to the first table to find the rows that meet the conditions.

For example, there are three tables T1, T2, T3, whose join type is:

Table Join Typet1 ranget2 reft3 ALL

The resulting pseudo code is

For each row in t1 matching range {for each row in t2 matching reference key {for each row in t3 {if row satisfies join conditions, send to client}

That is, the T1 table passes the range scan, and T2 associates T1 and T3 as a full table scan.

Note: first find a row according to the condition range of the T1 table, match it with T2, and then find the qualified row of T3.

Block nested Loop JOIN algorithm-Block Nested-Loop Join

The application of this algorithm is as follows: because the previous nesting algorithm reads the following table after each row of the table, so the internal table will be read many times, so the database uses the join cache (join buffer) to store the intermediate results, and then when reading the internal table, find a row, which is compared with the data in this cache to improve efficiency. For example, when reading 10 rows from the outside at a time, and then reading the internal table, it is compared with these 10 rows of data.

The conditions for MySQL to use join buffer are:

1. The join_buffer_size system variable determines the size of buffer used by each join.

2. Join buffer can be used only when the join type is index or all.

3. Each join will be assigned a join buffer, that is, a sql may use multiple join buffer

4. Join buffer will not be assigned to the first extraordinary scale

5. Only the columns that need to be referenced will be placed in the join buffer, not the entire row

The resulting pseudo code is as follows:

For each row in T1 matching range {for each row in T2 matching reference key {store used columns from T1, T2 in join buffer here stores the columns used by T1 and T2 in join buffer if buffer is full {for each row in T3 {for each T1, T2 combination in join buffer {if row satisfies join conditions, send to client}} empty buffer}} if buffer is not empty {for each row in T3 {for each T1 T2 combination in join buffer {if row satisfies join conditions, send to client}

Note: the data is only stored in join buffer in the second cycle, which confirms point 4 above.

After reading this article, I believe you have some understanding of "sample Analysis of JOIN and LEFT JOIN and RIGHT JOIN sentences for SQL sentence Optimization". If you want to know more about it, welcome to follow the industry information channel, thank you for reading!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.