SQL Server 2000 索引视图提高性能2

你的名字 2022-06-16 19:58 274阅读 0赞

**创建索引视图**

创建索引视图所需的步骤与视图的成功实现密不可分。

1.  确保将在视图中引用的所有现有表的 SET 选项都正确。
2.  创建任何新表和视图之前，确保会话的 SET 选项已正确设置。
3.  确保视图定义是确定的。
4.  使用 WITH SCHEMABINDING 选项创建视图。
5.  创建视图的唯一群集索引。

**使用 SET 选项以获得一致的结果**

如果在执行查询时启用不同的 SET 选项，则在 SQL Server 中对同一个表达式求值会产生不同的结果。例如，将 SET 选项 CONCAT\_NULL\_YIELDS\_NULL 设置为 ON 之后，表达式 'abc' + NULL 返回的值是 NULL。而将 CONCAT\_NULL\_YIEDS\_NULL 设置为 OFF 之后，该表达式得出的结果却是 'abc'。索引视图要求多个 SET 选项的值都固定，以确保这些视图能够得到正确维护并返回一致的结果。

只要出现以下情况，就必须将下表中的 SET 选项设置为**要求的值**列中所示的值：

*  创建了索引视图。
 *  对索引视图中引用的任何表执行了任何 INSERT、UPDATE 或 DELETE 操作。
 *  查询优化器使用索引视图来生成查询计划。

<table> 
 <tbody> 
 <tr> 
 <td>SET 选项</td> 
 <td>要求 的值</td> 
 <td>默认 服务器 的值</td> 
 <td>OLE DB 和 ODBC 的值</td> 
 <td>DB LIB 的值</td> 
 </tr> 
 <tr> 
 <td>ANSI_NULLS</td> 
 <td>ON</td> 
 <td>OFF</td> 
 <td>ON</td> 
 <td>OFF</td> 
 </tr> 
 <tr> 
 <td>ANSI_PADDING</td> 
 <td>ON</td> 
 <td>ON</td> 
 <td>ON</td> 
 <td>OFF</td> 
 </tr> 
 <tr> 
 <td>ANSI_WARNING</td> 
 <td>ON</td> 
 <td>OFF</td> 
 <td>ON</td> 
 <td>OFF</td> 
 </tr> 
 <tr> 
 <td>ARITHABORT</td> 
 <td>ON</td> 
 <td>OFF</td> 
 <td>OFF</td> 
 <td>OFF</td> 
 </tr> 
 <tr> 
 <td>CONCAT_NULL_YIELDS_NULL</td> 
 <td>ON</td> 
 <td>OFF</td> 
 <td>ON</td> 
 <td>OFF</td> 
 </tr> 
 <tr> 
 <td>NUMERIC_ROUNDABORT</td> 
 <td>OFF</td> 
 <td>OFF</td> 
 <td>OFF</td> 
 <td>OFF</td> 
 </tr> 
 <tr> 
 <td>QUOTED_IDENTIFIER</td> 
 <td>ON</td> 
 <td>OFF</td> 
 <td>ON</td> 
 <td>OFF</td> 
 </tr> 
 </tbody> 
</table>

如果使用的是 OLE DB 或 ODBC 服务器连接，唯一必须修改的值是 ARITHABORT 的设置。所有 DB LIB 值都必须使用 **sp\_configure** 在服务器级上正确设置或使用 SET 命令从应用程序正确设置。有关 SET 选项的详细信息，请参阅关于 SQL Server 2000 的“SQL Server 联机图书”中的“使用 SQL Server 中的选项（英文）”。

**使用确定性函数**

索引视图的定义必须是确定性的。如果选择列表中的所有表达式以及 WHERE 和 GROUP BY 子句都是确定性的，则视图就是确定性的。只要用特定的一组输入值对确定性表达式进行求值，一定会返回同一个结果。只有确定性函数可以加入确定性表达式。例如，**DATEADD** 是确定性函数，因为将任何给定的一组变量值赋予它的三个参数进行求值，返回的总是同一个结果。而 **GETDATE** 则不是确定性函数，因为始终用同一个变量调用它，而它每次执行后返回的值都不相同。有关详细信息，请参阅关于 SQL Server 2000 的“SQL Server 联机图书”中的“确定性和非确定性函数”。

即便某个表达式是确定性的，但如果其中包含浮动表达式，确切的结果就可能取决于处理器的体系结构或微代码的版本。要确保 SQL Server 2000 中数据的完整性，此类表达式只能加入索引视图的非关键列。不包含浮动表达式的确定性表达式被称为精确的表达式。只有精确的确定性表达式可以加入索引视图的关键列和 WHERE 或 GROUP BY 子句。

使用 **COLUMNPROPERTY** 函数和  IsDeterministic 属性来确定视图列是否是确定性的。使用 **COLUMNPROPERTY** 函数和 IsPrecise 属性来确定包含架构绑定的视图中的确定性列是否是精确的。如果为 TRUE，则 **COLUMNPROPERTY** 会返回 1，如果为 FALSE，则返回 0，如果是无效的输入（列不是确定性的），则返回 NULL。例如，`SELECT COLUMNPROPERTY(Object_Id('Vdiscount1'),'SumDiscountPrice','IsPrecise')` 返回的是 0，因为 **SumDiscountPrice** 列引用了表 **Order Details** 中的浮动列 **Discount**。而同一视图中的列 **SumPrice** 既是确定性的又是精确的。

**注意：   **该 SELECT 语句所基于的视图能够在示例部分找到（视图 1）。

**其它要求**

除“设计准则”、“使用 SET 选项以获得一致的结果”和“使用确定性函数”部分中列出的要求之外，还必须符合以下要求。

**基表要求**

*  基表在创建时必须正确设置 SET 选项，否则就不能被包含架构绑定的视图引用。
 *  表必须通过视图定义中的两部分名称（所有者.表名）引用。

**函数要求**

*  用户定义的函数必须使用 WITH SCHEMABINDING 选项创建。
 *  用户定义的函数必须通过两部分名称（所有者.函数）引用。

**视图要求**

*  视图必须使用 WITH SCHEMABINDING 选项创建。
 *  视图必须只引用同一数据库中的基表，而不能引用其它视图。

**语法限制**

对视图定义的语法有几个限制。视图定义不能包含以下内容：

*  COUNT(\*)
 *  ROWSET 函数
 *  派生表
 *  自联接
 *  DISTINCT
 *  STDEV、VARIANCE、AVG
 *  Float\* 列、文本列、ntext 列、图像列
 *  子查询
 *  全文谓词（CONTAIN、FREETEXT）
 *  可空表达式的 SUM
 *  MIN、MAX
 *  TOP
 *  OUTER 联接
 *  UNION

**注意：   **索引视图可以包含浮动列，不过，此类列不能包含在群集索引关键字中。

**GROUP BY 限制**

如果未使用 GROUP BY，表达式不能在选择列表中使用。

如果使用了 GROUP BY，则 VIEW 定义：

*  必须包含 COUNT\_BIG(\*)。
 *  不得包含 HAVING、CUBE 或 ROLLUP。

这些限制只适用于索引视图定义。查询可以在其执行计划中使用索引视图，即便该索引视图并不符合这些 GROUP BY 限制。

**索引要求**

*  执行 CREATE INDEX 语句的用户必须是视图所有者。
 *  如果视图定义中包含 GROUP BY 子句，唯一群集索引的关键字只能引用 GROUP BY 子句中指定的列。

**示例**

本部分的示例阐述索引视图在两种主要查询（聚合和联接）中的使用问题。同时还说明查询优化器在确定某个索引视图是否可用时使用的条件。有关这些条件的完整列表，请参阅[查询优化器如何使用索引视图][Link 1]。

查询基于 **Northwind**（SQL Server 2000 中提供的数据库样本）中的表，并可以写入的方式执行。创建视图的前后，最好使用 SQL 查询优化器中的“显示执行计划”工具来查看查询优化器选定的计划。尽管示例中阐述了优化器是如何选择成本最低的执行计划的，但因为 **Northwind** 数据库样本太小，因此无法体现性能的提高。

以下查询显示如何从 **Order Details** 表中返回具有最大总折扣的五种产品的两个方法。

**查询 1**

SELECT TOP 5 ProductID, SUM(UnitPrice*Quantity) - SUM(UnitPrice*Quantity*(1.00-Discount))AS Rebate FROM [Order Details] GROUP BY ProductID ORDER BY Rebate DESC

**查询 2**

SELECT TOP 5 ProductID, SUM(UnitPrice*Quantity*Discount)AS Rebate FROM [Order Details] GROUP BY ProductID ORDER BY Rebate DESC

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

*  对 **Order Details** 表的群集索引扫描，估计有 2,155 行。
 *  哈希匹配/聚合运算符，该运算符基于 GROUP BY 列将选定的行放入哈希表，然后计算每行的 SUM 聚合。
 *  基于 ORDER BY 子句的 TOP 5 排序运算符。

**视图 1**

添加包括 **Rebate** 列所需聚合的索引视图将更改查询 1 的查询执行计划。在数百万行的大表上，查询的性能也将明显提高。

CREATE VIEW Vdiscount1 WITH SCHEMABINDING AS SELECT SUM(UnitPrice*Quantity)AS SumPrice, SUM(UnitPrice*Quantity*(1.00-Discount)) AS SumDiscountPrice, COUNT_BIG(*) AS Count, ProductID FROM dbo.[Order Details] GROUP BY ProductID GO CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount1 (ProductID)

第一个查询的执行计划显示 Vdiscount1 视图由查询优化器使用。不过，由于该视图不包含 SUM(UnitPrice\*Quantity\*Discount) 聚合，因此不会被第二个查询使用。可以创建另一个可以同时满足上述两个查询的索引视图。

**视图 2**

CREATE VIEW Vdiscount2 WITH SCHEMABINDING AS SELECT SUM(UnitPrice*Quantity)AS SumPrice, SUM(UnitPrice*Quantity*(1.00-Discount))AS SumDiscountPrice, SUM(UnitPrice*Quantity*Discount)AS SumDiscountPrice2, COUNT_BIG(*) AS Count, ProductID FROM dbo.[Order Details] GROUP BY ProductID GO CREATE UNIQUE CLUSTERED INDEX VDiscountInd ON Vdiscount2 (ProductID)

有了该索引视图，现在两个查询的查询执行计划包含：

*  对 Vdiscount2 视图的群集索引扫描，估计有 77 行
 *  基于 ORDER BY 子句的 TOP 5 排序函数

查询优化器选择该视图是因为它提供了最低的执行成本，尽管在查询中并未引用该视图。

**查询 3**

查询 3 类似于前几个查询，只是 ProductID 已被 OrderID 所取代，视图定义中没有包括该列。这违背了以下条件：查询选择列表中的所有表达式都必须能从未包括在视图定义内的表的视图选择列表中派生。

SELECT TOP 3 OrderID, SUM(UnitPrice*Quantity*Discount) OrderRebate FROM dbo.[Order Details] GROUP BY OrderID ORDER BY OrderRebate desc

要求单独的索引视图来满足该查询。可以对 Vdiscount2 进行修改，使它包括 OrderID，但是所生成视图的行数将与原表的行数相同，因此，提供的性能也不会高于使用基表所提供的性能。

**查询 4**

该查询可生成每个产品的平均价格。

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS AvgPrice, SUM(od.Quantity) AS Units FROM [Order Details] od, Products p WHERE od.ProductID=p.ProductID GROUP BY ProductName, 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-Discount))AS Price, COUNT_BIG(*)AS Count, SUM(Quantity)AS Units FROM dbo.[Order Details] GROUP BY ProductID Go CREATE UNIQUE CLUSTERED INDEX iv3 ON View3 (ProductID)

**查询 5**

该查询与查询 4 相同，只不过包括一个附加搜索条件。即使该附加搜索条件只引用未包括在视图定义内的表中的列，视图 3 也将用于该查询。

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS AvgPrice, SUM(od.Quantity)AS Units FROM [Order Details] AS od, Products AS p WHERE od.ProductID=p.ProductID AND p.ProductName like '%Tofu%' GROUP BY ProductName, od.ProductID

**查询 6**

查询优化器不能将视图 3 用于该查询。附加搜索条件 od.UnitPrice>10 包含视图定义内的表中的列，而该列却不出现在 GROUP BY 列表中，搜索谓词也不出现在视图定义中。

**查询 7**

相反，查询优化器可以将视图 3 用于查询 7，原因是新搜索条件 od.ProductID in (1,2,13,41) 中定义的列包括在视图定义内的 GROUP BY 子句中。

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS AvgPrice, SUM(od.Quantity) AS Units FROM [Order Details] AS od, Products AS p WHERE od.ProductID = p.ProductID AND od.ProductID in (1,2,13,41) GROUP BY ProductName, od.ProductID

**视图 4**

该视图在视图定义中包括了列 od.Discount，可以满足查询 6 的条件。

CREATE VIEW View4 WITH SCHEMABINDING AS SELECT ProductName, od.ProductID, SUM(od.UnitPrice*(1.00-Discount)) AS AvgPrice, SUM(od.Quantity) AS Units, COUNT_BIG(*) AS Count FROM dbo.[Order Details] AS od, dbo.Products AS p WHERE od.ProductID = p.ProductID AND od.UnitPrice > 10 GROUP BY ProductName, od.ProductID GO CREATE UNIQUE CLUSTERED INDEX VdiscountInd on View4 (ProductName, ProductID)

**查询 8**

视图 4 的同一个索引还将用于一个添加了与表 Orders 的联接的查询。该查询符合以下条件：查询 FROM 子句中列出的表是索引视图的 FROM 子句中表的超集。

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AS AvgPrice, SUM(od.Quantity) AS Units FROM dbo.[Order Details] AS od, dbo.Products AS p, dbo.Orders AS o WHERE od.ProductID = p.ProductID and o.OrderID = od.OrderID AND od.UnitPrice > 10 GROUP BY ProductName, od.ProductID

最后两个查询是查询 8 的变体。每个变体都违背了一个优化器条件，因此与查询 8 不同，不能使用视图 4。

**查询 8a**

由于视图定义中的 UnitPrice > 10 与查询中的 UnitPrice > 25 之间的 WHERE 子句不匹配，所以 Q8a 不能使用索引视图。查询搜索条件谓词必须是视图定义中搜索条件谓词的超集。

SELECT ProductName, od.ProductID, AVG(od.UnitPrice*(1.00-Discount)) AvgPrice, SUM(od.Quantity) AS Units FROM dbo.[Order Details] AS od, dbo.Products AS p, dbo.Orders AS o WHERE od.ProductID = p.ProductID and o.OrderID = od.OrderID AND od.UnitPrice > 25 GROUP BY ProductName, od.ProductID

**查询 8b**

注意，表 **Orders** 没有参与索引视图 V4 的定义。尽管如此，在该表中添加谓词将禁止使用索引视图，原因是添加的谓词可能会消除聚合中的其它行（如查询 8b 中所示）。

**有关详细信息**

Microsoft SQL Server 2000 联机图书包含索引视图的详细信息。有关其它信息，请参阅以下资源：

*  [Microsoft SQL Server Web 站点][Microsoft SQL Server Web]（中文）。
 *  [Microsoft SQL Server 开发人员中心（英文）][Microsoft SQL Server]。
 *  [SQL Server 杂志][SQL Server]（英文）。
 *  Microsoft.public.sqlserver.server 和 microsoft.public.sqlserver.datawarehouse 新闻组，其站点是：[http://communities.microsoft.com/newsgroups/default.asp?icp=chinacommunity&slcid=cn&MSCOMTB=ICP\_中文新闻组][http_communities.microsoft.com_newsgroups_default.asp_icp_chinacommunity_slcid_cn_MSCOMTB_ICP]（中文）。
 *  关于 SQL Server 的 Microsoft 正式课程。有关最新的课程信息，请参阅 [ Microsoft 培训和服务站点（英文）][Microsoft]。

[Link 1]: http://www.microsoft.com/China/msdn/Archives/msdnonline/features/articles/Indexedviews.ASP#indexedviews_topic4
[Microsoft SQL Server Web]: http://www.microsoft.com/china/sql
[Microsoft SQL Server]: http://msdn.microsoft.com/sqlserver
[SQL Server]: http://www.sqlmag.com/
[http_communities.microsoft.com_newsgroups_default.asp_icp_chinacommunity_slcid_cn_MSCOMTB_ICP]: http://communities.microsoft.com/newsgroups/default.asp?icp=chinacommunity&slcid=cn&MSCOMTB=ICP_%C3%96%C3%90%C3%8E%C3%84%C3%90%C3%82%C3%8E%C3%85%C3%97%C3%A9
[Microsoft]: http://www.microsoft.com/trainingandservices