SQL Optimization-logical Optimization-database constraint rules and semantics optimization, SQL Semantics

SQL Optimization-logical Optimization-database constraint rules and semantics optimization, SQL Semantics

1) database integrity

① Entity Integrity (Entity Integrity): yourself

A) a link corresponds to an entity set in the real world. -- ER Model

B) Entities in the real world have some unique identifiers. -- Primary key

C) if the primary keyword is a combination of multiple attributes, null values cannot be obtained for all primary attributes. -- Implicit Index

② Domain Integrity: Local

Ensure the rationality of database Field Values. The attribute value should be the value in the field, which is specified by the relational model.

Including:

A) CHECK)

B) DEFAULT value (DEFAULT)

C) NOT blank (not null)

D. It can be NULL.

③ Referential Integrity: Relationship between yourself and other "entities"

Integrity of reference is a constraint that defines the relationship between the master keyword and external keyword reference.

A) relational databases usually contain multiple interrelated relationships, and the relationships between them are implemented through public attributes.

B) the so-called public attribute is the primary keyword of a relational R (referred to as a reference link or a target link) and an external keyword of another relational K (referred to as a reference link. If the value of the external keyword in reference relationship k is either the same as the value of the primary key of the tuples in the referenced relationship R, or the null value is obtained, the primary and external keyword references that establish an association between the two relationships comply with the requirements of the integrity rules. If the external keyword of reference link K is also the primary keyword, the primary keyword cannot be null according to the entity integrity requirements. Therefore, the value of the external keyword of the reference relationship k can only take the primary key value that already exists in the corresponding referenced relationship R.

④ User-Defined Integrity: restrictions added by users

User-Defined integrity sets constraints on the data involved in a specific application based on the requirements of the application environment and actual needs. This constraint mechanism should not be provided by the application, but should be defined and tested by the relational model. User-Defined integrity mainly includes field validity constraints and record validity.

2) semantic Optimization

① Basic Concepts

SQL can be optimized due to semantic reasons. There are two basic concepts:

A) semantic conversion. Because of integrity restrictions and other reasons, a conversion is called semantic conversion.

B) semantic optimization. Because the optimization of semantic conversion is called semantic optimization.

Semantic conversion is actually the inference of "Specific Semantics" based on integrity constraints and other information, and thus a query with different query efficiency but the same results is obtained.

Semantic optimization refers to SQL optimization from the semantic perspective, rather than a formal optimization. Therefore, the scope of optimization may cover other types of optimization.

② Join Elimination (not supported by MySQL)

You do not need to evaluate the cost for some connection operations. you can deduce the results or get a simplified operation based on known information (mainly integrity constraints, but not integrity constraints.

For example:

Use two base tables A and B for A natural connection to create A view V. If the query on view V only involves the information of one of the base tables, the query of a view can be fully converted into a query of a base table.

③Join Introduction)

Increasing Connections helps reduce the original relationship or reduce the original link selection rate.

④ Predicate Introduction (not supported by MySQL)

Introduce new predicates based on integrity constraints, such as index-based columns, which may make queries faster;

For example:

A table has a column constraint of "c1 <c2", and an index exists in column c2. If the WHERE condition in the query statement is "c1> 200", you can infer "c2> 200 ", when the condition is changed to "c1> 200 AND c2> 200 AND c1 <c2", the query statement can be optimized using the index on the c2 column; if the selection rate of the index on the c2 column is low, the optimization effect will be higher.

④ Detecting the Empty Answer Set (supported by MySQL)

The predicate in the query statement is inconsistent with the constraint. It can be inferred that the condition result is FALSE, and the final result set may be empty;

For example:

The CHECK constraint limits the range of the "score" column to 60 to 100. If a query condition is "score <60", the condition cannot be specified immediately.

⑤ Order Optimizer (MySQL partially)

The ORDERBY operation is usually completed by the index or sort (sort). If the index can be used, the sorting operation can be omitted. In addition, the ORDERBY operation is optimized based on Grouping and other operations.

⑥ Exploiting Uniqueness (supported by MySQL)

Check whether unnecessary DISTINCT operations exist based on uniqueness, index, and other features.

For example:

Execute the DISTINCT operation on the primary key. If yes, remove DISTINCT.

From the book "Art of database query optimizer"