I'm extremely new to Views so please forgive me if this is a silly question, but I have a View that is really helpful in optimizing a pretty unwieldy query, and allows me to select against a small subset of columns in the View, however, I was hoping that the View would actually be stored somewhere so that selecting against it wouldn't take very long.
我对Views非常陌生，所以如果这是一个愚蠢的问题，请原谅我，但我有一个View非常有助于优化一个非常笨拙的查询，并允许我选择View中的一小部分列，但是，我希望View实际上会被存储在某个地方，以便选择它不会花费很长时间。
I may be mistaken, but I get the sense (from the speed with which create view executes and from the duration of my queries against my View) that the View is actually run as a query prior to the external query, every time I select against it.
我可能会弄错，但是我得到的意义（从创建视图执行的速度和我对查询的查询持续时间）得到的视图实际上是在外部查询之前作为查询运行，每次我选择反对它。
I'm really hoping that I'm overlooking some mechanism whereby when I run CREATE VIEW it can do the hard work of querying the View query *then, so that my subsequent select against this static View would be really swift.
我真的希望我忽略了一些机制，当我运行CREATE VIEW时，它可以完成查询View查询*的艰苦工作，这样我对这个静态View的后续选择就会非常迅速。
BTW, I totally understand that obviously this VIEW would be a snapshot of the data that existed at the time the VIEW was created and wouldn't reflect any new info that was inserted/updated subsequent to the VIEW's creation. That's actually EXACTLY what I need.
顺便说一下，我完全理解，显然这个VIEW将是创建VIEW时存在的数据的快照，并且不会反映在创建VIEW之后插入/更新的任何新信息。这实际上是我需要的。
TIA
TIA
3 个解决方案
#1
15
What you want to do is materialize your view. Have a look at http://www.fromdual.com/mysql-materialized-views.
你想要做的是实现你的观点。请查看http://www.fromdual.com/mysql-materialized-views。
#2
2
What you're talking about are materialised views, a feature of (at least) DB2 but not MySQL as far as I know.
你所谈论的是物化视图，至少是DB2的一个特性，但据我所知，不是MySQL。
There are ways to emulate them by creating/populating a table periodically, or on demand, but a true materialised view knows when the underlying data has changed, and only recalculates if required.
有一些方法可以通过定期或按需创建/填充表来模拟它们，但真正的物化视图知道基础数据何时发生了变化，并且只在需要时才重新计算。
If the data will never change once the view is created (as you seem to indicate in a comment), just create a brand new table to hold the subset of data and query that. People always complain about slow speed but rarely about data storage requirements :-)
如果创建视图后数据永远不会改变（如您在评论中所示），只需创建一个全新的表来保存数据子集并进行查询。人们总是抱怨速度慢但很少关于数据存储要求:-)
#3
0
Since a view is basically a SELECT statement you can use query cache to improve performance.
由于视图基本上是SELECT语句，因此您可以使用查询缓存来提高性能。
But first you should check if :
但首先你要检查是否：
- you can add indexes in the tables involved to speed up the query (use
EXPLAIN) - 您可以在相关表中添加索引以加快查询速度（使用EXPLAIN）
- the data isn't changing very often you can materialize the view (make snapshots)
- 数据不经常变化你可以实现视图（制作快照）
#1
15
What you want to do is materialize your view. Have a look at http://www.fromdual.com/mysql-materialized-views.
你想要做的是实现你的观点。请查看http://www.fromdual.com/mysql-materialized-views。
#2
2
What you're talking about are materialised views, a feature of (at least) DB2 but not MySQL as far as I know.
你所谈论的是物化视图，至少是DB2的一个特性，但据我所知，不是MySQL。
There are ways to emulate them by creating/populating a table periodically, or on demand, but a true materialised view knows when the underlying data has changed, and only recalculates if required.
有一些方法可以通过定期或按需创建/填充表来模拟它们，但真正的物化视图知道基础数据何时发生了变化，并且只在需要时才重新计算。
If the data will never change once the view is created (as you seem to indicate in a comment), just create a brand new table to hold the subset of data and query that. People always complain about slow speed but rarely about data storage requirements :-)
如果创建视图后数据永远不会改变（如您在评论中所示），只需创建一个全新的表来保存数据子集并进行查询。人们总是抱怨速度慢但很少关于数据存储要求:-)
#3
0
Since a view is basically a SELECT statement you can use query cache to improve performance.
由于视图基本上是SELECT语句，因此您可以使用查询缓存来提高性能。
But first you should check if :
但首先你要检查是否：
- you can add indexes in the tables involved to speed up the query (use
EXPLAIN) - 您可以在相关表中添加索引以加快查询速度（使用EXPLAIN）
- the data isn't changing very often you can materialize the view (make snapshots)
- 数据不经常变化你可以实现视图（制作快照）