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

领测软件测试网

　　 
　　视图限制
　　
　　如要在 SQL Server 2005 中的视图上创建一个索引，相应的视图定义必须包含：
　　
　　ANY、NOT ANY　　　　　　　　　　　　　　　　　　　　　　　　OPENROWSET、OPENQUERY、OPENDATASOURCE
　　

　　不精确的（浮型、实型）值上的算术　　　　　　　　　　　　　　OPENXML
　　
　　COMPUTE、COMPUTE BY　　　　　　　　　　　　　　　　　　　　 ORDER BY
　　
　　CONVERT 生成一个不精确的结果　　　　　　　　　　　　　　　　OUTER 联接
　　
　　COUNT(*)　　　　　　　　　　　　　　　　　　　　　　　　　　引用带有一个已禁用的聚集索引的基表
　　
　　GROUP BY ALL　　　　　　　　　　　　　　　　　　　　　　　　引用不同数据库中的表或函数
　　
　　派生的表（FROM 列表中的子查询）　　　　　　　　　　　　　　 引用另一个视图
　　
　　DISTINCT　　　　　　　　　　　　　　　　　　　　　　　　　　ROWSET 函数
　　
　　EXISTS、NOT EXISTS　　　　　　　　　　　　　　　　　　　　　自联接
　　
　　聚合结果（比如：SUM(x)+SUM(x)）上的表达式　　　　　　　　　 STDEV、STDEVP、VAR、VARP、AVG
　　
　　全文谓词 (CONTAINS、FREETEXT、CONTAINSTABLE、FREETEXTTABLE) 子查询
　　
　　不精确的常量（比如：2.34e5）　　　　　　　　　　　　　　　　可为空的表达式上的 SUM
　　
　　内嵌或表值函数　　　　　　　　　　　　　　　　　　　　　　　表提示（比如：NOLOCK）
　　
　　MIN、MAX　　　　　　　　　　　　　　　　　　　　　　　　　　text、ntext、image、filestream 或 XML 列
　　
　　不具有确定性的表达式　　　　　　　　　　　　　　　　　　　　TOP
　　
　　非 unicode 排序　　　　　　　　　　　　　　　　　　　　　　 UNION
　　
　　SQL Server 2005 可检测到的矛盾情况表示视图将为空（比如，当 0=1 及 ...）
　　
　　注意 索引视图可能包含浮型和实型列；但是，如果这类列为非永久性的计算列，则不能包含在聚集索引键中。
　　
　　GROUP BY 限制
　　
　　如果存在 GROUP BY，VIEW 定义为：
　　
　　一定包含 COUNT_BIG(*)。
　　
　　一定不包含 HAVING、CUBE、ROLLUP 或 GROUPING()。
　　
　　这些限制仅适用于索引视图定义。即便不能满足上述 GROUP BY 限制，查询也可以在其执行计划中使用索引视图。
　　
　　有关索引的要求
　　
　　执行 CREATE INDEX 语句的用户必须是视图所有者。
　　
　　如果视图定义包含 GROUP BY 子句，唯一的聚集索引的键只能引用 GROUP BY 子句所指定的列。
　　
　　一定不能在启用 IGNORE_DUP_KEY 选项的情况下创建索引。
　　
　　六、示例
　　
　　本节中的例子阐述了如何结合两类主要的查询使用索引视图：聚合和联接。同时，说明了查询优化器在确定某个索引视图是否适用时所用的条件。有关完整的条件列表的信息，参阅“查询优化器如何使用索引视图”。
　　
　　这些查询基于 AdventureWorks 中的表。AdventureWorks 是 SQL Server 2005 所提供的示例数据库，并可作为写入方式来执行。在创建视图前后，用户可能想用 SQL Server Management Studio 中显示预计的执行计划工具，来查看查询优化器所选择的计划。虽然这些例子说明了优化器选择低成本执行计划的方式，但是 AdventureWorks 示例由于太小而无法显示出性能方面的提升。
　　
　　在开始运用这些示例之前，确保通过运行下列命令对会话设置正确的选项：
　　
　　设置
　　SET ANSI_NULLS ON
　　SET ANSI_PADDING ON
　　SET ANSI_WARNINGS ON
　　SET CONCAT_NULL_YIELDS_NULL ON
　　SET NUMERIC_ROUNDABORT OFF
　　SET QUOTED_IDENTIFIER ON
　　SET ARITHABORT ON
　　
　　下列查询显示了两种方法用于从 Sales.SalesOrderDetail 表返回具有最大总折扣的五个产品。
　　
　　查询 1
　　SELECT TOP 5 ProductID, Sum(UnitPrice*OrderQty) -
　　Sum(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rebate
　　FROM Sales.SalesOrderDetail
　　GROUP BY ProductID
　　ORDER BY Rebate DESC
　　
　　查询 2
　　SELECT TOP 5 ProductID,
　　SUM(UnitPrice*OrderQty*UnitPriceDiscount) AS Rebate
　　FROM Sales.SalesOrderDetail
　　GROUP BY ProductID
　　ORDER BY Rebate DESC
　　
　　查询优化器所选的执行计划包含：
　　
　　一个聚集索引扫描，位于估计行数为 121,317 的 Sales.SalesOrderDetail 表上。
　　
　　一个哈希匹配/聚合操作符，用于将所选的行放入基于 GROUP BY 列的哈希表，并计算每行的 SUM 聚合。
　　
　　一个 TOP 5 分类操作符，基于 ORDER BY 子句。
　　
　　视图 1
　　
　　添加包含 Rebate 列所需聚合的索引视图将更改“查询 1”的查询执行计划。在大型表（含数百万行）上，查询的性能也会得到大幅提升。
　　
　　CREATE VIEW Vdiscount1 WITH SCHEMABINDING AS
　　SELECT SUM(UnitPrice*OrderQty) AS SumPrice,
　　SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS SumDiscountPrice,
　　COUNT_BIG(*) AS Count, ProductID
　　FROM Sales.SalesOrderDetail
　　GROUP BY ProductID
　　GO
　　CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)
　　
　　第一个查询的执行计划显示 Vdiscount1 视图被优化器所用。然而，该视图将不被第二个查询所用，因为其不包含 SUM(UnitPrice*OrderQty*UnitPriceDiscount) 聚合。可再创建一个索引视图，来同时应付这两个查询。
　　
　　视图 2
　　CREATE VIEW Vdiscount2 WITH SCHEMABINDING AS
　　SELECT SUM(UnitPrice*OrderQty)AS SumPrice,
　　SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount))AS SumDiscountPrice,
　　SUM(UnitPrice*OrderQty*UnitPriceDiscount)AS SumDiscountPrice2,
　　COUNT_BIG(*) AS Count, ProductID
　　FROM Sales.SalesOrderDetail
　　GROUP BY ProductID
　　GO
　　CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount2 (ProductID)
　　
　　使用这个索引视图，在丢弃 Vdiscount1 后，这两个查询的查询执行计划现在包含：
　　
　　一个聚集索引扫描，位于估计行数为 266 的 Vdiscount2 视图上
　　
　　一个 TOP 5 分类函数，基于 ORDER BY 子句
　　
　　查询优化器选择了该视图，因为虽然没有在查询中引用该视图，但其提供了最低的执行成本。
　　
　　查询 3
　　
　　“查询 3”与上述查询类似，但 ProductID 被列 SalesOrderID （未包含在视图定义中）所替换。这违反了条件：视图定义中表上的选择列表中的所有表达式必须派生自视图选择列表，以便使用查询计划中的索引视图。
　　
　　SELECT TOP 3 SalesOrderID,
　　SUM(UnitPrice*OrderQty*UnitPriceDiscount) OrderRebate
　　FROM Sales.SalesOrderDetail
　　GROUP BY SalesOrderID
　　ORDER BY OrderRebate DESC
　　必须用一个单独的索引视图来应付该查询。可修改 Vdiscount2 以包含 SalesOrderID；但是，结果视图将和原始表包含同样多的行，并不会通过使用基表提高性能。
　　
　　查询 4
　　
　　该查询可生成每个产品的平均价格。
　　
　　SELECT p.Name, od.ProductID,
　　AVG(od.UnitPrice*(1.00-od.UnitPriceDiscount)) AS AvgPrice,
　　SUM(od.OrderQty) AS Units
　　FROM Sales.SalesOrderDetail AS od, Production.Product AS p
　　WHERE od.ProductID=p.ProductID
　　GROUP BY p.Name, od.ProductID
　　
　　复杂的聚合（比如：STDEV、VARIANCE、AVG）不能包含在索引视图的定义中。然而，通过包含（经组合）执行复杂聚合的一些简单的聚合函数，索引视图可用以执行含 AVG 的查询。
　　
　　视图 3
　　
　　该索引视图包含执行 AVG 函数所需的简单聚合函数。在创建“视图 3”后执行“查询 4”时，执行计划将显示所用的视图。优化器可从视图的简单聚合列 Price 和 Count 派生 AVG 表达式。
　　
　　CREATE VIEW View3 WITH SCHEMABINDING AS
　　SELECT ProductID, SUM(UnitPrice*(1.00-UnitPriceDiscount)) AS Price,
　　COUNT_BIG(*) AS Count, SUM(OrderQty) AS Units
　　FROM Sales.SalesOrderDetail
　　GROUP BY ProductID
　　GO
　　CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)
　　
　　查询 5
　　
　　该查询与“查询 4”相同，但包含一个附加的搜索条件。即使附加的搜索条件只从未包含在视图定义中的表引用列，“视图 3”也将作用于该查询。
　　
　　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.Name like '%Red%'
　　GROUP BY p.Name, od.ProductID
　　
　　查询 6
　　
　　查询优化器无法对该查询使用“视图 3”。添加的搜索条件 od.UnitPrice>10 包含来自视图定义中表的列，但该列不显示在 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.Name 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 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, 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
　　GO
　　CREATE UNIQUE CLUSTERED INDEX VdiscountInd on View4 (Name, ProductID)
　　
　　查询 8
　　
　　“视图 4”上相同的索引也将用于在其中添加对表 Sales.SalesOrderHeader 的联接的查询。该查询满足条件：查询 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.SalesOrderID
　　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*(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 不加入索引视图 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.ProductID 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
　　SELECT 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.UnitPrice
　　GO
　　CREATE UNIQUE CLUSTERED INDEX VdiscountInd
　　ON View4a (Name, ProductID, UnitPrice)
　　视图 5
　　
　　“视图 5”在其选择和 GROUP BY 列表中包含一个表达式。请注意，LineTotal 是一个计算列，因此本身是一个表达式。反过来，该表达式嵌套在对 FLOOR 函数的调用中。
　　
　　CREATE VIEW View5 WITH SCHEMABINDING AS
　　SELECT FLOOR(LineTotal) FloorTotal, COUNT_BIG(*) C
　　FROM Sales.SalesOrderDetail
　　GROUP BY FLOOR(LineTotal)
　　GO
　　CREATE UNIQUE CLUSTERED INDEX iView5 ON View5(FloorTotal)
　　查询 9
　　
　　“查询 9”在其选择和 GROUP BY 列表中包含表达式 FLOOR(LineTotal)。通过对 SQL Server 2005 中表达式的视图匹配的新扩展，该查询使用“视图 5”上的索引。
　　
　　SELECT TOP 5 FLOOR(LineTotal), Count(*)
　　FROM Sales.SalesOrderDetail
　　GROUP BY FLOOR(LineTotal)
　　ORDER BY COUNT(*) DESC
　　视图 6
　　
　　“视图 6”存储月末三天中有关线项目的信息。这样可将这些行聚集在少量页面上，从而可以迅速应对这些天里对 Sales.SalesOrderDetail 的查询。
　　
　　CREATE VIEW View6 WITH SCHEMABINDING AS
　　SELECT SalesOrderID, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
　　ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, rowguid,
　　ModifiedDate
　　FROM Sales.SalesOrderDetail
　　WHERE ModifiedDate IN ( convert(datetime, '2004-07-31', 120),
　　convert(datetime, '2004-07-30', 120),
　　convert(datetime, '2004-07-29', 120) )
　　GO
　　CREATE UNIQUE CLUSTERED INDEX VEndJulyO4Ind
　　ON View6(SalesOrderID, SalesOrderDetailID)
　　GO
　　
　　查询 10
　　
　　下面的查询可匹配“视图 6”，同时系统可生成一个计划，用于扫描视图上的 VendJuly04Ind 索引，但不扫描整个 Sales.SalesOrderDetail 表。此查询还说明了表达式等价（由于查询中日期的顺序不同于视图，而且数据格式也不同）和谓词归入（由于查询要求将结果的子集保存在视图中）。
　　
　　SELECT h.*, SalesOrderDetailID, CarrierTrackingNumber, OrderQty,
　　ProductID, SpecialOfferID, UnitPrice, UnitPriceDiscount, d.rowguid,
　　d.ModifiedDate
　　FROM Sales.SalesOrderHeader as h, Sales.SalesOrderDetail as d
　　WHERE (d.ModifiedDate = '20040729' OR d.ModifiedDate = '20040730')
　　and d.SalesOrderID=h.SalesOrderID
　　
　　视图 7
　　
　　开发人员有时还会发现使用索引视图强制专门的完整性约束很方便。例如，可通过索引视图强制约束：“除非列中存在多个 0 值，否则表 T 的列 a 就是唯一的”。下方索引视图“视图 7”就强制了这一约束。如果运行下面的脚本，其将成功运行直至最终的插入操作。该语句被禁止，因为其添加了一个非零重复值。
　　
　　USE tempdb
　　GO
　　CREATE TABLE T(a int)
　　GO
　　CREATE VIEW View7 WITH SCHEMABINDING
　　AS SELECT a
　　FROM dbo.T
　　WHERE a <> 0
　　GO
　　CREATE UNIQUE CLUSTERED INDEX IV on View7(a)
　　GO
　　-- legal:
　　INSERT INTO T VALUES(1)
　　INSERT INTO T VALUES(2)
　　INSERT INTO T VALUES(0)
　　INSERT INTO T VALUES(0) -- duplicate 0
　　
　　-- dissalowed:
　　INSERT INTO T VALUES(2)
　　
　　七、有关索引视图的常见问题
　　
　　问：为何对可创建索引的视图类型存在限制？
　　
　　答：为了确保在逻辑上可对视图进行增量维护，限制创建维护成本较高的视图，并限制 SQL Server 实施的复杂性。较大的视图集不具有确定性并与内容相关；其内容的“更改”独立于 DML 操作。无法对这些内容进行索引。在其定义中调用 GETDATE 或 SUSER_SNAME 的任何视图就属于这类视图。
　　
　　问：视图上的第一个索引为何必须为 CLUSTERED 和 UNIQUE？
　　
　　答：必须为 UNIQUE 以便在维护索引视图期间，轻松地按键值查找视图中的记录，并阻止创建带有重复项目的视图（要求维护特殊的逻辑）。必须为 CLUSTERED，因为只有聚集索引才能在强制唯一性的同时存储行。
　　
　　问：为何查询优化器不选取我的索引视图用于查询计划？
　　
　　答：优化器不选取索引视图主要有三种原因：
　　
　　(1) 使用 SQL Server Enterprise 或 Developer 版本之外的其他版本。只有 Enterprise 和 Developer 版本才支持自动的查询对索引视图匹配。按名称引用索引视图并包含 NOEXPAND 提示，让查询处理器使用所有其他版本中的索引视图。
　　
　　(2) 使用索引视图的成本可能超出从基表获取数据的成本，或者查询过于简单，使得针对基表的查询的速度既快又容易查找。当在较小的表上定义索引视图时，经常会发生这种情况。如要强制查询处理器使用索引视图，那么可使用 NOEXPAND 提示。如果最初不通过显式的方式引用视图，这样做就可能要求重新编写查询。您可获得带有 NOEXPAND 的查询的实际成本，并将之与不引用该视图的查询计划的实际成本相比较。如果两者的成本相近，那么您就可以认定用不用索引视图都不重要。
　　
　　(3) 查询优化器不将查询与索引视图相匹配。重新检查视图和查询的定义，确保两者在结构上可相匹配。CASTS、converts 以及其他在逻辑上不会更改查询结果的表达式可能会阻止匹配。另外，表达式规范化和等价以及 SQL Server 执行的归入测试方面存在一些限制。可能无法显示某些等价表达式是相同的，或者逻辑上被其他表达式归入的表达式被真正归入，因此可能会错失匹配。
　　
　　问：我每周更新一次数据仓库。索引视图使查询速度大大提升，却降低了每周更新的速度？该怎么办呢？
　　
　　答：可以考虑在每周更新前丢弃索引视图，更新完后再重新创建。
　　
　　问：我的视图存在重复项目，而我确实想对其进行维护。该怎么办呢？
　　
　　答：可以考虑创建一个视图，按您所要的视图中的所有列和表达式进行分组，并添加一个 COUNT_BIG(*) 列，然后在组合的列上创建一个唯一的聚集索引。分组过程可确保唯一性。虽然不是完全相同的视图，但可以满足您的需要。
　　
　　问：我在一个视图上定义了另一个视图。SQL Server 不让我索引顶级视图。该怎么办呢？
　　
　　答：可以考虑手动将嵌套视图的定义扩展到顶级视图，然后对其进行索引（索引最低层的视图，或者不索引该视图）。
　　
　　问：为何一定要对索引视图定义 WITH SCHEMABINDING？
　　
　　答：为了
　　
　　使用 schemaname.objectname 明确识别视图所引用的所有对象，而不管是哪个用户访问该视图，同时
　　
　　不会以导致视图定义非法或强制 SQL Server 在该视图上重新创建索引的方式，更改视图定义中所引用的对象。
　　
　　问：为何不能在索引视图中使用 OUTER JOIN？
　　
　　答：当将数据插入基表时，行会在逻辑上从基于 OUTER JOIN 的索引视图上消失。这会使执行 OUTER JOIN 视图的增量更新变得相对复杂，而执行性能将比基于标准 (INNER) JOIN 的视图慢一些。

文章来源于领测软件测试网 https://www.ltesting.net/