SQL optimization--Logical optimization--view rewriting and equivalence predicate rewriting

1) View overrides

Type of view:

A) A view constructed with the SPJ format, called a simple view.

CREATE VIEW v1 as SELECT x, Y, z from T;

b) A view constructed in a non-SPJ format (with operations such as GroupBy), called a complex view.

CREATE VIEW v2 as SELECT x, Y, Z from T ORDER by X;

View Overrides:

A) The View object appears in the query statement

b) The View object disappears after query optimization

c) The query statement of the Vanishing View object, fused into the initial query statement

MySQL View rewrite guidelines:

A) MySQL supports view optimization.

b) The optimization method is to turn the view into a query against the base table, and then make a similar subquery optimization.

c) MySQL can usually only rewrite simple views, and complex views cannot be rewritten.

2) equivalent predicate rewrite: rewrite the logical expression into an equivalent and more efficient form.

A) Like rule

The like predicate, a pattern-matching comparison operation supported by the SQL standard, is an equivalent rewrite of the LIKE predicate that overrides the LIKE predicate as an equivalent predicate to better utilize the index for optimization. such as:

name like ' abc% ' rewritten as: name >= ' ABC ' and name < ' ABD '

benefits of applying the like rule: A full table scan is only available for the LIKE predicate before conversion, and if an index exists on the name column, the conversion can be Index Scan.

in the like-matched expression, there is no wildcard (% or _), which is equivalent to "=" . such as:

name like ' ABC ' rewritten as: name = ' ABC '

Indexes can be used to improve query efficiency if there is an index on the name column

b) Between-and rules

Between-and predicate, which is a range comparison operation supported by SQL standard;

The Between-and rule, which is an equivalent rewrite of the Between-and predicate, is to rewrite the Between-and predicate as another equivalent predicate to better utilize the index for optimization. such as:

Sno between rewritten as: sno>=10 and Sno <=20

the benefits of the Between-and rule are: If an index is established on the SNO, an index scan can be used instead of a full table scan that is qualified by the original Between-and predicate, thus improving the efficiency of the query.

c) in convert or rule

in is an in operator only operation, not in a subquery. in converting an or rule, which is an or equivalent override of the in predicate, which overwrites the in predicate as an equivalent or predicate, to better utilize the index for optimization. overriding the in predicate equivalent to several or predicates may improve execution efficiency. such as:

Age in (8,12,21) rewritten as: age=8 or age=12 or age=21

whether the efficiency can be improved after applying in or rules, it is necessary to see if the database supports the in predicate only full table scan . If the database has only a full table scan supported on the in predicate and an index exists on the Age column of the table in the OR predicate, the query efficiency increases after the conversion.

D) in convert any rule

In converts any rule, which is an any equivalent override of the in predicate, which overrides the any predicate that is equivalent to the in predicate. in can be converted to Or,or and can be converted to any, so you can directly convert in to any. overriding the in predicate equivalent to any predicate may improve execution efficiency. such as:

Age in (8,12,21) rewritten as: Age any (8,.)

Whether the efficiency can be improved after applying in to the any rule, depending on the database's support for any operation.

e) or convert any rule

or to convert any rule, which is an any equivalent override of the OR predicate, which overwrites the OR predicate as an equivalent of any predicate, to better utilize the Min/max operation for optimization. such as:

sal>1000 OR

Dno=3 and (sal>1100 or sal>base_sal+100) or

sal>base_sal+200 OR

Sal>base_salx2

Rewritten as:

Dno=3 and (sal>1100 or sal>base_sal+100) or

sal> any (1000,base_sal+200,base_salx2)

or to convert any rule, depending on the database support for any operation. (PostgreSQL V9.2.3 and MySQL V5.6.10 currently do not support this rule.) )

f) All/any conversion Set function rules

The all/any conversion set function rule is that the ALL/ANY predicate is rewritten as an equivalent aggregate function Min/max predicate operation to better utilize the Min/max operation for optimization. such as:

Sno>any (2*5+3,SQRT (9)) rewritten as: sno>sqrt (9)

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

The ① example has ">" and "any", which means that the minimum value in "(Ten, 2*5+3,sqrt (9))" is found, so it can be rewritten as "sno>sqrt (9)". Generally, the execution efficiency of the aggregate function Max (), MIN () is generally more efficient than the any and all predicates, so rewriting them in this case can be a better effect.

② If an index exists, the solution max/min is more efficient.

g) Not rule

The equivalent rewrite of the NOT predicate is as follows:

Not (Col_1!=2) rewritten as col_1=2

Not (Col_1!=col_2) rewritten as col_1=col_2

Not (Col_1 =col_2) rewritten as col_1!=col_2

Not (Col_1 <col_2) rewritten as col_1>=col_2

Not (Col_1 >col_2) rewritten as col_1<=col_2

benefits of not rule rewriting: If an index is established on the Col_1, an index scan can be used instead of the original full table scan to improve the efficiency of the query.

h) or override the set rule

The or condition is overridden as a set operation, as in the following SQL example:

SELECT * FROM Student

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

Assuming that all conditional expressions have indexes on the columns (that is, both the sex column and the Age column are indexed), the database might force the query optimizer to use sequential scanning for the where statement in the example, because the statement is retrieving a collection of or operations. In order to use the index to process the above query, you can change the statement to the following form:

SELECT * From student

WHERE sex= ' F ' and age>15

UNION

SELECT * From student

WHERE age>18;

After the rewritten form, you can use the index of the column sex and age to perform an index scan , and then provide the final result by performing the union operation.

A book from the Art of database query optimizer

A

SQL optimization--Logical optimization--view rewriting and equivalence predicate rewriting