MySQL 有没有办法缓存视图，以便快速查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16687736/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Is there a way to cache a View so that queries against it are quick?
提问by Yevgeny Simkin
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.
我对视图非常陌生，所以如果这是一个愚蠢的问题，请原谅我，但是我有一个视图，它对优化非常笨拙的查询非常有帮助，并且允许我选择视图中的一小部分列，但是，我希望视图实际上会存储在某个地方，以便选择它不会花费很长时间。
I may be mistaken, but I get the sense (from the speed with which create viewexecutes 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.
我可能弄错了，但我感觉到（从create view执行速度和我对视图的查询持续时间），每次我选择反对它时，视图实际上是在外部查询之前作为查询运行的。
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 时，它可以完成查询视图查询 *then 的艰苦工作，这样我随后针对这个静态视图的选择就会非常迅速。
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
采纳答案by nakosspy
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。
回答by paxdiablo
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 emulatethem 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 :-)
如果创建视图后数据永远不会更改（正如您在评论中所指出的那样），只需创建一个全新的表来保存数据子集并查询它。人们总是抱怨速度慢，但很少抱怨数据存储要求:-)
回答by Stephan
Since a view is basically a SELECTstatement you can use query cacheto improve performance.
由于视图基本上是一个SELECT语句，因此您可以使用查询缓存来提高性能。
But first you should check if :
但首先你应该检查是否：
- you can add indexes in the tables involved to speed up the query (use
EXPLAIN) - the data isn't changing very often you can materialize the view (make snapshots)
- 您可以在所涉及的表中添加索引以加快查询速度（使用
EXPLAIN） - 数据不会经常变化，您可以实现视图（制作快照）
回答by mukul pahwa
Use a materiallised view.. It can store data like count sum etc but yes after updating the table you need to refresh the view to get correct results as they are not auto updated.. Moreover after querying from view the results are stored in cache so the memory cycles reduces to 2 which are 4 in case of querying from the table itself. So it gets efficient from the second time.. When you query for 1st time from view the data is fetched from main memory and is stored in cache after it.
使用实体化视图。它可以存储诸如计数总和等数据，但是在更新表后，您需要刷新视图以获得正确的结果，因为它们不会自动更新。此外，从视图查询后，结果存储在缓存中在从表本身查询的情况下，内存周期减少到 2 个，即 4 个。所以它从第二次开始变得高效..当您从视图中查询第一次时，数据是从主内存中获取的，然后存储在缓存中。
