SQL Optimization-logical Optimization-non-SPJ optimization, sqlspj

SQL Optimization-logical Optimization-non-SPJ optimization, sqlspj

1) group by group Conversion(Not supported by MySQL)

① Move group operations down

The GROUPBY operation may greatly reduce the number of relational tuples. If you can group a link and then connect between tables, the connection efficiency may be improved. This optimization method is to execute group operations in advance. The meaning of "Move Down" is to keep grouping operations close to leaf nodes as much as possible on the query tree, so that grouping operation nodes are less than some selection operations.

② Group operations move up

If the connection operation can filter out most of the tuples, perform the GROUPBY operation after the connection is established, which may improve the efficiency of grouping operations. This optimization method is to set group operations and then execute them. The meaning of "move up" is the opposite of "Move Down.

For SQL statements in non-SPJ format with operations such as GROUPBY, the technology mentioned earlier in this section applies only to group operations based on the semantics of the GROUPBY operation. Because the GROUPBY operation moves down or up cannot guarantee a better query efficiency after rewriting, you must use the cost-based method in the query optimizer to estimate the advantages and disadvantages of several paths.

③ MySQL group by optimization

MySQL typically scans the entire table and creates a temporary table for grouping. "Usingtemporary" appears in the query execution plan, indicating that MySQL adopts the conventional processing method. For the optimization of GROUPBY, the index should be used as much as possible.

2) order by optimization

① Order By Elimination (OBYE)

The optimizer removes unnecessary sorting operations (such as using indexes) in statements before generating execution plans ), avoid sorting operations or operations caused by sorting in the execution plan (such as sorting on the index column, you can use the index to remove sorting operations ).

② Sort push down (Sort push down)

Push the sorting operation down to the base table as much as possible. The joined result of the ordered base table conforms to the sorting semantics, so as to avoid sorting in the final large join result set.

③ MySQL group by optimization

The condition for using indexes is that the column objects in the grouping clause originate from the same btree index (Hash indexes cannot be used for optimization) partially Ordered keys of all or prefix parts (indexes cannot be used if the index columns used by the Group do not match the index creation sequence ).

The main methods are as follows:

A) Loose IndexScan

You can directly use indexes to retrieve Group Columns in A group operation. You do not have to consider that all the keys of the index satisfy the WHERE clause. as long as some of the column objects in the WHERE clause are matched (loose, use partial columns in the index as "loose ").

B) Tight IndexScan

All keys in the index match the column object in the WHERE clause (tight, using all columns in the index as "strict ").

3) DISTINCT Optimization

① DISTINCT Elimination (Distinct Elimination) (supported by MySQL)

If a table contains a primary key, a unique constraint, or an index, DISTINCT in the query statement can be eliminated (this optimization method is also involved in semantic optimization, which is essentially the scope of semantic Optimization Research ).

② DISTINCT Push Down (not supported by MySQL)

When generating an execution plan for the anti-semi-join query containing DISTINCT, perform the anti-semi-Join Operation first and then perform the DISTICT operation. It may be better to execute the DISTICT operation first and then the anti-semi-Join Operation; this is to use the link semantics to ensure that the unique feature is used for DISTINCT optimization.

③ DISTINCT migration (Distinct Placement) (not supported by MySQL)

Execute DISTINCT on the result of the connection operation, and may move DISTINCT to a subquery for priority (some books call this technology "DISTINCT configuration ").

4) LIMIT Optimization

① MySQL LIMIT Optimization

A) Impact of LIMIT on single table scan: If index scan is available and takes less than full table scan, use index scan to implement LIMIT (LIMIT takes a small number of rows, otherwise, the optimizer is more inclined to use full table scan ).

B) Impact of LIMIT on Sorting: if the LIMIT clause and ORDERBY clause are used together, after the number of ordered metagroups set by LIMIT is obtained, subsequent sorting operations will not be performed.

C) Impact of LIMIT on deduplication: if the LIMIT and DISTINCT clauses are used together, after the unique number of metagroups set by LIMIT is obtained, subsequent deduplication will not be performed.

D) LIMIT is affected by the Group: if the LIMIT clause and the GROUPBY clause are used together, the LIMIT operation does not have to be counted until the next group starts to calculate the total number of groups in an indexed ORDER.

E) LIMIT 0: returns an empty result set.

F) MySQL supports LIMIT optimization without HAVING clauses.

5) set Operation Optimization

① MySQL set Operation Optimization

A) MySQL Syntax:

SELECT...

UNION [ALL | DISTINCT] SELECT...

[UNION [ALL | DISTINCT] SELECT…]

B) query rewrite rules: OR rewrite the union rules-MySQL does not support.

C) it is complicated to introduce cost estimation to evaluate the overwrite cost.

6) Summary

① Common heuristic rules

A) Eliminate nested connections: if they are all inner connections, you can remove the parentheses that indicate the nested relationship.

A join (B join C) = A join B join C

B) Select Operation PUSH-down.

C) Projection operation push-down.

② Common experience rules

A) Perform sorting on the index key. Generally, data is read in order without sorting.

B) when the selection rate is lower than 10%, the indexing effect is usually better than reading table data.

C) when the table data volume is small, full table scan may be better than other methods (such as using indexes ).

From the book "Art of database query optimizer"