SQL optimization--Logical optimization--outer connection, nested connection and connection elimination

1) External Connection removal

Introduction to ① external connections

1) Left Join/left OUTER join: outer JOIN

The result set for the left outward join consists of all the rows of the left table specified in the outer clause, not just the rows that match the join column. If a row in the left table does not have a matching row in the right table, all select list columns in the right table in the associated result set row are null values.

2) Right Join/right OUTER join: Left OUTER JOIN

A right outer join is a reverse connection of a left outward connection. All rows of the right table will be returned. If a row in the right table does not have a matching row in the left table, a null value will be returned for left table.

3) Full Join/full OUTER join: All-out connection

The full outer join returns all rows from the left and right tables. When a row does not have a matching row in another table, the selection list column for the other table contains a null value. If there are matching rows between the tables, the entire result set row contains the data values of the base table.

② Outer Connection elimination

make an outer connection into an inner connection A OUTER JOIN B deform to A JOIN B

The significance of ③ outer connection elimination

A) the query optimizer needs to perform more actions and times than internal connections when processing an outer JOIN operation .

b) After the outer join is eliminated, the optimizer can choose a more flexible choice when selecting the multiple table connection order , which allows for better table join order and faster query execution.

c) Some of the table's join algorithms (such as block nested joins and index looping connections, etc.) are used to reduce the unnecessary I/O overhead and speed up the execution of the algorithm by using the table with the smallest or most restrictive filters as "appearances" (in front of the connection order, the outer loop layer of the multi-layered loop body).

Conditions for elimination of ④ outer joins

The condition in the WHERE clause satisfies a "null value Deny" (also known as the "reject-null" condition).

The Where condition guarantees that a null-generated row is excluded from the result from the right side of the outer join (the right table) and that the condition ensures that the condition is not met when it is applied on a column object with a null value on the right table, so that the result value of the condition is flase or Unkonowen, so The right table will not have a null-valued row generation , so the query is semantically equivalent to an inner join .

explain SELECT * from X left JOIN Y on (x.x_num=y.y_num)

WHERE y.y_numis not NULL;

⑤ Example

A) discrimination on and where differences

On t_1_id = t_2_id: t_1_id and t_2_id to connect

WHERE t_1_id = t_2_id: when the values of t_1_id and t_2_id are equal

Summary of ⑥ outer connection elimination

A) Note the semantic differences between outer joins and inner joins

b) External connection optimization condition: null value denied

c) The nature of the outer join optimization: Semantic is the outer connection, but the wher condition makes the outer connection can degenerate into the inner connection

2) Connection Removal

① Situation one (MySQL not supported )

Unique key/primary key as a join condition, the three-table connection can remove the intermediate table (the columns of the intermediate table are only used as join conditions).

② Situation Two ( MySQL does not support )

Some special forms that can eliminate connection operations (tables that can be eliminated do not appear in any clauses except as connection objects).

③ situation three ( MySQL does not support )

The primary foreign key relationship table is connected, which eliminates the primary key table, which does not affect the query of the foreign key table.

④ Summary

A) Note the technical differences between connection elimination and outer join elimination.

b) Connection elimination removes an object that is connected.

c) Outer Join elimination is the semantics of outer joins, which are transformed into inner joins.

3) Nesting Connection elimination

① concept

There are multiple levels of connections, with parentheses identifying the priority of the connection. Nested connection elimination, is to eliminate the level of nested connections, multiple levels of connection reduced to a less hierarchical connection, as far as possible "flat".

② Summary

A) A nested connection eliminates the level of connectivity, which is a change in the semantic order of a connection.

b) The connection is eliminated, and some of the connected objects are erased.

c) Outside the connection elimination, the elimination is the semantics of the outer connection, make the outer connection deformation for inner connection.

A book from the Art of database query optimizer

SQL optimization--Logical optimization--outer connection, nested connection and connection elimination