database 数据库视图会影响查询性能吗？

Do database views affect query performance?

提问by Morten Christiansen

Are database views only a means to simplify the access of data or does it provide performance benefits when accessing the views as opposed to just running the query which the view is based on? I suspect views are functionally equivalent to just the adding the stored view query to each query on the view data, is this correct or are there other details and/or optimizations happening?

数据库视图只是一种简化数据访问的方法，还是在访问视图时提供性能优势，而不是仅运行视图所基于的查询？我怀疑视图在功能上等同于将存储的视图查询添加到视图数据的每个查询中，这是正确的还是有其他细节和/或优化正在发生？

回答by Craig Stuntz

Although a certain query running inside a view and the same query running outside of the view should perform equivalently, things get much more complicated quickly when you need to join two views together. You can easily end up bringing tables that you don't need into the query, or bringing tables in redundantly. The database's optimizer may have more trouble creating a good query execution plan. So while views can be very good in terms of allowing more fine grained security and the like, they are not necessarily good for modularity.

尽管在视图内运行的某个查询和在视图外运行的相同查询的性能应该相同，但当您需要将两个视图连接在一起时，事情会变得更加复杂。您可以轻松地将不需要的表带入查询中，或者将表带入冗余中。数据库的优化器在创建良好的查询执行计划时可能会遇到更多问题。因此，虽然视图在允许更细粒度的安全性等方面非常好，但它们不一定适合模块化。

回答by Christian Oudard

It depends on the RDBMS, but usually there isn't optimization going on, and it's just a convenient way to simplify queries. Some database systems use "materialized views" however, which do use a caching mechanism.

它取决于 RDBMS，但通常不会进行优化，这只是一种简化查询的便捷方式。然而，一些数据库系统使用“物化视图”，它们确实使用缓存机制。

回答by Robin Day

I have always considered Views to be like a read-only Stored Procedures. You give the database as much information as you can in advance so it can pre-compile as best it can.

我一直认为视图就像只读存储过程。您可以提前为数据库提供尽可能多的信息，以便它可以尽可能地进行预编译。

You can index views as well allowing you access to an optimised view of the data you are after for the type of query you are running.

您还可以索引视图，从而允许您访问针对您正在运行的查询类型所需要的数据的优化视图。

回答by dkretz

Usually a view is just a way to create a common shorthand for defining result sets that you need frequently.

通常，视图只是为定义经常需要的结果集而创建通用简写的一种方式。

However, there is a downside. The temptation is to add in every column you think you might need somewhere sometime when you might like to use the view. So YAGNI is violated. Not only columns, but sometimes additional outer joins get tacked on "just in case". So covering indexes might not cover any more, and the query plan may increase in complexity (and drop in efficiency).

但是，有一个缺点。诱惑是在您可能想使用视图的某个时候添加您认为可能需要的每一列。所以YAGNI被侵犯了。不仅是列，有时还会附加额外的外连接，以“以防万一”。因此覆盖索引可能不再覆盖，并且查询计划可能会增加复杂性（并降低效率）。

YAGNI is a critical concept in SQL design.

YAGNI 是 SQL 设计中的一个关键概念。

回答by Michael Dorfman

Generally speaking, views should perform equivalently to a query written directly on the underlying tables.

一般来说，视图应该与直接在基础表上编写的查询等效。

But: there may be edge cases, and it would behoove you to test your code. All modern RDBMS systems have tools that will let you see the queryplans, and monitor execution. Don't take my (or anybody else's) word for it, when you can have the definitive data at your fingertips.

但是：可能存在边缘情况，您应该测试您的代码。所有现代 RDBMS 系统都有一些工具可以让您查看查询计划并监控执行情况。不要轻信我（或其他任何人）的话，因为您可以随时掌握明确的数据。

回答by Michael

I know this is an old thread. Discussion is good, but I do want to throw in one more thought. Performance also depends on what you are using to pull data with. For example, if you are front-ending with something like Microsoft Access you can definately gain performance for some complex queries by using a view. This is because Access does not always pull from the SQL server as we would like -- in some cases it would pull entire tables across then try to process locally from there! Not so if you use a view.

我知道这是一个旧线程。讨论很好，但我确实想再提出一个想法。性能还取决于您用于提取数据的工具。例如，如果您使用 Microsoft Access 之类的前端，您肯定可以通过使用视图来提高某些复杂查询的性能。这是因为 Access 并不总是像我们希望的那样从 SQL 服务器中提取——在某些情况下，它会提取整个表，然后尝试从那里进行本地处理！如果您使用视图，则并非如此。

回答by Codex

Yes, in all modern RDBMS's (MSSQL after 2005? etc) view's query plans are cached removing the overhead of planning the query and speeding up performance over the same SQL performed in-line. Previously to this (and it applies to parameterized SQL/Prepared Statements as well) people correctly thought stored procedures performed better.

是的，在所有现代 RDBMS（2005 年之后的 MSSQL？等）中，视图的查询计划都被缓存，从而消除了计划查询的开销，并通过在线执行的相同 SQL 提高了性能。在此之前（它也适用于参数化 SQL/准备好的语句），人们正确地认为存储过程执行得更好。

Many still hang onto this today making it a modern DB myth. Ever since Views/PS's got the cached query planning of SPs they've been pretty much even.

许多人今天仍然坚持这一点，使其成为现代数据库的神话。自从 Views/PS 获得了 SP 的缓存查询计划后，它们就变得非常均匀了。