SQLServer唯一索引和非唯一索引的区别简析.doc

SQL Server唯一索引和非唯一索引的区别简析SQL Server创建索引时，可以指定Unique使之成为唯一索引。“唯一”顾名思义，但是两都到底有什么区别呢？因为索引也是一种物理结构，所以还是要从存储和结构上分析。索引结构分叶级和非叶级，分析时我们要分开来看，这个很重要。文中涉及的索引行大小计算，参考MSDN估计数据库大小索引部分。1. 非唯一聚集索引和唯一聚集索引 创建两个测试表，各10000条整数，tb1唯一，tb2非唯一，有1000条为9999的重复值。Codecreate table tb1(col1 int);declare i int=1while i10001begin insert into tb1 values(i); set i=i+1; end;create unique clustered index ucix on tb1 (col1)go-create table tb2(col2 int);declare i int=1while i9001begin insert into tb2 values(i); set i=i+1; end;goinsert into tb2 values(9999)go 1000;create clustered index cix on tb2 (col2)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与键列一起组成索引键值。同时无论在叶级还是非叶页级，都比唯一索引占用更多存储空间。2.堆表上的唯一和非唯一的非聚集索引Codecreate table IndexTest(id int identity, UniqueCol int, NonuniqueCol int) go set nocount on; declare i int=1; while i100000 begin insert into IndexTest values(i,i); set i=i+1; end set nocount off; go create unique index UIX_UniqueCol on IndexTest (UniqueCol); create index IX_NonuniqueCol on IndexTest (NonuniqueCol);go select ,ips.index_id,ips.index_type_desc,index_depth,index_level,page_count,record_count,min_record_size_in_bytes as minSize,max_record_size_in_bytes as maxSize,avg_record_size_in_bytes as avgSizefrom sys.dm_db_index_physical_stats(DB_ID(test),OBJECT_ID(IndexTest),null,null,DETAILED) ipsinner join sys.indexes ion ips.object_id=i.object_id and ips.index_id=i.index_idorder by name,index_level两者的页级大小是一样的，非叶级页的相差8bytes。跟1.中的分析方法一样，挑两个非叶级页出来对比一下，看相差在哪里。非唯一索引行多了一个HEAP RID，MSDN说明：“如果非聚集索引不是唯一的，数据行定位符将与非聚集索引键组合使用，以便为每一行生成唯一的键值。如果非聚集索引在堆上，则数据行定位符是堆 RID。其大小是 8 个字节。” 两者叶级索引行大小=INT型4字节+无变长列+1字节行头+3字节NULL位图+8字节RID=4+0+1+3+8=16 唯一索引的非叶级行=INT型4字节+无变长列+1字节行头+3字节NULL位图+6字节子页索引=14 非唯一索引的非叶级行=INT型4字节+无变长列+1字节行头+3字节NULL位图+6字节子页索引+8字节的RID=22 小结：堆表上的非唯一索引在非叶级索引行上比唯一索引多出一列行定位符RID，而叶级是一样的，都有RID列。所以非唯一要占用更多的空间。 3.唯一聚集索引表上的唯一和非唯一非聚集索引跟2.中的测试数据一样，只是把ID列改成聚集主键。执行：Code这里有意思的是聚集索引的非叶级行只有11字节，跟同样的1.中的14相差了3字节。这3字节是因为现在这个表，索引列是自增主键，是不能为NULL的，所以就没有NULL位图的3个字节的开销了。两者的叶级行大小一样，看一下长的是不是一样：两者的区别在于对聚集索引键的引用上，即“id”和“id(key)”。MSDN说明：“如果非聚集索引不是唯一的，数据行定位符将与非聚集索引键组合使用，以便为每一行生成唯一的键值。如果非聚集索引在聚集索引之上，则数据行定位符是聚集键。”唯一非聚集索引中的“id”是做为行定位符引用的，非唯一非聚集索引中的“id(key)”,不仅是做为行定位符引用，并且还是此索引本身键列(“key”的含义)。非叶级行相差4个字节，对比一下页的内容就知道差在哪里：从上图看出非唯一索引比唯一的多了一列”id(key)”,这是前者引用了聚集索引键并做做自己的键列，而唯一索引不需要这样。小结：唯一聚集索引表上的非唯一非聚集索引与唯一非聚集索引，在叶级上大小是一样的，在非叶级行多引用一列“聚集键”，所以前者占用的存储空间也会更大一些。4. 非唯一聚集索引表上的唯一和非唯一非聚集索引 测试数据中给制造了一些重复数据。Codealter table IndexTest add constraint PK_IndexTest primary key clustered (ID)由前文的分析可知上图所有索引的minSize和maxSize相差8字节都是由UNIQUEIFIER产生。两个非聚集索引的叶级行一样大，原因跟3. 中分析的一样，只是两者除了引用聚集键之外，还会引用跟聚集键结合使用的UNIQUEIFIER。但是非叶级页两差有着较大的差异，查看页面：从页面内容可以看出，非唯一非聚集比唯一多了两列，其它这两列可以看成一列，原因就是当聚集索引不唯一时，会生成UNIQUIFIER并结合，用以保证聚集键唯一。小结：非唯一聚集索引表上的非唯一非聚集索引在叶级行上大小是一样的，而在非叶级行上前者比后大，所以也占用更多存