SQL optimization--Logical optimization--conditional simplification

1) Query criteria

Query Criteria Concepts:

An expression that filters and joins a tuple in a SQL query statement, form is the expression that appears in the where/join-on/having clause.

2) Conditional Simplification technology

① : The conditions associated with a single table are placed in the process of scanning a single table for execution.

SELECT *

From A, B

WHERE A.a=1 and a.b=b.b;

Execution order:

A) scan a, and with conditional a.a=1, a table as the appearance of nested loops

b) Scan B to perform connection operation with filter conditions a.b=b.b

Description: The database system supports push-down conditions, regardless of whether the condition corresponds to a Column object with or without indexes. The system is automatically optimized without manual intervention.

② Conditional Simplification

A) incorporate the having condition into the Where condition (MySQL does not support )

Advantages: It is easy to unify and dissolve the conditional clauses, and save the time of resolving the times.

Note: There is no case that the having condition can be incorporated into the where condition, and only if the groupby condition or aggregation function does not exist in the SQL statement can the having condition be combined with the where condition.

Db> select * from T3 where a3>1 have b3=3; Simplification is:

SELECT * from T3 where a3>1 and b3=3;

b) Remove redundant parentheses in the expression (MySQL support )

Pros: You can reduce the levels of and and or trees that occur when parsing. ---Reduce CPU consumption

Example:

( (A and B) and (C and D)) Simplification is: A and B and C and D

c) Constant delivery (MySQL support )

Advantages: The different relationships can effectively implement the "selective push" after the condition separation, which can greatly reduce the scale of the intermediate relationship.

Example:

Col_1 = col_2 and col_2 = 3 Simplification is: col_1=3 and col_2=3

Note: Any of the operators "=, <, >, <=, >=, <>, <=>, like" can be passed in a constant in the Col_1 < operator > col_2 condition.

d) Eliminate dead code (mysql support ): Simplify conditions and remove unnecessary conditions.

Example:

Where (0 > 1 and S1 = 5),"0 > 1" makes and constant false, then where condition is constant false. At this point, it is unnecessary to optimize and execute the SQL statement, speeding up the query execution speed.

e) Expression calculation (mysql support ): The expression that can be solved is calculated and the result is obtained.

Example:

WHERE Col_1 = 1 + 2 Transform to: WHERE col_1 = 3

f) Equation transformation (MySQL not supported )

Degenerate conditions (such as the order of the operands of the inverse relational operator), which alters the access path of some tables

Example:

-A = 3 can be simplified to: A =-3

The benefit is that if there is an index on a, you can use an index scan to speed up access.

g) Inequality Transformation (MySQL not supported )

Simplify the condition and remove unnecessary repetition conditions.

Example:

a > b = 6 and a > 2 can be simplified to: B = 6 and a > ten

h) Boolean-expression transformation

(1) Predicate transitive closure (MySQL not supported )

Some comparison operators, such as "<", ">" and so on, have transitivity and can play the role of simplifying expressions.

Example:

A>b and B>2 can deduce a>b and b>2 and a>2 , "A>2" is an implied condition, so the "a>2" and "b>2" respectively pushed to the corresponding relationship, you can reduce the tuple that participates in the comparison operation "A>b".

(2) The Boolean expression is converted to an equivalent combined paradigm (CNF)(mysql support )

Any Boolean expression can be converted to an equivalent combined paradigm (CNF), the format of the combined paradigm is: C1 and C2 and ... and Cn, where Ck (1<=k<=n) is called a fetch, and each collection is a Boolean expression that does not contain and.

Description

① if one is false, the entire expression is false, so the code can be found when a collection is false, that is, stop the judgment of other items, speed up the judgment;

WHERE (0 > 1 and S1 = 5)

① In addition, because the AND operator is exchangeable, the optimizer can calculate the expression in the order of first easy and difficult, and once it finds that a collection is false, it stops the judgment of the other items and speeds up the judgment.

WHERE (a.a+b.b > A.B = 5 and 0 > 1 )

Solve first:0 > 1 , Value false, other no longer solved

(3) Index utilization (mysql support )

If an index exists on a collection, it can be faster to determine whether the index is available, such as the ability to quickly derive the value of the fetch by using the index. Similarly, children in an or expression can also take advantage of an index

Example:

WHERE (a.a> and a.b = 5 and ...)

If there is an index on column A of the 1:a table and the B column is not indexed, use the index on a to find the tuple, and "A.B = 5" is used as a filtering condition.

If the index is not present on column A of the 2:a table and the B column has an index, a tuple is found using the index on B, and "A.a> 100" is used as a filtering condition.

i) is null expression optimization (mysql support )

An index is used to support the optimization of an IS-NULL expression.

A book from the Art of database query optimizer

SQL optimization--Logical optimization--conditional simplification