聚集索引和非聚集索引（转）

前些天看看 SQL Server 的聚集索引与非聚集索引的资料，拿来做做实现，分享一下心得。


聚集索引：
  该索引中键值的逻辑顺序决定了表中相应行的物理顺序。

如果用 新华字典 作例子来一个例子的话。
  [拼音]就可以看作是聚集索引
    例如 吖、阿、啊 在字典的最前面。
    左、作、坐 在字典的最后面。
    拼音[逻辑顺序]很接近，在字典中页数的位置[物理顺序]也很接近。

适用场合：
  含有大量非重复值的列
  使用BETWEEN,>,>=,<或<=返回一个范围值的列
  被连续访问的列
  返回大型结果集的查询
  经常被使用连接或GROUP BY子句的查询访问的列



非聚集索引：
  非聚集索引与聚集索引一样有 B 树结构，但是有两个重大差别：
  数据行不按非聚集索引键的顺序排序和存储。
  非聚集索引的叶层不包含数据页。
  相反，叶节点包含索引行。每个索引行包含非聚集键值以及一个或多个行定位器，
  这些行定位器指向有该键值的数据行（如果索引不唯一，则可能是多行）。

如果用 新华字典 作例子来一个例子的话。
  [笔画]就可以看作是非聚集索引
    例如 化 仇 仃 仅 仂 这几个字，都是 单人旁，笔画数相同的。
    笔画[逻辑顺序]很接近，在字典中页数的位置[物理顺序]则在不同的位置上。

适用场合：
  含有大量非重复值的列
  非连续访问的列
  返回小型结果集的查询



-- 由于 SQL SERVER 默认是在主键上建立聚集索引的。
-- 一个表，又只允许有一个 聚集索引。
-- 这里创建主键的时候，要指定这个主键，使用非聚集索引
CREATE TABLE TestDoc(
  id             INT identity(1, 1),
  createDate      DATETIME,
  owner            VARCHAR(10),
  docInfo        TEXT,
  PRIMARY KEY NONCLUSTERED (id)
);
go

 

 

 

-- 首先插入 36500*5 = 182500 条数据.
DECLARE
  @i AS INT,
  @myDate AS DATETIME;
BEGIN
  SET @i = 0;
  SET @myDate = CONVERT(DATETIME, '1949.10.01', 102);
  WHILE @i < 36500
  BEGIN
    SET @i = @i + 1;
    INSERT INTO TestDoc
        VALUES ( DATEADD(dd, @i, @myDate), '张三', NULL);
    INSERT INTO TestDoc
        VALUES ( DATEADD(dd, @i, @myDate), '李四', NULL);
    INSERT INTO TestDoc
        VALUES ( DATEADD(dd, @i, @myDate), '王五', NULL);
    INSERT INTO TestDoc
        VALUES ( DATEADD(dd, @i, @myDate), '赵六', NULL);
    INSERT INTO TestDoc
        VALUES ( DATEADD(dd, @i, @myDate), 'Admin', NULL);
  END;
END
go


-- 复制自己一次 = 182500 * 2 = 365000
INSERT INTO TestDoc
  SELECT createDate, owner + '1', docInfo FROM TestDoc;
go

-- 再自己复制自己一次 = 365000 *2 = 730000
INSERT INTO TestDoc
  SELECT createDate, owner + '2', docInfo FROM TestDoc;
go

-- 再自己复制自己一次 = 730000 *2 = 1460000
INSERT INTO TestDoc
  SELECT createDate, owner + '3', docInfo FROM TestDoc;
go

-- 再自己复制自己一次 = 1460000 *2 = 2920000
INSERT INTO TestDoc
  SELECT createDate, owner + '4', docInfo FROM TestDoc;
go

 

 

-- 用于测试执行性能的存储过程.
-- 该存储过程，只在很大的数据量中，查询很少量的数据. [1/36500]
CREATE PROCEDURE TestQuery1
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS(SELECT * FROM sys.Tables WHERE name='#temp1')
  DROP TABLE #temp1;

  IF EXISTS(SELECT * FROM sys.Tables WHERE name='#temp2')
  DROP TABLE #temp2;


  -- 定义一个开始之间.
  DECLARE @startDate AS DATETIME;

  -- 首先只查询一个字段的
  -- 设置 开始执行时间.
  SET @startDate = GETDATE();
  -- 执行查询.
  SELECT
    * INTO #temp1
  FROM
    TestDoc
  WHERE
    createDate = CONVERT(DATETIME, '2008.01.01', 102);
  -- 输出查询经过的时间.
  PRINT DATEDIFF(MS, @startDate, GETDATE());


  -- 然后查询多个字段的
  -- 设置 开始执行时间.
  SET @startDate = GETDATE();
  -- 执行查询.
  SELECT
    * INTO #temp2
  FROM
    TestDoc
  WHERE
    createDate = CONVERT(DATETIME, '2008.01.01', 102)
    AND owner LIKE '张三%';
  -- 输出查询经过的时间.
  PRINT DATEDIFF(MS, @startDate, GETDATE());

END
go




-- 用于测试执行性能的存储过程.
-- 该存储过程，在很大的数据量中，查询比较大量的数据. [1/100]
CREATE PROCEDURE TestQuery2
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS(SELECT * FROM sys.Tables WHERE name='#temp1')
  DROP TABLE #temp1;

  IF EXISTS(SELECT * FROM sys.Tables WHERE name='#temp2')
  DROP TABLE #temp2;

  -- 定义一个开始之间.
  DECLARE @startDate AS DATETIME;

  -- 首先只查询一个字段的
  -- 设置 开始执行时间.
  SET @startDate = GETDATE();
  -- 执行查询.
  SELECT
    * INTO #temp1
  FROM
    TestDoc
  WHERE
    createDate >= CONVERT(DATETIME, '2008.01.01', 102)
    AND createDate < CONVERT(DATETIME, '2009.01.01', 102);
  -- 输出查询经过的时间.
  PRINT DATEDIFF(MS, @startDate, GETDATE());


  -- 然后查询多个字段的
  -- 设置 开始执行时间.
  SET @startDate = GETDATE();
  -- 执行查询.
  SELECT
    * INTO #temp2
  FROM
    TestDoc
  WHERE
    createDate >= CONVERT(DATETIME, '2008.01.01', 102)
    AND createDate < CONVERT(DATETIME, '2009.01.01', 102)
    AND owner LIKE '张三%';
  -- 输出查询经过的时间.
  PRINT DATEDIFF(MS, @startDate, GETDATE());

END
go



-- 用于测试执行性能的存储过程.
-- 该存储过程，在很大的数据量中，查询很大量的数据. [1/10]
CREATE PROCEDURE TestQuery3
AS
BEGIN
  SET NOCOUNT ON;

  IF EXISTS(SELECT * FROM sys.Tables WHERE name='#temp1')
  DROP TABLE #temp1;

  IF EXISTS(SELECT * FROM sys.Tables WHERE name='#temp2')
  DROP TABLE #temp2;

  -- 定义一个开始之间.
  DECLARE @startDate AS DATETIME;

  -- 首先只查询一个字段的
  -- 设置 开始执行时间.
  SET @startDate = GETDATE();
  -- 执行查询.
  SELECT
    * INTO #temp1
  FROM
    TestDoc
  WHERE
    createDate >= CONVERT(DATETIME, '1999.01.01', 102)
    AND createDate < CONVERT(DATETIME, '2009.01.01', 102);
  -- 输出查询经过的时间.
  PRINT DATEDIFF(MS, @startDate, GETDATE());


  -- 然后查询多个字段的
  -- 设置 开始执行时间.
  SET @startDate = GETDATE();
  -- 执行查询.
  SELECT
    * INTO #temp2
  FROM
    TestDoc
  WHERE
    createDate >= CONVERT(DATETIME, '1999.01.01', 102)
    AND createDate < CONVERT(DATETIME, '2009.01.01', 102)
    AND owner LIKE '张三%';
  -- 输出查询经过的时间.
  PRINT DATEDIFF(MS, @startDate, GETDATE());

END
go

-- 在没有索引的情况下：
EXECUTE TestQuery1;
GO
EXECUTE TestQuery2;
GO
EXECUTE TestQuery3;
GO

 

-- 创建非聚集索引
CREATE NONCLUSTERED INDEX idx_TestDoc ON TestDoc(createDate);
go

-- 使用非聚集索引的情况下
EXECUTE TestQuery1;
GO
EXECUTE TestQuery2;
GO
EXECUTE TestQuery3;
GO

-- 删除前面创建的索引
DROP INDEX idx_TestDoc ON TestDoc;
go

-- 创建新的聚集索引
CREATE CLUSTERED INDEX idx_TestDoc ON TestDoc(createDate);
go

-- 使用聚集索引的情况下
EXECUTE TestQuery1;
GO
EXECUTE TestQuery2;
GO
EXECUTE TestQuery3;
GO

 

最后的测试结果，大概的对比情况如下： 单位 毫秒

数据百分比  无索引    非聚集    聚集
1/36500     393-470   0-16      0-33
1/100       413-500   416-486   43-56
1/10        740-916   730-940   273-293

测试的结果显示：
在 返回大型结果集的查询 下
使用非聚集索引，性能比没有索引的性能好不了多少，可能还会更差。
使用聚集索引能够提高一定的性能。

转自http://hi.baidu.com/wangzhiqing999/blog/item/ea57b6932325457854fb9634.html