SQL 为什么要在视图上放置索引？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4737490/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why would you want to put an index on a view?
提问by Slider345
Microsoft SQL Server allows you to add an index to a view, but why would you want to do this?
Microsoft SQL Server 允许您向视图添加索引，但为什么要这样做呢？
My understanding is that a view is really just a subquery, i.e., if I say SELECT * FROM myView, i'm really saying SELECT * FROM (myView's Query)
我的理解是视图实际上只是一个子查询，即，如果我说 SELECT * FROM myView，我实际上是在说 SELECT * FROM (myView's Query)
It seems like the indexes on the underlying tables would be the ones that matter the most. So why would you want a separate index on the view?
基础表上的索引似乎是最重要的索引。那么为什么要在视图上设置单独的索引呢？
回答by Larry Lustig
If the view is indexed then any queries that can be answered using the index only will never need to refer to the underlying tables. This can lead to an enormous improvement in performance.
如果视图被索引，那么任何可以仅使用索引回答的查询将永远不需要引用基础表。这可以极大地提高性能。
Essentially, the database engine is maintaining a "solved" version of the query (or, rather, the index of the query) as you update the underlying tables, then using that solved version rather than the original tables when possible.
本质上，数据库引擎在您更新基础表时维护查询的“已解决”版本（或者，查询的索引），然后在可能的情况下使用该已解决版本而不是原始表。
Here is a good articlein Database Journal.
回答by Quassnoi
Microsoft SQL Server allows you to add an index to a view, but why would you want to do this?
Microsoft SQL Server 允许您向视图添加索引，但为什么要这样做呢？
To speed up the queries.
加快查询速度。
My understanding is that a view is really just a subquery, i.e., if I say SELECT * FROM myView, i'm really saying SELECT * FROM (myView's Query)
我的理解是视图实际上只是一个子查询，即，如果我说 SELECT * FROM myView，我实际上是在说 SELECT * FROM (myView's Query)
Not always.
不总是。
By creating a clustered index on a view, you materialize the view, and updates to the underlying tables physically update the view. The queries against this view may or may not access the underlying tables.
通过在视图上创建聚集索引，您可以具体化视图，并且对基础表的更新以物理方式更新视图。针对此视图的查询可能会也可能不会访问基础表。
Not all views can be indexed.
并非所有视图都可以编入索引。
For instance, if you are using GROUP BYin a view, for it to be indexable it should contain a COUNT_BIGand all aggregate functions in it should distribute over UNION ALL(only SUMand COUNT_BIGactually are). This is required for the index to be maintainable and the update to the underlying tables could update the view in a timely fashion.
例如，如果您GROUP BY在视图中使用，为了使其可索引，它应该包含 aCOUNT_BIG并且其中的所有聚合函数都应该分布UNION ALL（仅SUM并且COUNT_BIG实际上是）。这是索引可维护所必需的，并且对基础表的更新可以及时更新视图。
回答by Daniel Casserly
the following link provides better worded information than i can say especially in the section under performance increases. Hope it helps
以下链接提供了比我能说的更好的措辞信息，尤其是在性能提升部分。希望能帮助到你
回答by nvogel
You create an index on a view for the same reason as on a base table: to improve the performance of queries against that view. Another reason for doing it is to implement some uniqueness constraint you can't implement against base tables. SQL Server unfortunately doesn't allow constraints to be created on views.
您在视图上创建索引的原因与在基表上创建索引的原因相同：提高针对该视图的查询性能。这样做的另一个原因是实现一些无法针对基表实现的唯一性约束。遗憾的是，SQL Server 不允许在视图上创建约束。
