博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

索引视图

Posted on 2007-02-08 15:10  .NET酷  阅读(587)  评论(0编辑  收藏  举报

这是微软的一段关于索引视图的介绍

索引视图

索引视图有助于提高 T-SQL 的性能

Itzik Ben-Gan

T-SQL 是一种允许您以逻辑方式设计请求的语言。这里所说的逻辑的意思是,当编写查询时,您指定的是希望获得什么 结果,而不是希望以何种方式 获得结果。对如何处理查询进行设计是查询优化器的工作。您所遇到的需要 T-SQL 解决方案的每个问题通常都有许多不同的解决方案,它们最终会返回同样的结果。在理想情况下,假设有两个执行同一任务的不同查询,优化器会为二者生成相同的执行计划- 最优化的计划。SQL Server 开发组似乎正在朝这个方向努力。利用 SQL Server 的每个发行版本,优化器会设计出更高级、更有效的计划,执行同一任务的不同查询使用这个相同计划的可能性也在提高。

不过,在许多情况下,您编写查询的方式仍然会动态地影响查询的性能。为了使优化器在不同的计划中作出选择并找到一个高效的计划,您需要完成一些准备工作- 也就是,创建索引。大多数人是将普通索引作为其数据库优化的一部分创建的,但我还没有看到索引视图的广泛实现。索引视图可以极大地提高查询性能,尤其在聚合数据的时候。在某些情况下,对视图进行创建并索引后,您甚至不需要更改引用基表的原始查询,这是因为优化器的智能程度足以使用那些索引。

下面我们将讨论使用索引视图来获得性能提高的两个问题。我假设您已经很熟悉索引视图的基本概念。(有关索引视图的详细信息,请参阅 Kalen Delaney "Introducing Indexed Views" 2000 5 月发表,InstantDoc ID 8410)一文。)

关于新客户的问题

第一个问题涉及一个典型的市场营销请求- 在每个期间(例如,月份),根据定单活动而定的新客户、现有客户和全部客户的数量分别是多少?某个月的新客户就是第一次下达定单的客户。现有客户是那些在以前的月份下达过定单的客户。您可以将 Northwind 数据库中的 Orders 表作为最初的基本数据。表 1(表 1)显示了预期输出的简化版本。创建解决方案并验证您的代码在逻辑上正确无误之后,可针对一个更大的 Orders 表测试其性能,您可以通过运行第 18 单 1 显示的代码,在 tempdb 中创建该表。

18 清单 2 显示了我针对此问题的第一个解决方案。定义派生表 M 的最内部查询从 Orders 返回了不同的月份和客户组合。月份表示为指向当月第一天的一个 datetime 值。定义派生表 N 的查询使用 CASE 表达式,对于每个月份和客户组合,如果该客户在当月之前还没有下达过定单,则该表达式返回 1,否则,返回 0。也就是说,如果客户是新客户,CASE 表达式返回 1。最外部查询按月份对 N 中的行进行分组,并通过对 N 表中名为 new 的列中的所有 1 进行汇总,来计算新客户的总数。客户的总数就是该组中的行数 (COUNT(*)),因为该组中的每一行都代表一个不同的客户。现有客户的数量是客户总数减去新客户的数量。

当我编写此解决方案时,我并没有考虑性能,我只想到要用逻辑方式来编写该查询。在我的笔记本电脑上,对我在 tempdb 中创建的较大的 Orders 表运行该查询时,该查询发生了超过 137,000 次的逻辑读取,运行的时间长达 17 秒。普通索引并没有提供多少帮助- 当我在 customerid 列和 orderdate 列上创建了组合索引后,该查询运行了 15 秒。看来,这个问题需要采用另一种方法,这次,我决定先考虑性能。

这个较大的 Orders 表会包括每个月份的每个客户的许多定单。如果您有一个只包含月份和客户的不同组合的汇总表,就可以编写一个更易于执行的查询。索引视图的实质就是对表进行汇总。请运行 清单 3 中的代码,创建一个名为 Vymcusts 的索引视图,该视图只包含月份和客户的不同组合。注意,如果您希望对一个使用聚合的视图进行索引,则必须在该视图内包括 COUNT_BIG(*) 函数。如果 SQL Server 知道每组中的行数,就可以有效地维护索引视图。

接下来,我测试了几个不同的解决方案,所有方案执行起来都比第一个要好得多。我们来看一下我编写出的执行效果最好的解决方案。我创建了另一个名为 Vnewcusts 的视图,该视图包含每个客户的最小年/月份 (ym) 值:

CREATE VIEW Vnewcusts

AS

SELECT customerid, MIN(ym) AS min_ym

FROM Vymcusts (NOEXPAND)

GROUP BY customerid

客户的 ym 最小值就是该客户的第一份定单的月份。NOEXPAND 提示要求优化器将索引视图视为表,不要尝试使用来自基表 Orders 的索引。最后,我将 Vymcusts (VY) 左联接到了 Vnewcusts (VN)。如果 VY VN 同时拥有同一个客户,并且 VY 中的当前月份是该客户的最小月份 (VY.ym = VN.min_ym),则 VY 中的某一行会在 VN 中寻找匹配的行。如果该月份不是该客户的最小月份,则外部联接在 VN.customerid 中以及在 VN 的所有列中均返回 NULL。查询按 VY.ym 列对外部联接的结果进行分组。SQL Server 通过对 VN.customerid 中的非 Null 值进行计数来计算新客户的总数。清单 4 显示了这个最终的查询,该查询在我的笔记本电脑上只运行了 1 秒,仅发生了 280 次逻辑读取。这样的性能令人非常满意。

列基数

我发现索引视图对解决另一个问题也非常有用,这就是计算列基数 - 列中不同的值的数量。计算表中每列的基数会占用大量系统开销,因为 SQL Server 需要重新排列每列的值,这样就可以很容易地对不同的值进行计数。现在我们来看一些示例,它们使用一个具有三个数据列和一百万行的表。请运行 清单 5 显示的脚本,在 tempdb 中创建一个名为 T1 的表,该表包括的各列是 c1c2 c3,这些列分别包含 50,000100,000 200,000 个不同的值。

在没有适当索引的情况下,下面的查询将计算每个数据列的基数,该查询的执行性能比较差:

SET STATISTICS TIME ON

SET STATISTICS IO ON

SELECT COUNT(DISTINCT c1), 

COUNT(DISTINCT c2), COUNT(DISTINCT c3) FROM T1

我们在查询分析器中看看该查询的执行计划,我们注意到,SQL Server T1 的聚集索引扫描了三次,对每一列中的值都执行了哈希操作来计数不同值的数量。因此,该查询用了很长的时间才完成,并且发生了大量 I/O 系统开销。在我的笔记本电脑上,该查询运行了 23 秒,发生了 9294 次逻辑读取。

为了获得更好的性能,很明显,第一个步骤就是对每一列进行索引:

CREATE NONCLUSTERED INDEX idx_nc_c1 ON T1(c1)

CREATE NONCLUSTERED INDEX idx_nc_c2 ON T1(c2)

CREATE NONCLUSTERED INDEX idx_nc_c3 ON T1(c3)

这样一来,SQL Server 可以用排序的方式单独扫描每个索引,并对不同的值进行计数。现在,查询只运行 5 秒钟,发生 4083 次逻辑读取。

您可能对这样的性能提高感到满意,但对于包括更多列和更多行的表,可能还需要进一步提高性能。获得更好查询性能的一个方式(不过要以修改性能为代价)是编写 INSERTUPDATE DELETE 触发器,它们在单独的非正式表中维护每列的非重复计数值。您也可以在不使用触发器的情况下解决该问题,方法是:使用索引视图,而不是普通的非聚集索引。

对于每一列,您可以创建一个只包含感兴趣的列和 COUNT_BIG(*) 函数的索引视图,该函数是对有聚合的视图进行索引所必需的。例如,可为 c1 创建以下索引视图:

CREATE VIEW V_T1_c1 WITH SCHEMABINDING

AS

SELECT c1, COUNT_BIG(*) 

 AS cnt FROM dbo.T1 GROUP BY c1

GO

CREATE UNIQUE CLUSTERED INDEX

 idx_uc_c1 ON V_T1_c1(c1)

然后,运行 清单 6 显示的脚本,为 c2 c3 创建索引视图。现在,重新运行该查询,测量一下持续时间和 I/O,并查看执行计划。优化器的智能程度足以理解该执行计划,对列的非重复计数进行计算,它可以只对在其 GROUP BY 子句中使用该列的索引视图内的行数进行计数。现在,该查询运行的时间不到一秒,发生的逻辑读取不到 1000 次。

 

程序清单:

清单 1: Code That Creates an Orders Table of 100,000 Rows
USE tempdb
CREATE TABLE Orders
(
 orderid    INT         NOT NULL IDENTITY PRIMARY KEY,
 orderdate DATETIME    NOT NULL,
 customerid VARCHAR(15) NOT NULL,
 filler     CHAR(200)   NOT NULL DEFAULT 'a'
)
 
/* Populate with 100,000 rows, 1000 different dates,
 up to 2000 different customers*/
SET NOCOUNT ON
DECLARE @i AS INT
SET @i = 1
WHILE @i <= 100000
BEGIN
 INSERT INTO Orders(orderdate, customerid)
    VALUES(
      GETDATE()-1000+@i%1000,
      'Cust'+CAST(CAST(RAND()*1000 AS INT)+@i%1000 AS VARCHAR(10)))
 SET @I = @I + 1
END

 

清单2: Solution 1 to the New Customers Problem
SET STATISTICS TIME ON
SET STATISTICS IO ON
 
SELECT CONVERT(CHAR(6), ym, 112) AS ym,
 COUNT(*) - SUM(new) AS existing,
 SUM(new) AS new,
 COUNT(*) AS total
FROM (SELECT ym,
        CASE WHEN EXISTS(
                    SELECT * FROM Orders AS O
                    WHERE O.customerid = M.customerid
                      AND O.orderdate < M.ym)
          THEN 0 ELSE 1
        END AS new
      FROM (SELECT DISTINCT
              CAST(CONVERT(CHAR(6), orderdate, 112)
                   + '01' AS DATETIME) AS ym,
              customerid
            FROM Orders) AS M) AS N
GROUP BY ym
清单 3: Indexed View Containing Only Months and Customers
CREATE VIEW Vymcusts WITH SCHEMABINDING
AS
 
SELECT
 CONVERT(CHAR(6), orderdate, 112) AS ym,
 customerid,
 COUNT_BIG(*) AS numorders
FROM dbo.orders
GROUP BY
 CONVERT(CHAR(6), orderdate, 112),
 customerid
GO
 
CREATE UNIQUE CLUSTERED INDEX idx_uc_customerid_ym
 ON Vymcusts(customerid, ym)

 

清单 4: Final Solution to the New Customers Problem
SELECT
 VY.ym,
 COUNT(*) - COUNT(VN.customerid) AS existing,
 COUNT(VN.customerid) AS new,
 COUNT(*) AS total
FROM Vymcusts AS VY
 LEFT OUTER JOIN Vnewcusts AS VN
    ON VY.ym = VN.min_ym
    AND VY.customerid = VN.customerid
GROUP BY VY.ym
ORDER BY VY.ym
 
清单 5: Code That Creates and Populates 
the T1 Table with 1 Million Rows
 
SET STATISTICS TIME OFF
SET STATISTICS IO OFF
 
USE tempdb
CREATE TABLE T1
(
 key_col int NOT NULL IDENTITY PRIMARY KEY,
 c1        int NOT NULL,
 c2        int NOT NULL,
 c3        int NOT NULL
)
 
SET NOCOUNT ON
DECLARE @i AS int
SET @I = 1
WHILE @i <= 1000000
BEGIN
 INSERT INTO T1 VALUES(@i % 50000, @i % 100000, @i % 200000)
 SET @i = @i + 1
END
清单 6: Code That Creates Indexed Views for c2 and c3
CREATE VIEW V_T1_c2 WITH SCHEMABINDING
AS
SELECT c2, COUNT_BIG(*) AS cnt FROM dbo.T1 GROUP BY c2
GO
CREATE UNIQUE CLUSTERED INDEX idx_uc_c2 ON V_T1_c2(c2)
GO
 
CREATE VIEW V_T1_c3 WITH SCHEMABINDING
AS
SELECT c3, COUNT_BIG(*) AS cnt FROM dbo.T1 GROUP BY c3
GO
CREATE UNIQUE CLUSTERED INDEX idx_uc_c3 ON V_T1_c3(c3)
GO