Views have been introduced since MySQL 5.0. A view is actually a virtual table that does not store data itself. The actual data for this virtual table is the result of an SQL query that accesses the view. MySQL handles views in much the same way it handles tables, satisfying many needs in this way. Views and tables share a namespace in MySQL; however, MySQL handles them differently. For example, views do not have triggers, and you cannot use DROP tables to remove views.

This article focuses on how views are implemented and how views interact with the query optimizer so that we can use this knowledge to understand how views can improve performance. The following uses the World sample database as an example to show how views work.

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

The simplest way to implement a view is to execute a SELECT query and put the results into a temporary table. You can then reference the temporary table where the view appears. For example the following query statement:

SELECT Code, Name FROM Oceania WHERE Name = 'Australia';
Copy the code

Here is how the server might execute the above statement:

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

There are obvious performance issues with this form, and the best way to do this is to change the distribution query for views and queries to a SINGLE SQL statement, as shown below:

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

There are two algorithms that are used in MySQL, called MERGE and TEMTABLE, and the MERGE algorithm is used whenever possible. Even more, MySQL can merge nested views. Here is the difference between the two algorithms:MySQL uses the TEMPTABLE algorithm when there is a GROUP BY, DISTINCT, aggregate function, UNION, subquery, or other data table relationship in the view that is not one-to-one. If you want to know whether the view is MERGE or TEMPTABLE, you can check with the EXPLAIN directive:

EXPLAIN SELECT * FROM <The name of the view>;
Copy the code

If DERIVED is in select_type, the TEMPTABLE algorithm is used. Therefore, if hidden derived tables are expensive to produce, EXPLAIN becomes low performance and slow to execute because it requires the actual execution and construction of derived tables. This algorithm is a property of the view and is not affected by the type of query. For example, if an algorithm is specified when a view is created, future queries against the view will not change the algorithm, even if there is room for optimization:

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

Updatable view

Updatable views can UPDATE hidden underlying tables through views, and as long as the specified conditions are maintained, UPDATE, DELETE, and even INSERT operations can be used just as they would with a regular table. For example, the following operations are valid:

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

If the view includes GROUP BY, UNION, aggregate function, or some other concept, then the view cannot be updated. All views that use the TEMPTABLE algorithm cannot be updated.

The CHECK OPTION clause is used to ensure that any rows of data that are changed through the view need to keep matching the view’s WHERE condition after being changed. In the example above, if a row with a different Continent value is inserted, the server will report an error.

View performance

Many people don’t think about using views to improve performance, but in some cases they can. Views can also be used to improve performance in other areas. For example, during table structure reconstruction, the view of the modified 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  -- Does not include id number
  FROM private.employeeinfo;
GRANT SELECT ON public.* to public_user;
Copy the code

View performance using the TEMPTABLE algorithm can be poor (although it can be better than equivalent SQL queries). The space that can be optimized for this view is not high.

Views can fool developers into thinking they are simple when in fact they are very complex. If developers don’t understand the complexity of views, they won’t notice the difference between a view and a normal table query. If you use the EXPLAIN directive, you can sometimes find hundreds of rows of analysis output because queries that appear to be tables are views, and views may refer to other tables or even other views.

When using views to improve performance, you need to analyze and test carefully. Even the view of the MERGE algorithm adds an additional burden, and it is difficult to predict the performance impact. Views actually use another optimization approach in MySQL. In high-concurrency scenarios, views can cause the query optimizer to spend a lot of time planning and statistics, and even cause server lag. At this point, you need to use plain SQL instead of the view.

View limitations

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