SQL optimization -- Logical optimization -- condition simplification, SQL --

SQL optimization -- Logical optimization -- condition simplification, SQL --

1) query Conditions

Query condition concept:

In SQL query statements, the expressions used to filter and connect tuples are expressions in the WHERE/JOIN-ON/HAVING clause.

2) conditional simplification Technology

① Conditional push-down: place the conditions related to a single table in the Process of scanning a single table.

SELECT *

From a, B

Where a. a = 1 and A. B = B. B;

Execution sequence:

A) scan Table A with the condition A. a = 1. Use Table A as the External table of the nested loop.

B) scan table B and perform the join operation with the filter Condition A. B = B. B.

Note: The database system supports conditional push-down, regardless of whether the column object corresponding to the condition has an index. The system automatically performs optimization without human intervention.

② Condition simplification

A) Merge HAVING conditions into WHERE conditions (not supported by MySQL)

Advantage: it is easy to unify and centralize condition clauses, saving time for multiple resolutions.

Note: not all HAVING conditions can be incorporated into the WHERE condition. Only when the GROUPBY condition or clustering function does not exist in the SQL statement can the HAVING condition and WHERE condition be merged.

DB> select * from t3 where a3> 1 having b3 = 3; simplified:

Select * from t3 where a3> 1 and b3 = 3;

B) Remove the redundant parentheses in the expression (supported by MySQL)

Advantage: it can reduce the and or tree layers generated during syntax analysis. --- Reduce CPU consumption

Example:

(A AND B) AND (c AND d) is reduced to: a AND B AND c AND d

C) constant transfer (MySQL supports)

Advantage: for different relationships, we can effectively implement "select push-down" after condition separation, which can greatly reduce the size of intermediate relationships.

Example:

Col_1 = col_2 AND col_2 = 3: col_1 = 3 ANDcol_2 = 3

Note: Any of the operators "=, <,>, <=, >=, <>, <=>, and LIKE, constant transfer may occur in the condition "col_1 <operator> col_2.

D) Eliminate dead code (supported by MySQL): simplify the conditions and remove unnecessary conditions.

Example:

WHERE (0> 1 AND s1 = 5), "0> 1" makes AND constant false, then the WHERE condition constant is false. In this case, it is unnecessary to optimize and execute the SQL statement to speed up query execution.

E) expression calculation (supported by MySQL): calculates the expressions that can be solved to obtain the results.

Example:

WHERE col_1 = 1 + 2 is transformed into: WHERE col_1 = 3

F) Equality Transformation (not supported by MySQL)

Simplify conditions (such as reversing the order of the operands of Relational operators) to change the access path of some tables.

Example:

-A = 3: a =-3

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

G) Inequality Transformation (not supported by MySQL)

Simplify conditions to remove unnecessary duplicate conditions.

Example:

A> 10 AND B = 6 AND a> 2 can be reduced to: B = 6 AND a> 10

H) Boolean expression Transformation

(1) Closure of predicate transfer (not supported by MySQL)

Some comparison operators, such as "<" and ">", are passed and can simplify expressions.

Example:

A> B AND B> 2 You can export a> B AND B> 2 ANDa> 2. "a> 2" is an implicit condition, in this way, "a> 2" and "B> 2" are pushed down to the corresponding relationship, which can reduce the tuples involved in the comparison operation "a> B.

(2) The Boolean expression is converted into an equivalent CNF (supported by MySQL)

Any Boolean expression can be converted into an equivalent CNF. The CNF format is C1, C2, AND... AND Cn; where Ck (1 <= k <= n) is called a combination item, AND each combination item is a Boolean expression that does not contain AND.

Note:

① As long as one of the items is false, the entire expression is false. Therefore, when one item is found to be false in the Code, the judgment of other items can be stopped to speed up the judgment;

WHERE (0> 1 AND s1 = 5)

① Because the AND operator is interchangeable, the optimizer can calculate the expression in the order of ease AND difficulty. Once a merging item is found to be false, it stops the judgment of other merging items AND speeds up the judgment.

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

First solve: 0> 1. The value is false.

(3) index utilization (supported by MySQL)

If an index exists on a joint item, you can first determine whether the index is available. If you can use the index to quickly obtain the value of the joint item, you can speed up the judgment. Similarly, the sub-item in the OR expression can also use the index

Example:

WHERE (A. a> 100 and a. B = 5 AND ...)

Case 1: an index exists in column A of table a, and column B does not have an index. Then, use the index on column a to find the tuples. "A. B = 5" is used as the filter condition.

Case 2: No index exists in column A of table a. If Column B has an index, use the index on column B to find the tuples. "A. a> 100" is used as the filter condition.

I) IS NULL expression optimization (supported by MySQL)

Indexes support optimization of the "is null" expression.

From the book "Art of database query optimizer"