SQL有三个类型的索引,唯一索引 不能有重复,但聚集索引,非聚集索引可以有重复

重要:

(1) SQL如果创建时候,不指定类型那么默认是非聚集索引

(2) 聚集索引和非聚集索引都可以有重复记录,唯一索引不能有重复记录。

(3) 主键 默认是加了唯一约束的聚集索引,但是也可以在主键创建时,指定为唯一约束的非聚集索引,因此主键仅仅是默认加了唯一约束的聚集索引,不能说主键就是加了唯一约束的聚集索引

有点拗口,可以参考我的博客:主键就是聚集索引吗?

为列创建索引实际上就是为列进行排序,以方便查询.建立一个列的索引,就相当与建立一个列的排序。

    主键是唯一的,所以创建了一个主键的同时,也就这个字段创建了一个唯一的索引,

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

    主键一唯一索引的区别:

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

聚集索引:将表内的数据按照一定的规则进行排列的目录。正因为如此,一个表中的聚焦索引只有一个。对此我们要注意“主键就是聚焦索引”这是极端错误的,是对聚焦索引的一种浪费。(虽然SQLServer默认主键就是聚焦索引)使用聚焦索引的最大好处就是按照查询要求,迅速缩小查询范围,避免进行全表扫描。其次让每个数目都不相同的字段作为聚焦索引也不符合“大数目不同情况下不应建立聚集索引的原则”。

一、索引的作用

1、帮助检索数据;

2、提高联接效率;

3、节省ORDER BY、GROUP BY的时间;

4、保证数据唯一性(仅限于唯一索引)。

 

二、索引的设计

在确定要建立一个索引时,首先我们要确定它是聚集还是非聚集、单列还是多列、唯一还是非唯一、列是升序还是降序、它的存储是如何的,比如:分区、填充因子等。下面逐条来看:

1、聚集索引

(1)首先指出一个误区,主键并不一定是聚集索引,只是在SQL SERVER中,未明确指出的情况下,默认将主键定义为聚集,而Oracle中则默认是非聚集,因为SQL SERVER中的ROWID未开放使用。

(2)聚集索引适合用于需要进行范围查找的列,因为聚集索引的叶子节点存放的是有序的数据行,查询引擎可根据WHERE中给出的范围,直接定位到两端的叶子节点,将这部分节点页的数据根据链表顺序取出即可;

(3)聚集索引尽量建立在值不会发生变更的列上,否则会带来非聚集索引的维护;

(4)尽量在建立非聚集索引之前建立聚集索引,否则会导致表上所有非聚集索引的重建;

(5)聚集索引应该避免建立在数值单调的列上,否则可能会造成IO的竞争,以及B树的不平衡,从而导致数据库系统频繁的维护B树的平衡性。聚集索引的列值最好能够在表中均匀分布。

3、唯一索引

(1)再指出一个误区,聚集索引并不一定是唯一索引,由于SQL SERVER将主键默认定义为聚集索引,事实上,索引是否唯一与是否聚集是不相关的,聚集索引可以是唯一索引,也可以是非唯一索引;

(2)将索引设置为唯一,对于等值查找是很有利的,当查到第一条符合条件的纪录时即可停止查找,返回数据,而非唯一索引则要继续查找,同样,由于需要保证唯一性,每一行数据的插入都会去检查重复性;

 

下面是一个简单的比较表

 

 

  主键 聚集索引
用途 强制表的实体完整性 对数据行的排序,方便查询用
一个表多少个 一个表最多一个主键 一个表最多一个聚集索引
是否允许多个字段来定义 一个主键可以多个字段来定义 一个索引可以多个字段来定义
     
是否允许 null 数据行出现 如果要创建的数据列中数据存在null,无法建立主键。
创建表时指定的 PRIMARY KEY 约束列隐式转换为 NOT NULL。
没有限制建立聚集索引的列一定必须 not null .
也就是可以列的数据是 null
参看最后一项比较
是否要求数据必须唯一 要求数据必须唯一 数据即可以唯一,也可以不唯一。看你定义这个索引的 UNIQUE 设置。
(这一点需要看后面的一个比较,虽然你的数据列可能不唯一,但是系统会替你产生一个你看不到的唯一列)
     
创建的逻辑 数据库在创建主键同时,会自动建立一个唯一索引。
如果这个表之前没有聚集索引,同时建立主键时候没有强制指定使用非聚集索引,则建立主键时候,同时建立一个唯一的聚集索引
如果未使用 UNIQUE 属性创建聚集索引,数据库引擎 将向表自动添加一个四字节 uniqueifier 列。
必要时,数据库引擎 将向行自动添加一个 uniqueifier 值,使每个键唯一。此列和列值供内部使用,用户不能查看或访问。
 
posted @ 2018-05-03 09:46  aspirant  阅读(14616)  评论(0编辑  收藏  举报