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

聚集索引和非聚集索引的区别:
举个最简单的例子,聚集索引就是你查询到叶子节点时,叶子节点存储的就是你想要的数据,直接拿走就可以了。
非聚集索引就是叶子节点存储的就是你想要的数据的一个索引,你需要再回表查询多一次才能拿到想要的数据。

注意:一个表只能有一个聚集索引,即只能按照一种顺序来排列。没有明确指出,默认是主键。
理解:就好比汉语字典的正文编排顺序就是按照字母a-z顺序来排序的,此时的字母就是一个聚集索引,你在目录中使用字母进行查询,得到的就是在正文中的排序,就是你想要的数据。
当然字典目录还有一种偏旁部首排序,比如您查“张”字,我们可以看到在查部首之后的检字表中“张”的页码是672页,检字表中“张”的上面是“驰”字,但页码却是63页,“张”的下面是“弩”字,页面是390页。很显然,这些字并不是真正的分别位于“张”字的上下方,现在您看到的连续的“驰、张、弩”三字实际上就是他们在非聚集索引中的排序,是字典正文中的字在非聚集索引中的映射。我们可以通过这种方式来找到您所需要的字,但它需要两个过程,先找到目录中的结果,然后再翻到您所需要的页码。我们把这种目录纯粹是目录,正文纯粹是正文的排序方式称为“非聚集索引”。

何时适合使用聚集索引和非聚集索引:
在这里插入图片描述
补充:面试被问到时间列和状态列哪个更适合做索引?
显然是时间列更适合,比如您的某个表有一个时间列,恰好您把聚合索引建立在了该列,这时您查询2004年1月1日至2004年10月1日之间的全部数据时,这个速度就将是很快的,因为您的这本字典正文是按日期进行排序的,聚类索引只需要找到要检索的所有数据中的开头和结尾数据即可;而不像非聚集索引,必须先查到目录中查到每一项数据对应的页码,然后再根据页码查到具体内容。

聚集索引的误区:
主键就是聚集索引吗?
首先,主键的创建一定是依赖于索引的,但是到底是依赖哪种索引呢?如果你创建表的时候,没有显式的指出哪种索引,MySQL会默认创建的是聚集索引,这就是导致了我们经常所说的主键就是聚集索引这个误区。
明确声明主键依赖的是非聚集索引:

CREATE TABLE student
(
    stud_id INT IDENTITY(1,1) NOT NULL,
    stud_name NVARCHAR(20) NOT NULL,
    CONSTRAINT pk_student PRIMARY KEY NONCLUSTERED (stud_id)
);

通常,我们会在每个表中都建立一个ID列,以区分每条数据,并且这个ID列是自动增大的,步长一般为1。如果把这个列设为主键,MySQL会默认此列为聚集索引。

而一个表只能有一个聚集索引,所以聚集索引显得异常珍贵。如果我们把聚集索引用在ID列上,实际意义并不大。因为表的ID都是随机生成或自动生成的,我们并不知道每条记录的ID号,所以在实践中我们很难使用ID号进行查询,这就造成了这个聚集索引的浪费。

所以一个表的聚集索引的选择,我们通常会出现在where语句中的热点数据进行选择。

下面我们就来看一下在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列作为聚集索引的一个最重要的因素。

当然对于多表关联查询的话,比如student表的id作为teacher表的外键,如果通过查询teacher所教的学生,那么student表的id列作为聚集索引会提高查询速度。

补充:唯一索引实际上就是要求指定的列中所有的数据必须不同。

主键一唯一索引的区别:

     1 一个表的主键只能有一个,而唯一索引可以建多个。
     2 主键可以作为其它表的外键。
     3 主键不可为null,唯一索引可以为null。
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值