SQL optimization--Query optimization technology classification

there are usually several ways to tune a database:

1) Manual tuning.

The

main reliance on people, inefficient, requiring the operator to fully understand the principle of common sense depends on, but also the application, database management system, operating system and hardware have a broad and deep understanding.

2) Case-based tuning .

This

paper summarizes the recommended configuration values of database parameters and the design of data logic layer in typical application cases, and provides some references for the user's tuning work. However, this approach ignores the dynamic nature of the system and the differences between the different systems.

3) Self-tuning.

In order to establish a model for database system, the database system automatically configures the parameters according to the factors that affect the performance efficiency of database system. Some of the business databases, the realization of some self-tuning technology.

Classification of query optimization techniques: ① query Reuse, ② query rewrite rules, ③ query algorithm optimization, ④ parallel query optimization, ⑤ distributed query optimization, ⑥ other optimization techniques

1) query Reuse : Refers to the use of the previous execution results, in order to save the query to calculate the whole process of time and reduce resource consumption.

The following two types are mainly divided into:

a) Reuse of query results . A buffer block is allocated in the buffer, which holds the SQL statement text and the final result set, and returns the result directly when the same SQL input is entered. The reuse technology of query results saves the time of query plan generation and reduces the resource consumption in the whole process of query execution.

b) Reuse of query plans. caches the execution plan of a query statement and its corresponding syntax tree structure. Query plan reuse technology reduces the time and resource consumption of query plan generation.

2) Query rewriting : is an equivalent conversion of a query statement, that is, any state of any related pattern will produce the same result.

Query optimization technology type:

A) syntax level. Query Language layer optimization, based on the syntax of optimization.

B) Class of generations. Queries are optimized using formal logic, using the principle of relational algebra.

c) Semantic level. Based on the integrity constraints, the query statements are semantically understood and some optimizations can be inferred.

d) Physical level. Physical optimization technology, based on cost estimation model, compares the lowest cost in various execution modes.

Query rewriting is based on the syntax, generation and semantics of optimization, can be unified into the scope of logical optimization: Based on the cost estimation model is the physical level of optimization, is to choose the least cost path from the connection path process.

Query optimization rewrite ideas:

A) Convert a procedural query to a descriptive query, such as a view rewrite.

b) Convert complex queries (such as nested subqueries, outer join elimination, nested join elimination) as much as possible into a multi-table join query.

c) Convert inefficient predicates to equivalent high-efficiency predicates (such as equivalent predicate overrides).

3) query optimization algorithm: The process of solving the efficient execution plan for a given query statement. That is, the query plan, also known as the Query tree, consists of a series of internal operators, which form an execution scheme of the query according to certain operational relationships.

Strategies for generating optimal query plans:

A) rule-based optimization.

According to experience or some methods that have been known or proved to be effective, defined as "rules" (such as rules based on relational algebra, rules based on experience, etc.), using these rules to simplify the query plan generation process in accordance with the operation can be reduced, using heuristic rules to exclude some obvious bad access path, This is rule-based optimization.

b) based on cost optimization.

Based on a cost evaluation model, in the process of generating the query plan, the cost of each access path (the access path mainly includes the above three "relationship nodes") is calculated, and then the least cost is chosen as the sub-path, so that all tables are connected to a complete path. The mainstream database uses rules-based and cost-based technologies.

4) Parallel query optimization: in parallel database system, the goal of query optimization is to find the query execution plan with minimum response time, which needs to decompose the query work into some sub-work that can run in parallel. Some business databases provide the capability of parallel queries to optimize query execution.

Within the same SQL, query parallelism can be divided into:

A) in-operation parallelism. The same operations, such as single-table scan operations, two-table join operations, sorting operations, etc., are decomposed into separate sub-operations, which are executed concurrently by different CPUs.

b) parallelism between operations. A SQL query statement can be decomposed into multiple sub-operations, executed by multiple CPUs.

5) Distributed query optimization: in Distributed Database system, query strategy optimization is the focus of query optimization. Mainly is the data transmission strategy, a, b two nodes of the connection, is a node data transmission to B nodes or from B to A or the first to filter and then transfer, etc.) and local processing optimization (traditional single-node database query optimization technology).

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

Total Cost = I/o cost + CPU Cost + communication cost

SQL optimization--Query optimization technology classification