`
zheyiw
  • 浏览: 1017141 次
  • 性别: Icon_minigender_1
  • 来自: 广州
社区版块
存档分类
最新评论

聚集索引和非聚集索引

    博客分类:
  • SQL
阅读更多
一、聚集索引
  一种索引,该索引中键值的逻辑顺序决定了表中相应行的物理顺序。
  聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。  
聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行 的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此 类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节 省成本。   
当索引值唯一时,使用聚集索引查找特定的行也很有效率。例如,使用唯一雇员 ID 列 emp_id 查找特定雇员的最快速的方法,是在 emp_id 列上创建聚集索引或 PRIMARY KEY 约束。

二、何时使用聚集索引或非聚集索引
下面的表总结了何时使用聚集索引或非聚集索引(很重要):
动作描述			使用聚集索引	使用非聚集索引
列经常被分组排序		应  			应
返回某范围内的数据		应  			不应
一个或极少不同值		不应			不应
小数目的不同值			应  			不应
大数目的不同值			不应			应
频繁更新的列  			不应			应
外键列					应  			应
主键列					应  			应
频繁修改索引列  		不应			应



三、结合实际,谈索引使用的误区

  理论的目的是应用。虽然我们刚才列出了何时应使用聚集索引或非聚集索引,但在实践中以上规则却很容易被忽视或不能根据实际情况进行综合分析。下面我们将根据在实践中遇到的实际问题来谈一下索引使用的误区,以便于大家掌握索引建立的方法。

1、主键就是聚集索引
  这种想法笔者认为是极端错误的,是对聚集索引的一种浪费。虽然SQL SERVER默认是在主键上建立聚集索引的。
  通常,我们会在每个表中都建立一个ID列,以区分每条数据,并且这个ID列是自动增大的,步长一般为1。我们的这个办公自动化的实例中的列Gid就是如此。此时,如果我们将这个列设为主键,SQL SERVER会将此列默认为聚集索引。这样做有好处,就是可以让您的数据在数据库中按照ID进行物理排序,但笔者认为这样做意义不大。
  显而易见,聚集索引的优势是很明显的,而每个表中只能有一个聚集索引的规则,这使得聚集索引变得更加珍贵。
  从我们前面谈到的聚集索引的定义我们可以看出,使用聚集索引的最大好处就是能够根据查询要求,迅速缩小查询范围,避免全表扫描。在实际应用中,因为 ID号是自动生成的,我们并不知道每条记录的ID号,所以我们很难在实践中用ID号来进行查询。这就使让ID号这个主键作为聚集索引成为一种资源浪费。其次,让每个ID号都不同的字段作为聚集索引也不符合“大数目的不同值情况下不应建立聚合索引”规则;当然,这种情况只是针对用户经常修改记录内容,特别是索引项的时候会负作用,但对于查询速度并没有影响。
  在办公自动化系统中,无论是系统首页显示的需要用户签收的文件、会议还是用户进行文件查询等任何情况下进行数据查询都离不开字段的是“日期”还有用户本身的“用户名”。
  通常,办公自动化的首页会显示每个用户尚未签收的文件或会议。虽然我们的where语句可以仅仅限制当前用户尚未签收的情况,但如果您的系统已建立了很长时间,并且数据量很大,那么,每次每个用户打开首页的时候都进行一次全表扫描,这样做意义是不大的,绝大多数的用户1个月前的文件都已经浏览过了,这样做只能徒增数据库的开销而已。事实上,我们完全可以让用户打开系统首页时,数据库仅仅查询这个用户近3个月来未阅览的文件,通过“日期”这个字段来限制表扫描,提高查询速度。如果您的办公自动化系统已经建立的2年,那么您的首页显示速度理论上将是原来速度8倍,甚至更快。
  在这里之所以提到“理论上”三字,是因为如果您的聚集索引还是盲目地建在ID这个主键上时,您的查询速度是没有这么高的,即使您在“日期”这个字段上建立的索引(非聚合索引)。下面我们就来看一下在1000万条数据量的情况下各种查询的速度表现(3个月内的数据为25万条):

(1)仅在主键上建立聚集索引,并且不划分时间段:
Select gid,fariqi,neibuyonghu,title from tgongwen
用时:128470毫秒(即:128秒)

(2)在主键上建立聚集索引,在fariq上建立非聚集索引:
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
用时:53763毫秒(54秒)

(3)将聚合索引建立在日期列(fariqi)上:
select gid,fariqi,neibuyonghu,title from Tgongwen
where fariqi> dateadd(day,-90,getdate())
用时:2423毫秒(2秒)

  虽然每条语句提取出来的都是25万条数据,各种情况的差异却是巨大的,特别是将聚集索引建立在日期列时的差异。事实上,如果您的数据库真的有1000 万容量的话,把主键建立在ID列上,就像以上的第1、2种情况,在网页上的表现就是超时,根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。得出以上速度的方法是:在各个select语句前加:
declare @d datetime
set @d=getdate()

并在select语句后加:
select [语句执行花费时间(毫秒)]=datediff(ms,@d,getdate())

2、只要建立索引就能显著提高查询速度
  事实上,我们可以发现上面的例子中,第2、3条语句完全相同,且建立索引的字段也相同;不同的仅是前者在fariqi字段上建立的是非聚合索引,后者在此字段上建立的是聚合索引,但查询速度却有着天壤之别。所以,并非是在任何字段上简单地建立索引就能提高查询速度。
  从建表的语句中,我们可以看到这个有着1000万数据的表中fariqi字段有5003个不同记录。在此字段上建立聚合索引是再合适不过了。在现实中,我们每天都会发几个文件,这几个文件的发文日期就相同,这完全符合建立聚集索引要求的:“既不能绝大多数都相同,又不能只有极少数相同”的规则。由此看来,我们建立“适当”的聚合索引对于我们提高查询速度是非常重要的。

3、把所有需要提高查询速度的字段都加进聚集索引,以提高查询速度
  上面已经谈到:在进行数据查询时都离不开字段的是“日期”还有用户本身的“用户名”。既然这两个字段都是如此的重要,我们可以把他们合并起来,建立一个复合索引(compound index)。
  很多人认为只要把任何字段加进聚集索引,就能提高查询速度,也有人感到迷惑:如果把复合的聚集索引字段分开查询,那么查询速度会减慢吗?带着这个问题,我们来看一下以下的查询速度(结果集都是25万条数据):(日期列fariqi首先排在复合聚集索引的起始列,用户名neibuyonghu排在后列):

(1)select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>''2004-5-5''
查询速度:2513毫秒

(2)select gid,fariqi,neibuyonghu,title from Tgongwen
            where fariqi>''2004-5-5'' and neibuyonghu=''办公室''
查询速度:2516毫秒

(3)select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=''办公室''
查询速度:60280毫秒

  从以上试验中,我们可以看到如果仅用聚集索引的起始列作为查询条件和同时用到复合聚集索引的全部列的查询速度是几乎一样的,甚至比用上全部的复合索引列还要略快(在查询结果集数目一样的情况下);而如果仅用复合聚集索引的非起始列作为查询条件的话,这个索引是不起任何作用的。当然,语句1、2的查询速度一样是因为查询的条目数一样,如果复合索引的所有列都用上,而且查询结果少的话,这样就会形成“索引覆盖”,因而性能可以达到最优。同时,请记住:无论您是否经常使用聚合索引的其他列,但其前导列一定要是使用最频繁的列。

四、其他书上没有的索引使用经验总结

1、用聚合索引比用不是聚合索引的主键速度快
  下面是实例语句:(都是提取25万条数据)

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
使用时间:3326毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where gid<=250000
使用时间:4470毫秒

这里,用聚合索引比用不是聚合索引的主键速度快了近1/4。

2、用聚合索引比用一般的主键作order by时速度快,特别是在小数据量情况下

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by fariqi
用时:12936

select gid,fariqi,neibuyonghu,reader,title from Tgongwen order by gid
用时:18843

  这里,用聚合索引比用一般的主键作order by时,速度快了3/10。事实上,如果数据量很小的话,用聚集索引作为排序列要比使用非聚集索引速度快得明显的多;而数据量如果很大的话,如10万以上,则二者的速度差别不明显。


3、使用聚合索引内的时间段,搜索时间会按数据占整个数据表的百分比成比例减少,而无论聚合索引使用了多少个:

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-1-1''
用时:6343毫秒(提取100万条)

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi>''2004-6-6''
用时:3170毫秒(提取50万条)

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
用时:3326毫秒(和上句的结果一模一样。如果采集的数量一样,那么用大于号和等于号是一样的)

select gid,fariqi,neibuyonghu,reader,title from Tgongwen
            where fariqi>''2004-1-1'' and fariqi<''2004-6-6''
用时:3280毫秒


4、日期列不会因为有分秒的输入而减慢查询速度
  下面的例子中,共有100万条数据,2004年1月1日以后的数据有50万条,但只有两个不同的日期,日期精确到日;之前有数据50万条,有5000个不同的日期,日期精确到秒。

select gid,fariqi,neibuyonghu,reader,title from Tgongwen
          where fariqi>''2004-1-1'' order by fariqi
用时:6390毫秒

select gid,fariqi,neibuyonghu,reader,title from Tgongwen
            where fariqi<''2004-1-1'' order by fariqi

用时:6453毫秒


五, 聚集索引是如此的重要和珍贵,所以笔者总结了一下,一定要将聚集索引建立在:
(1)、您最频繁使用的、用以缩小查询范围的字段上;
(2)、您最频繁使用的、需要排序的字段上。

其他注意事项
  “水可载舟,亦可覆舟”,索引也一样。索引有助于提高检索性能,但过多或不当的索引也会导致系统低效。因为用户在表中每加进一个索引,数据库就要做更多的工作。过多的索引甚至会导致索引碎片。
  所以说,我们要建立一个“适当”的索引体系,特别是对聚合索引的创建,更应精益求精,以使您的数据库能得到高性能的发挥。
  当然,在实践中,作为一个尽职的数据库管理员,您还要多测试一些方案,找出哪种方案效率最高、最为有效。




建表脚本例子:
IF OBJECT_ID ('Wms_MaterialWeightMaster', 'U') IS NULL
   BEGIN
      --物流称重主表
      CREATE TABLE Wms_MaterialWeightMaster
      (
         CompanyID       [dbo].[T_ComCode] NOT NULL,
         BillNo          [dbo].[T_BillNo_L] NOT NULL,
         StockID         [dbo].[T_ID] NOT NULL,
         BillDate        SMALLDATETIME DEFAULT GetDate () NOT NULL,
         WaveBillNo      [dbo].[T_BillNo_L] NOT NULL,
         ConsigneeID     [dbo].[T_ID] NOT NULL,
         ConsigneeType   INT NOT NULL,
         OperatorID      [dbo].[T_ID] NOT NULL,
         IsPrint         [dbo].[T_Boolean] DEFAULT 0 NOT NULL,
         PrintTime       DATETIME NULL,
         SourceBoxSum    INT NOT NULL,
         ModifyDTM       DATETIME DEFAULT GetDate () NOT NULL
      );


      --建聚集索引(把聚集索引建在时间列上,能提高查询效率)
      CREATE CLUSTERED INDEX CL_Wms_MaterialWeightMaster
         ON Wms_MaterialWeightMaster (CompanyID, StockID, BillDate);

      --建主键
      ALTER TABLE Wms_MaterialWeightMaster
      ADD CONSTRAINT PK_Wms_MaterialWeightMaster PRIMARY KEY (CompanyID,BillNo);
   END
GO
分享到:
评论

相关推荐

    索引介绍聚集索引和非聚集索引

    ### 索引介绍:聚集索引与非聚集索引 #### 一、索引的基本概念 ...总之,了解聚集索引和非聚集索引之间的区别对于优化数据库性能至关重要。通过合理设计和维护索引,可以显著提高数据库系统的整体性能。

    聚集索引和非聚集索引的区别

    聚集索引和非聚集索引是数据库管理系统中两种重要的索引类型,它们在数据库查询优化中扮演着关键角色。理解这两种索引的区别对于优化数据库性能至关重要。 首先,聚集索引(Clustered Index)决定了表中数据的实际...

    SQL Server 索引结构及其使用(聚集索引与非聚集索引)

    本文将详细介绍聚集索引和非聚集索引的概念、区别、使用场景和误区。 聚集索引是一种特殊的目录,根据一定规则排列的目录。例如,汉语字典的正文部分本身就是一个聚集索引。聚集索引的优势在于能够根据查询要求,...

    SQL Server 聚集索引和非聚集索引的区别分析

    索引分为两种主要类型:聚集索引和非聚集索引,它们在数据存储和查询性能上具有显著区别。 **聚集索引(Clustered Index)** 聚集索引决定了数据在磁盘上的物理顺序,这意味着表中的每一行数据都是按照聚集索引的...

    数据库非聚集索引 聚集索引 模式 索引

    总的来说,非聚集索引和聚集索引各有特点,适用于不同的场景,而索引模式的构建则是平衡查询性能和存储维护的关键。通过深入学习《非聚集索引.docx》、《聚集索引.docx》和《索引模式.docx》等文档,可以更全面地...

    聚集索引与非聚集索引的区别

    ### 聚焦索引与非聚焦索引的深度解析 ...理解聚焦索引和非聚焦索引的特点及其应用场景,可以帮助我们在实际工作中做出更合理的决策。通过对索引的有效管理和优化,可以大大提高数据库系统的整体性能。

    SQLServer聚集索引与非聚集索引讲解[借鉴].pdf

    本文主要讲解了SQL Server中的聚集索引和非聚集索引的概念、原理和应用场景。聚集索引(Clustered Index)和非聚集索引(Non-Clustered Index)是SQL Server中两种不同的索引类型,了解它们的区别和应用场景对于提高...

    SQLServer索引基础知识----聚集索引,非聚集索引[归纳].pdf

    Bookmark Lookup 是一种特殊的查询方式,它可以将非聚集索引和聚集索引结合起来,提高查询的效率。Bookmark Lookup 的过程如下所示: 先在非聚集中找,然后再在聚集索引中找。 五、结论 SQL Server 索引是关系型...

    sqlserver 聚集索引和非聚集索引实例

    索引分为两种主要类型:聚集索引(Clustered Index)和非聚集索引(Nonclustered Index)。下面将详细介绍这两种索引的概念、特点以及在实际操作中的应用。 **聚集索引(Clustered Index)** 聚集索引决定了数据在...

    数据库查询速度优化 建立索引

    数据库索引可以分为聚集索引和非聚集索引两种。聚集索引是一种特殊的目录,它的正文内容本身就是一种按照一定规则排列的目录。这种索引可以快速地查询到所需的数据,例如,我们可以使用聚集索引来快速地查询某个日期...

    SQL Server 2000索引结构及使用方法

    本文将详细介绍 SQL Server 2000 的索引结构和使用方法,包括聚集索引和非聚集索引的定义、区别、使用场景和注意事项。 一、索引结构 索引是一种特殊的目录,可以帮助我们快速地查找数据。SQL Server 2000 提供了...

    创建索引和调优索引

    索引可以分为多种类型,包括聚集索引和非聚集索引,每种都有其特定的用途和优势。 聚集索引,正如其名,决定了数据行在表中的物理排序。这种索引的键值直接对应于数据行,意味着一个表只能有一个聚集索引。如果表...

    SQL优化-索引

    SQL优化中的索引是提升数据库查询性能的关键技术。索引可以理解为数据库中的一种特殊目录,帮助快速定位和访问数据。...在实际操作中,应根据具体业务需求灵活运用聚集索引和非聚集索引,以实现最佳的数据库性能。

    Mysql-索引原理分析

    聚集索引和非聚集索引的主要区别在于数据存储的方式。在聚集索引中,索引和数据是合一的,而在非聚集索引中,索引是单独的结构,需要通过书签来定位实际数据。因此,非聚集索引查询通常比聚集索引慢,因为需要额外的...

    深入浅出理解索引结构

    下面是一些示例SQL语句,用于创建聚集索引和非聚集索引: ```sql -- 创建聚集索引 CREATE CLUSTERED INDEX idx_department ON employees(department); -- 创建非聚集索引 CREATE NONCLUSTERED INDEX idx_name ON ...

    SQL Server和Oracle中索引介绍

    在SQL Server中,索引分为聚集索引和非聚集索引。聚集索引决定了表中数据行的物理存储顺序,每张表只能有一个聚集索引。当数据行的排序与索引键相匹配时,这样的表被称为聚集表。聚集索引适用于经常用于范围查询、...

Global site tag (gtag.js) - Google Analytics