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

最新推荐文章于 2024-10-09 23:12:44 发布
最新推荐文章于 2024-10-09 23:12:44 发布
阅读量4.7k 收藏 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. 文中用词有些绕。

唯一索引（unique index）和非唯一索引（普通索引）（index） 区别
weixin_33939380的博客
11-03 2932
如果在一个列上同时建唯一索引和普通索引的话，mysql会自动选择唯一索引。 谷歌一下： 唯一索引和普通索引使用的结构都是B-tree,执行时间复杂度都是O(log n)。 补充下概念：1、普通索引(非唯一索引)　　普通索引（由关键字KEY或INDEX定义的索引）的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHEREcolumn=）或排序条件（O...
shp2sqlserver 用法简析
09-11
该工具的工作原理类似于 PostGIS 的 `shp2pgsql`，但其区别在于 `shp2sqlserver` 直接将数据写入数据库而非生成 SQL 脚本。这意味着用户可以直接在 SQL Server 中处理 Shapefile 数据而无需编写额外的脚本来导入数据...
唯一索引和非唯一索引的区别简析
baicongli9053的博客
02-28 516
SQL Server创建索引时，可以指定Unique使之成为唯一索引。“唯一”顾名思义，但是两都到底有什么区别呢？因为索引也是一种物理结构，所以还是要从存储和结构上分析。 索引结构分叶级和非叶级，分析时我们要分开来看，这个很重要。 文中涉及的索引行大小计算，参考MSDN估计数据库大小索引部分。 1. 非唯一聚集索引和唯一聚集索引 创建两个测试表，各10000条整数，tb1唯一，tb2...
【SQL】深入了解 SQL 索引：数据库性能优化的利器
最新发布
人生苦短，睡觉要紧，睡醒再说
10-09 2093
SQL 索引是一种特殊的数据结构，通过指针将数据位置与索引键关联起来，使得查询操作更加高效，用于提高数据库表中数据检索的速度。可以将索引看作是书籍的目录，帮助快速找到所需的信息。合理地创建和使用索引，可以大幅度提升查询效率，但过多或不当的索引会影响数据修改的性能。SQL 索引是提高数据库性能的重要工具，但需要合理使用。了解索引的类型、工作原理及其优缺点，将帮助开发者在设计数据库时做出更明智的决策。本文将系统地介绍 SQL 索引的相关知识，包括索引的种类、创建方法、使用场景及其维护与优化技巧。
【转载】深入理解四种数据库索引类型（- 唯一索引/非唯一索引 - 主键索引（主索引） - 聚集索引/非聚集索引 - 组合索引）...
weixin_33701294的博客
01-15 1141
原文链接：https://www.jianshu.com/p/f49ee80c4956 唯一索引/非唯一索引 主键索引（主索引） 聚集索引/非聚集索引 组合索引 唯一索引/非唯一索引 唯一索引 1.唯一索引是在表上一个或者多个字段组合建立的索引，这个或者这些字段的值组合起来在表中不可以重复。 非唯一索引 2.非唯一索引是在表上一个或者多个字段组合建立的索引，这个或者这...
唯一索引和非唯一索引
u014429186的博客
11-13 7332
 从Oracle索引的特征上，我们可以简单的把索引分为Unique Indexes and Nonunique Indexes，即唯一索引和非唯一索引。 1      对于唯一索引，唯一索引确保被索引的字段或多个联合字段在表中绝对不会有重复值；通常，我们在建表时，创建唯一约束或者主键约束，再或者建表以后给表添加唯一、主键约束时，Oracle会自动在主键、唯一约束的字段上创建唯一索引，并且索
SQL Server唯一索引UNIQUE和聚集索引、非聚集索引的区别
小易的坐骑
11-17 4862
<br />索引 实际上是一组指向表中数据的指针,索引的排列顺序其实就是这组指针的顺序. <br />聚集索引 :表的物理存储顺序与指针(即逻辑)顺序相同 <br />非聚集索引 :物理与逻辑顺序不同 <br /><br /><br />因为一个表只能有一个物理顺序,所以,聚集索引的个数最多只能是1. <br /><br /><br />唯一索引 是给所做的索引增加了唯一性的约束,添加,修改索引列中数据时,不允许出现重复值.它可以是聚集索引,也可以是非聚集的,就看你如何定义
唯一索引和非唯一索引_唯一索引及其依据
culi3182的博客
08-16 1349
唯一索引和非唯一索引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...
SqlServer2016模糊匹配的三种方式及效率问题简析
09-09
在SQL Server 2016中，模糊...总之，在选择SQL Server 2016中的模糊匹配方法时，应根据实际的查询模式、数据规模和索引策略来权衡效率和功能。在满足业务需求的前提下，尽可能地优化查询性能，减少不必要的资源消耗。
简析SQL Server中的数据查询.pdf
09-19
在深入分析《简析SQL Server中的数据查询.pdf》文档内容之前，我们首先要了解SQL Server的基本概念。SQL Server是微软推出的一款关系型数据库管理系统，主要用于企业级数据存储和处理，支持复杂的数据查询和分析，是...
简析SQL Server数据库用视图来处理复杂的数据查询关系
09-10
通过上述方式，我们可以利用SQL Server的视图来简化复杂的数据查询，提高数据管理效率，并确保数据的安全性和一致性。在实际应用中，根据具体需求选择合适的方式来创建和使用视图，可以有效地提升数据库的管理和使用...
telephone 为空 唯一索引_唯一索引与非唯一索引区别（Unique Index, non-unique index）...
weixin_42420393的博客
01-17 283
索引是我们经常使用的一种数据库搜索优化手段。适当的业务操作场景使用适当的索引方案可以显著的提升系统整体性能和用户体验。在Oracle中，索引有包括很多类型。不同类型的索引适应不同的系统环境和访问场景。其中，唯一性索引是我们经常使用到的一种。唯一性索引unique index和一般索引normal index最大的差异就是在索引列上增加了一层唯一约束。添加唯一性索引的数据列可以为空，但是只要存在数据...
数据库唯一索引和普通索引的区别？
u014230945的博客
11-13 1013
1、如果业务字段更新频繁的化，选择唯一索引还是普通索引？ 如果业务上能保证唯一性的化尽量使用普通索引而不是唯一索引。 2、针对查询而言普通索引的查询是在查询到符合条件的内容以后，继续进行下一项内容查询，如果下一项不符合则返回；但是唯一索引的查询是查询到满足条件的内容直接返回，整体上少一次查询比较，但是整体效率相差不大。 3、针对更新操作来说两者就有很大的差距了。 要在更新操作上理解两者之间的差距首先理解一下change buffer。 change buffer...
mysql的唯一索引和普通索引有什么区别
m0_61716416的博客
07-29 1107
性能方面：在查询性能上，唯一索引和普通索引没有显著的差异，主要取决于索引的选择性和查询的复杂性。唯一索引在保证数据唯一性方面有额外开销，但对于普通查询来说，其性能表现与普通索引类似。使用场景唯一索引：适用于需要保证某列值唯一的场景，例如用户名、电子邮件地址等。普通索引：适用于不需要唯一性的列，用于提高查询速度，例如用于快速查找和排序。选择索引类型应基于实际需求。如果数据需要唯一性约束，则使用唯一索引；如果只是为了提高查询性能，则可以使用普通索引。当你在字段上设置UNIQUE。
MySQL普通索引、唯一索引、主索引、外键索引、复合索引、全文索引的使用
乐杨俊浅谈LAMP
05-20 4519
MySQL普通索引、唯一索引、主索引、外键索引、复合索引、全文索引的使用
SqlServer设置唯一键/索引
wys0127的博客
04-14 4701
1、首先，打开sqlserver软件，右键点击要设计的表选择设计，选中一列右键设置主键，该列就设置成了主键了，删除主键同理，设置好了前面有一把钥匙。3、这里注意，刚刚选择的列不是在那个选中的列上设置，而是以整个表来设置的，因此能看到刚才设置的主键。2、设置唯一键和索引键也是右键选中的列（空白的地方也行），点击‘索引/键’。8、最后就设置完成了，点击保存，到此就完成了。(Ctrl + S)7、再点击类型可以设置唯一键/索引/列存储索引，选择需要设置的。5、然后点击列，出来隐藏的省略号，再点击它。
SQL Server 2012新特性_列存储索引(3)
Burgess_Liu的专栏
04-09 4108
本篇Blog将介绍列存储索引相关的性能、查询提示及使用限制等方面的知识。若要了解列存储及列存储索引的概念及特征，请点击这里；若要了解列存储索引的创建与使用，请点击这里。  列存储的性能 要了解类存储索引的性能到底如何，不妨先来搭建一下测试的环境。下面我们创建基本两张完全一样的表，不同的是，一张表没有列存储索引，而另一张则有： 不带column Store Index (N'tbl_Engin
用一条SQL语句 查询出每门课程都大于80分的学生姓名" 的实现方式
weixin_34226182的博客
07-03 602
建表: 1 create table Score( 2 name varchar(20), 3 course varchar(20), 4 score int) 5 6 go 7 8 insert Score 9 select 'HanMeimei','Chinese',81 union all 10 select 'HanMeimei','...
MySQL学习笔记（15-28）
m0_60264772的博客
11-28 3997
目录 索引的优劣 索引分类 mysql索引结构 那些情况建索引 哪些情况不要建索引 性能分析 索引的优劣 1.优势 类似大学图书馆图书编号建索引，提高了数据检索的效率，降低数据库的IO成本 通过索引对数据进行排序，降低数据排序的成本，降低了CPU的消耗 2.劣势 实际上索引也是一张表，该表保存了主键与存在索引的字段，并指向实体表的记录，所以索引列也是占用空间的 虽然索引大大提高了查询速度，但是会降低更新表的速度，比如 update，insert，delete操作，因为更新表时，My
SQL Server索引设计：群集与非群集深度解析
理解并有效地利用群集和非群集索引是优化SQL Server数据库性能的关键。设计良好的索引不仅可以加速查询，还能减少磁盘I/O，降低数据库系统的整体负载。在实践中，需要不断学习和调整索引策略，以适应不断变化的业务...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值