Suppose I have a table A, creating a view V from that table.
假设我有一个表A，从该表创建一个视图V.
Then I do several queries from V. I wonder if V will be re-constructed each time I query? or it will be constructed only 1 time, and being saved somewhere in memory by DBMS for next queries (which I think similar to query from a table)?
然后我从V做了几个查询。我想知道每次查询时是否会重新构造V？或者它将只构造一次，并由DBMS保存在内存中的某个地方用于下一个查询（我认为类似于从表中查询）？
4 个解决方案
#1
2
In general, no. V is a transient set of rows that is computed when requested by a query. Because you can apply additional WHERE and ORDER BY criteria when querying from a view, the execution plan for two queries against the same view could conceivably be quite different. The database generally cannot reuse the results of a previous query against a view to satisfy the next query against that view.
一般来说，没有。 V是一组瞬态行，在查询请求时计算。因为在从视图查询时可以应用其他WHERE和ORDER BY条件，所以针对同一视图的两个查询的执行计划可能会完全不同。数据库通常不能对视图重用先前查询的结果以满足针对该视图的下一个查询。
That said, there is a relatively new technology in some engines called Materialized Views. I have never used them myself, but my understanding is that these views are pre-computed based on updates that are made to the underlying tables. So with Materialize Views you do get improved SELECT performance, but at the expense of decrease INSERT, UPDATE, and DELETE performance.
也就是说，在一些名为Materialized Views的引擎中有一种相对较新的技术。我自己从未使用它们，但我的理解是这些视图是根据对基础表进行的更新预先计算的。因此，使用Materialize Views可以提高SELECT性能，但代价是降低INSERT，UPDATE和DELETE性能。
You should also be aware that multi-column indexes can be used to precompute certain selections and sort orders involving individual tables. If you issue a query against a table that can be satisfied using a compound index (only the columns in the index are required by the query, and the sort order matches the index) then the table itself need never be read, only the index.
您还应该知道，多列索引可用于预先计算某些选择并对涉及单个表的排序进行排序。如果针对可以使用复合索引满足的表发出查询（查询只需要索引中的列，并且排序顺序与索引匹配），则表本身不需要读取，只读取索引。
#2
1
Views in MySQL are not a de facto caching solution.
MySQL中的视图不是事实上的缓存解决方案。
MySQL runs the query against the base tables every time you query a view on those base tables. The results of the query are not stored for the view.
每次查询这些基表上的视图时，MySQL都会对基表运行查询。查询的结果不会存储在视图中。
As a result, there is no need to "refresh" the view as there is when using materialized views in Oracle Microsoft SQL Server. Even the SQL in a MySQL view definition is re-evaluated every time you query the view.
因此，无需像在Microsoft Microsoft SQL Server中使用实体化视图那样“刷新”视图。每次查询视图时，甚至会重新评估MySQL视图定义中的SQL。
If you need something like materialized views in MySQL, one tool that might help is FlexViews. This stores the results of a query in an ordinary base table, and then monitors changes recorded in MySQL's binary log, applying relevant changes to the base table. This tool can be quite useful, but it has some caveats:
如果你需要像MySQL中的物化视图那样的东西，可能有用的一个工具就是FlexViews。这将查询结果存储在普通基表中，然后监视MySQL二进制日志中记录的更改，并将相关更改应用于基表。这个工具非常有用，但它有一些注意事项：
- FlexViews is written in PHP, and as such it has some performance limitations. Depending on your write traffic load, FlexViews may not be able to keep up.
- FlexViews是用PHP编写的，因此它有一些性能限制。根据您的写入流量负载，FlexView可能无法跟上。
- It doesn't support every possible type of SELECT query.
- 它不支持所有可能类型的SELECT查询。
- FlexViews-managed materialized view tables are not updateable. That is, you can UPDATE this view table, but the change will not apply to the base tables.
- FlexViews管理的物化视图表不可更新。也就是说，您可以更新此视图表，但更改将不适用于基表。
#3
0
According to Pinal Dave, a view must be refreshed in order to reflect changes made to its referenced table(s). I'm not sure this makes a view of a simple 1-table query any more efficient than querying the table directly (it probably doesn't) but I think it means that views containing complex joins and subqueries may be more efficient than their non-view counterparts.
根据Pinal Dave的说法，必须刷新视图以反映对其引用表的更改。我不确定这会使一个简单的1表查询比直接查询表更有效（它可能没有），但我认为这意味着包含复杂连接和子查询的视图可能比非非查询更有效 - 查看同行。
Pinal Dave has more to say about the other limitations of SQL views (or features, if you like). Maybe you can learn something useful there.
Pinal Dave还有更多关于SQL视图（或功能，如果你喜欢）的其他限制的说法。也许你可以在那里学到有用的东西。
#4
-1
Mysql Views do not support Indexes. (as like in Oracle, where you can create index in Oracle Views) But mysql views can use the indexes in underlying table when created with Merge Algorithm.
Mysql视图不支持索引。 （就像在Oracle中一样，您可以在Oracle Views中创建索引）但是当使用合并算法创建时，mysql视图可以使用基础表中的索引。
If you have to use views, then adjust your JOIN BUFFER.
如果必须使用视图，请调整JOIN BUFFER。
Using, Something like this
使用，像这样的东西
set global join_buffer_size=314572800;
set global join_buffer_size = 314572800;
Do profile the differences before and after changing the buffer size. I have seen after increasing join buffers, the view query executes in same time (in ms) as the table of the same size will do.
在更改缓冲区大小之前和之后分析差异。我在增加连接缓冲区后看到，视图查询在同一时间（以毫秒为单位）执行，因为相同大小的表将执行。
#1
2
In general, no. V is a transient set of rows that is computed when requested by a query. Because you can apply additional WHERE and ORDER BY criteria when querying from a view, the execution plan for two queries against the same view could conceivably be quite different. The database generally cannot reuse the results of a previous query against a view to satisfy the next query against that view.
一般来说，没有。 V是一组瞬态行，在查询请求时计算。因为在从视图查询时可以应用其他WHERE和ORDER BY条件，所以针对同一视图的两个查询的执行计划可能会完全不同。数据库通常不能对视图重用先前查询的结果以满足针对该视图的下一个查询。
That said, there is a relatively new technology in some engines called Materialized Views. I have never used them myself, but my understanding is that these views are pre-computed based on updates that are made to the underlying tables. So with Materialize Views you do get improved SELECT performance, but at the expense of decrease INSERT, UPDATE, and DELETE performance.
也就是说，在一些名为Materialized Views的引擎中有一种相对较新的技术。我自己从未使用它们，但我的理解是这些视图是根据对基础表进行的更新预先计算的。因此，使用Materialize Views可以提高SELECT性能，但代价是降低INSERT，UPDATE和DELETE性能。
You should also be aware that multi-column indexes can be used to precompute certain selections and sort orders involving individual tables. If you issue a query against a table that can be satisfied using a compound index (only the columns in the index are required by the query, and the sort order matches the index) then the table itself need never be read, only the index.
您还应该知道，多列索引可用于预先计算某些选择并对涉及单个表的排序进行排序。如果针对可以使用复合索引满足的表发出查询（查询只需要索引中的列，并且排序顺序与索引匹配），则表本身不需要读取，只读取索引。
#2
1
Views in MySQL are not a de facto caching solution.
MySQL中的视图不是事实上的缓存解决方案。
MySQL runs the query against the base tables every time you query a view on those base tables. The results of the query are not stored for the view.
每次查询这些基表上的视图时，MySQL都会对基表运行查询。查询的结果不会存储在视图中。
As a result, there is no need to "refresh" the view as there is when using materialized views in Oracle Microsoft SQL Server. Even the SQL in a MySQL view definition is re-evaluated every time you query the view.
因此，无需像在Microsoft Microsoft SQL Server中使用实体化视图那样“刷新”视图。每次查询视图时，甚至会重新评估MySQL视图定义中的SQL。
If you need something like materialized views in MySQL, one tool that might help is FlexViews. This stores the results of a query in an ordinary base table, and then monitors changes recorded in MySQL's binary log, applying relevant changes to the base table. This tool can be quite useful, but it has some caveats:
如果你需要像MySQL中的物化视图那样的东西，可能有用的一个工具就是FlexViews。这将查询结果存储在普通基表中，然后监视MySQL二进制日志中记录的更改，并将相关更改应用于基表。这个工具非常有用，但它有一些注意事项：
- FlexViews is written in PHP, and as such it has some performance limitations. Depending on your write traffic load, FlexViews may not be able to keep up.
- FlexViews是用PHP编写的，因此它有一些性能限制。根据您的写入流量负载，FlexView可能无法跟上。
- It doesn't support every possible type of SELECT query.
- 它不支持所有可能类型的SELECT查询。
- FlexViews-managed materialized view tables are not updateable. That is, you can UPDATE this view table, but the change will not apply to the base tables.
- FlexViews管理的物化视图表不可更新。也就是说，您可以更新此视图表，但更改将不适用于基表。
#3
0
According to Pinal Dave, a view must be refreshed in order to reflect changes made to its referenced table(s). I'm not sure this makes a view of a simple 1-table query any more efficient than querying the table directly (it probably doesn't) but I think it means that views containing complex joins and subqueries may be more efficient than their non-view counterparts.
根据Pinal Dave的说法，必须刷新视图以反映对其引用表的更改。我不确定这会使一个简单的1表查询比直接查询表更有效（它可能没有），但我认为这意味着包含复杂连接和子查询的视图可能比非非查询更有效 - 查看同行。
Pinal Dave has more to say about the other limitations of SQL views (or features, if you like). Maybe you can learn something useful there.
Pinal Dave还有更多关于SQL视图（或功能，如果你喜欢）的其他限制的说法。也许你可以在那里学到有用的东西。
#4
-1
Mysql Views do not support Indexes. (as like in Oracle, where you can create index in Oracle Views) But mysql views can use the indexes in underlying table when created with Merge Algorithm.
Mysql视图不支持索引。 （就像在Oracle中一样，您可以在Oracle Views中创建索引）但是当使用合并算法创建时，mysql视图可以使用基础表中的索引。
If you have to use views, then adjust your JOIN BUFFER.
如果必须使用视图，请调整JOIN BUFFER。
Using, Something like this
使用，像这样的东西
set global join_buffer_size=314572800;
set global join_buffer_size = 314572800;
Do profile the differences before and after changing the buffer size. I have seen after increasing join buffers, the view query executes in same time (in ms) as the table of the same size will do.
在更改缓冲区大小之前和之后分析差异。我在增加连接缓冲区后看到，视图查询在同一时间（以毫秒为单位）执行，因为相同大小的表将执行。