SQL Server唯一索引和非唯一索引的区别简析

Question

如题所述

Answer 1

　　SQL Server创建索引时，可以指定Unique使之成为唯一索引。“唯一”顾名思义，但是两都到底有什么区别呢？因为索引也是一种物理结构，所以还是要从存储和结构上分析。

　　索引结构分叶级和非叶级，分析时我们要分开来看，这个很重要。

　　文中涉及的索引行大小计算，参考MSDN估计数据库大小索引部分。

　　1. 非唯一聚集索引和唯一聚集索引

　　创建两个测试表，各10000条整数，tb1唯一，tb2非唯一，有1000条为9999的重复值。

　　view sourceprint?

　　01.<img onclick="this.style.display='none'; document.getElementById('Code_Closed_Text_402704').style.display='none'; document.getElementById('Code_Open_Image_402704').style.display='inline'; document.getElementById('Code_Open_Text_402704').style.display='inline';"id="Code_Closed_Image_402704" align="top" src="" width="11" height="16"style="display: none;"><img alt="加载中..." title="图片加载中..."src="http://www.it165.net/statics/images/s_nopic.gif"><img onclick="this.style.display='none'; document.getElementById('Code_Open_Text_402704').style.display='none'; getElementById('Code_Closed_Image_402704').style.display='inline'; getElementById('Code_Closed_Text_402704').style.display='inline';"id="Code_Open_Image_402704" style="display: none;" align="top" src="" width="11"height="16"><img alt="加载中..." title="图片加载中..."src="http://www.it165.net/statics/images/s_nopic.gif">Codecreate table tb1

　　02.(col1 int);

　　03.declare @i int=1

　　04.while @i<10001

　　05.begin

　　06.insert into tb1 values(@i);

　　07.set @i=@i+1;

　　08.end;

　　09.create unique clustered index ucix on tb1 (col1)

　　10.go

　　11.-------

　　12.create table tb2

　　13.(col2 int);

　　14.declare @i int=1

　　15.while @i<9001

　　16.begin

　　17.insert into tb2 values(@i);

　　18.set @i=@i+1;

　　19.end;

　　20.go

　　21.insert into tb2 values(9999)

　　22.go 1000;

　　23.create clustered index cix on tb2 (col2)

　　24.go

　　先查询索引的一些基本状况:

　　从上面的结果可以看到，无论是叶级还是非叶级，非唯一聚集索引的索引行都比唯一的大一些，所以所占页也多一点。当然，因为测试数据很小，又是int，所以不明显。

　　那到底大在哪里呢？将两者的非叶级页和叶级页放在一起比一下就知道了。先找出页号，再用DBCC PAGE来查看。

　　通过Paul S. Randal写的存储过程sp_allocationMetadata可以查到根页和每级索引的首页。

　　就挑这两个页做对比。

　　发现多出一个UNIQUIFIER，同样叶级也是一样。MSDN说明：

　　“如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。”

　　还有UNIQUIFIER不是一个全局自增列，重复记录增加时此值会发生改变,并且它是一个可为null的变长列。

　　现在来算一算索引行大小：

　　两个表都是只有一个int型可为NULL的字段，而聚集索引叶级是存储数据本身

　　叶级是一个4字节的INT列，无变长列，加上3字节的NULL位图，再加上4字节的行头开销：两个表的叶级minSize =4+0+3+4=11

　　非叶级是一个4字节的INT列，无变长列，加上3字节的NULL位图，加上1字节的行头开销，再加6字节的子页指针：两个表的非叶级minSize=4+0+3+1+6=14

　　tb1的索引行大小是一致的minSize=maxSize,因为它是唯一的。tb2的索引行大小不一致，有大有小，大的索引行是因为：a)不唯一 b)UNIQUIFIER

　　唯一标识列增加了2+1*2+4=8字节开销，tb2的min和max相差就是这8字节。

　　tb2的叶级maxSize=4+8+3+4=19

　　tb2的非叶级maxSize=4+8+3+1+6=22

　　小结：非唯一聚集索引为保证索引键值唯一性，会生成UNIQUIFIER与键列一起组成索引键值。同时无论在叶级还是非叶页级，都比唯一索引占用更多存储空间。

Answer 2

这里说的聚集索引是聚簇索引吧。。。 聚簇索引即建立在聚簇上的索引，创建聚簇索引时，需要对已有表数据重新进行排序（若表中已有数据），即删除原始的表数据后再将排序结果按物理顺序插回，故聚簇索引建立完毕后，建立聚簇索引的列中的数据已经全部按序排列。 一个表中只能包含一个聚簇索引，但该索引可以包含多个列。 B-树索引中，聚簇索引的叶层就是数据页。 非聚簇索引类似书本索引，索引与数据存放在不同的物理区域，建立非聚簇索引时数据本身不进行排序。一个表中科含多个非聚簇索引。 B-树索引中，非聚簇索引的叶层仍是索引页，其以指针指向数据页实际存储位置。 唯一性索引保证表中没有两行在定义索引的列上具有重复值，ORACLE自动为主键和唯一键列创建唯一索引；主键本身就是唯一索引，反之不成立（唯一索引允许一个NULL值），唯一性索引比非唯一性索引效率高，故在一般情况下，在无重复值的列上应尽量建立唯一性索引。 若为谋个表的某个列创建了唯一索引，则即使这个列没有唯一值约束，也会被强制限制不能插入重复记录。

Answer 3

顾名思义，
唯一索引，就是是不允许其中任何两行具有相同索引值的索引，即不允许索引列出现重复值。
非唯一索引，在索引列中可以出现重复值。
祝你成功！