SQL Optimization-logical Optimization-external connections, nested connections, and elimination of connections, SQL nesting

SQL Optimization-logical Optimization-external connections, nested connections, and elimination of connections, SQL nesting

1) eliminate external connections

① External connection Introduction

1) left join/left outer join: LEFT OUTER JOIN

The result set of left outer Join includes all rows in the LEFT table specified in the left outer clause, not just the rows matched by the join column. If a row in the left table does not match a row in the right table, all selection list columns in the right table in the row of the associated result set are null.

2) right join/right outer join: RIGHT OUTER JOIN

The right outer join is a reverse join of the left Outer Join. All rows in the right table are returned. If a row in the right table does not match a row in the left table, a null value is returned for the left table.

3) full join/full outer join: all OUTER JOIN

All rows in the left and right tables are returned. If a row does not match a row in another table, the selection list column of the other table contains a null value. If there are matched rows between tables, the entire result set row contains the data value of the base table.

② Eliminate external connections

Convert outer join to inner join a outer join B and convert it to a join B.

③ Meaning of external connection Elimination

A) query the operations and time required by the optimizer to process external connectionsMore than internal connections.

B) after the external connection is eliminated, the optimizer selectsMulti-Table connection SequenceYou can have more flexible options to select a better table connection sequence and speed up query execution.

C) some table connection algorithms (such as block nested connections and index loop connections) when a table with a small size or the strictest filtering conditions is used as an "External table" (placed at the beginning of the connection order, it is the External Loop layer of a multi-layer loop body ), this reduces unnecessary I/O overhead and accelerates Algorithm Execution.

④ Conditions for external connection Elimination

The condition in the WHERE clause satisfies the "NULL value rejection" (also known as the "reject-NULL" condition ).

The WHERE condition ensures that rows with NULL values generated on the right (right table) are excluded from the results (that is, when the condition ensures that the column object with NULL values is applied on the right strap, if the condition is not met, the result value of the condition is FLASE or UNKONOWEN, so that no row with a NULL value is generated in the right table), the query can be semantically equivalent to an internal join.

Explain SELECT * from x left join y on (X. X _ num = Y.Y _ num)

WHERE Y.Y _ num is not null;

⑤ Example

A) differentiate ON and WHERE

ON t_1_id = t_2_id: t_1_id and t_2_id are connected.

WHERE t_1_id = t_2_id: When t_1_id and t_2_id are equal

Summary of external connection Elimination

A) Pay attention to the semantic differences between external connections and internal connections.

B) condition for Outer Join optimization: the null value is rejected.

C) The essence of Outer Join optimization: the semantics is outer join, but the WHER condition allows outer join to become inner join.

2) Eliminate connections

① Case 1 (not supported by MySQL)

The unique key/primary key is used as the connection condition, and the intermediate table can be removed from the connections in the three tables (the columns in the intermediate table are only used as the connection condition ).

② Case 2 (not supported by MySQL)

Some special forms can be used to remove Join Operations (tables that can be eliminated do not exist in any clause except as join objects ).

③ Case 3 (not supported by MySQL)

The connection between tables with the primary and foreign key relationships eliminates the primary key tables, which does not affect the query of the foreign key tables.

④ Summary

A) Pay attention to the technical differences between connection elimination and external connection elimination.

B. Remove an object to be connected after the connection is eliminated.

C) The Outer Join removes the semantics of the Outer Join and is transformed into the inner join.

3) Eliminate nested connections

1. Concept

The connection has multiple layers, and brackets are used to identify the priority of the connection. The elimination of nested connections is to eliminate nested connection layers and reduce connections of multiple layers to fewer connections, so as to "flat" as much as possible ".

② Conclusion

A) the hierarchy of links eliminated by nested connections. This is a change in the semantic order of connections.

B) the connection is eliminated. Some connected objects are eliminated.

C) The outer join is eliminated. the semantics of the outer join is eliminated, and the outer join is transformed into an inner join.

From the book "Art of database query optimizer"