Database-Query Optimizer

All databases perform query optimization on SQL statements. What is the process like?

First, the query optimizer uses the equivalent rule system to generate an expression equivalent to the given expression. In terms of concept, a new expression is generated when the expression matches one side of the equivalence rule. To reduce the space requirement, most optimizers will point the expressions with common subexpressions to the shared subexpressions. In addition, if you consider the execution cost estimation, you can avoid checking some expressions.

For the equivalence rules of relational algebra, see: http://jsjedu.hxu.edu.cn/sjkyl/4/4.2.4.htm.

After an expression is generated, You need to define what each operation usesAlgorithmAnd how to coordinate the operation execution. One way to select an execution plan is to simply select an algorithm with the minimum cost for each operation. However, considering the overall situation, for example, although merging connections at a given level is more expensive than hash connections, it produces ordered output, reducing the future computing cost. The actual query optimizer combines the following two optimization methods:

Cost-based Optimization

Use equivalent rules to generate a series of query execution plans for a given query statement, and select one with the lowest cost. Generally, for N relationships, there are (2 (n-1 ))! /(N-1 )! Different connection sequence. In fact, instead of checking all connections, we need to find the optimal connection sequence for each subset of n given relational sets, we also need to find the optimal connection order for the sorting order of each subset and the result of the subset join. The total number of subsets of N relationships is 2 ^ N, that is, about 2 ^ n join expressions need to be stored. The time cost of the algorithm used to process the sorting order is 3 ^ n.

Heuristic optimization

Many system heuristic methods reduce the number of alternative solutions in a cost-based approach. The heuristic optimizer does not verify whether the cost is reduced after a rule is used for conversion. Rule 1: Perform the selection operation as soon as possible. Rule 2: perform projection operations as soon as possible. Typical steps of heuristic algorithms:

1. Split the selection into a single sequence of selection operations.

2. Push the selected operation down the query tree to the earliest possible place for execution.

3. Determine which selection operations have the smallest relationship with connection operations. The Union Law of The Join Operation is used to limit the selection of leaf nodes with strict operation conditions.

4. Replace the Cartesian Product Operation followed by the selected condition with the join operation.

5. Break down the projection attributes and push them down on the query tree as much as possible.

6. Identify the sub-trees that can be operated by executors in the streamline mode and execute them in the pipeline.

Instance:

Systemr optimizer: only considering the sequence in which the operands are connected to the original relationship, it is convenient for pipeline computing.

Sybase optimizer: scans secondary indexes to consider the probability of pages containing the tuples in the buffer zone.

Oracle7: Minimize the number of nested loop connections without indexes in the inner link and the number of sorted and merged connections.