通过 SQL Server 2005 索引视图提高性能(7)

本节中的例子阐述了如何结合两类主要的查询使用索引视图：聚合和联接。同时，说明了查询优化器在确定某个索引视图是否适用时所用的条件。有关完整的条件列表的信息，参阅“查询优化器如何使用索引视图”。

这些查询基于 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

查询优化器所选的执行计划包含：

视图 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 后，这两个查询的查询执行计划现在包含：

查询优化器选择了该视图，因为虽然没有在查询中引用该视图，但其提供了最低的执行成本。

查询 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