视图比简单查询更快吗? [英] Is a view faster than a simple query?

问题描述

是一个

select *  from myView

比查询本身更快地创建视图(为了具有相同的结果集):

faster than the query itself to create the view (in order to have the same resultSet):

select * from ([query to create same resultSet as myView])

?

我不太清楚视图是否使用某种缓存使其比简单查询更快.

It's not totally clear to me if the view uses some sort of caching making it faster compared to a simple query.

推荐答案

是，视图可以分配一个聚集索引，当它们分配时，它们将存储可以加速结果查询的临时结果.

Yes, views can have a clustered index assigned and, when they do, they'll store temporary results that can speed up resulting queries.

Microsoft 自己的文档非常清楚地表明 Views 可以提高性能.

Microsoft's own documentation makes it very clear that Views can improve performance.

首先，人们创建的大多数视图都是简单视图并且不使用此功能，因此与直接查询基表没有区别.简单的视图就地展开，因此不会直接促进性能改进 - 确实如此.然而，索引视图可以显着提高性能.

First, most views that people create are simple views and do not use this feature, and are therefore no different to querying the base tables directly. Simple views are expanded in place and so do not directly contribute to performance improvements - that much is true. However, indexed views can dramatically improve performance.

让我直接看文档:

在视图上创建唯一聚集索引后，视图的结果集会立即具体化并持久保存在数据库的物理存储中，从而节省了在执行时执行此昂贵操作的开销.

After a unique clustered index is created on the view, the view's result set is materialized immediately and persisted in physical storage in the database, saving the overhead of performing this costly operation at execution time.

第二，这些索引视图可以工作，即使它们没有被另一个查询直接引用，因为优化器会在适当的时候使用它们代替表引用.

Second, these indexed views can work even when they are not directly referenced by another query as the optimizer will use them in place of a table reference when appropriate.

再次说明文档:

索引视图可以通过两种方式在查询执行中使用.查询可以直接引用索引视图，或者更重要的是，如果查询优化器确定该视图可以替代最低成本查询计划中的部分或全部查询，则可以选择该视图.在第二种情况下，使用索引视图代替基础表及其普通索引.不需要在查询中引用视图，查询优化器就可以在查询执行期间使用它.这允许现有应用程序从新创建的索引视图中受益，而无需更改这些应用程序.

The indexed view can be used in a query execution in two ways. The query can reference the indexed view directly, or, more importantly, the query optimizer can select the view if it determines that the view can be substituted for some or all of the query in the lowest-cost query plan. In the second case, the indexed view is used instead of the underlying tables and their ordinary indexes. The view does not need to be referenced in the query for the query optimizer to use it during query execution. This allows existing applications to benefit from the newly created indexed views without changing those applications.

可以在 这里.

更新 2: 答案受到批评，理由是它是索引"提供性能优势，而不是视图".然而，这很容易被驳倒.

Update 2: the answer has been criticized on the basis that it is the "index" that provides the performance advantage, not the "View." However, this is easily refuted.

假设我们是一个小国的软件公司；我将以立陶宛为例.我们在全球范围内销售软件并将我们的记录保存在 SQL Server 数据库中.我们非常成功，因此，在几年内，我们拥有 1,000,000 多条记录.但是，出于税收目的，我们经常需要报告销售情况，而且我们发现我们在本国仅销售了 100 份软件.通过创建仅包含立陶宛记录的索引视图，我们可以将我们需要的记录保存在 MS 文档中所述的索引缓存中.当我们运行 2008 年立陶宛销售报告时，我们的查询将搜索深度仅为 7 的索引(Log2(100) 和一些未使用的叶子).如果我们在没有 VIEW 的情况下做同样的事情而只依赖表中的索引，我们将不得不遍历搜索深度为 21 的索引树！

Let us say that we are a software company in a small country; I'll use Lithuania as an example. We sell software worldwide and keep our records in a SQL Server database. We're very successful and so, in a few years, we have 1,000,000+ records. However, we often need to report sales for tax purposes and we find that we've only sold 100 copies of our software in our home country. By creating an indexed view of just the Lithuanian records, we get to keep the records we need in an indexed cache as described in the MS documentation. When we run our reports for Lithuanian sales in 2008, our query will search through an index with a depth of just 7 (Log2(100) with some unused leaves). If we were to do the same without the VIEW and just relying on an index into the table, we'd have to traverse an index tree with a search depth of 21!

显然，与单独使用索引相比，视图本身将为我们提供性能优势(3 倍).我曾尝试使用一个真实世界的例子，但您会注意到，一个简单的立陶宛销售清单会给我们带来更大的优势.

Clearly, the View itself would provide us with a performance advantage (3x) over the simple use of the index alone. I've tried to use a real-world example but you'll note that a simple list of Lithuanian sales would give us an even greater advantage.

请注意，我只是在示例中使用了直 b 树.虽然我相当确定 SQL Server 使用了 b 树的某些变体，但我不知道详细信息.尽管如此，这一点仍然成立.

Note that I'm just using a straight b-tree for my example. While I'm fairly certain that SQL Server uses some variant of a b-tree, I don't know the details. Nonetheless, the point holds.

更新 3: 问题是关于索引视图是否只使用放置在基础表上的索引.也就是说，解释为:索引视图只是标准索引的等价物，它不提供任何新的或独特的视图."当然，如果这是真的，那么上面的分析就是错误的！让我引用 Microsoft 文档中的一段话来说明为什么我认为这种批评是无效或不正确的:

Update 3: The question has come up about whether an Indexed View just uses an index placed on the underlying table. That is, to paraphrase: "an indexed view is just the equivalent of a standard index and it offers nothing new or unique to a view." If this was true, of course, then the above analysis would be incorrect! Let me provide a quote from the Microsoft documentation that demonstrate why I think this criticism is not valid or true:

使用索引来提高查询性能并不是一个新概念；然而，索引视图提供了使用标准索引无法实现的额外性能优势.

Using indexes to improve query performance is not a new concept; however, indexed views provide additional performance benefits that cannot be achieved using standard indexes.

连同上面关于物理存储中数据持久性的引用以及文档中关于如何在视图上创建索引的其他信息，我认为可以肯定地说索引视图不是只是一个缓存的 SQL Select 恰好使用在主表上定义的索引.因此，我继续支持这个答案.

Together with the above quote regarding the persistence of data in physical storage and other information in the documentation about how indices are created on Views, I think it is safe to say that an Indexed View is not just a cached SQL Select that happens to use an index defined on the main table. Thus, I continue to stand by this answer.

这篇关于视图比简单查询更快吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！