1、1通过 SQL Server 2005 索引视图提高性能发布日期: 4/18/2005撰稿人:Eric Hanson投稿人:Gail Erickson、Lubor Kollar、Jason Ward摘要:本文档介绍了 SQL Server 2005 Enterprise Edition 中经过改进的索引视图功能。文中对索引视图进行了说明介绍,并讨论了可通过该功能改善性能的一些具体情况。 目录什么是 索引视图? 3通过索引视图改善性能 .4在视图上 使用非聚集索引 5应用索引视图的优点 .5查询优化器如何使用索引视图 .7优化器考虑事项 .7SQL Server 2005 的索引视图有哪些新增功
2、能? .9设计注意事项 11设计方针 12有能帮助选择索引视图的工具吗? .14更新数据时索引视图会有什么变化? .14维护成本注意 事项 .15创建索引视图 16使用 SET 选项获得一致的结果 .17使用具有确定性的功能 .18其他要求 19有关基表的要求 19有关函数的要求 20有关视图的要求 20视图限制 20GROUP BY 限制 21有关索引的要求 22示例 .22有关索引视图的常见问题 34如需获得更多信息 363 什么是索引视图?多年以来,Microsoft SQL Server 一直支持创建称为视图的虚拟表。通常,这些视图的主要作用是: 提供一种安全机制,将用户限制到一个或多
3、个基表的某个数据子集中。 提供一种机制,允许开发人员自定义用户通过逻辑方式查看存储在基表中的数据的方式。通过 SQL Server 2000,SQL Server 视图的功能得到了扩展,实现了系统性能方面的收益。可在视图上创建唯一的聚集索引及非聚集索引,来提高最复杂的查询的数据访问性能。在 SQL Server 2000 和 2005 中,具有唯一的聚集索引的视图即为索引视图。本文所讨论的内容适用于 SQL Server 2005,其中有许多内容也适用于 SQL Server 2000。 从数据库管理系统 (DBMS) 的角度看来,视图是对数据(一种元数据类型)的一种描述。当创建了一个典型视图
4、时,通过封装一个 SELECT 语句(定义一个结果集来表示为虚拟表)来定义元数据。当在另一个查询的 FROM 子句中引用视图时,将从系统目录检索该元数据,并替代该视图的引用扩展元数据。视图扩展之后,SQL Server 查询优化器会为执行查询编译一个执行计划。查询优化器会搜索针对某个查询的一组可能的执行计划,并根据对执行每个查询计划所需的实际时间的估计,选择所能找到的成本最低的计划。对于非索引视图,解析查询所必需的视图部分会在运行时被具体化。任何计算(比如:联接或聚合)都在每个引用视图的查询执行时完成 1。在视图上创建了唯一的聚集索引后,该视图的结果集随即被具体化,并保存在数据库的物理存储中,
5、从而在执行时节省了执行这一高成本操作的开销。在查询执行中,可通过两种方式使用索引视图。查询可直接引用索引视图,或者更重要的是,如果查询优化器确定该视图可替换成本最低的查询计划中的部分或全部查询,那么就可以选定它。在第二种情况中,使用索引视图替代基础表及其一般索引。不必在查询中引用视图以使查询优化器在查询执行时使用该视图。这使得现有的应用程序可以从新创建的索引视图中受益,而不必进行更改。注意 索引视图是 SQL Server 2000 和 2005 各版本的一个功能。在 SQL Server 2000 和 2005 的 Developer 和 Enterprise 版本中,查询处理器可使用索引视
6、图来解析结构上与该视图相匹配的查询,即便不按名称来引用视图。在其他版本中,必须按名称来引用视图,并对视图引用使用 NOEXPAND 提示来查询索引视图的内容。通过索引视图改善性能运用索引提高查询性能不算是一个新概念;但是,索引视图提供了一些借助标准索引无法取得的性能收益。索引视图可通过以下方式提高查询性能: 可预先计算聚合并将其保存在索引中,从而在查询执行时,最小化高成本的计算。 可预先联接各个表并保存最终获得的数据集。 可保存联接或聚合的组合。该图说明了当查询优化器使用索引视图时,通常所能取得的性能改进。所列举的查询在复杂性上有所不同(比如:聚合计算的数量、所用表的数量或谓词的数量)并包含来
7、自真实的生产环境的具有数百万行的表。 5 在视图上使用非聚集索引其次,视图上的非聚集索引可提供更好的查询性能。与表上的非聚集索引类似,视图上的非聚集索引可提供更多选项,供查询优化器在编译过程中选择。例如,如果查询包含聚集索引所未涉及的列,那么优化器可在计划中选择一个或多个辅助索引,避免对索引视图或基表进行费时的完全扫描。对架构添加索引会增加数据库的开销,因为索引需要持续的维护。在索引数量和维护开销间寻求适当的平衡点时,应谨慎权衡。 返回页首应用索引视图的优点在实施索引视图前,分析数据库工作负荷。运用查询及各种相关工具(比如:SQL Profiler)方面的知识来确定可从索引视图获益的查询。频繁
8、发生聚合和联接的情况最适合使用索引视图。无论是否频繁发生,只要某个查询需要很长的响应时间,同时快速获得响应的开销很高,那么就适合使用索引视图。例如,一些开发人员发现为高级主管们在月末运行的报告,创建预先计算和存储查询的应答的索引视图很有用。不是所有的查询都能从索引视图中获益。与一般索引类似,如果未使用索引视图,就无法从中受益。在这种情况下,不仅无法实现性能改善,而且会在磁盘空间、维护和优化方面产生额外的成本。然而,当使用索引视图时,可大大改善(在数量级上)数据访问。这是因为查询优化器使用存储在索引视图(大幅降低了查询执行的成本)中预先计算的结果。查询优化器仅考虑对具有高成本的查询使用索引视图。
9、从而避免出现这样的情况:在查询优化成本高于使用索引视图所节约的成本时尝试匹配各种索引视图。在成本少于 1 的查询中很好使用索引视图。从实施索引视图中获益的应用程序包括: 决策支持工作负荷 数据集市 数据仓库 联机分析处理 (OLAP) 存储和源 数据挖掘工作负荷从查询类型和模式方面来看,获益的应用程序一般包含:相反,执行许多写入操作的联机事务处理 (OLTP) 系统或者频繁更新的数据库应用程序可能无法运用索引视图,因为同时更新视图和底层基表会带来更高的维护成本。 大型表的联接和聚合 查询的重复模式 几组相同或重叠的列上的重复聚合 相同键上相同表的重复联接 以上各项的组合Comment A1:
10、定 义 : 如 果 一 个 集 合S2中 的 每 一 个 元 素 都 在 集 合 S1中 ,且 集 合 S1中 可 能 包 含 S2中 没 有 的元 素 , 则 集 合 S1就 是 S2的 一 个 超集 。Comment A2: 例如查询中有聚合函数AVG而在视图中有 SUM和 COUNT聚合函数,那么查询中的 AVG可以由SUM和 COUNT函数派生AVG=SUM/COUNT7 查询优化器如何使用索引视图SQL Server 查询优化器自动决定何时对给定的查询执行使用索引视图。不必在查询中直接引用视图以供优化器在查询执行计划中使用。所以,现有的应用程序可运用索引视图,而不用更改应用程序本身;
11、只是必须创建索引视图。优化器考虑事项查询优化器通过考虑几个条件来决定索引视图能否涵盖整个或部分查询。这些条件对应查询中的一个 FROM 子句并由下列这几个部分组成: 查询 FROM 子句中的表必须是索引视图 FROM 子句中的表的超集。 查询中的联接条件必须是视图中的联接条件的超集。 查询中的聚合列必须可从视图中的聚合列的子集派生。 查询选择列表中的所有表达式必须可从视图选择列表或未包含在视图定义中的表派生。 如果与其他谓词所匹配的行的超集相匹配,那么该谓词将 归入 另一个谓词。例如,“T.a=10”归入“T.a=10 and T.b=20”。任何谓词都可归入其自身。视图中限制表值的那部分谓词
12、必须归入查询中限制相同表的那部分谓词。此外,必须以 SQL Server 可验证的方式实现这一点。 属于视图定义中的表的查询搜索条件谓词的所有列必须出现在下列视图定义的一项或多项中:1. 一个 GROUP BY 列表。2. 视图选择列表(如不存在 GROUP BY)。 3. 视图定义中相同或等价的谓词。情况 (1) 和 (2) 允许 SQL Server 对视图的列应用查询谓词,以便进一步限制视图的列。情况 (3) 比较特殊。在这种情况下,不需要对列进行筛选,因此该列不必出现在视图中。如果查询不止包含一个 FROM 子句(子查询、派生表、UNION),优化器可能选择几个索引视图来处理查询,并将
13、它们应用到不同 FROM 子句。2本文档的末尾提供了涉及这些情况的具体查询。推荐的最佳实务是让查询优化器决定在查询执行计划中使用哪些索引(如果有的话)。使用 NOEXPAND 视图提示当 SQL Server 处理按名称引用视图的查询时,视图的定义只有在仅引用基表时才会被正常扩展。这个过程称为 视图扩展 。其属于一种宏扩展形式。NOEXPAND 视图提示可强制查询优化器将视图视为带有聚集索引的普通表。其可防止视图扩展。只有在 FROM 子句中直接引用索引视图,才会应用 NOEXPAND 提示。例如,SELECT Column1, Column2, .FROM Table1, View1 WIT
14、H (NOEXPAND) WHERE .如要确保让 SQL Server 通过自己读取视图而不是从基表读取数据来处理查询,那么可使用 NOEXPAND。如果出于某种原因,SQL Server 选择了一个查询计划来对基表处理查询,而您想让其使用视图,那么可以考虑使用 NOEXPAND。必须在除 Developer 和 Enterprise 版本外的 SQL Server 的所有版本中使用 NOEXPAND 来让 SQL Server 直接对索引视图处理查询。可以看到 SQL Server 为计划的图形表达式选择了一个使用 SQL Server Management Studio 工具的显示预计的
15、执行计划功能的语句。或者,可以看到使用 SHOWPLAN_ALL、SHOWPLAN_TEXT 或 SHOWPLAN_XML 的不同的非图形表达式。参阅 SQL Sever 联机丛书中有关 SHOWPLAN 的不同版本的相关讨论。9 使用 EXPAND VIEWS 查询提示处理按名称引用视图的查询时,除非对视图引用添加 NOEXPAND 提示,否则 SQL Server 总会扩展视图。该提示会尝试匹配索引视图和扩展查询,除非在查询末尾的一个 OPTION 子句中指定 EXPAND VIEWS 查询提示。例如,假设数据库中有一个索引视图 View1。在下方的查询中,根据其逻辑定义(其 CREATE
16、 VIEW 语句)对 View1 进行了扩展,然后 EXPAND VIEWS 选项会阻止在计划中使用 View1 的索引视图来解析该查询。SELECT Column1, Column2, . FROM Table1, View1 WHERE .OPTION (EXPAND VIEWS)如要确保让 SQL Server 通过从查询所引用的基表直接访问数据来处理该查询,而不必访问索引视图,那么可使用 EXPAND VIEWS。在某些情况下,EXPAND 视图有助于消除因使用索引视图而导致的锁争用。在测试应用程序时,NOEXPAND 和 EXPAND VIEWS 都可帮助用户在使用和不使用索引视图的
17、情况下进行性能评估。 返回页首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))。 不精确的永久性列。不精确的列是一种浮型
18、或实型的列,或者是一种派生自浮型或实型列的计算列。在 SQL Server 2000 中,如果不属于索引键的一部分,不精确的列就可用于索引视图的选择列表。不精确的列不能用于视图定义中的其他地方(比如:WHERE 或 FROM 子句)。如果不精确的列永久保存在基表中,那么 SQL Server 2005 允许其加入键或视图定义。永久性列包含常规列和标记为 PERSISTED 的计算列。 不精确的非永久性列无法加入索引或索引视图的根本原因是:必须使数据库脱离原计算机,然后再附加到另一台计算机。完成转移之后,保存在索引或索引视图中的所有计算列值在新硬件上的派生方式必须与旧硬件完全相同,精确到每个位。
19、否则,这些索引视图在新硬件上会遭到逻辑破坏。由于这种破坏,在新硬件上,针对索引视图的查询会根据计划是否使用了索引视图或基表来派生视图数据,返回不同的应答。此外,无法在新计算机上正常维护索引视图。可惜,不同计算机上的浮点硬件(即便采用相同制造商的相同处理器体系结构)在处理器的版本上并不总是完全相同。对于某些浮点值 a 和 b,固件升级可能导致新硬件上的 (a*b) 不同于旧硬件上的 (a*b)。例如,结果可能非常相近,但仍存在细微差别。在进行索引之前一直保留不精确的计算值可解决这种分离/附加的不一致性问题,因为在进行索引和索引视图的数据库更新和维护期间,在相同的计算机上评估了所有表达式。 通用语
20、言运行时 (CLR) 类型。SQL Server 2005 的一个主要的新功能是支持基于 CLR 的用户定义的类型 (UDT) 和 UDF。假如列或表达式具有确定性或是永久且精确的,或者二者兼具,那么就可在 CLR UDT 列或从这些列派生而来的表达式上定义索引视图。不能在索引视图上使用 CLR 用户定义的聚合。 11 优化器匹配查询和索引视图(使之可在查询计划中使用)的功能经扩展包含: 新的表达式类型,位于查询或视图的 SELECT 列表或条件中,涉及: 标量表达式(比如 (a+b)/2)。 标量聚合。 标量 UDF。 间隔归入。优化器可检测索引视图定义中的间隔条件是否覆盖或“归入”查询中的
21、间隔条件。例如,优化器可确定“a10 and a12 and a10”与“1010 包含来自视图定义中表的列,但该列不显示在 GROUP BY 列表中,而搜索谓词也不显示在视图定义中。SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount) AS AvgPrice, SUM(od.OrderQty) AS Units FROM Sales.SalesOrderDetail AS od, Production.Product AS p WHERE od.ProductID=p.ProductID AND p.Na
22、me like %Red% GROUP BY p.Name, od.ProductID查询 7相反,查询优化器可对“查询 7”使用“视图 3”,因为新的搜索条件 od.ProductID in (1,2,13,41) 中定义的列包含在视图定义的 GROUP BY 子句中。SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount) AS AvgPrice, SUM(od.OrderQty) AS Units FROM Sales.SalesOrderDetail AS od, Production.Product
23、AS p WHERE od.ProductID = p.ProductID AND od.UnitPrice 10 GROUP BY p.Name, od.ProductID视图 4通过包含视图定义中的 SumPrice 和 Count 列以便计算查询中的 AVG,该视图将满足“查询 6”的条件。CREATE VIEW View4 WITH SCHEMABINDING AS SELECT p.Name, od.ProductID, SUM(od.UnitPrice*(1.00-UnitPriceDiscount) AS SumPrice, SUM(od.OrderQty) AS Units,
24、COUNT_BIG(*) AS Count FROM Sales.SalesOrderDetail AS od, Production.Product AS p WHERE od.ProductID = p.ProductID AND od.UnitPrice 10GROUP BY p.Name, od.ProductIDGOCREATE UNIQUE CLUSTERED INDEX VdiscountInd on View4 (Name, ProductID)查询 8“视图 4”上相同的索引也将用于在其中添加对表 Sales.SalesOrderHeader 的联接的查询。该查询满足条件:查
25、询 FROM 子句中所列的表是索引视图的 FROM 子句中的表的超集。SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount) AS AvgPrice, SUM(od.OrderQty) AS Units FROM Sales.SalesOrderDetail AS od, Production.Product AS p, Sales.SalesOrderHeader AS o WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrder
26、ID 29 AND od.UnitPrice 10 GROUP BY p.Name, od.ProductID最后两个查询在“查询 8”的基础上进行了修改。每个修改后的查询都违反了优化器的条件之一,并且不同于“查询 8”,无法使用“视图 4”。查询 8a“查询 8a”(Q8a) 无法使用索引视图,因为 WHERE 子句无法将视图定义中的 UnitPrice 10 与查询中的 UnitPrice 25 相匹配,而且 UnitPrice 未出现在视图中。查询搜索条件谓词必须是视图定义中的搜索条件谓词的超集。SELECT p.Name, od.ProductID, AVG(od.UnitPrice*
27、(1.00-UnitPriceDiscount) AvgPrice, SUM(od.OrderQty) AS Units FROM Sales.SalesOrderDetail AS od, Production.Product AS p, Sales.SalesOrderHeader AS o WHERE od.ProductID = p.ProductID AND o.SalesOrderID = od.SalesOrderID AND od.UnitPrice 25 GROUP BY p.Name, od.ProductID查询 8b注意,表 Sales.SalesOrderHeader
28、 不加入索引视图 V4 定义。尽管这样,在该表上添加一个谓词将不允许使用索引视图,因为所添加的谓词可能会更改或消除加入下方“查询 8b”所示的聚合的其他行。SELECT p.Name, od.ProductID, AVG(od.UnitPrice*(1.00-UnitPriceDiscount) AS AvgPrice, SUM(od.OrderQty) AS Units FROM Sales.SalesOrderDetail AS od, Production.Product AS p,Sales.SalesOrderHeader AS o WHERE od.ProductID = p.Pr
29、oductID AND o.SalesOrderID = od.SalesOrderID AND od.UnitPrice 10 AND o.OrderDate 20040728 GROUP BY p.Name, od.ProductID视图 4a“视图 4a”通过将 UnitPrice 列包含在选择列表和 GROUP BY 子句中,扩展了“视图 4”。“ 查询 8a”可使用“ 视图 4a”,因为将进一步筛选 UnitPrice 值(已知大于 10)以便只留下大于 25 的值。以下是间隔归入的一个例子。CREATE VIEW View4a WITH SCHEMABINDING AS SELEC
30、T p.Name, od.ProductID, od.UnitPrice,SUM(od.UnitPrice*(1.00-UnitPriceDiscount) AS SumPrice, SUM(od.OrderQty) AS Units, COUNT_BIG(*) AS Count FROM Sales.SalesOrderDetail AS od, Production.Product AS p WHERE od.ProductID = p.ProductID AND od.UnitPrice 10 GROUP BY p.Name, od.ProductID, od.UnitPriceGOCREATE UNIQUE CLUSTERED INDEX VdiscountInd ON View4a (Name, ProductID, UnitPrice)视图 5