"mysql Optimization Theme" View application can still be so optimized? Have to collect (8)

I. Overview of the view (technical text):

(1) What is a view?

A view is a table of visualizations based on the result set of an SQL statement.

The view contains rows and columns, just like a real table. A field in a view is a field from a real table in one or more databases. A view is not present in the database as a stored set of data values, but in a database table that is actually referenced, which can consist of a single-table query, a multi-table union query, a group query, and a calculation (expression) query. Row and column data is derived from the table referenced by the query that defines the view, and is generated dynamically when the view is referenced.

(2) Advantages of the view:

A. Simplify query statements (the view mechanism allows users to focus on the data they care about.) If the data is not directly from the base table, you can define the view to make the database look simple, clear, and simplify the user's data query operations. ）

B. Permission control is possible

The permissions of the table are closed, but the corresponding view permissions are opened, and only some data columns are open in the view.

C, Big data table sub-table, such as a table of data has 1 million, then you can divide the table into four views.

Based on the calculation of the ID to take the remainder

D, users can view the same data in various ways:

This flexibility is necessary to enable different users to view the same data in different ways, and when many different kinds of users share the same database.

E, provides a certain degree of logical independence to the refactoring database:

Views enable applications and database tables to be somewhat independent.

(3) Disadvantages of the view:

1) Poor performance:

Turn the view query into a query for the base table, and if the view is defined by a complex multi-table query, then even a simple query of the view, SQL Server will turn it into a complex combination that takes some time.

2) Modification Restrictions:

When a user attempts to modify some of the information that is attempted, the database must turn it into a modification of some information about the base table, which is convenient for simple attempts, but may not be modifiable for more complex attempts.

(4) View usage scenarios (in fact, it is necessary to use the view above several advantages of the time):

1) When permission control is required.

2) If a query result is very frequent, it is often to take this query results to make subqueries, the use of the view is more convenient.

3) The key information comes from a number of complex related tables, can create a view to extract the information we need to simplify the operation;

(5) Classification of views:

1) Relational View:

It belongs to a database object, which is the most common kind of association query;

2) Inline View:

It does not belong to any user, nor object, and is created in a way that is completely different from the normal view, has no reusability, and cannot obtain data through a data dictionary;

3) Object view:

It is a view based on the type of the Table object, characterized by inheritance, encapsulation, and so on as needed to build object types to encapsulate complex queries (official: Rebuilding a data table is not implemented to cater to object types);

4) materialized view:

It is mainly used for database disaster recovery (backup), the materialized view can be stored and queried, replicated through the Dblink connection in the main database materialization view, when the main library exception storehouse takes over to implement disaster tolerance;

Second, the use of the view (technical text):

1. Create a View

1. Create or Replace view v_test asselect * FROMUSER;

Plus or replace indicates that the statement can also replace the existing view

2. Tune the View

1. SELECT * from V_test;

3. Modify the View

1. ALTER VIEW V_test Asselect * from User1;

4. Delete View

1. Drop view if exists v_test;

5. View View

1. Show tables;

Views are placed in the view table under the INFORMATION_SCHEMA database

6. View definition

Show table status from companys like ' v_test ';

Before this, we must be clear! Additions and deletions are eventually changed to the underlying table. And although the data can be updated in the view, there are a number of limitations. In general, it's a good idea to use a view as a virtual table for querying data instead of updating the data through a view. Because when you use a view to update data, you may fail to update data if you do not fully consider the limitations of updating data in the view. (technical text) Three, view algorithm--there are two algorithms for execution

A, Merge: The execution of the merge, each time the execution, the SQL statement of our view and the external query view of the SQL statement, the final execution.

B, temptable: Temporal table mode, whenever queried, the SELECT statement used by the view generates a temporary table of results, and then queries within the current temporary table.

Four, the view uses the attention Point (technical article):

(1) Modify the operation to be very very careful, or inadvertently you have modified the basic table of multiple data;

(2) The performance of query statements in the view should be adjusted to the optimal;

(3) Although the above mentioned, some of the views can be modified. However, it is more forbidden to modify the view.

For updatable views, you must have a one-to-one relationship or a special one-to-many field that is not constrained between the rows in the view and the rows in the base table. There are also specific other structures that make the view non-updatable.

The non-modifiable situation is as follows: The view contains the following cannot be modified.

(a) Aggregate function (SUM (), MIN (), MAX (), COUNT (), etc.).

(b) DISTINCT. The following error.

(c) GROUP by

(iv) having

(v) Union or UNION ALL

(vi) Sub-query in the select list

(eight) non-updatable view in FROM clause

(ix) A subquery in the WHERE clause that refers to the table in the FROM clause.

(10) algorithm = temptable (using a temporary table always makes the view non-updatable).

"mysql Optimization Theme" View application can still be so optimized? Have to collect (8)