sql server 索引

SQL SERVER 建立索引!

SQL Server 聚集索引/非聚集索引2011-03-26 19:55前些天看看 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;ENDgo-- 复制自己一次 = 182500 * 2 = 365000INSERT INTO TestDoc  SELECT createDate, owner + '1', docInfo FROM TestDoc;go-- 再自己复制自己一次 = 365000 *2 = 730000INSERT INTO TestDoc  SELECT createDate, owner + '2', docInfo FROM TestDoc;go-- 再自己复制自己一次 = 730000 *2 = 1460000INSERT INTO TestDoc  SELECT createDate, owner + '3', docInfo FROM TestDoc;go-- 再自己复制自己一次 = 1460000 *2 = 2920000INSERT INTO TestDoc  SELECT createDate, owner + '4', docInfo FROM TestDoc;go  -- 用于测试执行性能的存储过程.-- 该存储过程，只在很大的数据量中，查询很少量的数据. [1/36500]CREATE PROCEDURE TestQuery1ASBEGIN  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());ENDgo-- 用于测试执行性能的存储过程.-- 该存储过程，在很大的数据量中，查询比较大量的数据. [1/100]CREATE PROCEDURE TestQuery2ASBEGIN  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());ENDgo-- 用于测试执行性能的存储过程.-- 该存储过程，在很大的数据量中，查询很大量的数据. [1/10]CREATE PROCEDURE TestQuery3ASBEGIN  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());ENDgo-- 在没有索引的情况下：EXECUTE TestQuery1;GOEXECUTE TestQuery2;GOEXECUTE TestQuery3;GO -- 创建非聚集索引CREATE NONCLUSTERED INDEX idx_TestDoc ON TestDoc(createDate);go-- 使用非聚集索引的情况下EXECUTE TestQuery1;GOEXECUTE TestQuery2;GOEXECUTE TestQuery3;GO-- 删除前面创建的索引DROP INDEX idx_TestDoc ON TestDoc;go-- 创建新的聚集索引CREATE CLUSTERED INDEX idx_TestDoc ON TestDoc(createDate);go-- 使用聚集索引的情况下EXECUTE TestQuery1;GOEXECUTE TestQuery2;GOEXECUTE TestQuery3;GO 最后的测试结果，大概的对比情况如下： 单位 毫秒数据百分比  无索引    非聚集    聚集1/36500     393-470   0-16      0-331/100       413-500   416-486   43-561/10        740-916   730-940   273-293测试的结果显示：在 返回大型结果集的查询 下使用非聚集索引，性能比没有索引的性能好不了多少，可能还会更差。使用聚集索引能够提高一定的性能。 

来源：http://hi.baidu.com/wangzhiqing999/blog/item/ea57b6932325457854fb9634.html