MySQL View Principle Analysis

Views were introduced in MySQL 5.0 and later. A view is actually a virtual data table that does not store data itself. The data of this virtual table actually comes from the result of the SQL query that accesses the view. MySQL handles views in a similar way to how it handles tables, and this approach can be used to meet many needs. Views and tables share the same namespace in MySQL, however, MySQL handles them differently. For example, views do not have triggers, and you cannot remove views using DROP TABLE.

The following uses the world sample database as an example to illustrate the working mechanism of views.

CREATE VIEW Oceania AS
	SELECT * FROM Country WHERE Continent = 'Oceania'
  WITH CHECK OPTION;

The simplest way to implement a view is to execute a SELECT query and put the results into a temporary table. After that, you can reference this temporary table wherever the view appears. For example, the following query:

SELECT Code, Name FROM Oceania WHERE Name = 'Australia';

The following is a possible form of the server executing the above statement (the temporary table name is arbitrary, and the actual internal content is unknown):

CREATE TEMPORARY TABLE TMP_Oceania_123 AS 
	SELECT * FROM Country WHERE Continent = 'Oceania';
SELECT Code, Name FROM TMP_Oceania_123 WHERE NAME = 'Australia';

This form obviously has performance issues. The best way is to change the distribution query of views and queries into one SQL statement, as shown below:

SELECT Code, Name FROM Country
WHERE Continent = 'Oceania' AND Name = 'Australia';

In MySQL, two algorithms are used, called MERGE and TEMTABLE, and the MERGE algorithm is used as much as possible. MySQL can even merge nested views. The following figure shows the difference between the two algorithms:

MySQL uses the TEMPTABLE algorithm when a view has GROUP BY, DISTINCT, aggregate functions, UNION, subqueries, or other tables that do not have a one-to-one relationship. If you want to know whether a view uses MERGE or TEMPTABLE, you can use the EXPLAIN command to check:

EXPLAIN SELECT * FROM <view name>;

If there is DERIVED in select_type, it means that the TEMPTABLE algorithm is used. Therefore, if the hidden derived tables are expensive to generate, EXPLAIN becomes very inefficient and slow to execute because it needs to actually execute and build the derived tables. This algorithm is a property of the view and is not affected by the query type. For example, if an algorithm is specified when a view is created, the algorithm will not be changed for subsequent queries against this view, even if there is room for optimization:

CREATE ALGORITHM=TEMPTABLE VIEW v1 AS
SELECT * FROM Country;

Updatable Views

Updatable views can update the underlying table hidden by the view. As long as the specified conditions hold, you can use UPDATE, DELETE, and even INSERT operations, just like operating ordinary tables. For example, the following operations are valid:

UPDATE Oceania SET Population = Population * 1.1 WHERE NAME = 'Australia';

If the view includes GROUP BY, UNION, aggregate functions, or some other concepts, then the view is not updatable. All views using the TEMPTABLE algorithm cannot be updated.

The CHECK OPTION clause is used to ensure that any data rows modified through the view continue to match the view's WHERE condition after being modified. For example, in the example above, if a row with a different Continent value is inserted, the server will report an error.

Performance of views

Many people don't think of using views to improve performance, but in some cases they can. In addition, views can be used to improve performance in other areas. For example, when the table structure is reconstructed, the view of the modified data table can be used without modification. You can also use views to implement field permission control without adding the overhead of creating column permissions:

CREATE VIEW public.employeeinfo AS
	SELECT firstname, lastname --excluding ID number FROM private.employeeinfo;
GRANT SELECT ON public.* to public_user;

The performance of views using the TEMPTABLE algorithm can be very poor (although it is likely to be better than the equivalent SQL query). This view does not have much room for optimization.

Views may fool developers into thinking that they are simple, when in fact they are very complex. If the developer does not understand the complexity of views, then they will not notice the difference between views and ordinary table queries. If you use the EXPLAIN command, you may sometimes find that hundreds of lines of analysis results are output. This is because the query that appears to be a data table is actually a view, and the view may reference other data tables or even other views.

Using views to improve performance requires careful analysis and testing. Even views with the MERGE algorithm can add extra overhead, and the impact on performance is difficult to predict. Views actually use another optimization approach in MySQL. In high-concurrency scenarios, views may cause the query optimizer to spend a lot of time on planning and statistics, and even cause server-side lag. At this time, you need to use ordinary SQL to replace the view.

Limitations of views

MySQL does not support physical views like other database servers do (physical views are views that generate and store results in an invisible table and are periodically updated to refresh the view from the source data). MySQL also does not support indexes on views. MySQL also does not retain the original SQL of the view. If we edit the view by executing the SHOW CREATE VIEW command and change the return result SQL, we will find that the result is very strange. The query SQL will be expanded according to the specification and wrapped in an internal format without formatting, comments, and indentation.

The above is the detailed analysis of the principle of MySQL view (View). For more information about the principle of MySQL view (View), please pay attention to other related articles on 123WORDPRESS.COM!