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

用SQLServer2000索引视图提高性能(下)使用“索引微调向导” “索引微调向导”除建议使用基表的索引之外，还建议使用索引视图使用该向导可提高管理员确定索引和索引视图相结合的能力，从而优化针对数据库执行的典型混合查询的性能由于“索引微调向导”强制使用所有必需的 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 表中返回具有最大总折扣的五种产品的两个方法查询 1SELECT TOP 5 ProductID, SUM(UnitPrice*Quantity) - SUM(UnitPrice*Quantity*(1.00-Discount))AS RebateFROM [Order Details]GROUP BY ProductIDORDER BY Rebate DESC查询 2SELECT TOP 5 ProductID, SUM(UnitPrice*Quantity*Discount)AS RebateFROM [Order Details]GROUP BY ProductIDORDER BY Rebate DESC查询优化器选定的执行计划包含：对 Order Details 表的群集索引扫描，估计有 2,155 行。

哈希匹配/聚合运算符，该运算符基于 GROUP BY 列将选定的行放入哈希表，然后计算每行的 SUM 聚合 基于 ORDER BY 子句的 TOP 5 排序运算符 视图 1添加包括 Rebate 列所需聚合的索引视图将更改查询 1 的查询执行计划在数百万行的大表上，查询的性能也将明显提高CREATE VIEW Vdiscount1 WITH SCHEMABINDING ASSELECT 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   GOCREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)第一个查询的执行计划显示 Vdiscount1 视图由查询优化器使用不过，由于该视图不包含 SUM(UnitPrice*Quantity*Discount) 聚合，因此不会。