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?
数据库视图仅仅是简化数据访问的一种手段，还是在访问视图时提供性能优势，而不是仅仅运行视图所基于的查询?我怀疑视图在功能上等同于将存储的视图查询添加到视图数据上的每个查询，这是正确的吗?
7 个解决方案
#1
9
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.
虽然在视图内部运行的某个查询和在视图外部运行的相同查询应该执行相同的操作，但是当您需要将两个视图连接在一起时，事情会变得非常复杂。您可以很容易地在查询中引入不需要的表，或者冗余地引入表。数据库的优化器在创建一个好的查询执行计划时会遇到更多的麻烦。因此，虽然视图在允许更细粒度的安全性等方面可以做得很好，但它们不一定适合模块化。
#2
7
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，但通常不会进行优化，而且它只是简化查询的一种方便方式。然而，有些数据库系统使用“物化视图”，它们使用缓存机制。
#3
7
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.
您还可以对视图进行索引，以便访问您正在运行的查询类型的数据的优化视图。
#4
5
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设计中的一个重要概念。
#5
2
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系统都有工具，可以让您看到queryplans，并监视执行。不要相信我(或其他人)的话，当你能掌握确切的数据时。
#6
2
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之类的东西作为前端，那么您可以通过使用视图来为某些复杂的查询获得性能。这是因为访问并不总是像我们希望的那样从SQL服务器拉出——在某些情况下，它会拉过整个表，然后尝试从那里本地处理!如果使用视图，就不是这样。
#7
0
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.
许多人至今仍坚持这一点，使它成为现代DB迷思。自从视图/PS获得了缓存的SPs查询计划后，它们几乎是均匀的。
#1
9
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.
虽然在视图内部运行的某个查询和在视图外部运行的相同查询应该执行相同的操作，但是当您需要将两个视图连接在一起时，事情会变得非常复杂。您可以很容易地在查询中引入不需要的表，或者冗余地引入表。数据库的优化器在创建一个好的查询执行计划时会遇到更多的麻烦。因此，虽然视图在允许更细粒度的安全性等方面可以做得很好，但它们不一定适合模块化。
#2
7
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，但通常不会进行优化，而且它只是简化查询的一种方便方式。然而，有些数据库系统使用“物化视图”，它们使用缓存机制。
#3
7
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.
您还可以对视图进行索引，以便访问您正在运行的查询类型的数据的优化视图。
#4
5
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设计中的一个重要概念。
#5
2
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系统都有工具，可以让您看到queryplans，并监视执行。不要相信我(或其他人)的话，当你能掌握确切的数据时。
#6
2
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之类的东西作为前端，那么您可以通过使用视图来为某些复杂的查询获得性能。这是因为访问并不总是像我们希望的那样从SQL服务器拉出——在某些情况下，它会拉过整个表，然后尝试从那里本地处理!如果使用视图，就不是这样。
#7
0
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.
许多人至今仍坚持这一点，使它成为现代DB迷思。自从视图/PS获得了缓存的SPs查询计划后，它们几乎是均匀的。