SQL optimization--Logical optimization--Non-SPJ optimization

1)group by group conversion (MySQL not supported )

① group Operation Move Down

GROUPBY operations may significantly reduce the number of relationship tuples, and if a relationship can be grouped before a connection between tables, it is likely to increase the efficiency of the connection. This optimization is done in advance of the grouping operation. The meaning of the move Down is that in the query tree, the grouping operation is as close to the leaf node as possible, so that the node of the grouping operation is lower than some selection operation.

② group operation up Move

If the connection operation can filter out most tuples, then the groupby operation after the first connection, may improve the efficiency of the grouping operation. This optimization is done by placing the grouping operation behind. The meaning of the move up is exactly the opposite of the move down.

For SQL statements in non-SPJ format with operations such as GROUPBY, the techniques mentioned earlier in this section apply, but they combine the semantics of the GROUPBY operation with grouping operations. Because the GROUPBY operation cannot guarantee that the rewritten query will be more efficient, the cost-based approach in the query optimizer should be used to estimate the merits and demerits of certain paths.

③mysql GROUP BY optimization

MySQL's handling of GROUPBY usually takes the form of scanning an entire table and creating a temporary table to perform the grouping operation. The word "usingtemporary" appears in the query execution plan, which means that MySQL uses a conventional approach. for GROUPBY optimization, use the index as much as possible.

2) ORDER by optimization

① Sort elimination (order by elimination, Obye)

Before generating execution plans, the optimizer eliminates the need for sorting operations in the statement, such as the use of indexes, to avoid sorting operations in the execution plan or for operations caused by sorting, such as sorting on indexed columns, which can be eliminated by using the index to eliminate sorting operations.

② sort push down (sort pushed down)

By pushing the sort operation to the base table as much as possible, the result of the ordered base table being concatenated conforms to the ordering semantics, which avoids the sorting operation on the final large connected result set.

③mysql GROUP BY optimization

The criteria for using an index are: The Column object in the grouping clause originates from a partially ordered key of the same Btree index (which does not support the optimization of the hash index) for all or a portion of the prefix (the index column used by the grouping does not match the order established by the index).

The main ways are:

A) Loose Indexscan

Complete the retrieval of grouped columns directly with the index, without having to consider that all keys of the index satisfy the WHERE clause, as long as there is a partial match to the Column object in the Where (loose, which is partially listed as "loose" in the index).

b) Tight Indexscan

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

3) DISTINCT Optimization

①distinct Elimination (DISTINCT elimination) ( MySQL Support )

If there are primary keys, unique constraints, indexes, etc. in the table, the DISTINCT in the query statement can be eliminated (this optimization is also involved in semantic optimization, which is essentially the category of semantic optimization research).

②DISTINCT push-in (DISTINCT push down)(MySQL not supported )

When generating an inverse semi-join query execution plan with DISTINCT, the inverse semi-connection is first performed and then the distict operation is performed, and perhaps the distict operation and then the inverse semi-connection may be better; this is an optimization that uses the connection semantics to ensure that the unique feature is DISTINCT.

③distinct migration (DISTINCT Placement)(MySQL not supported )

Performing DISTINCT on the results of the join operation may move the DISTINCT to a subquery first (some books refer to this technique as "DISTINCT configuration").

4) Limit Optimization

Limit optimization for ①mysql

A) effect of limit on single-table scanning: If an index scan is available and spends less than a full table scan, use index scanning to implement the limit (limit takes a very small number of rows, otherwise the optimizer prefers to use a full table scan).

b) The effect of limit on sorting: If the limit and the ORDER BY clause are used together, subsequent sorting operations will no longer take place when the number of ordered tuples is taken to the limit set number.

c) The effect of limit on the weight: if the limit and the distinct clause are used together, subsequent de-redo operations will not take place until the number of unique tuples that have the limit set number is taken.

D) limit is affected by grouping: If the limit and GROUPBY clauses are used together, the limit operation does not have to count until the next grouping is calculated by GROUPBY the total number of each group by index.

e) LIMIT 0: Returns an empty result set directly.

f) MySQL supports optimization of a LIMIT without a HAVING clause.

5) set operation optimization

Set operation optimization for ①mysql

A) MySQL syntax:

SELECT ...

UNION [All | Distinct]select ...

[UNION [All | Distinct]select ...]

b) Query rewrite rules: OR override the Set rule---MySQL is not supported.

c) The need to introduce cost estimates to assess the cost of rewriting is more complex.

6) Summary

Common heuristic rules for ①

A) nested join elimination: If it is an inner join, you can remove the parentheses that represent the nested relationship.

A join (b join c) = = A Join B join C

b) Select the action to push down.

c) Push down the projection operation.

② common rules of thumb

A) Perform a sort operation on the index key, usually using the order of the index to read the data sequentially without ordering.

b) When the selection rate is less than 10%, the effect of using the index is usually better than reading the table data.

c) A full table scan may be preferable to other methods (such as using an index) when the table has a small amount of data.

A book from the Art of database query optimizer

SQL optimization--Logical optimization--Non-SPJ optimization