blocks|key|372438|text|我认为Peter+Zaitsev的博客包含了大部分细节。从个人经验来看，如果你通常保持视图简单，那么视图可以表现得很好。在我的一个客户那里，他们不断地将一个视图叠加在另一个视图之上，最终导致了一场性能噩梦。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|372439|通常，我使用视图来显示表的不同方面。例如，在“我的员工”表中，向我显示经理或对非HR员工隐藏“薪资”字段。此外，请始终确保在查询和视图上运行解释，以准确地了解MySQL内部发生的事情。|372440|如果你想在你的场景中得到确凿的证据，我建议你测试一下。很难说使用视图总是一个性能杀手，然后再说一次，一个糟糕的视图很可能会毁了你的性能。|372441|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

I think the blog by Peter Zaitsev has most of the details. Speaking from personal experience views can perform well if you generally keep them simple. At one of my clients they kept on layering one view on top of another and it ended up in a perfomance nightmare. 

Generally I use views to show a different aspect of a table. For example in my employees table show me the managers or hide the salary field from non HR employees. Also always make sure you run a EXPLAIN on the query and view to understand exactly what is happening inside MySQL.

If you want solid proof in your scenario I would suggest that you test. It is really hard to say using views is always a performance killer then again a badly written view is probably going to kill your performance.

blocks|key|372508|text|如果我们讨论的是“如果你使用视图，如何确保性能”，而不是一般的视图的性能效果，我认为这归结为克制(就像你自己)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|372509|如果你只是编写视图来使你的查询在所有情况下都很简单，那么你可能会遇到很大的麻烦，但不要注意你的视图在性能方面实际上是有用的。你在最后执行的任何查询都应该是正常运行的(参见@eggyal链接中的注释示例)。当然，这是一种同义反复，但并不因此而降低其价值|372510|你尤其需要小心，不要从视图中创建视图，因为这可能会使创建视图变得更容易。|372511|最后，您需要了解使用视图的原因。任何时候，为了使编程端的工作更轻松，最好使用存储过程。|372512|为了让事情保持在控制之下，你可能想写下为什么你有一个特定的视图，并决定你为什么要使用它。对于你的程序中的每一个“新”使用，重新检查你是否真的需要这个视图，为什么你需要它，以及这是否仍然能给你一个合理的执行路径。不断检查你的使用，以保持它的速度，并保持检查，如果你真的需要那个视图。|372513|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|L|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|M|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|N|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|O|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|P|8|@]|9|@]|A|$]]|$1|J|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|K|$]]

If we are discussing "if you use views, how to ensure performance", and not the performance effect of views in general, I think that it boils down to restraint (as in yourself).

You can get in to big trouble if you just write views to make your query's simple in all cases, but do not take care that your views are actually usefull performance-wise. Any query's you're doing in the end should be running sane (see the comment example from that link by @eggyal). Ofcourse that's a tautology, but therefore not any less valuable

You especially need to be carefull not to make views from views, just because that might make it easier to make that view.

In the end you need to look at the reason you are using views. Any time you do this to make life easier on the programming end you might be better of with a stored procedure IMHO.

To keep things under control you might want to write down why you have a certain view, and decide why you are using it. For every 'new' use within your programming, recheck if you actually need the view, why you need it, and if this would still give you a sane execution-path. Keep on checking your uses to keep it speedy, and keep checking if you really need that view.

blocks|key|372579|text|这是tl;dr总结，你可以在Peter+Zaitsev和其他地方找到详细的评价。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|372580|MySQL中的视图通常不是一个好主意。在Grooveshark，我们认为它们是有害的，并总是避免使用它们。如果您很小心，您可以让它们工作，但它们充其量是记住如何选择数据的一种方法，或者使您不必重新键入复杂的连接。在最坏的情况下，它们会导致大量的效率低下，隐藏复杂性，导致意外的嵌套子选择(需要临时表并导致磁盘颠簸)，等等。|372581|最好避免使用它们，并将您的查询保持在代码中。|372582|entityMap^0|0|0|0^^$0|@$1|2|3|4|5|6|7|H|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|I|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|J|8|@]|9|@]|A|$]]|$1|F|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|G|$]]

Here's a tl;dr summary, you can find detailed evaluations from Peter Zaitsev and elsewhere.

Views in MySQL are generally a bad idea. At Grooveshark we consider them to be harmful and always avoid them. If you are careful you can make them work but at best they are a way to remember how to select data or keep you from having to retype complicated joins. At worst they can cause massive inefficiencies, hide complexity, cause accidental nested subselects (requiring temporary tables and leading to disk thrashing), etc.

It's best to just avoid them, and keep your queries in code.

blocks|key|588308|text|它们达到了它们的目的，但隐藏的复杂性和低效通常比更直接的方法更重要。我曾经遇到过一条连接两个视图的SQL语句，并对它们的结果进行排序。视图也在排序，所以执行时间似乎可以用几个小时来衡量。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|588309|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|D|8|@]|9|@]|A|$]]|$1|B|3|-4|5|6|7|E|8|@]|9|@]|A|$]]]|C|$]]

They serve their purpose, but the hidden complexities and inefficiencies usually outweigh a more direct approach. I once encountered a SQL statement that was joining on two views, and sorting them the results. The views were sorting as well, so the execution time could be measured in what seemed like hours.

If you are going down the road of using views, how can you ensure good performance?

Or is it better not to use views in the first place and just incorporate the equivalent into your select statements?

MySql views performance

MySQL

如果您正在使用视图，您如何确保良好的性能？或者，最好一开始就不使用视图，直接将等效项合并到select语句中？

问MySql视图性能
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySql视图性能EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问MySql视图性能
EN