MySql 查看性能

MySql views performance

提问by Ed Heal

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?

或者最好不要首先使用视图，而只是将等效项合并到您的选择语句中？

回答by Somnath Muluk

It Depends.

这取决于。

It totally depends on what you are viewing through view. But most probably reducing your effort and giving higher performance. When SQL statement references a nonindexed view, the parser and query optimizer analyze the source of both the SQL statement and the view and then resolve them into a single execution plan. There is not one plan for the SQL statement and a separate plan for the view.

这完全取决于您通过视图查看的内容。但很可能会减少您的努力并提供更高的性能。当 SQL 语句引用非索引视图时，解析器和查询优化器会分析 SQL 语句和视图的来源，然后将它们解析为单个执行计划。没有针对 SQL 语句的一个计划和针对视图的单独计划。

A view is not compiled. Its a virtual table made up of other tables. When you create it, it doesn't reside somewhere on your server. The underlying queries that make up the view are subject to the same performance gains or dings of the query optimizer. I've never tested performance on a view VS its underlying query, but i would imagine the performance may vary slightly. You can get better performance on an indexed view if the data is relatively static. This may be what you are thinking maybe in terms of "compiled".

未编译视图。它是由其他表组成的虚拟表。当您创建它时，它不会驻留在您的服务器上的某处。构成视图的底层查询受到与查询优化器相同的性能提升或影响的影响。我从未测试过视图 VS 其底层查询的性能，但我认为性能可能会略有不同。如果数据相对静态，您可以在索引视图上获得更好的性能。这可能是您在“编译”方面的想法。

Advantages of views:

视图的优点：

1. View the data without storing the data into the object.
2. Restrict the view of a table i.e. can hide some of columns in the tables.
3. Join two or more tables and show it as one object to user.
4. Restrict the access of a table so that nobody can insert the rows into the table.

1. 查看数据而不将数据存储到对象中。
2. 限制表的视图，即可以隐藏表中的某些列。
3. 连接两个或多个表并将其作为一个对象显示给用户。
4. 限制对表的访问，以便没有人可以将行插入表中。

See these useful links:

查看这些有用的链接：

1. Performance of VIEW vs. SQL statement
2. Is a view faster than a simple query?
3. Mysql VIEWS vs. PHP query
4. Are MySql Views Dynamic and Efficient?
5. Materialized View vs. Tables: What are the advantages?
6. Is querying over a view slower than executing SQL directly?
7. A workaround for the performance problems of TEMPTABLE views
8. See performance gains by using indexed views in SQL Server

1. VIEW 与 SQL 语句的性能
2. 视图是否比简单查询更快？
3. Mysql VIEWS 与 PHP 查询
4. MySql 视图是否动态且高效？
5. 物化视图与表：有什么优势？
6. 查询视图比直接执行 SQL 慢吗？
7. TEMPTABLE 视图性能问题的解决方法
8. 通过在 SQL Server 中使用索引视图查看性能提升

回答by Namphibian

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.

我认为 Peter Zaitsev 的博客包含了大部分细节。如果您通常保持简单，从个人经验来看，观点可能会表现良好。在我的一个客户那里，他们不断将一个视图叠加在另一个视图上，结果却是一场性能噩梦。

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.

通常我使用视图来显示表格的不同方面。例如，在我的员工表中向我显示经理或对非人力资源员工隐藏工资字段。还要始终确保对查询和视图运行 EXPLAIN 以准确了解 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.

如果您想在您的场景中获得可靠的证据，我建议您进行测试。很难说使用视图总是一个性能杀手，然后再一次写得不好的视图可能会扼杀你的性能。

回答by Jay Paroline

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

这是 tl;dr 摘要，您可以找到 Peter Zaitsev 和其他地方的详细评估。

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.

MySQL 中的视图通常是一个坏主意。在 Grooveshark，我们认为它们是有害的，并始终避免它们。如果您小心，您可以使它们工作，但充其量它们是记住如何选择数据或使您不必重新键入复杂连接的一种方式。在最坏的情况下，它们会导致大量的低效率、隐藏复杂性、导致意外的嵌套子选择（需要临时表并导致磁盘抖动）等。

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

最好避免它们，并将您的查询保留在代码中。

回答by GDP

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.

它们服务于它们的目的，但隐藏的复杂性和低效率通常比更直接的方法更重要。我曾经遇到过一个 SQL 语句，它连接两个视图，并对结果进行排序。视图也在排序，所以执行时间可以用几个小时来衡量。

回答by petermeissner

A thing not mentioned so far but making a huge difference is adequate indexing of the views' sourcetables.

到目前为止还没有提到但产生巨大差异的一件事是对视图的源表进行足够的索引。

As mentioned above, views do not reside in your DBbut are rebuild every time. Thus everything that makes the rebuild easier for the DB increases performance of the view.

如上所述，视图并不驻留在您的数据库中，而是每次都重建。因此，使 DB 的重建更容易的一切都会提高视图的性能。

Often, views join data in a way that is very bad for storage (no normal form) but very good for further usage (doing analysis, presenting data to user, ...) and therewith joining and aggregating data from different tables.

通常，视图以一种非常不利于存储的方式连接数据（非标准形式）但非常适合进一步使用（进行分析，向用户呈现数据，...），从而连接和聚合来自不同表的数据。

Whether or not the columns on which the operations are made are indexed or not makes a huge difference on the performance of a view. If the tables and their relevant columns are indexed already accessing the view does not end in re-computing the indexes over and over again first. (on the downside, this is done when data is manipulated in the source tables)

进行操作的列是否被索引对视图的性能有巨大的影响。如果表及其相关列已被索引，则访问视图不会首先一遍又一遍地重新计算索引。（不利的一面是，这是在源表中操作数据时完成的）

! Index all columns used in JOINS and GROUP BY clauses in your CREATE VIEW statement !

！索引您的 CREATE VIEW 语句中 JOINS 和 GROUP BY 子句中使用的所有列！

回答by Nanne

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

如果您只是编写视图以使您的查询在所有情况下都变得简单，那么您可能会遇到大麻烦，但不要注意您的视图实际上在性能方面很有用。您最后所做的任何查询都应该运行正常（请参阅@eggyal 来自该链接的评论示例）。当然这是同义反复，但因此同样有价值

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.

为了控制事情，您可能需要写下您为什么有某个视图，并决定您为什么使用它。对于您编程中的每个“新”用途，请重新检查您是否确实需要该视图，为什么需要它，以及这是否仍会为您提供合理的执行路径。继续检查您的用途以使其快速，并继续检查您是否真的需要该视图。