精华内容
下载资源
问答
  • 聚集索引和非聚集索引的区别

    万次阅读 多人点赞 2019-05-19 01:25:35
    微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别: ...

    欢迎大家关注我的公众号【老周聊架构】,Java后端主流技术栈的原理、源码分析、架构以及各种互联网高并发、高性能、高可用的解决方案。

    一、深入浅出理解索引结构

    实际上,可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:

    其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查“安”字,因为“安”的拼音是“an”,而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的,那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。

    如果遇到不认识的字,不知道它的发音,这时候,需要去根据“偏旁部首”查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合“部首目录”和“检字表”而查到的字的排序并不是真正的正文的排序方法,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。

    通过以上例子,我们可以理解到什么是“聚集索引”和“非聚集索引”。进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。

    二、区别及优缺点

    区别:

    • 聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个
    • 聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续
    • 聚集索引:物理存储按照索引排序;聚集索引是一种索引组织形式,索引的键值逻辑顺序决定了表数据行的物理存储顺序。
      非聚集索引:物理存储不按照索引排序;非聚集索引则就是普通索引了,仅仅只是对数据列创建相应的索引,不影响整个表的物理存储顺序。
    • 索引是通过二叉树的数据结构来描述的,我们可以这么理解聚簇索引:索引的叶节点就是数据节点。而非聚簇索引的叶节点仍然是索引节点,只不过有一个指针指向对应的数据块。

    优势与缺点:

    聚集索引插入数据时速度要慢(时间花费在“物理存储的排序”上,也就是首先要找到位置然后插入),查询数据比非聚集数据的速度快。

    三、需要搞清楚的几个问题

    第一:聚集索引的约束是唯一性,是否要求字段也是唯一的呢?

    分析:如果认为是的朋友,可能是受系统默认设置的影响,一般我们指定一个表的主键,如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,SQL会默认在此字段上创建一个聚集索引,而主键都是唯一的,所以理所当然的认为创建聚集索引的字段也需要唯一。
      
    结论:聚集索引可以创建在任何一列你想创建的字段上,这是从理论上讲,实际情况并不能随便指定,否则在性能上会是恶梦。

    第二:为什么聚集索引可以创建在任何一列上,如果此表没有主键约束,即有可能存在重复行数据呢?
      
    粗一看,这还真是和聚集索引的约束相背,但实际情况真可以创建聚集索引。

    分析其原因是:如果未使用 UNIQUE 属性创建聚集索引,数据库引擎将向表自动添加一个四字节 uniqueifier 列。必要时,数据库引擎 将向行自动添加一个 uniqueifier 值,使每个键唯一。此列和列值供内部使用,用户不能查看或访问。

    第三:是不是聚集索引就一定要比非聚集索引性能优呢?
      
    如果想查询学分在60-90之间的学生的学分以及姓名,在学分上创建聚集索引是否是最优的呢?

    答:否。既然只输出两列,我们可以在学分以及学生姓名上创建联合非聚集索引,此时的索引就形成了覆盖索引,即索引所存储的内容就是最终输出的数据,这种索引在比以学分为聚集索引做查询性能更好。

    第四:在数据库中通过什么描述聚集索引与非聚集索引的?
      
    索引是通过二叉树的形式进行描述的,我们可以这样区分聚集与非聚集索引的区别:聚集索引的叶节点就是最终的数据节点,而非聚集索引的叶节仍然是索引节点,但它有一个指向最终数据的指针。

    第五:在主键是创建聚集索引的表在数据插入上为什么比主键上创建非聚集索引表速度要慢?

    有了上面第四点的认识,我们分析这个问题就有把握了,在有主键的表中插入数据行,由于有主键唯一性的约束,所以需要保证插入的数据没有重复。我们来比较下主键为聚集索引和非聚集索引的查找情况:聚集索引由于索引叶节点就是数据页,所以如果想检查主键的唯一性,需要遍历所有数据节点才行,但非聚集索引不同,由于非聚集索引上已经包含了主键值,所以查找主键唯一性,只需要遍历所有的索引页就行(索引的存储空间比实际数据要少),这比遍历所有数据行减少了不少IO消耗。这就是为什么主键上创建非聚集索引比主键上创建聚集索引在插入数据时要快的真正原因。

    四、何时使用聚集索引或非聚集索引

    动作描述 使用聚集索引 使用非聚集索引
    列经常被分组排序
    返回某范围内的数据 不应
    一个或极少不同值 不应 不应
    小数目的不同值 不应
    大数目的不同值 不应
    频繁更新的列 不应
    外键列
    主键列
    频繁修改索引列 不应

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

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

    1、主键就是聚集索引–错误想法的

    这种想法是极端错误的,是对聚集索引的一种浪费。虽然默认是在主键上建立聚集索引的。

    通常,我们会在每个表中都建立一个ID列,以区分每条数据,并且这个ID列是自动增大的,步长一般为1。如果我们将这个列设为主键,mysql会将此列默认为聚集索引。这样做有好处,就是可以让您的数据在数据库中按照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毫秒

    展开全文
  • 我看书上说,聚集索引是叶子节点存数据;...那**为什么还会有“针对xx字段建立聚集索引还是建立非聚集索引”这种问题啊**? 如果一个表用的是InnoDB,那不管是主键索引还是二级(列)索引都应该是聚集索引啊。
  • 一.索引简介 众所周知,索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构,SQL的主流索引结构有B+树以及Hash结构,聚集...SQL Sever索引类型有:唯一索引,主键索引,聚集索引,非聚集索引。 MySQL

    一.索引简介

    众所周知,索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构,SQL的主流索引结构有B+树以及Hash结构,聚集索引以及非聚集索引用的是B+树索引。

    关于索引的分类,可以查看之前的文章:https://blog.csdn.net/vtopqx/article/details/105563332

    这篇文章会总结SQL Server以及MySQL的InnoDB和MyISAM两种SQL的索引。

    SQL Sever索引类型有:唯一索引,主键索引,聚集索引,非聚集索引。

    MySQL 索引类型有:唯一索引,主键(聚集)索引,非聚集索引,全文索引。

    二.聚集索引

    聚集(clustered)索引,也叫聚簇索引。

    定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。

    单单从定义来看是不是显得有点抽象,打个比方,一个表就像是我们以前用的新华字典,聚集索引就像是拼音目录,而每个字存放的页码就是我们的数据物理地址,我们如果要查询一个“哇”字,我们只需要查询“哇”字对应在新华字典拼音目录对应的页码,就可以查询到对应的“哇”字所在的位置,而拼音目录对应的A-Z的字顺序,和新华字典实际存储的字的顺序A-Z也是一样的,如果我们中文新出了一个字,拼音开头第一个是B,那么他插入的时候也要按照拼音目录顺序插入到A字的后面,现在用一个简单的示意图来大概说明一下在数据库中的样子:

    地址 id username score
    0x01 1 小明 90
    0x02 2 小红 80
    0x03 3 小华 92
    .. .. .. ..
    0xff 256 小英 70

    注:第一列的地址表示该行数据在磁盘中的物理地址,后面三列才是我们SQL里面用的表里的列,其中id是主键,建立了聚集索引。

    结合上面的表格就可以理解这句话了吧:数据行的物理顺序与列值的顺序相同,如果我们查询id比较靠后的数据,那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同,所以也就只能建立一个聚集索引了。




    聚集索引实际存放的示意图

    从上图可以看出聚集索引的好处了,索引的叶子节点就是对应的数据节点(MySQL的MyISAM除外,此存储引擎的聚集索引和非聚集索引只多了个唯一约束,其他没什么区别),可以直接获取到对应的全部列的数据,而非聚集索引在索引没有覆盖到对应的列的时候需要进行二次查询,后面会详细讲。因此在查询方面,聚集索引的速度往往会更占优势。

    创建聚集索引

    如果不创建索引,系统会自动创建一个隐含列作为表的聚集索引。

    1.创建表的时候指定主键(注意:SQL Sever默认主键为聚集索引,也可以指定为非聚集索引,而MySQL里主键就是聚集索引)

    create table t1(
    	id int primary key,
    	name nvarchar(255)
    )
    

    2.创建表后添加聚集索引

    SQL Server

    create clustered index clustered_index on table_name(colum_name)
    

    MySQL

    alter table table_name add primary key(colum_name)
    

    值得注意的是,最好还是在创建表的时候添加聚集索引,由于聚集索引的物理顺序上的特殊性,因此如果再在上面创建索引的时候会根据索引列的排序移动全部数据行上面的顺序,会非常地耗费时间以及性能。

    三.非聚集索引

    非聚集(unclustered)索引。

    定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。

    其实按照定义,除了聚集索引以外的索引都是非聚集索引,只是人们想细分一下非聚集索引,分成普通索引,唯一索引,全文索引。如果非要把非聚集索引类比成现实生活中的东西,那么非聚集索引就像新华字典的偏旁字典,他结构顺序与实际存放顺序不一定一致。




    非聚集索引实际存放的示意图

    非聚集索引的二次查询问题

    非聚集索引叶节点仍然是索引节点,只是有一个指针指向对应的数据块,此如果使用非聚集索引查询,而查询列中包含了其他该索引没有覆盖的列,那么他还要进行第二次的查询,查询节点上对应的数据行的数据。

    如有以下表t1:

    id username score
    1 小明 90
    2 小红 80
    3 小华 92
    .. .. ..
    256 小英 70

    以及聚集索引clustered index(id), 非聚集索引index(username)。

    使用以下语句进行查询,不需要进行二次查询,直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。

    select id, username from t1 where username = '小明'
    select username from t1 where username = '小明'
    

    但是使用以下语句进行查询,就需要二次的查询去获取原数据行的score:

    select username, score from t1 where username = '小明'
    

    在SQL Server里面查询效率如下所示,Index Seek就是索引所花费的时间,Key Lookup就是二次查询所花费的时间。可以看的出二次查询所花费的查询开销占比很大,达到50%。


     

    在SQL Server里面会对查询自动优化,选择适合的索引,因此如果在数据量不大的情况下,SQL Server很有可能不会使用非聚集索引进行查询,而是使用聚集索引进行查询,即便需要扫描整个聚集索引,效率也比使用非聚集索引效率要高。


     

    本人试过在含有30w行表上建立非聚集索引,查询非聚集索引覆盖以外的列就会变成聚集索引的全索引扫描(index scan)查询来避免二次查询,而在另外一张200w行表才会用到非聚集索引seek对应的列再进行kek lookup,有关于SQL Server的有Index seek,index scan, table scan,key LookUp这几个概念,可以查看这个blog,描写比较详细。

    但在MySQL里面就算表里数据量少且查询了非键列,也不会使用聚集索引去全索引扫描,但如果强制使用聚集索引去查询,性能反而比非聚集索引查询要差,这就是两种SQL的不同之处。

    还有一点要注意的是非聚集索引其实叶子节点除了会存储索引覆盖列的数据,也会存放聚集索引所覆盖的列数据。

    如何解决非聚集索引的二次查询问题

    复合索引(覆盖索引)

    建立两列以上的索引,即可查询复合索引里的列的数据而不需要进行回表二次查询,如index(col1, col2),执行下面的语句

    select col1, col2 from t1 where col1 = '213';
    

    要注意使用复合索引需要满足最左侧索引的原则,也就是查询的时候如果where条件里面没有最左边的一到多列,索引就不会起作用。

    在SQL Server中还有include的用法,可以把非聚集索引里包含的列包含进来,而不一定需要建立复合索引。

    四.总结与使用心得

    1. 使用聚集索引的查询效率要比非聚集索引的效率要高,但是如果需要频繁去改变聚集索引的值,写入性能并不高,因为需要移动对应数据的物理位置。
    2. 非聚集索引在查询的时候可以的话就避免二次查询,这样性能会大幅提升。
    3. 不是所有的表都适合建立索引,只有数据量大表才适合建立索引,且建立在选择性高的列上面性能会更好。

    原文转载至:https://www.cnblogs.com/s-b-b/p/8334593.html

    展开全文
  • 微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别...

    一、深入浅出理解索引结构

      实际上,您可以把索引理解为一种特殊的目录。微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别:
      其实,我们的汉语字典的正文本身就是一个聚集索引。比如,我们要查字,就会很自然地翻开字典的前几页,因为的拼音是an,而按照拼音排序汉字的字典是以英文字母a开头并以z结尾的,那么字就自然地排在字典的前部。如果您翻完了所有以a开头的部分仍然找不到这个字,那么就说明您的字典中没有这个字;同样的,如果查字,那您也会将您的字典翻到最后部分,因为的拼音是zhang。也就是说,字典的正文部分本身就是一个目录,您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为聚集索引
      如果您认识某个字,您可以快速地从自动中查到这个字。但您也可能会遇到您不认识的字,不知道它的发音,这时候,您就不能按照刚才的方法找到您要查的字,而需要去根据偏旁部首查到您要找的字,然后根据这个字后的页码直接翻到某页来找到您要找的字。但您结合部首目录检字表而查到的字的排序并不是真正的正文的排序方法,比如您查字,我们可以看到在查部首之后的检字表中的页码是672页,检字表中的上面是字,但页码却是63页,的下面是字,页面是390页。很显然,这些字并不是真正的分别位于字的上下方,现在您看到的连续的驰、张、弩三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为非聚集索引
      通过以上例子,我们可以理解到什么是聚集索引非聚集索引。进一步引申一下,我们可以很容易的理解:每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序。

    二、何时使用聚集索引或非聚集索引

    下面的表总结了何时使用聚集索引或非聚集索引(很重要):

    动作描述

    使用聚集索引

    使用非聚集索引

    列经常被分组排序

    返回某范围内的数据

    不应

    一个或极少不同值

    不应

    不应

    小数目的不同值

    不应

    大数目的不同值

    不应

    频繁更新的列

    不应

    外键列

    主键列

    频繁修改索引列

    不应

      事实上,我们可以通过前面聚集索引和非聚集索引的定义的例子来理解上表。如:返回某范围内的数据一项。比如您的某个表有一个时间列,恰好您把聚合索引建立在了该列,这时您查询200411日至2004101日之间的全部数据时,这个速度就将是很快的,因为您的这本字典正文是按日期进行排序的,聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容。

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

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

    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列上,就像以上的第12种情况,在网页上的表现就是超时,根本就无法显示。这也是我摒弃ID列作为聚集索引的一个最重要的因素。得出以上速度的方法是:在各个select语句前加:

    declare @d datetime set @d=getdate()

    并在select语句后加:

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

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

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

    (1select gid,fariqi,neibuyonghu,title from Tgongwen where fariqi>''2004-5-5''

    查询速度:2513毫秒

    (2select gid,fariqi,neibuyonghu,title from Tgongwen             where fariqi>''2004-5-5'' and neibuyonghu=''办公室''

    查询速度:2516毫秒

    (3select gid,fariqi,neibuyonghu,title from Tgongwen where neibuyonghu=''办公室''

    查询速度:60280毫秒

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

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

    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万条数据,200411日以后的数据有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. use xsgl   
    2. go   
    3.    
    4.   
    5. /*  
    6.   
    7.             聚集索引  
    8.                 1:正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。  
    9.             2. 一个表只能有一个聚集索引,如果一个表有聚集索引,那么表本身就是按照聚集索引排序的  
    10.             3. 一般表中都有一个ID字段,而习惯是将Id做为主键,系统会默认把主键作为聚集索引,然而这样做并不是一定  
    11.                 有效的,因为在有的时候,Id对于我们的查询来说并没有多大用处,我们可以把这个珍贵的资源用到我们经常查询的  
    12.                 字符,比如时间  
    13. */  
    14. select * from student  --因为默认的是ID为聚焦索引,那么排序会按照ID进行排序  
    15.   
    16. create clustered index index_age on student(age)  
    展开全文
  • 一、每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序. 聚集索引一定是建立在: 1、最频繁... 可见,建立非聚集索引需要冗余的表空间,好处就是可以建立多个,但速度要比聚集索引慢。<br /

    一、每个表只能有一个聚集索引,因为目录只能按照一种方法进行排序.

    聚集索引一定是建立在:

    1、最频繁使用的、用以缩小查询范围的字段上。

    2、最频繁使用的、需要排序的字段上。

    聚集索引将一张表中的多行数据按排好的顺序进行物理存储,一个表只能建一个;
    非聚集索引是指向表中的数据并独立于表的一种物理存储结构,一个表可以建多个;
    可见,建立非聚集索引需要冗余的表空间,好处就是可以建立多个,但速度要比聚集索引慢。

    使用聚集索引
    聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿,后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序,因此一个表只能包含一个聚集索引。但该索引可以包含多个列(组合索引),就像电话簿按姓氏和名字进行组织一样。
    聚集索引对于那些经常要搜索范围值的列特别有效。使用聚集索引找到包含第一个值的行后,便可以确保包含后续索引值的行在物理相邻。例如,如果应用程序执行 的一个查询经常检索某一日期范围内的记录,则使用聚集索引可以迅速找到包含开始日期的行,然后检索表中所有相邻的行,直到到达结束日期。这样有助于提高此 类查询的性能。同样,如果对从表中检索的数据进行排序时经常要用到某一列,则可以将该表在该列上聚集(物理排序),避免每次查询该列时都进行排序,从而节 省成本。
    当索引值唯一时,使用聚集索引查找特定的行也很有效率。例如,使用唯一雇员   ID   列   emp_id   查找特定雇员的最快速的方法,是在   emp_id   列上创建聚集索引或   PRIMARY   KEY   约束。
    说明     如果该表上尚未创建聚集索引,且在创建   PRIMARY   KEY   约束时未指定非聚集索引,PRIMARY   KEY   约束会自动创建聚集索引。
    也可以在   lname(姓氏)列和   fname(名字)列上创建聚集索引,因为雇员记录常常是按姓名而不是按雇员   ID   分组和查询的。
    注意事项
    定义聚集索引键时使用的列越少越好,这一点很重要。如果定义了一个大型的聚集索引键,则同一个表上定义的任何非聚集索引都将增大许多,因为非聚集索引条目 包含聚集键。当把   SQL   脚本保存到可用空间不足的磁盘上时,索引优化向导不返回错误
    在分析过程中,索引优化向导会消耗相当多的   CPU   及内存资源。最好在生产服务器的测试版上执行优化,而不要在生产服务器上执行。此外,最好在另一台计算机上而非运行   SQL   Server   的计算机上运行该向导。该向导不能用于在   SQL   Server   6.5   版或更早版本的数据库中选择或创建索引及统计信息。
    在创建聚集索引之前,应先了解您的数据是如何被访问的。可考虑将聚集索引用于:  
    包含大量非重复值的列。
    使用下列运算符返回一个范围值的查询:BETWEEN、> 、> =、 <   和   <=。
    被连续访问的列。
    返回大型结果集的查询。
    经常被使用联接或   GROUP   BY   子句的查询访问的列;一般来说,这些是外键列。对   ORDER   BY   或   GROUP   BY   子句中指定的列进行索引,可以使   SQL   Server   不必对数据进行排序,因为这些行已经排序。这样可以提高查询性能。
    OLTP   类型的应用程序,这些程序要求进行非常快速的单行查找(一般通过主键)。应在主键上创建聚集索引。  
    聚集索引不适用于:  
    频繁更改的列  
    这将导致整行移动(因为   SQL   Server   必须按物理顺序保留行中的数据值)。这一点要特别注意,因为在大数据量事务处理系统中数据是易失的。
    宽键  
    来自聚集索引的键值由所有非聚集索引作为查找键使用,因此存储在每个非聚集索引的叶条目内。
    非聚集索引
    非聚集索引与聚集索引一样有   B   树结构,但是有两个重大差别:  
    数据行不按非聚集索引键的顺序排序和存储。
    非聚集索引的叶层不包含数据页。  
    相反,叶节点包含索引行。每个索引行包含非聚集键值以及一个或多个行定位器,这些行定位器指向有该键值的数据行(如果索引不唯一,则可能是多行)。
    非聚集索引可以在有聚集索引的表、堆集或索引视图上定义。非聚集索引中的行定位器有两种形式:  
    如果表是堆集(没有聚集索引),行定位器就是指向行的指针。该指针用文件标识符   (ID)、页码和页上的行数生成。整个指针称为行   ID。
    如果表没有聚集索引,或者索引在索引视图上,则行定位器就是行的聚集索引键。如果聚集索引不是唯一的索引,SQL   Server   2000   将添加在内部生成的值以使重复的键唯一。用户看不到这个值,它用于使非聚集索引内的键唯一。SQL   Server   通过使用聚集索引键搜索聚集索引来检索数据行,而聚集索引键存储在非聚集索引的叶行内。  
    由于非聚集索引将聚集索引键作为其行指针存储,因此使聚集索引键尽可能小很重要。如果表还有非聚集索引,请不要选择大的列作为聚集索引的键。

    展开全文
  • MySQL系列—聚集索引和非聚集索引

    千次阅读 2017-08-12 11:19:29
    非聚集索引非聚集索引表示数据存储在一个地方,索引存储在另一个地方,索引带有指针指向数据的存储位置,非聚集索引检索效率比聚集索引低,但对数据更新影响较小。 聚集索引 该索引中键值的逻辑顺序决定了表中...
  • 一.索引简介 众所周知,索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构,...SQL Sever索引类型有:唯一索引,主键索引,聚集索引,非聚集索引。 MySQL 索引类型有:唯一索引,主键(聚集)索引...
  • 聚集索引非聚集索引

    2014-05-06 15:19:14
    微软的SQL SERVER提供了两种索引:聚集索引(clustered index,也称聚类索引、簇集索引)和非聚集索引(nonclustered index,也称非聚类索引、非簇集索引)。下面,我们举例来说明一下聚集索引和非聚集索引的区别...
  •  非聚集索引:物理存储不按照索引排序 优势与缺点 聚集索引:插入数据时速度要慢(时间花费在“物理存储的排序”上,也就是首先要找到位置然后插入),查询数据比非聚集数据的速度快 聚集索引的区别  聚集索引:...
  • 一.索引简介 众所周知,索引是关系型数据库中给数据库表中一列或多列的值排序后的存储...SQL Sever索引类型有:唯一索引,主键索引,聚集索引,非聚集索引。 MySQL 索引类型有:唯一索引,主键(聚集)索引,非聚...
  • 详解聚集索引与非聚集索引 最近由于系统的需求,所以要了解一下如何优化sql server,下面是我的一些总结,不足的地方希望大家批评指正。说要优化大家一定会想到索引,那我们就从索引开始吧。 微软的SQL ...
  • SQL建立一个聚集索引与非聚集索引

    千次阅读 2009-11-26 15:34:00
    1.建立一个聚集索引 create unique clustered index IX_1 on tablename(autoid) 2.建立一个非聚集索引 create index IX_2 on tablename(autoid,stu_no) 
  • SQL SERVER提供了两种索引:聚集索引和非聚集索引。其中聚集索引表示表中存储的数据按照索引的顺序存储,检索效率比非聚集索引高,但对数据更新影响较大。非聚集索引表示数据存储在一个地方,索引存储在另一个地方,...
  • 非聚集索引不会重新组织表中的数据,而是对每一行存储索引列值并用一个指针指向数据所在的页面。(一个值指向多行等于该值的数据)。sqlserver默认情况下建立的索引是非聚集索引。非聚集索引在排序时会对所有的取值...
  • 前面两篇文章讲解了一个数据表只存在聚集索引和只存在非聚集索引的情况,接下来我们来讨论一下当聚集索引和非聚集索引同时存在的情况,这种情况也是大多数表都存在的情况。 CREATE TABLE Department11( ...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 36,658
精华内容 14,663
关键字:

怎么建立非聚集索引