Database and database Learning

Database and database Learning
Physical Optimization

Algebraic optimization changes the order and combination of operations in a query statement, and does not involve the underlying access path.
There are many access solutions for a query statement, and their execution efficiency is different. It is not enough to perform algebra optimization only.
Physical optimization is to select an efficient and reasonable operation algorithm or access path to obtain an optimized query plan.

Optimization of access path selection based on heuristic rules

1. heuristic rules for selecting operations:

Ii. heuristic rules for connection operations:
1. If both tables have been sorted by Connection Properties
Select sort-merge Method
2. If a table has an index on the connection property
Select the index connection method
3. If none of the above two rules apply, one of the tables is small.
Use the Hash join method

. You can select the nested loop method and select a small table. Specifically, the table occupies a small number of blocks (B) as the External table (External Loop table ).
Reason:
Set the number of blocks occupied by the connection table R and S to Br and Bs respectively.
The number of memory buffer Blocks Used for connection operations is K.
Assign K-1 block to external
If R is an external table, the number of blocks accessed by nested loops is:
Br + (Br/(K-1) * Bs
Apparently, a small table should be selected as the External table.

I. Statistics
The cost-based optimization method calculates the execution cost of various Operation algorithms, which is closely related to the database status.

Cost-based Optimization

Statistics required by the optimizer stored in the data dictionary:
1. For each basic table
Total number of tuples in the table (N)
Tuples (l)
Number of blocks occupied (B)
Number of overflow blocks occupied (BO)

2. Each column in the base table

Number of different values in this column (m)
Selection rate (f)
If the distribution of different values is even, f = 1/m
If the distribution of different values is uneven, the selection rate of each value is equal to the number of groups with this value/N
Maximum value of this column
Minimum value of this column
Whether the column has been indexed
Index type (B + tree index, Hash index, and clustered index)
3. Index (for example, B + tree index)
Index layers (L)
Number of different index values
Index selection base number S (S tuples have a certain index value)
Number of leaf nodes in the index (Y)

Ii. Price Estimation example

Full table scan algorithm Cost Estimation Formula

If the basic table size is B, the cost of the full table scan algorithm is cost = B.

If the selection condition is code = value, the average search cost is cost = B/2.

If the comparison condition is>, >=, <, <= operation
Assume that half of the tuples meet the conditions, we need to access half of the leaf nodes.
Access half of the table Storage blocks through indexes. cost = L + Y/2 + B/2

Complex queries, especially those involving connections and nesting
Do not place all optimized tasks on RDBMS
We should find out the optimization rules of RDBMS to write SQL statements suitable for Automatic Optimization of RDBMS.
Understand the specific query plan representation and analyze the actual query execution policies