用 SQL Server 2000 索引视图提高性能

通过索引视图提高的性能

使用索引来提高查询性能并不是什么新观念，不过，索引视图还具有使用标准索引不能获得的其它性能优点。索引视图能够在以下方面提高查询性能：

能够预先计算聚合并将其存储在索引中，从而最大限度地减少在执行查询期间进行成本很高的计算。
能够预先联接表并存储生成的数据集。
能够存储联接或聚合的组合。
下图说明了查询优化器使用索引视图时一般能够提高多少性能。提供的查询复杂程度各不相同（例如，聚合计算的数量、所用表的数量或谓词数），并包括来自实际生产环境的数百万行的大表。

图 1. 当查询优化器使用索引视图时一般能够提高多少性能

使用视图的辅助索引

视图的辅助性非群集索引可以提高其它查询性能。与表的辅助索引类似，视图的辅助索引也可以提供更多选项，以便查询优化器在编译过程中从中进行选择。例如，如果查询包括群集索引未涉及的列，优化器可以在计划中选择一个或多个辅助索引，从而避免对索引视图或基表进行费时的全局扫描。

由于索引需要不断维护，所以为架构添加索引会增加数据库的额外开销。因此应该认真考虑，找到索引和维护额外开销之间的平衡点。

使用索引视图的好处

实现索引视图之前，请先分析数据库的工作量。运用自己对查询以及各种工具（例如 sql 分析器）的知识来鉴别使用索引视图可以获益的查询。如果经常进行聚合和联接，最好使用索引视图。

并非所有查询都会从索引视图中获益。与普通索引类似，如果未使用索引视图，就没有好处可言。在此情况下，不但不能提高性能，还会加大磁盘空间的占用、增加维护和优化的成本。但是，如果使用了索引视图，它们可以（成数量级地）明显地提高数据访问的性能。这是因为查询优化器使用存储在索引视图中的预先计算的结果，从而大大降低了执行查询的成本。

查询优化器只在查询的成本比较大时才考虑使用索引视图。这样可以避免在查询优化成本超出因使用索引视图而节省的成本时，试图使用各种索引视图。当查询成本低于 1 时，几乎不使用索引视图。

使用索引视图可以受益的应用包括：

决定支持工作量
数据集市
联机分析处理 (olap) 库和源
数据挖掘工作量
从查询的类型和模式的角度来看，受益的应用可被归纳为包含以下内容的应用：

大表的联接和聚合
查询的重复模式
重复聚合相同或重叠的列集
针对相同关键字重复联接相同的表
上述的组合
相反，包含许多写入的联机事务处理 (oltp) 系统或更新频繁的数据库，可能会因为要同时更新视图和根本基表而使维护成本增加，所以不能利用索引视图。

查询优化器如何使用索引视图

sql server 查询优化器可自动确定何时可以将索引视图用于给定的查询执行中。查询中无需直接引用视图，优化器就可以将该视图用于查询执行计划。因此，无需对现有的应用程序本身进行任何更改，这些应用程序即可利用索引视图。唯一需要做的就是创建索引视图。

优化器的考虑因素

查询优化器会考虑几个条件来确定索引视图能涵盖部分查询还是整个查询。这些条件符合查询中的单个 from 子句并包含以下内容：

查询 from 子句中的表必须是索引视图 from 子句中的表的超集。
查询中的联接条件必须是视图中联接条件的超集。
查询中的聚合列必须是视图中的聚合列的子集。
查询选择列表中的所有表达式都必须源自于视图选择列表或源自于不包括在视图定义中的表。
查询搜索条件谓词必须是视图定义中搜索条件谓词的超集。视图搜索谓词中的每个合取项都必须以同样的形式出现在查询搜索谓词中。
查询搜索条件谓词中的所有列（属于视图定义中的表）都必须出现在下列一项或多项中：
视图定义中的同一个谓词。
group by 列表。
视图选择列表（若没有 group by 列表）。
如果查询包含多个 from 子句（子查询、派生表、union），优化器可以选择多个索引视图来管理含有多个 from 子句的查询。

注意：   也存在例外情形，即优化器可能将两个 from 子句折叠成一个（将子查询折叠成联接或将派生表折叠成联接变体）。如果出现此类情况，索引视图替换可能会涵盖原查询中的多个 from 子句。

本文档结尾介绍了演示这些条件的查询示例。而建议的最佳方法就是：让查询优化器来确定在查询执行计划中使用哪些索引（如果有的话）。

使用 noexpand 选项

noexpand 选项强制查询优化器象对待包含群集索引的普通表一样对待视图。在此情况下，必须在 from 子句中直接引用索引视图。例如：

select column1, column2, ... from table1, view1 with (noexpand)where ...

使用 expand views 选项

另外，用户可以在查询结束时通过使用 expand views 选项，明确地将索引视图排除在考虑之外。例如：

select column1, column2, ... from table1, view1 where ...option (expand views)

如果使用该选项，查询优化器在评估低成本的方法（该方法涉及查询中引用的列）时将忽略所有视图索引。

设计的考虑因素

为数据库系统找到适当的索引集是相当复杂的。尽管在设计普通索引时要考虑许多可能性，但将索引视图添加到架构会极大地增加设计和潜在结果的复杂性。例如，索引视图可用于：

查询中所引用表的任何子集。
查询中条件的任何子集（属于表的上述子集）
分组列。
聚合函数，如 sum。
应同时设计表的索引和索引视图，以便从各个结构中获得最佳结果。由于索引和索引视图都可能对给定的查询有用，所以单独设计它们会导致多余的建议方案，以致存储和维护开销较高。在调整数据库的物理设计时，必须均衡考虑各种查询集的性能要求与数据库系统必须支持的更新操作。因此，为索引视图找到一种合理的物理设计是一项很具挑战性的任务，因而应该尽可能地使用“索引微调向导”。

如果存在许多索引视图可供查询优化器考虑用于特定查询，查询优化成本会显著增加。查询优化器可能考虑为查询中表的任意子集定义的所有索引视图。拒绝每一个视图之前，必须对它进行语法分析，然后研究其是否可能成为潜在的替换体。这可能需要一些时间，尤其是在有数百个此类的视图用于给定的查询时。

视图必须符合几项要求，您才能为其创建唯一的群集索引。在设计阶段，请考虑以下要求：

视图以及视图中引用的所有表都必须在同一数据库中，并具有同一个所有者。
索引视图无需包含要供优化器使用的查询中引用的所有表。
必须先为视图创建唯一群集索引，然后才可以创建其它索引。
创建基表、视图和索引以及修改基表和视图中的数据时，必须正确设置某些 set 选项（在本文档的后文中讨论）。另外，如果这些 set 选项正确，查询优化器将不考虑索引视图。
视图必须使用架构绑定创建，视图中引用的任何用户定义的函数必须使用 schemabinding 选项创建。
另外，还要求有一定的磁盘空间来存放由索引视图定义的数据。

设计准则

设计索引视图时，请考虑以下准则：

设计的索引视图必须能用于多个查询或多个计算。
例如，包含某列的 sum 和某列的 count_big 的索引视图可用于包含函数 sum、count、count_big 或 avg 的查询。由于只需检索视图中的少数几行，而不是基表中的所有行，且执行 avg 函数要求的部分计算已经完成，所以查询将比较快。

使索引保持紧凑。
通过使用最少的列数和尽可能少的字节数，优化器在查找行数据时可获得最高的效率。相反，如果定义了大的群集索引关键字，则为视图定义的任何辅助性非群集索引都将明显增大，这是因为非群集索引项除包含索引定义的列之外，还将包含群集关键字。

考虑生成的索引视图的大小。
在单纯的聚合情况下，如果索引视图的大小类似于原表的大小，使用索引视图可能无法明显提高任何性能。

设计多个较小的索引视图来加快部分进程的速度。
有时可能无法设计出能满足整个查询需要的索引视图。此时即可考虑创建这样一些索引视图，每个索引视图执行一部分查询。

考虑以下示例：

经常执行的查询会聚合一个数据库中的数据，再聚合另一个数据库中的数据，然后联接结果。由于索引视图不能引用多个数据库中的表，所以您不能设计一个视图来执行整个进程。不过，可以为要进行聚合的每个数据库创建索引视图。如果优化器能够将索引视图与现有查询相匹配，至少聚合处理将会因为不必记录现有查询而提高速度。尽管联接处理不会加快，整个查询的速度却因使用了存储在索引视图中的聚合而加快。
经常执行的查询会聚合多个表中的数据，然后使用 union 来将结果结合起来。union 不允许在索引视图中使用。您可以设计一些视图来执行每个单独的聚合运算。然后优化器可以选择索引视图来加快查询的速度，而无需记录查询。尽管 union 处理没有改进，单个聚合进程却得以改进。

使用“索引微调向导”

“索引微调向导”除建议使用基表的索引之外，还建议使用索引视图。使用该向导可提高管理员确定索引和索引视图相结合的能力，从而优化针对数据库执行的典型混合查询的性能。

由于“索引微调向导”强制使用所有必需的 set 选项（以确保结果集的正确性），其索引视图将会成功创建。不过，如果您的应用程序的选项没有按照要求设置，可能无法利用这些视图。对那些参与索引视图定义的表执行的插入、更新或删除操作可能会失败。

维护索引视图

sql server 自动维护索引视图，这与维护任何其它索引的情况类似。对于普通索引而言，每个索引都直接连接到单个表。通过对基础表执行每个 insert、update 或 delete 操作，索引相应地进行了更新，以便使存储在该索引中的值始终与表一致。

索引视图的维护与此类似。不过，如果视图引用了多个表，则对这些表中的任何一个进行更新都需要更新索引视图。与普通索引不同的是，对任何一个参与的表执行一次行插入操作都可能导致在索引视图中进行多次行插入操作。更新和删除操作的情况也是如此。因此，较之于维护表的索引，维护索引视图的代价更为高昂。

在 sql server 2000 中，某些视图可以更新。如果某个视图可以更新，则使用 insert、update 和 delete 语句可通过该视图直接修改根本基表。为某个视图创建索引并不会妨碍该视图的更新。有关可更新视图的详细信息，请参阅关于 sql server 2000 的“sql server 联机图书”中的“通过视图修改数据（英文）”。

维护成本的考虑因素

设计索引视图时应该考虑以下几点：

数据库中需要有一个额外的存储空间用于索引视图。索引视图的结果集以类似于典型表存储空间的方式物理保存在数据库中。
sql server 自动维护视图。因此，对定义视图所据的基表的任何更改都可能引起视图索引的一处或多处更改，从而导致维护开销的增加。
一个视图获得的净性能提高就是视图提供的查询执行节约总计与存储和维护该视图耗费的成本之间的差。

估计视图将占用的所需存储空间要相对简单一些。用 sql 查询分析器的“显示估计的执行计划”工具求视图定义中 select 语句的值。该工具将得出查询返回的行数和行大小的近似值。将这两个值相乘，即可估计出视图的可能大小。不过这只是一个近似值。视图索引的实际大小只能通过创建视图索引来精确得出。

从 sql server 执行的自动维护考虑因素的观点出发，“显示估计的执行计划”的功能可能会对此开销的影响有所了解。如果用 sql 查询分析器评估修改视图的语句（针对视图的 update 语句、针对基表的 insert 语句），showplan 将包括该语句的维护操作。同时考虑此成本和此操作将在生产环境中发生的次数，可以指示视图维护的可能成本。

通常建议对视图或基表进行的任何修改和更新都应该尽可能地成批执行，而不要单独进行。这样可以减少视图维护的某些开销。

创建索引视图

创建索引视图所需的步骤与视图的成功实现密不可分。

确保将在视图中引用的所有现有表的 set 选项都正确。
创建任何新表和视图之前，确保会话的 set 选项已正确设置。
确保视图定义是确定的。
使用 with schemabinding 选项创建视图。
创建视图的唯一群集索引。

使用 set 选项以获得一致的结果

如果在执行查询时启用不同的 set 选项，则在 sql server 中对同一个表达式求值会产生不同的结果。例如，将 set 选项 concat_null_yields_null 设置为 on 之后，表达式 'abc' + null 返回的值是 null。而将 concat_null_yieds_null 设置为 off 之后，该表达式得出的结果却是 'abc'。索引视图要求多个 set 选项的值都固定，以确保这些视图能够得到正确维护并返回一致的结果。

只要出现以下情况，就必须将下表中的 set 选项设置为要求的值列中所示的值：

创建了索引视图。
对索引视图中引用的任何表执行了任何 insert、update 或 delete 操作。
查询优化器使用索引视图来生成查询计划。
set
选项 要求
的值 默认
服务器
的值 ole db
和
odbc 的值 db lib
的值
ansi_nulls on off on off
ansi_padding on on on off
ansi_warning on off on off
arithabort on off off off
concat_null_yields_null on off on off
numeric_roundabort off off off off
quoted_identifier on off on off

如果使用的是 ole db 或 odbc 服务器连接，唯一必须修改的值是 arithabort 的设置。所有 db lib 值都必须使用 sp_configure 在服务器级上正确设置或使用 set 命令从应用程序正确设置。有关 set 选项的详细信息，请参阅关于 sql server 2000 的“sql server 联机图书”中的“使用 sql server 中的选项（英文）”。

使用确定性函数

索引视图的定义必须是确定性的。如果选择列表中的所有表达式以及 where 和 group by 子句都是确定性的，则视图就是确定性的。只要用特定的一组输入值对确定性表达式进行求值，一定会返回同一个结果。只有确定性函数可以加入确定性表达式。例如，dateadd 是确定性函数，因为将任何给定的一组变量值赋予它的三个参数进行求值，返回的总是同一个结果。而 getdate 则不是确定性函数，因为始终用同一个变量调用它，而它每次执行后返回的值都不相同。有关详细信息，请参阅关于 sql server 2000 的“sql server 联机图书”中的“确定性和非确定性函数”。

即便某个表达式是确定性的，但如果其中包含浮动表达式，确切的结果就可能取决于处理器的体系结构或微代码的版本。要确保 sql server 2000 中数据的完整性，此类表达式只能加入索引视图的非关键列。不包含浮动表达式的确定性表达式被称为精确的表达式。只有精确的确定性表达式可以加入索引视图的关键列和 where 或 group by 子句。

使用 columnproperty 函数和 isdeterministic 属性来确定视图列是否是确定性的。使用 columnproperty 函数和 isprecise 属性来确定包含架构绑定的视图中的确定性列是否是精确的。如果为 true，则 columnproperty 会返回 1，如果为 false，则返回 0，如果是无效的输入（列不是确定性的），则返回 null。例如，select columnproperty(object_id('vdiscount1'),'sumdiscountprice','isprecise') 返回的是 0，因为 sumdiscountprice 列引用了表 order details 中的浮动列 discount。而同一视图中的列 sumprice 既是确定性的又是精确的。

注意：   该 select 语句所基于的视图能够在示例部分找到（视图 1）。

其它要求

除“设计准则”、“使用 set 选项以获得一致的结果”和“使用确定性函数”部分中列出的要求之外，还必须符合以下要求。

基表要求

基表在创建时必须正确设置 set 选项，否则就不能被包含架构绑定的视图引用。
表必须通过视图定义中的两部分名称（所有者.表名）引用。
函数要求

用户定义的函数必须使用 with schemabinding 选项创建。
用户定义的函数必须通过两部分名称（所有者.函数）引用。
视图要求

视图必须使用 with schemabinding 选项创建。
视图必须只引用同一数据库中的基表，而不能引用其它视图。
语法限制

对视图定义的语法有几个限制。视图定义不能包含以下内容：

count(*)
rowset 函数
派生表
自联接
distinct
stdev、variance、avg
float* 列、文本列、ntext 列、图像列
子查询
全文谓词（contain、freetext）
可空表达式的 sum
min、max
top
outer 联接
union
注意：   索引视图可以包含浮动列，不过，此类列不能包含在群集索引关键字中。

group by 限制

如果未使用 group by，表达式不能在选择列表中使用。

如果使用了 group by，则 view 定义：

必须包含 count_big(*)。
不得包含 having、cube 或 rollup。
这些限制只适用于索引视图定义。查询可以在其执行计划中使用索引视图，即便该索引视图并不符合这些 group by 限制。

索引要求

执行 create index 语句的用户必须是视图所有者。
如果视图定义中包含 group by 子句，唯一群集索引的关键字只能引用 group by 子句中指定的列。

示例

本部分的示例阐述索引视图在两种主要查询（聚合和联接）中的使用问题。同时还说明查询优化器在确定某个索引视图是否可用时使用的条件。有关这些条件的完整列表，请参阅查询优化器如何使用索引视图。

查询基于 northwind（sql server 2000 中提供的数据库样本）中的表，并可以写入的方式执行。创建视图的前后，最好使用 sql 查询优化器中的“显示执行计划”工具来查看查询优化器选定的计划。尽管示例中阐述了优化器是如何选择成本最低的执行计划的，但因为 northwind 数据库样本太小，因此无法体现性能的提高。

以下查询显示如何从 order details 表中返回具有最大总折扣的五种产品的两个方法。

查询 1

select top 5 productid, sum(unitprice*quantity) -
sum(unitprice*quantity*(1.00-discount))as rebate
from [order details]
group by productid
order by rebate desc

查询 2

select top 5 productid, sum(unitprice*quantity*discount)as rebate
from [order details]
group by productid
order by rebate desc

查询优化器选定的执行计划包含：

对 order details 表的群集索引扫描，估计有 2,155 行。
哈希匹配/聚合运算符，该运算符基于 group by 列将选定的行放入哈希表，然后计算每行的 sum 聚合。
基于 order by 子句的 top 5 排序运算符。
视图 1

添加包括 rebate 列所需聚合的索引视图将更改查询 1 的查询执行计划。在数百万行的大表上，查询的性能也将明显提高。

create view vdiscount1 with schemabinding
as
select sum(unitprice*quantity)as sumprice,
sum(unitprice*quantity*(1.00-discount))
as sumdiscountprice, count_big(*) as count, productid
from dbo.[order details]
group by productid
   go
create unique clustered index vdiscountind on vdiscount1 (productid)

第一个查询的执行计划显示 vdiscount1 视图由查询优化器使用。不过，由于该视图不包含 sum(unitprice*quantity*discount) 聚合，因此不会被第二个查询使用。可以创建另一个可以同时满足上述两个查询的索引视图。

视图 2

create view vdiscount2 with schemabinding
as
select sum(unitprice*quantity)as sumprice,
sum(unitprice*quantity*(1.00-discount))as sumdiscountprice,
sum(unitprice*quantity*discount)as sumdiscountprice2, count_big(*)
as count, productid
from dbo.[order details]
group by productid
go
create unique clustered index vdiscountind on vdiscount2 (productid)

有了该索引视图，现在两个查询的查询执行计划包含：

对 vdiscount2 视图的群集索引扫描，估计有 77 行
基于 order by 子句的 top 5 排序函数
查询优化器选择该视图是因为它提供了最低的执行成本，尽管在查询中并未引用该视图。

查询 3

查询 3 类似于前几个查询，只是 productid 已被 orderid 所取代，视图定义中没有包括该列。这违背了以下条件：查询选择列表中的所有表达式都必须能从未包括在视图定义内的表的视图选择列表中派生。

select top 3 orderid, sum(unitprice*quantity*discount) orderrebate
from dbo.[order details]
group by orderid
order by orderrebate desc

要求单独的索引视图来满足该查询。可以对 vdiscount2 进行修改，使它包括 orderid，但是所生成视图的行数将与原表的行数相同，因此，提供的性能也不会高于使用基表所提供的性能。

查询 4

该查询可生成每个产品的平均价格。

select productname, od.productid,
 avg(od.unitprice*(1.00-discount)) as avgprice, sum(od.quantity) as units
from [order details] od, products p
where od.productid=p.productid
group by productname, od.productid

索引视图的定义中不能包括复杂的聚合（例如，stdev、variance、avg），不过，如果索引视图中包括几个联合起来执行复杂聚合的简单聚合函数，即可用于执行包含 avg 的查询。

视图 3

该索引视图包含执行 avg 函数所需的简单聚合函数。在创建了视图 3 后执行查询 4 时，执行计划会显示正被使用的视图。优化器可以从视图的简单聚合列 price 和 count 和 count 中导出 avg 表达式。

create view view3 with schemabinding
as
select productid, sum(unitprice*(1.00-discount))as price,
count_big(*)as count, sum(quantity)as units
from dbo.[order details]
group by productid
go
create unique clustered index iv3 on view3 (productid)

查询 5

该查询与查询 4 相同，只不过包括一个附加搜索条件。即使该附加搜索条件只引用未包括在视图定义内的表中的列，视图 3 也将用于该查询。

select productname, od.productid, avg(od.unitprice*(1.00-discount))
as avgprice, sum(od.quantity)as units
from [order details] as od, products as p
where od.productid=p.productid
and p.productname like '%tofu%'
group by productname, od.productid

查询 6

查询优化器不能将视图 3 用于该查询。附加搜索条件 od.unitprice>10 包含视图定义内的表中的列，而该列却不出现在 group by 列表中，搜索谓词也不出现在视图定义中。

select productname, od.productid, avg(od.unitprice*(1.00-discount))
 as avgprice, sum(od.quantity) as units
from [order details] od, products p
where od.productid = p.productid
and od.unitprice > 10
group by productname, od.productid

查询 7

相反，查询优化器可以将视图 3 用于查询 7，原因是新搜索条件 od.productid in (1,2,13,41) 中定义的列包括在视图定义内的 group by 子句中。

select productname, od.productid, avg(od.unitprice*(1.00-discount))
as avgprice, sum(od.quantity) as units
from [order details] as od, products as p
where od.productid = p.productid
and od.productid in (1,2,13,41)
group by productname, od.productid

视图 4

该视图在视图定义中包括了列 od.discount，可以满足查询 6 的条件。

create view view4 with schemabinding
as
select productname, od.productid, sum(od.unitprice*(1.00-discount))
as avgprice, sum(od.quantity) as units, count_big(*) as count
from dbo.[order details] as od, dbo.products as p
where od.productid = p.productid
and od.unitprice > 10
group by productname, od.productid
go
create unique clustered index vdiscountind on view4 (productname, productid)

查询 8

视图 4 的同一个索引还将用于一个添加了与表 orders 的联接的查询。该查询符合以下条件：查询 from 子句中列出的表是索引视图的 from 子句中表的超集。

select productname, od.productid, avg(od.unitprice*(1.00-discount))
as avgprice, sum(od.quantity) as units
from dbo.[order details] as od, dbo.products as p, dbo.orders as o
where od.productid = p.productid and o.orderid = od.orderid
and od.unitprice > 10
group by productname, od.productid

最后两个查询是查询 8 的变体。每个变体都违背了一个优化器条件，因此与查询 8 不同，不能使用视图 4。

查询 8a

由于视图定义中的 unitprice > 10 与查询中的 unitprice > 25 之间的 where 子句不匹配，所以 q8a 不能使用索引视图。查询搜索条件谓词必须是视图定义中搜索条件谓词的超集。

select productname, od.productid, avg(od.unitprice*(1.00-discount)) avgprice,
sum(od.quantity) as units
from dbo.[order details] as od, dbo.products as p, dbo.orders as o
where od.productid = p.productid and o.orderid = od.orderid
and od.unitprice > 25
group by productname, od.productid

查询 8b

注意，表 orders 没有参与索引视图 v4 的定义。尽管如此，在该表中添加谓词将禁止使用索引视图，原因是添加的谓词可能会消除聚合中的其它行（如查询 8b 中所示）。

select productname, od.productid, avg(od.unitprice*(1.00-discount))
as avgprice, sum(od.quantity) as units
from dbo.[order details] as od, dbo.products as p, dbo.orders as o
where od.productid = p.productid and o.orderid = od.orderid
and od.unitprice > 10
and o.orderdate > '01/01/1998'
group by productname, od.productid