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

最新推荐文章于 2023-10-20 18:04:38 发布
最新推荐文章于 2023-10-20 18:04:38 发布
阅读量4.5k 收藏 2
点赞数
分类专栏： 数据库 sqlServer

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

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

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

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

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

    view source print ?
    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

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

    image

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

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

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

    image

    就挑这两个页做对比。

    image

    发现多出一个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.堆表上的唯一和非唯一的非聚集索引

    view source print ?
    01. <img onclick="this.style.display='none'; document.getElementById('Code_Closed_Text_431847').style.display='none'; document.getElementById('Code_Open_Image_431847').style.display='inline'; document.getElementById('Code_Open_Text_431847').style.display='inline';"id="Code_Closed_Image_431847" 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_431847').style.display='none'; getElementById('Code_Closed_Image_431847').style.display='inline'; getElementById('Code_Closed_Text_431847').style.display='inline';"id="Code_Open_Image_431847" 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 IndexTest
    02. (id int identity,
    03. UniqueCol int,
    04. NonuniqueCol int)
    05. go
    06. set nocount on;
    07. declare @i int=1;
    08. while @i<100000
    09. begin
    10. insert into IndexTest
    11. values(@i,@i);
    12. set @i=@i+1;
    13. end
    14. set nocount off;
    15. go
    16. create unique index UIX_UniqueCol on IndexTest (UniqueCol);
    17. create index IX_NonuniqueCol on IndexTest (NonuniqueCol);
    18. go
    19. select i.name,ips.index_id,ips.index_type_desc,index_depth,index_level,page_count,record_count,
    20. min_record_size_in_bytes as minSize,max_record_size_in_bytes as maxSize,avg_record_size_in_bytes as avgSize
    21. from sys.dm_db_index_physical_stats(DB_ID('test'),OBJECT_ID('IndexTest'),null,null,'DETAILED') ips
    22. inner join sys.indexes i
    23. on ips.object_id=i.object_id and ips.index_id=i.index_id
    24. order by name,index_level

    image

    两者的页级大小是一样的，非叶级页的相差8bytes。

    跟1.中的分析方法一样，挑两个非叶级页出来对比一下，看相差在哪里。

    image

    非唯一索引行多了一个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列改成聚集主键。执行：

    view source print ?
    1. <img onclick="this.style.display='none'; document.getElementById('Code_Closed_Text_597393').style.display='none'; document.getElementById('Code_Open_Image_597393').style.display='inline'; document.getElementById('Code_Open_Text_597393').style.display='inline';"id="Code_Closed_Image_597393" 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_597393').style.display='none'; getElementById('Code_Closed_Image_597393').style.display='inline'; getElementById('Code_Closed_Text_597393').style.display='inline';"id="Code_Open_Image_597393" style="display: none;" align="top" src="" width="11"height="16"><img alt="加载中..." title="图片加载中..."src="http://www.it165.net/statics/images/s_nopic.gif">Code alter table IndexTest add constraint PK_IndexTest primary key clustered (ID)

    image

    这里有意思的是聚集索引的非叶级行只有11字节，跟同样的1.中的14相差了3字节。这3字节是因为现在这个表，索引列是自增主键，是不能为NULL的，所以就没有NULL位图的3个字节的开销了。

    两者的叶级行大小一样，看一下长的是不是一样：
    image

    两者的区别在于对聚集索引键的引用上，即“id”和“id(key)”。MSDN说明：

    “如果非聚集索引不是唯一的，数据行定位符将与非聚集索引键组合使用，以便为每一行生成唯一的键值。如果非聚集索引在聚集索引之上，则数据行定位符是聚集键。”

    唯一非聚集索引中的“id”是做为行定位符引用的，非唯一非聚集索引中的“id(key)”,不仅是做为行定位符引用，并且还是此索引本身键列(“key”的含义)。

    非叶级行相差4个字节，对比一下页的内容就知道差在哪里：

    image

    从上图看出非唯一索引比唯一的多了一列”id(key)”,这是前者引用了聚集索引键并做做自己的键列，而唯一索引不需要这样。

    小结：唯一聚集索引表上的非唯一非聚集索引与唯一非聚集索引，在叶级上大小是一样的，在非叶级行多引用一列“聚集键”，所以前者占用的存储空间也会更大一些。

    4. 非唯一聚集索引表上的唯一和非唯一非聚集索引

      测试数据中给制造了一些重复数据。

    view source print ?
    01. <img onclick="this.style.display='none'; document.getElementById('Code_Closed_Text_565480').style.display='none'; document.getElementById('Code_Open_Image_565480').style.display='inline'; document.getElementById('Code_Open_Text_565480').style.display='inline';"id="Code_Closed_Image_565480" 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_565480').style.display='none'; getElementById('Code_Closed_Image_565480').style.display='inline'; getElementById('Code_Closed_Text_565480').style.display='inline';"id="Code_Open_Image_565480" 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 IndexTest
    02. (id int ,
    03. UniqueCol int,
    04. NonuniqueCol int)
    05. go
    06. set nocount on;
    07. declare @i int=1;
    08. while @i<100000
    09. begin
    10. insert into IndexTest
    11. values(@i,@i,@i);
    12. set @i=@i+1;
    13. end
    14. set nocount off;
    15. go
    16. create clustered index CIX_ID on IndexTest(ID);
    17. create unique index UIX_UniqueCol on IndexTest (UniqueCol);
    18. create index IX_NonuniqueCol on IndexTest (NonuniqueCol);
    19. go
    20. update IndexTest
    21. set NonuniqueCol=99999,id=99999
    22. where id>90000
    23. go

    image

    由前文的分析可知上图所有索引的minSize和maxSize相差8字节都是由UNIQUEIFIER产生。

    两个非聚集索引的叶级行一样大，原因跟3. 中分析的一样，只是两者除了引用聚集键之外，还会引用跟聚集键结合使用的UNIQUEIFIER。

    但是非叶级页两差有着较大的差异，查看页面：

    image

    从页面内容可以看出，非唯一非聚集比唯一多了两列，其它这两列可以看成一列，原因就是当聚集索引不唯一时，会生成UNIQUIFIER并结合，用以保证聚集键唯一。

    小结：非唯一聚集索引表上的非唯一非聚集索引在叶级行上大小是一样的，而在非叶级行上前者比后大，所以也占用更多存储空间。

    总结：

      1. 在表和索引设计阶段，如果可能，字段设定为不允许NULL，索引设定为唯一。这样节约存储空间并提高了IO效率。

      2. 聚集索引键列应该尽量选用窄的字段，因为非聚集索引会引用其键列。如果聚集键过大则会使非聚集索引同时也占用更多存储空间。

      3. SQL Server在创建索引时，默认是创建非唯一的。所以在创建索引时，要认真考虑是否可以创建为唯一索引。

      4. 文中用词有些绕。

scalad
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
SQL Server 聚集索引和非聚集索引的区别分析
09-11
聚集索引:物理存储按照索引排序 非聚集索引:物理存储不按照索引排序
sqlserver 聚集索引和非聚集索引实例
09-11
sqlserver 聚集索引和非聚集索引实例,学习sqlserver索引的朋友可以参考下。
sql server 索引详细介绍
2301_79082949的博客
07-12 1231
在选择索引键时，尽可能采用小数据类型的列作为键以使每个索引页能容纳尽可能多的索引键和指针，通过这种方式，可使一个查询必需遍历的索引页面降低到最小，此外，尽可能的使用整数做为键值，因为整数的访问速度最快。OFF 或 fillfactor 未指定，考虑到中间级页上的键集，将中间级页填充到接近其容量的程度，以留出足够的空间，使之至少能够容纳索引的最大的一行。视图的聚集索引必须唯一。索引分为聚集索引和非聚集索引，数据库中的索引类似于一本书的目录，在一本书中通过目录可以快速找到你想要的信息，而不需要读完全书。
SQL Server 中常见的索引类型
Xia13225498444的博客
07-07 2564
覆盖索引是一种非聚集索引，它包含了查询所需的所有列，因此查询可以直接从索引中获取所需的数据，而无需访问实际的数据页。- 空间索引用于处理具有空间数据（如地理坐标、几何形状）的列，以支持空间查询和分析操作，如距离计算、相交检查等。- 非聚集索引不会改变表中数据的物理存储顺序，而是创建一个独立的索引结构以提高查询性能。- 聚集索引确定表中数据的物理存储顺序，并且表中只能有一个聚集索引。- 唯一索引确保索引列中的值是唯一的，可以用于主键或唯一约束。- 唯一索引可以是聚集索引或非聚集索引。
猿思考系列7——索引不就那么点儿事儿？
hzldds2019的博客
04-28 165
看完上一个章节，相信你已经充分的掌握了数据库事务的一些事情，猿人工厂君也知道，内容对于新手而言，理解起来还是比较很吃力的，文中提到的原理和内容，有兴趣的可以和我一起探讨，猿人工厂君就不一...
唯一索引和非唯一索引的区别简析
baicongli9053的博客
02-28 440
SQL Server创建索引时，可以指定Unique使之成为唯一索引。“唯一”顾名思义，但是两都到底有什么区别呢？因为索引也是一种物理结构，所以还是要从存储和结构上分析。 索引结构分叶级和非叶级，分析时我们要分开来看，这个很重要。 文中涉及的索引行大小计算，参考MSDN估计数据库大小索引部分。 1. 非唯一聚集索引和唯一聚集索引 创建两个测试表，各10000条整数，tb1唯一，tb2...
【转载】深入理解四种数据库索引类型（- 唯一索引/非唯一索引 - 主键索引（主索引） - 聚集索引/非聚集索引 - 组合索引）...
weixin_33701294的博客
01-15 1009
原文链接：https://www.jianshu.com/p/f49ee80c4956 唯一索引/非唯一索引 主键索引（主索引） 聚集索引/非聚集索引 组合索引 唯一索引/非唯一索引 唯一索引 1.唯一索引是在表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中不可以重复。 非唯一索引 2.非唯一索引是在表上一个或者多个字段组合建立的索引，这个或者这...
唯一索引和非唯一索引
u014429186的博客
11-13 7201
 从Oracle索引的特征上，我们可以简单的把索引分为Unique Indexes and Nonunique Indexes，即唯一索引和非唯一索引。 1      对于唯一索引，唯一索引确保被索引的字段或多个联合字段在表中绝对不会有重复值；通常，我们在建表时，创建唯一约束或者主键约束，再或者建表以后给表添加唯一、主键约束时，Oracle会自动在主键、唯一约束的字段上创建唯一索引，并且索
SQL Server唯一索引UNIQUE和聚集索引、非聚集索引的区别
小易的坐骑
11-17 4766
<br />索引 实际上是一组指向表中数据的指针,索引的排列顺序其实就是这组指针的顺序. <br />聚集索引 :表的物理存储顺序与指针(即逻辑)顺序相同 <br />非聚集索引 :物理与逻辑顺序不同 <br /><br /><br />因为一个表只能有一个物理顺序,所以,聚集索引的个数最多只能是1. <br /><br /><br />唯一索引 是给所做的索引增加了唯一性的约束,添加,修改索引列中数据时,不允许出现重复值.它可以是聚集索引,也可以是非聚集的,就看你如何定义
唯一索引（unique index）和非唯一索引（普通索引）（index） 区别
weixin_33939380的博客
11-03 2522
如果在一个列上同时建唯一索引和普通索引的话，mysql会自动选择唯一索引。 谷歌一下： 唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。 补充下概念：1、普通索引(非唯一索引)　　普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（O...
浅析SQL Server 聚焦索引对非聚集索引的影响
09-09
本篇文章对SQL Server的聚焦索引和非聚集索引进行简单分析，从而总结出聚焦索引对非聚集索引的影响。有兴趣的朋友可以看下
SQL server 数据库之“索引”详解
a55555acn的博客
04-09 1176
什么是索引？ 数据库中的索引与书籍中的目录类似，索引使SQL Server编排数据的内部方法，它为SQL Server提供一种方法来编排查询数据的路由。 索引页是数据中存储索引的数据页。索引页存放检索数据行的关键字页及该数据行的地址指针。通过使用索引，可以大大提高数据库的检索速度、改善数据库性能。 索引的分类 1、唯一索引 唯一索引不允许两行具有相同的索引值。创建了唯一约束，将自动创建唯一索引。尽...
SQLServe联合主键、联合索引、唯一索引，聚集索引，和非聚集索引、主键、唯一约束和外键约束、索引运算总结
最新发布
munangs的博客
10-20 3532
介绍了以下的索引、主键、约束的创建、使用和注意事项：SQLServe联合主键、联合索引、唯一索引，聚集索引，和非聚集索引、主键、唯一约束和外键约束、索引运算总结；索引/键 表设计器 数据空间规范和ON [PRIMARY]介绍
Sql Server索引
jt3056的专栏
09-08 454
1.创建索引时遵循一般原则   在需要经常搜索的列上面创建索引   在主键上创建索引   在外键上创建索引   在经常用于Where子句的列上创建索引   在经常需要排序的列上创建索引   对于数据类型为text，image，bit的列不应该增加索引   当需要Update性能远高于Select性能时不应该增加索引 2.索引分类 聚集索引：物理存储“按照”索引排序；每个表“只能有
数据库索引简介（三）sql server索引类型
syq8023的博客
02-19 5749
1、索引类型 1）唯一索引（UNIQUE）：唯一索引不允许两行具有相同的索引值； 2）主键索引：为表定义一个主键将自动创建主键索引，主键索引是唯一索引的特殊类型。主键索引要求主键中的每个值是唯一的，并且不能为空； 3）聚集索引(Clustered)：表中各行的物理顺序与键值的逻辑（索引）顺序相同，每个表最多只能有一个； 4）非聚集索引(NonClustered)：非聚集索引指定表的逻辑顺序。数据存储在一个位置，索引存储在另一个位置，索引中包含指向数据存储位置的指针。可以有多个，小于249个。 唯一
唯一索引和非唯一索引_唯一索引及其依据
culi3182的博客
08-16 1251
唯一索引和非唯一索引This article is inspired by Craig's recent post "How to Use Unique Indexes in MySQL and Other Databases". 本文的灵感来自于Craig的最新文章“ 如何在MySQL和其他数据库中使用唯一索引 ”。 There are a few more tightly related...
数据库唯一索引和普通索引的区别？
u014230945的博客
11-13 962
1、如果业务字段更新频繁的化，选择唯一索引还是普通索引？ 如果业务上能保证唯一性的化尽量使用普通索引而不是唯一索引。 2、针对查询而言普通索引的查询是在查询到符合条件的内容以后，继续进行下一项内容查询，如果下一项不符合则返回；但是唯一索引的查询是查询到满足条件的内容直接返回，整体上少一次查询比较，但是整体效率相差不大。 3、针对更新操作来说两者就有很大的差距了。 要在更新操作上理解两者之间的差距首先理解一下change buffer。 change buffer...
sqlserver 索引(聚合索引和非聚合索引)的创建、删除、查询
liufeifeinanfeng的博客
08-01 4637
--查询索引 sp_helpindex t_test select * from sysindexes where id = OBJECT_ID('t_test') --创建主键同样会创建索引(聚合索引) alter table 表名 add constraint 主键名称 primary key(id) --删除主键 alter table 表名 drop constraint 主键...
MySQL学习笔记（15-28）
m0_60264772的博客
11-28 3851
目录 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 索引的优劣 1.优势 类似大学图书馆图书编号建索引，提高了数据检索的效率，降低数据库的IO成本 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗 2.劣势 实际上索引也是一张表，该表保存了主键与存在索引的字段，并指向实体表的记录，所以索引列也是占用空间的 虽然索引大大提高了查询速度，但是会降低更新表的速度，比如 update，insert，delete操作，因为更新表时，My
sqlserver怎么创建非唯一索引
05-25
在 SQL Server 中，可以使用 CREATE INDEX 语句来创建索引。要创建非唯一索引，需要在 CREATE INDEX 语句中指定 NONCLUSTERED 关键字。 以下是一个示例 CREATE INDEX 语句，用于在 SQL Server 中创建一个名为“idx_lastName”的非唯一索引，该索引基于“lastName”列： ``` CREATE NONCLUSTERED INDEX idx_lastName ON tableName (lastName); ``` 其中，tableName 是要为其创建索引的表的名称。 请注意，非唯一索引允许重复值，因此如果您要在表中创建唯一性约束，则应使用唯一索引。如果要在 CREATE INDEX 语句中创建唯一索引，则需要在索引名称前加上 UNIQUE 关键字。例如： ``` CREATE UNIQUE NONCLUSTERED INDEX idx_lastName ON tableName (lastName); ```

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值