The function and performance analysis of MySQL view

Definition: A view is a table that is exported from one or several base tables, and is a virtual table, unlike a base table.

Role:

1. Simplify the operation without making multi-table queries.

2. When different types of users share the same database, very flexible, (users with different

Way of looking at the same data.

3. The view provides a degree of logical independence to the refactoring database.

The logical independence of data refers to the addition of new relationships or the addition of new ones to existing relationships.

field, the user's application is not affected.

For example: A table of the original student (sno,sname,ssex,sage,sdept).

Later changes were: Sx (sno,sname,sage) and Sy (sno,ssex,sdept)

Two tables.

The original table student is the result of the natural connection of the SX and SY tables.

So if we start by building an attempt to:

Create View Student (sno,sname,ssex,sage,sdept)

As select SX. Sno,sx. Sname,sy. Ssex,sx,sage,sy,sdept

From Sx,sy where SX. Sno=sy. Sno;

Although the logical structure of the database has changed, the application does not have to be modified (because this

The relationship defined by the view does not change.)

"Note: Attempts to provide logical independence of data to a certain extent, such as

The update to the view is conditional, so statements that modify data in your application may still

Changes due to changes in the base table construction.

4. views provide secure protection of confidential data

With the view mechanism, you can define different views of different users when you design a database application, so that confidential data does not show up on a user view that should not see the data now. This way, the view mechanism automatically provides security protection for confidential data. For example, the student table covers the 15 faculties of the school student data, on which 15 views can be defined, each containing only one faculty student data, and only the director of each faculty is allowed to query and modify the primitive student view.

5, the appropriate use of the view can be more clearly expressed in the query

For example, it is often necessary to perform such a query "to find out the course number for each student who has obtained the highest grade". You can define a view to find the highest score for each student:
CREATE VIEW Vmgrade
as
SELECT Sno,max (Grade) Mgrade
From SC
GROUP by Sno;
then complete the query with the following query statement:

SELECT SC. Sno,cno from Sc,vmgrade WHERE SC. Sno = Vmgrade. Sno and SC. Grade = Vmgrade. Mgrade;

MySQL view algorithm and its performance analysis:

MySQL has two algorithms for working with views, called Merge and TempTable, respectively.

You can specify which algorithm to use when executing a "CREATE VIEW" statement, which refers to a merge

When working with views, the operation of the view is expanded according to the definition of the view, somewhat like

Macro expansion in the C language.

For example, the following tables are available:

CREATE TABLE ' comment ' (

' id ' int (one) is not NULL,

' user_id ' int (one) default NULL,

' content ' varchar (255) default NULL,

PRIMARY KEY (' id '),

KEY ' idx_comment_uid ' (' user_id ')

) Engine=innodb;

assuming user_id < 10000 is a VIP user, we can create a view to represent the VIP user's comments:

create VIEW vip_comment as SELECT * from comment WHERE user_id < 10000; when we manipulate the vip_comment view.

"There is generally no performance problem with MySQL processing views when you can use the merge algorithm,

However, the merge algorithm is not available at all times. In fact, as long as the definition of the view is slightly more complex, MySQL has no way to use the merge algorithm. To be exact, as long as the view definition

The merge algorithm is not available using the SQL construction block:

(1) Aggregation function (2) distinct (3) group by (4) have

(5) having (6) set operation (in MySQL only union,union all, no except and intersect) (7) subquery. 】

It is true that it is very difficult to optimize the view operation effectively in the case of complex views definition. So at this point, MySQL uses a status quo approach, which is to execute the view definition and save its results using temporary tables so that subsequent views are converted to operations on the temporary table. It must be said that from a software design point of view, such a method is very elegant, but from a performance point of view, this method is very poor.

For example, we want to use the following view to represent the number of comments per user, namely:

CREATE VIEW Comment_count as SELECT user_id, COUNT (*) as count from comment GROUP by user_id;

When we use this view, we may have a small abacus in mind. Now we're going to start with this view, and if there's really a problem with the performance, then we'll get a comment_count table, which will write down the number of comments per user. And we're going to use this view first to make it easier to change things in the future (this is also one of the main reasons why the view-the so-called outer pattern in the textbook-this thing exists, and the other main reason is the ease of access control). But when we meet MySQL, this idiot, our abacus will definitely fail.

Let's take a look at the execution policy when you specify USER_ID to select records from Comment_count:

Mysql> Explain select COUNT (*) from comment_count where user_id = 90;

As you can see, MySQL first executes the view definition of Comment_count first.

Store the results in a temporary table and select the one that satisfies the "user_id=90"

Record, so that although we end up only need to count the number of comments for user number 90th, and comment

The table's user_id field is also indexed, and MySQL scans the entire comment table and presses

The USER_ID group calculates the number of comments for all users.

"It is important to note that the materialization of the system is performed even when the explain is carried out,

So if you comment a lot, explain is the same slow. The root of the problem is

There are many problems with MySQL's query optimization. For the above query, to achieve a comparison

A good optimization effect is generally handled in the database as follows:

1. Convert the operation of the view into a subquery in the FROM clause.

SELECT * FROM (select User_id,count (*) as Count from comment

Group by user_id) as Comment_count where user_id=90;

2. Sub-query promotion. Because a group by is used in a subquery, the outside condition is first

As the having condition of ascension

Select User_id,count (*) as count from comment group by usr_id

Having user_id=90;

3. Conversion to a where condition because the aggregation function is not involved in the having condition

Select user_id, COUNT (*) as count from comment where user_id=90

Group BY USER_ID;

4. Since the where condition is specified, user_id is already a constant, according to the constant group by

No meaning, so remove group by.

Select User_id,count (*) as count from comment where user_id=90

In addition to the 4th step, it is not possible to determine whether MySQL performs this optimization based on explain output and query performance, and the first 3 classes of optimized MySQL do not. Therefore, MySQL has a long way to go to handle the above query effectively.

PS: The query optimization capability of PostgreSQL is much stronger, and the above query can produce the optimized final execution plan in PostgreSQL. PostgreSQL is concerned about query optimization estimates related to the school style of PostgreSQL or the rule system in PostgreSQL.