SQL Optimization-query optimization technology classification, SQL Optimization-Classification

SQL Optimization-query optimization technology classification, SQL Optimization-Classification

Database optimization methods are generally as follows:

1) manual optimization.

It mainly depends on people and is inefficient. It requires the operator to fully understand the principles on which common sense depends. It also requires extensive and profound understanding of applications, database management systems, operating systems, and hardware.

2) Case-Based Optimization.

Summarizes the recommended configuration values of database parameters and the design of the data logic layer in typical application cases, so as to provide some reference for user optimization. However, this method ignores the dynamic nature of the system and the differences between different systems.

3) Self-tuning.

Create a model for the database system. The database system automatically configures parameters based on "factors affecting the performance and efficiency of the database system. Some commercial databases have implemented some self-tuning technologies.

Classification of query optimization technologies: ① query reuse, ② query rewriting rules, ③ query algorithm optimization, ④ parallel query optimization, ⑤ distributed query optimization, and ⑥ other optimization technologies

1)Query Reuse: It refers to the use of previous execution results as much as possible to save time for querying the entire process of computing and reduce resource consumption.

There are two types:

A) Reuse of query results. Allocate a buffer block in the cache to store the SQL statement text and the final result set. When the same SQL statement is input, the result is directly returned. The Reuse Technology of query results saves the query plan generation time and reduces the resource consumption throughout the query execution process.

B) Reuse of query plans.Cache the execution plan of a query statement and its corresponding syntax tree. The Reuse Technology of the query plan reduces the time and resource consumption of the query plan generation.

2) Query Rewriting: Is an equivalent conversion of a query statement, that is, any State in any relevant mode will produce the same result.

Query Optimization Technology type:

A) syntax level. The query language layer is optimized based on syntax.

B) algebra level. The query uses the formal logic for optimization and the relational algebra principle for optimization.

C) semantic level. Based on integrity constraints, you can understand the semantics of the query statements and recommend some optimization operations.

D) physical level. The physical optimization technology, based on the cost estimation model, compares the minimum cost of various execution methods.

Query Rewriting is based on optimization of syntax, algebra, and semantics. It can be unified to the scope of logical optimization: The cost-based estimation model is physical layer optimization, is the process of selecting the path with the minimum cost from the connection path.

Query Optimization rewrite ideas:

A) converts a procedural query to a descriptive query, for example, overwrites a view.

B) convert complex queries (such as nested subqueries, external join elimination, and nested join elimination) into multi-table join queries as much as possible.

C) convert less efficient predicates into equivalent efficient predicates (such as equivalent predicates rewriting ).

3) query optimization algorithm:The process of solving the efficient execution plan of a given query statement. The query plan, also known as the query tree, is composed of a series of internal operators. These operators constitute a query execution scheme based on certain operational relationships.

Policies for generating the optimal query plan:

A) Rule-based optimization.

Defined as "rules" based on experience or some methods that have been found or proved to be valid (for example, rules learned based on relational algebra and rules learned based on experience ), use these rules to simplify the query plan generation process and conform to the operation that can be simplified. use heuristic rules to exclude some obviously bad access paths. This is rule-based optimization.

B) cost-based optimization.

Based on a cost evaluation model, when a query plan is generated, the cost of each access path (the access path mainly includes the preceding three "relational nodes") is calculated, then, select the sub-path with the minimum cost to obtain a complete path until all tables are connected. Mainstream databases adopt rules-based and cost-based technologies.

4) parallel query optimization:In the parallel database system, the goal of query optimization is to find the query execution plan with the minimum response time. This requires that the query work be divided into subjobs that can run in parallel. Some commercial databases provide the parallel query function to Optimize Query execution operations.

In the same SQL statement, query parallelism can be divided:

A) parallel operation. The same operation, such as a single table scan operation, two table join operation, and sorting operation, is divided into multiple independent sub-operations, which are executed simultaneously by different CPUs.

B) Parallel Operation. An SQL query statement can be divided into multiple sub-operations and executed by multiple CPUs.

5) Distributed Query Optimization:In distributed database systems, query policy optimization is the focus of query optimization. Data transmission policies are used to connect the data at node A and Node B. Data at node A is transmitted to Node B, from Node B to node A, or filtered before transmission) and local processing optimization (traditional Query Optimization Technology for Single-node databases ).

In a distributed database system, the cost estimation model is:

Total cost = I/O cost + CPU cost + communication cost