SQL 索引如何作用于视图？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1721448/
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
How do indexes work on views?
提问by Shaul Behr
Can someone please explain to me in simple English how an index on a view works? I have a fairly simple understanding of indexes on tables; how would indexing a view work differently from just letting the indexes on the underlying tables do their thing naturally?
有人可以用简单的英语向我解释视图上的索引是如何工作的吗？我对表上的索引有一个相当简单的理解；索引视图的工作方式与让底层表上的索引自然地做它们的工作有何不同？
采纳答案by Andomar
Say you have a view that limits the table to certain rows:
假设您有一个将表限制为某些行的视图：
create view dbo.vw_HotProducts
as
select * from dbo.Products where Hot = 1
Now if you create an index on this view, the index only contains hot products. You can compare it to storing the result of the view in a temporary table. This can be very useful for complicated queries with multiple joins; basically their output is cached.
现在，如果您在此视图上创建索引，则该索引仅包含热门产品。您可以将其与将视图结果存储在临时表中进行比较。这对于具有多个连接的复杂查询非常有用；基本上他们的输出被缓存。
The big disadvantage of indexed views is that they are recreated every time the underlying table data changes. That restricts the use of indexed views to data that does not change often, typically in a data warehouse or business intelligence environment.
索引视图的一大缺点是每次底层表数据更改时都会重新创建它们。这将索引视图的使用限制为不经常更改的数据，通常在数据仓库或商业智能环境中。
回答by S.Lott
See http://msdn.microsoft.com/en-us/library/aa258260(SQL.80).aspx
请参阅http://msdn.microsoft.com/en-us/library/aa258260(SQL.80).aspx
Creating a unique clustered index on a view improves query performance because the view is stored in the database in the same way a table with a clustered index is stored.
在视图上创建唯一的聚集索引可以提高查询性能，因为视图在数据库中的存储方式与具有聚集索引的表的存储方式相同。
The view is transformed from a proper view into a table. The view definition is used to update this table.
视图从正确的视图转换为表格。视图定义用于更新此表。
Oracle calls these "materialized views".
Oracle 将这些称为“物化视图”。
回答by gbn
A view by itself is not real or "persisted", and has no performance benefit. It's simply a macro that's expanded.
视图本身不是真实的或“持久化”的，并且没有性能优势。它只是一个扩展的宏。
Add an index and it physically exists (persisted), so the optimiser will consider using it. It's not a macro then.
添加索引并且它物理存在（持久化），因此优化器会考虑使用它。那么它不是一个宏。
I'm sure Oracle calls them "materialised views" which is a better name.
我确信 Oracle 将它们称为“物化视图”，这是一个更好的名称。
A related FYI: a computed column has a PERSISTED option that does the same thing...
一个相关的仅供参考：计算列有一个 PERSISTED 选项，它做同样的事情......
