用SQL Server 2005索引视图提高性能一

核心提示： 三、SQL Server 2005 的索引视图的新增功能 与 SQL Server 2000 相比，SQL Server 2005 包含了许多索引视图的改进功能，用SQL Server 2005索引视图提高性能一(2)，可索引的视图组已扩展至包含基于下列各项的视图： 标量聚合，包括 SUM 和不带 GROUP

　　
　　 三、SQL Server 2005 的索引视图的新增功能
　　
　　 与 SQL Server 2000 相比，SQL Server 2005 包含了许多索引视图的改进功能。可索引的视图组已扩展至包含基于下列各项的视图：
　　
　　 标量聚合，包括 SUM 和不带 GROUP BY 的 COUNT_BIG。
　　
　　 标量表达式和用户定义的功能 (UDFs)。例如，给定一个表 T(a int, b int, c int) 和一个标量 UDF dbo.MyUDF(@x int)，T 上定义的索引视图可包含一个计算列（比如：a+b 或 dbo.MyUDF(a)）。
　　
　　 不精确的永久性列。不精确的列是一种浮型或实型的列，或者是一种派生自浮型或实型列的计算列。在 SQL Server 2000 中，如果不属于索引键的一部分，不精确的列就可用于索引视图的选择列表。不精确的列不能用于视图定义中的其他地方（比如：WHERE 或 FROM 子句）。如果不精确的列永久保存在基表中，那么 SQL Server 2005 允许其加入键或视图定义。永久性列包含常规列和标记为 PERSISTED 的计算列。
　　
　　 不精确的非永久性列无法加入索引或索引视图的根本原因是：必须使数据库脱离原计算机，然后再附加到另一台计算机。完成转移之后，保存在索引或索引视图中的所有计算列值在新硬件上的派生方式必须与旧硬件完全相同，精确到每个位。否则，这些索引视图在新硬件上会遭到逻辑破坏。由于这种破坏，在新硬件上，针对索引视图的查询会根据计划是否使用了索引视图或基表来派生视图数据，返回不同的应答。此外，无法在新计算机上正常维护索引视图。可惜，不同计算机上的浮点硬件（即便采用相同制造商的相同处理器体系结构）在处理器的版本上并不总是完全相同。对于某些浮点值 a 和 b，固件升级可能导致新硬件上的 (a*b) 不同于旧硬件上的 (a*b)。例如，结果可能非常相近，但仍存在细微差别。在进行索引之前一直保留不精确的计算值可解决这种分离/附加的不一致性问题，因为在进行索引和索引视图的数据库更新和维护期间，在相同的计算机上评估了所有表达式。
　　
　　 通用语言运行时 (CLR) 类型。SQL Server 2005 的一个主要的新功能是支持基于 CLR 的用户定义的类型 (UDT) 和 UDF。假如列或表达式具有确定性或是永久且精确的，或者二者兼具，那么就可在 CLR UDT 列或从这些列派生而来的表达式上定义索引视图。不能在索引视图上使用 CLR 用户定义的聚合。
　　
　　 优化器匹配查询和索引视图（使之可在查询计划中使用）的功能经扩展包含：
　　
　　 新的表达式类型，位于查询或视图的 SELECT 列表或条件中，涉及：
　　
　　 标量表达式（比如 (a+b)/2）。
　　
　　 标量聚合。
　　
　　 标量 UDF。
　　
　　 间隔归入。优化器可检测索引视图定义中的间隔条件是否覆盖或“归入”查询中的间隔条件。例如，优化器可确定“a>10 and a<20”覆盖“a>12 and a<18”。
　　
　　 表达式等价。某些表达式虽然在语法上有所不同，但最终的结果却相同，那么可以将其视为等价。例如，“a=b and c<>10”与“10<>c and b=a”等价。
　　
　　 另外，如果数据库中存在大量索引视图，那么对比在其上定义视图的表的编译性能，SQL Server 2005 通常要比 SQL Server 2000 快很多。.
　　
　　 四、设计注意事项
　　
　　 对数据库系统确定一组适当的索引可能很复杂。如果在设计一般索引时需要考虑众多可能性，那么对架构添加索引视图会大幅提高设计和潜在结果的复杂性。例如，索引视图可用于：
　　
　　 查询中引用的表的任何子集。
　　
　　 该表子集的查询中的条件的任何子集。
　　
　　 组合的列。
　　
　　 聚合函数（比如：SUM）。
　　
　　 应同时设计表和索引视图上的索引，以便从每个构造中获得最佳结果。由于索引和索引视图对给定查询可能都很有用，因此分开设计会导致多余的建议，从而产生较高的存储和维护开销。优化数据库的物理设计时，必须权衡一组不同的查询和数据库系统必须支持的更新的性能要求。所以，对索引视图确定一项适当的物理设计是一种富有挑战性的任务，应尽可能使用数据库优化顾问 (Database Tuning Advisor)。
　　
　　 如果为建立一个特殊的查询，查询优化器考虑了许多索引视图，那么查询优化成本就会显著增加。查询优化器可能会考虑在查询中的表的任何子集上定义的所有索引视图。在拒绝视图之前，必须调查每个视图以便进行替换。这可能要花一些时间，尤其当给定查询存在数百个这类视图时。
　　
　　 在其上创建一个唯一的聚集索引之前，视图必须满足几项要求。在设计阶段，考虑这些要求：
　　
　　 视图以及视图中引用的所有表必须在相同的数据库中，并具有相同的所有者。
　　
　　 索引视图不必包含查询中引用的供优化器使用的所有表。
　　
　　 在视图上创建任何其他的索引之前，必须先创建一个唯一的聚集索引。
　　
　　 在创建基表、视图和索引时，以及基表和视图中的数据被修改时，必须正确设置某些 SET 选项（在本文档后面所有详述）。此外，除非这些 SET 选项正确无误，否则查询优化器不会考虑索引视图。
　　
　　 必须使用架构绑定创建视图，并且还必须通过 SCHEMABINDING 选项创建该视图中引用的任何用户定义的函数。
　　
　　 需要额外的磁盘空间来保存索引视图所定义的数据。
　　
　　 设计方针
　　
　　 设计索引视图时考虑这些指导方针：
　　
　　 设计可供几个查询或多项操作使用的索引视图。
　　
　　 例如，包含列的 SUM 和 COUNT_BIG 的索引视图可供包含函数 SUM、COUNT、COUNT_BIG 或 AVG 的查询使用。查询的速度会更快，因为只需对视图中少量的行进行检索，而不必检索基表中所有的行，而且执行 AVG 函数所需的一部分计算已经完成。
　　
　　 使索引键保持简洁。
　　
　　 通过在索引键中尽可能使用最少的列和字节，可对索引视图的列实现更高效的访问，因为索引视图的列更窄，键比较的速度较更宽的键快一些。另外，在索引视图上定义的每个非聚集索引中，聚集索引键都被用作行定位器。较大的索引键的成本随视图上非聚集索引的数量成比例增长。
　　
　　 考虑最终索引视图的大小。
　　
　　 对于纯聚合，如果索引视图的大小与原始表的大小不相上下，可能就不会实现巨大的性能改善。
　　
　　 设计多个较小的索引视图来局部加速过程。
　　
　　 可能无法总对整个查询设计一个索引视图。如要怎么做，考虑创建若干个索引视图，各执行部分查询。
　　
　　 考虑这几个例子：
　　
　　 经常执行的查询会在一个数据库中聚合数据，并在另一个数据库中聚合数据，然后再联接结果。因为索引视图无法从多个数据库引用表，所以用户不能设计一个视图来执行整个过程。但是，可以在每个数据库中创建一个索引视图来进行各个数据库的聚合操作。如果优化器可匹配索引视图和现有的查询，那么至少聚合处理的速度会更快，同时不必对现有的查询进行重新编码。虽然联接处理不会加快，但整个查询将变快，因为其使用存储在索引视图中的聚合。
　　
　　 经常执行的查询聚合来自几个表的数据，然后使用 UNION 合并结果。索引视图不支持 UNION。可设计若干个视图来执行每个聚合操作。而后，优化器可选择索引视图来加快查询，而不必对查询进行重新编码。虽然未改进 UNION 处理，但改善了各个聚合过程。
　　
　　 有能帮助选择索引视图的工具吗？
　　
　　 数据库优化顾问 (DTA3) 是 SQL Server 2005 的一项功能，可帮助管理员优化物理数据库设计。除了建议使用基表上的索引以及表和索引分区策略外，DTA 还推荐使用索引视图。使用 DTA 可加强管理员确定索引、索引视图和分区策略（可优化对数据库执行的查询的典型组合的性能）的组合的能力。DTA 会向用户推荐广泛的索引视图。其中包括运用 SQL Server 2005 的索引视图的新功能（在“SQL Server 2005 的索引视图有哪些新增功能？”一节有所描述）的索引视图。DTA 并没有排除让数据库管理在设计物理存储结构时做出恰当判断的需要。但是，它可以简化物理数据库的设计过程。DTA 通过推荐一组假定的索引，索引视图和分区结果，与基于成本的查询优化器协同工作。DTA 使用优化器来估计当使用和不使用这些结构时的工作负荷成本，并推荐可提供较低的总成本的结构。
　　
　　 因为数据库优化顾问强制执行所有必须的 SET 选项（确保结果集正确无误），所以将成功完成索引视图的创建。然而，如果未能按要求设置选项，用户的应用程序可能无法运用这些视图。如果未按要求指定 SET 选项，对加入索引视图定义的表执行的插入、更新或删除操作就有可能失败。
　　
　　 更新数据时索引视图会有什么变化？
　　
　　 与其他任何索引一样，当基表数据变化时，SQL Server 会自动维护索引视图。对于一般索引，每个索引都直接与一个表相关联。随着在基础表上执行每一项 INSERT、UPDATE 或 DELETE 操作，索引将被相应地更新，从而使保存在索引中的值总是与表保持一致。
　　
　　 索引视图也得到相同的维护；但是，如果视图引用了若干个表，那么更新任何一个表都需要更新索引视图。不同于一般索引，在任何参与的表中插入一行都可能导致索引视图中发生多行更改。这是因为所插入的行可能与另一个表的多个行相联接。更新和删除行的情况也一样。因此，索引视图的维护成本可能比维护表上的索引更高。相反，维护具有高选择性条件的索引视图的成本可能要比维护表上的索引低得多，因为多数对视图所引用的基表的插入、删除和更新操作不会影响视图。不用访问其他数据库数据就可为索引视图筛选掉这些操作。
　　
　　 在 SQL Server 中，可更新某些视图。当某个视图可更新时，将使用 INSERT、UPDATE 和 DELETE 语句通过视图直接修改底层基表。在视图上创建索引不会阻止视图的更新。索引视图的更新确实会导致视图下基表的更新。这些更新会作为索引视图维护的一部分自动传播回索引视图。有关可更新的视图的详细信息，参阅面向 SQL Server 2005 的 SQL Server 联机丛书中的“通过视图修改数据”。
　　
　　 维护成本注意事项
　　
　　 设计索引视图时应考虑下面这几点：
　　
　　 索引视图的数据库需要附加存储。索引视图的结果集在物理上通过与典型表存储相似的方式保留在数据库中。
　　
　　 SQL Server 会自动维护视图；因此，对定义了视图的基表进行的任何更改都可能引发对索引视图进行一项或多项更改。所以，将产生额外的维护开销。
　　
　　 视图所获得的净性能提升为其所实现的总查询执行成本节约与存储和维护成本的差值。
　　
　　 比较容易获得接近于视图所需的存储。通过 SQL Server Management Studio 工具——显示预计的执行计划，评估视图定义所封装的 SELECT 语句。该工具将生成查询所返回的行数和行大小的近似值。通过将这两个值相乘，就可以获得接近于可能的视图大小；但是，只是近似。只有在视图定义中执行查询或在视图上创建索引，才能确定视图上索引的实际大小。
　　
　　 从 SQL Server 所执行的自动维护注意事项的角度来看，显示预计的执行计划功能可能会让用户在一定程度上了解这一开销的影响。如果通过 SQL Server Management Studio 评估修改视图的语句（视图上的 UPDATE、基表中的 INSERT），对该语句显示的执行计划将包括该语句的维护操作。如果就该操作在生产环境中所要执行的次数考虑该成本，那么可能会产生视图维护成本。
　　
　　 通常建议尽可能对视图或其底下的基表成批（而非单独）执行任何修改或更新操作。这样就会降低视图维护开销。