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

核心提示：视图限制 如要在 SQL Server 2005 中的视图上创建一个索引，相应的视图定义必须包含： ANY、NOT ANYOPENROWSET、OPENQUERY、OPENDATASOURCE 不精确的（浮型、实型）值上的算术OPENXML COMPUTE、COMPUTE BY ORDER BY CONVER

视图限制
　　
　　 如要在 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)