SQL Optimization-logical Optimization-view rewriting and equivalent predicate rewriting, SQL Predicate

SQL Optimization-logical Optimization-view rewriting and equivalent predicate rewriting, SQL Predicate

1) view Rewriting

View type:

A) a view constructed in SPJ format is called a simple view.

Create view v1 as select x, y, z FROM t;

B) A View (with operations such as GROUPBY) constructed in non-SPJ format is called a complex view.

Create view v2 as select x, y, z FROM t order by x;

View Rewriting:

A) view object appears in the query statement.

B) after the query is optimized, the view object disappears.

C) the query statements of the lost view object are integrated into the initial query statement.

MySQL view rewriting rules:

A) MySQL supports view optimization.

B) The optimization method is to convert the view to the query of the base table, and then perform optimization similar to the subquery.

C) MySQL can only rewrite simple views, but not complex views.

2) equivalent predicates Rewriting:Rewrite a logical expression to an equivalent and more efficient form.

A) LIKE rule

The LIKE predicate is a pattern matching comparison operation supported by the SQL standard. The LIKE rule is equivalent overwrite OF THE LIKE predicate, that is, rewrite the LIKE predicate to another equivalent predicate, to make better use of indexes for optimization. For example:

Name LIKE 'abc % 'is rewritten as: name> = 'abc' AND name <'abd'

Benefits of applying the LIKE rule: before conversion, the LIKE predicate can only scan the entire table. If the name column has an index, the LIKE predicate can scan the index.

In the LIKE matching expression, there is no wildcard (% or _), which is equivalent to "=", for example:

Name LIKE 'abc' is rewritten as: name = 'abc'

If the name column has an index, you can use the index to improve the query efficiency.

B) BETWEEN-AND rules

BETWEEN-AND is a range comparison operation supported by the SQL standard;

The BETWEEN-AND rule is equivalent rewriting of the BETWEEN-AND predicate, that is, rewriting the BETWEEN-AND predicate to another equivalent predicate to better use the index for optimization. For example:

Sno BETWEEN 10 AND 20 is rewritten as: sno> = 10 AND sno <= 20

The advantage of BETWEEN-AND rules is that if an index is created on sno, you can use index scanning to replace the full table scan defined by the original BETWEEN-AND predicate, thus improving the query efficiency.

C) IN conversion OR rules

IN is an IN operator operation only, not an IN subquery. The IN conversion OR rule is the OR equivalent rewriting of the IN predicate, that is, rewriting the IN predicate as the equivalent OR predicate to make better use of the index for optimization. Overwrite an IN predicate into several OR predicate, which may improve the execution efficiency. For example:

Age IN (8, 12, 21) is rewritten as: age = 8 OR age = 12 OR age = 21

Whether the efficiency can be improved after applying IN conversion OR rules depends on whether the database only supports full table scan for IN predicates. If the database only supports full table scan for the IN predicate and the age column IN the OR predicate table has an index, the query efficiency will be improved after conversion.

D) IN conversion ANY rule

The IN conversion of ANY rules is equivalent rewriting of any in predicates, that is, rewriting IN predicates to equivalent ANY predicates. IN can be converted to OR, OR can be converted to ANY, so IN can be directly converted to ANY. Rewriting an IN predicate to an ANY predicate may increase the execution efficiency. For example:

Age IN (8, 12, 21) is rewritten as: age ANY (8, 12, 21)

Whether the efficiency can be improved after applying IN to convert ANY rules depends on the database's support for ANY operations.

E) OR convert ANY rules

OR to convert ANY rules, that is, ANY equivalent rewriting of OR predicates, that is, rewriting OR predicates as equivalent ANY predicates to better optimize using MIN/MAX operations. For example:

Sal & gt; 1000 OR

Dno = 3 AND (sal> 1100 OR sal> base_sal + 100) OR

Sal> base_sal + 200 OR

Sal> base_sal × 2

Rewrite:

Dno = 3 AND (sal> 1100 OR sal> base_sal + 100) OR

Sal> ANY (1000, base_sal + 200, base_sal × 2)

OR conversion of ANY rules depends on the database's support for ANY operations. (Neither PostgreSQL V9.2.3 nor MySQL V5.6.10 currently supports this rule .)

F) ALL/ANY conversion set function rules

The ALL/ANY conversion set function rule is to rewrite ALL/ANY predicates into equivalent Aggregate functions MIN/MAX predicates for better use of MIN/MAX operations for optimization. For example:

Sno> ANY (10, 2*5 + 3, sqrt (9) is rewritten as: sno> sqrt (9)

The above example of the ALL/ANY conversion set function rule has two points to note:

① The example contains ">" and "ANY", which means to find the minimum value in "(10, 2*5 + 3, sqrt (9, therefore, it can be rewritten as "sno> sqrt (9 )". Generally, the execution efficiency of Aggregate functions such as MAX () and MIN () is generally higher than that of ANY and ALL predicates. Therefore, in this case, rewrite the Aggregate functions, it can achieve better results.

② If an index exists, the efficiency of solving MAX/MIN is higher.

G) NOT rule

Equivalent rewriting of NOT predicates, as follows:

NOT (col_1! = 2) rewrite to col_1 = 2

NOT (col_1! = Col_2) rewrite to col_1 = col_2

NOT (col_1 = col_2) is rewritten as col_1! = Col_2

NOT (col_1 <col_2) is rewritten as col_1> = col_2

NOT (col_1> col_2) is rewritten as col_1 <= col_2

The benefit of NOT rule Rewriting: if an index is created on col_1, you can use index scanning instead of the original full table scan to improve the query efficiency.

H) OR rewrite the Union Rule

The OR condition is rewritten as a union operation, which is shown in the following SQL example:

SELECT * FROM student

WHERE (sex = 'F' AND age> 15) OR age> 18;

Assuming that all the columns of the conditional expression have indexes (both the sex column and the age column have indexes), the database may force the query optimizer to use sequential scanning for the WHERE statement in the example, this statement is used to retrieve the set of OR operations. To use the index to process the preceding query, you can change the statement to the following format:

SELECT * FROM student

WHERE sex = 'F' and age> 15

UNION

SELECT * FROM student

WHERE age> 18;

After rewriting, you can use the index on the column sex and age to scan the index, and then perform the UNION operation to obtain the final result.

From the book "Art of database query optimizer"

A