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

核心提示： 查询 8 “视图 4”上相同的索引也将用于在其中添加对表 Sales.SalesOrderHeader 的联接的查询，该查询满足条件：查询 FROM 子句中所列的表是索引视图的 FROM 子句中的表的超集，用SQL Server 2005索引视图提高性能二(2)， SELECT p.Na

　　
　　 查询 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 的视图慢一些。