用SQLServer2000索引视图提高性能(下)

1、用sqlserver2000索引视图提高性能（下）使用“索引微调向导"“索引微调向导''除建议使用基表的索引之外，还建议使用索引视 图。使用该向导可提高管理员确定索引和索引视图相结合的能力，从而优化针对数据库执行 的典型混合查询的性能。由于“索引微调向导"强制使用所有必需的set选项（以确保结果 集的正确性），其索引视图将会成功创建。不过，如果您的应用程序的选项没有按照耍求设 置，可能无法利用这些视图。对那些参与索引视图定义的表执行的插入、更新或删除操作可 能会失败。维护索引视图sql server自动维护索引视图，这与维护任何其它索引的情况类 似。对于普通

2、索引而言，每个索引都直接连接到单个表。通过对基础表执行每个insert、 update或delete操作，索引相应地进行了更新，以便使存储在该索引中的值始终与 表一致。索引视图的维护与此类似。不过，如果视图引用了多个表，则对这些表中的任何一 个进行更新都需要更新索引视图。与普通索引不同的是，对任何一个参与的表执行一次行插入操作都可能导致在索引视图 中进行多次行插入操作。更新和删除操作的情况也是如此。因此，较之于维护表的索引，维 护索引视图的代价更为高昂。在sql server 2000中，某些视图可以更新。如果某个视图可 以更新，则使用insert. update和delete语句可通过该视图

3、直接修改根本基表。为 某个视图创建索引并不会妨碍该视图的更新。有关可更新视图的详细信息，请参阅关于sql server 2000的4<sql server联机图书”中的“通过视图修改数据（英文）”。null。维护成本的考虑因素设计索引视图时应该考虑以下几点：数据库屮需要有一个额外的存 储空间用于索引视图。索引视图的结果集以类似于典型表存储空间的方式物理保存在数据库 中。sql server自动维护视图。因此，对定义视图所据的基表的任何更改都可能引起视图 索引的一处或多处更改，从而导致维护开销的增加。一个视图获得的净性能提高就是视图 提供的查询执行节约总计与存储和维护该视图耗费的成本之间的

4、差。估计视图将占用的所需 存储空问要相对简单一些。用sql查询分析器的“显示估计的执行计划”工具求视图定义屮 select语句的值。该工具将得出查询返冋的行数和行大小的近似值。将这两个值相乘， 即可估计出视图的可能大小。不过这只是一个近似值。视图索引的实际大小只能通过创建视 图索引来精确得岀。从sql server执行的自动维护考虑因素的观点出发，“显示估计的执 行计划”的功能可能会对此开销的影响有所了解。如果用sql查询分析器评估修改视图的 语句（针对视图的update语句、针对基表的insert语句），showplan将包括该语 句的维护操作。同时考虑此成本和此操作将在生产坏境中发生的次数

5、，可以指示视图维护的 可能成本。通常建议对视图或基表进行的任何修改和更新都应该尽可能地成批执行，而不要 单独进行。这样可以减少视图维护的某些开销。创建索引视图创建索引视图所需的步骤与 视图的成功实现密不可分。确保将在视图中引用的所有现有表的set选项都正确。创建 任何新表和视图之前，确保会话的set选项已正确设置。确保视图定义是确定的。使用 with schemabinding选项创建视图。创建视图的唯一群集索引。使用set选项以 获得一致的结果如果在执行查询时启用不同的set选项，则在sql server中对同一个表 达式求值会产生不同的结果。例如，将set选项concat_null_yie

6、lds_null设置 为 on 之后，表达式 abc' + null 返回的值是 null。而将 concat_null_yieds_null设置为off z后，该表达式得出的结果却是abc爲索引 视图要求多个set选项的值都固定，以确保这些视图能够得到正确维护并返冋一致的结 果。只要出现以下情况，就必须将下表中的set选项设置为耍求的值列中所示的值：创建 了索引视图。对索引视图中引用的任何表执行了任何insert、update或delete操 作。查询优化器使用索引视图来生成查询计划。set选项 要求的值 默认服务器的值ole db 和 odbc 的值 db lib 的值 ansi_

7、nulls on off on off ansi_padding on on on off ansi warning on off on off arithabort on off off offconcat_null_yields_null on off on off numeric_roundabort off off off off quoteddentifier on off on off 如果使用的是 ole db 或 odbc 服 务器连接，唯一必须修改的值是arithabort的设置。所有db lib值都必须使用 sp_configure在服务器级上正确设置或使用set命令从应用

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

9、返回的值都不相同。有关详细信息，请参阅关于sql server 2000 的“sqlserver联机图书呻的“确定性和非确定性函数二即便某个表达式是确定性的，但如果其中包含浮动表达式，确切的结果就可能収决于处 理器的体系结构或微代码的版本。要确保sql server 2000中数据的完整性，此类表达式只 能加入索引视图的非关键列。不包含浮动表达式的确定性表达式被称为精确的表达式。只有 精确的确定性表达式可以加入索引视图的关键列和where或group by子句。使用 columnproperty函数和isdeterministic属性来确定视图列是否是确定性的。使用 columnpropert

10、y函数和isprecise属性來确定包含架构绑定的视图中的确定性列是否 是精确的。如果为true,则columnproperty会返回1,如果为false,则返回0, 如果是无效的输入（列不是确定性的），则返回 null o例如,select columnproperty（object_id（'vdiscountr）；sumdiscountprice7isprecise,）返冋的是 0,因 为sumdiscountprice列引用了表order details中的浮动列discount0而同一视图中的列 sumprice既是确定性的又是精确的。注意：该select语句所基于的视图能够在

11、示例部 分找到（视图1）。其它要求除“设计准则"、“使用set选项以获得一致的结果”和“使用 确定性函数”部分中列出的要求z外，还必须符合以下要求。基表要求基表在创建时必须正 确设置set选项，否则就不能被包含架构绑定的视图引用。表必须通过视图定义中的两 部分名称（所有者.表名）引用。函数要求 用户定义的函数必须使用 with schemabinding选项创建。用户定义的函数必须通过两部分名称（所有者.函数）引用。 视图要求 视图必须使用with schemabinding选项创建。视图必须只引用同一数据 库中的基表，而不能引用其它视图。语法限制对视图定义的语法有儿个限制。视图定义

12、不 能包含以下内容：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。

13、这些限制只 适用于索引视图定义。查询可以在其执行计划中使用索引视图，即便该索引视图并不符合这 些group by限制。索引要求执行create index语句的用户必须是视图所有者。如 果视图定义中包含group by子句，唯一群集索引的关键字只能引用group by子句中 指定的列。示例 本部分的示例阐述索引视图在两种主要查询（聚合和联接）中的使用问题。 同时还说明查询优化器在确定某个索引视图是否可用时使用的条件。有关这些条件的完整列 表，请参阅查询优化器如何使用索引视图。查询基于northwind （sql server 2000中提供的数据库样本）中的表，并可以写入的方式执行。创建视图的

14、前后，最好使用sql查询优化器中的“显示执行计划"工具来查看查 询优化器选定的计划。尽管示例中阐述了优化器是如何选择成本最低的执行计划的，但因为 northwind数据库样本太小，因此无法体现性能的提高。以下查询显示如何从onler details 表中返回具有最大总折扣的五种产品的两个方法。查询1 select top 5 productid, sum(unitprice*quantity) sum(unitprice*quantity*( 1.00-discount)as rebatefrom order detailsgroup by productidorder by reb

15、ate desc 查询 2select top 5 productid, sum(unitprice*quantity*discount)as rebatefrom order detailsgroup by productidorder by rebate desc查询优化器选定的执行计划包含：对order details表的 群集索引扫描，估计有2,155行。哈希匹配/聚合运算符，该运算符基于group by列将 选定的行放入哈希表，然后计算每行的sum聚合。基于order by子句的top 5排 序运算符。视图1添加包括rebate列所需聚合的索引视图将更改查询1的查询执行计 划。在数百

16、万行的大表上，查询的性能也将明显提高。create view vdiscountl with schemabindingasselectsum(unitprice*quantity)assumprice,sumcunitpricequantityc 1.00-discount)as sumdiscountprice, count_big(*) as count, productid from dbo.order detailsgroup by productid gocreate unique clustered index vdiscountlnd on vdiscountl (produc

17、tid)第一个查询的执行计划显示 vdiscountl视图由查询优化器使用。不过，由于该视图不包含sum(unitprice*quantity*discount)聚合，因此不会被笫二个 查询使用。可以创建另一个可以同时满足上述两个查询的索引视图。视图2create view vdiscount2 with schemabindingas select sum(unitprice*quantity)as sumprice, sum(unitprice*quantity*( 1.00-discount)assumdiscountprice,sum(unitprice*quantitydiscoun

18、t)as sumdiscountprice2, count_big(*) as count, productidfrom dbo. order detailsgroup by productidgocreate unique clustered index vdiscountlnd on vdiscount2 (productid)有了该索引视图，现在两个查 询的查询执行计划包含：对vdiscouno视图的群集索引扫描，估计有77行 基于order by子句的top5排序函数查询优化器选择该视图是因为它提供了最低的执行成本，尽管 在查询中并未引用该视图。查询3查询3类似于前儿个查询，只是pro

19、ductid已被 orderld所取代，视图定义中没有包括该列。这违背了以下条件：查询选择列表中的所有表 达式都必须能从未包括在视图定义内的表的视图选择列表中派生。select top 3 orderld, sum(unitprice*quantity*discount) orderrebatefrom dbo.order detailsgroup by orderldorder by orderrebate desc要求单独的索引视图来满足该查询。可以对vdiscounl2 进行修改，使它包括oixlehd,但是所生成视图的行数将与原表的行数相同，因此，提供的 性能也不会高于使用基表所提供的

20、性能。查询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.produclid 索引视图的定义屮不能包 括复杂的聚合(例如，stdev、variance、avg),不过，如果索引视图中包括几个联合 起来执行复杂聚合的简单聚

21、合函数，即可用于执行包含avg的查询。视图3该索引视图 包含执行avg函数所需的简单聚合函数。在创建了视图3后执行查询4时，执行计划会 显示正被使用的视图。优化器可以从视图的简单聚合列price和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

22、create unique clustered index iv3 on view3 (productid)查询5该查询与查询4相同，只不过包括一个附加搜索条件。即 使该附加搜索条件只引用未包括在视图定义内的表中的列，视图3也将用于该查询。select productname, od.productid, av g(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 an

23、d 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

24、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, av g(od.unitprice*( 1.00-discount) as avgprice, sum(od.quantity) as unitsfrom order de

25、tails as od, products as pwhere od.productid = p.productidand od.productid in (1,2,13,41)group by productname, od.productid 视图 4 该视图在视图定义 中包括了列od.discount,可以满足查询6的条件。create view view4 with schemabindingasselectproductname,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 > 10group by productname, od.productidgocreate unique clustered index vdiscountlnd on view4 (produ