SQL optimization--Logical optimization--the constraint rules and semantic optimization of database

1) Database Integrity

① Solid Integrity (Entity Integrity): Own

A) a relationship corresponds to an entity set in the real world. --er model

b) entities in the real world have some unique identity. --PRIMARY key

c) The primary key is a combination of multiple attributes, and all primary properties must not be null. --Implied index

② field integrity (Domain Integrity): own local

Ensure that database field values are reasonable. the attribute value should be a value in the field, as defined by the relationship pattern.

Including:

A) inspection (check)

b) Defaults (default)

c) is not empty (not NULL)

D) can be null (NULL), etc.

③ referential integrity (referential Integrity): relationships with other entities

Referential integrity is a constraint that defines the primary and external keyword references that relate to the relationship between the established relationships.

A) in a relational database, there are many interrelated relationships, and relationships are implemented through public properties.

b) The so-called public attribute, which is a primary key for a relationship R (referred to as a referenced relationship or a target relationship) and an external keyword that is another relationship K (referred to as a reference relationship). If the value of the external keyword in the reference relationship K is the same as the value of the primary key of a tuple in the referenced relationship R, or a null value, then the associated primary and external keyword references are established between the two relationships, conforming to the referential integrity rule requirements. If the reference relationship K's external keyword is also its primary key, according to the entity integrity requirements, the primary key must not be null value, so, the reference relationship k external keyword can actually only take the corresponding referenced relationship R in the existing primary key value.

④ user-defined integrity (user-definedintegrity): User-added restrictions

user-defined integrity is based on the requirements of the application environment and the actual needs of a specific application of the data involved in the binding conditions. This constraint mechanism should generally not be provided by the application, but should be defined and tested by the relational model. user-defined integrity primarily includes field validation constraints and record validity.

2) Semantic optimization

① Basic Concepts

For semantic reasons, SQL can be optimized. Consists of two basic concepts:

A) semantic transformation. The case of a conversion is called semantic translation because of the reason for the integrity limit.

b) Semantic optimization. Because the semantic transformation is formed by optimization called semantic optimization.

Semantic transformation is the inference of "a certain semantics" based on information such as integrity constraints, and then a query with different efficiency but the same result is obtained.

Semantic optimization is an optimization of SQL from a semantic point of view, not a form of optimization, so the scope of its optimization may cover other types of optimization scope.

② Connection cancellation (join elimination)(MySQL not supported )

For some connection operations, you do not have to evaluate the cost and deduce the result or get a simplified operation based on known information (mainly based on integrity constraints, but not on integrity constraints).

For example:

Make a natural connection with a, B two base table, create a view V, if executing a query on view v only involves information from one of the base tables, then the query on the views can be converted to a query on a base table entirely.

③ Connection Introduction ( Join Introduction )

Increasing the connection helps to reduce the initial relationship or the selection rate of the original relationship.

④ Predicate Introduction (predicate Introduction) ( MySQL does not support )

The introduction of new predicates, such as the introduction of index-based columns, may make queries faster, based on information such as integrity constraints;

For example:

on a table, there is a column constraint of "C1<C2", there is an index on the C2 column, and the Where condition in the query statement has "c1>200", you can infer "c2>200", wher condition changed to "c1>200 and c2>200 and C1<c2", The query statement can be optimized by using the index on the C2 column, and if the index on the C2 column has a low selection rate, the optimization effect will be higher.

④ detects an empty answer set (detecting the empty Answer set) ( MySQL Support )

The predicate in the query statement is inconsistent with the constraint, it can infer that the condition result is false, and perhaps the final result set can be empty;

For example:

The check constraint limits the range of "score" columns to 60 to 100, and one query condition is "score<60", which immediately infer that the condition is not valid.

⑤ Sort optimization (Order Optimizer) ( MySQL section support )

The order-by operation is usually done by index or sort (sort), and if the index is available, the sort operation can be omitted;

⑥ unique use (Exploiting uniqueness)(mysql support )

Use uniqueness, index and other features to check for unnecessary distinct operations

For example:

Perform the distinct operation on the primary key and, if any, eliminate the distinct.

A book from the Art of database query optimizer

SQL optimization--Logical optimization--the constraint rules and semantic optimization of database