SQL Server2008存储结构之非聚集索引

最新推荐文章于 2020-12-14 16:14:08 发布
最新推荐文章于 2020-12-14 16:14:08 发布
阅读量48 收藏
点赞数

SQL Server 2008连载之存储结构——非聚集索引

非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点：

基础表的数据行不按非聚集键的顺序排序和存储。

非聚集索引的叶层是由索引页而不是由数据页组成。

 

非聚集索引既可以建在堆表结构上也可以建在聚集索引表上；非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。

如果表是堆则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)。

如果表包含有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server 将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL Server 通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引来检索数据行。

B 树的页集合由 sys.system_internals_allocation_units 系统视图中的 root_page 指针定位。

 

0_1289965962xczH.gif

堆表

--创建一张堆表

CREATE TABLE testHeapIndex

(

   name   CHAR(200),

   type1  CHAR(900),

   type2  CHAR(900)

)

--分别创建一个唯一索引和一个非唯一索引

CREATE UNIQUE INDEX idx_testHeapIndex1 ON testHeapIndex(type1)

CREATE INDEX idx_testHeapIndex2 ON testHeapIndex(type2)

--插入测试数据

INSERT INTO testHeapIndex VALUES('A','A1','A2')

INSERT INTO testHeapIndex VALUES('B','B1','B2')

INSERT INTO testHeapIndex VALUES('C','C1','B2')

INSERT INTO testHeapIndex VALUES('D','D1','B2')

INSERT INTO testHeapIndex VALUES('E','E1','C2')

INSERT INTO testHeapIndex VALUES('F','F1','F1')

INSERT INTO testHeapIndex VALUES('G','G1','G1')

INSERT INTO testHeapIndex VALUES('H','H1','G1')

INSERT INTO testHeapIndex VALUES('I','I1','G1')

INSERT INTO testHeapIndex VALUES('J','J1','J1')

--获取该表的相应页面信息

SELECT A.NAME TABLE_NAME,B.NAME INDEX_NAME,B.INDEX_ID

  FROM SYS.OBJECTS A,SYS.INDEXES B

 WHERE A.OBJECT_ID=B.OBJECT_ID AND A.NAME='testHeapIndex'

TRUNCATE TABLE tablepage;

INSERT INTO tablepage EXEC ('DBCC IND(testdb,testHeapIndex,0)');

INSERT INTO tablepage EXEC ('DBCC IND(testdb,testHeapIndex,2)');

INSERT INTO tablepage EXEC ('DBCC IND(testdb,testHeapIndex,3)');

SELECT

  b.name table_name,

  CASE WHEN c.type=0 THEN '堆'

       WHEN c.type=1 THEN '聚集'

       WHEN c.type=2 THEN '非聚集'

       ELSE '其他'

  END index_type, 

  c.name index_name,

  PagePID,IAMPID,ObjectID,IndexID,Pagetype,IndexLevel,

  NextPagePID,PrevPagePID

  FROM tablepage a,sys.objects b,sys.indexes c

 WHERE A.ObjectID=b.object_id

   AND A.ObjectID=c.object_id

   AND a.IndexID=c.index_id

--获取该表的root页面地址，聚集索引的根节点必须通过下面脚本才能找到

SELECT c.name,a.type_desc,d.name,

       total_pages,used_pages,data_pages,

       testdb.dbo.f_get_page(first_page) first_page_address,

       testdb.dbo.f_get_page(root_page) root_address,

       testdb.dbo.f_get_page(first_iam_page) IAM_address

  FROM sys.system_internals_allocation_units a,sys.partitions b,sys.objects c,sys.indexes d

 WHERE a.container_id=b.partition_id and b.object_id=c.object_id

   AND d.object_id=b.object_id  AND d.index_id=b.index_id

   AND c.name in ('testHeapIndex')

--下面各个例子获取相关页面和root页面的脚本基本相同，不再重复

 

 

堆表上的唯一非聚集索引

0_1289965969HOhM.gif

首先堆表是由若干叶子页面组成的，相互之间没有链接关系，完全靠IAM页面进行管理和维护。

我们可以看到page(1:90)为该唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于I1的指向叶子页面page(1:93)，小于I1的则指向叶子页面page(1:55)页面。

叶子页面中则包括每个索引条目的索引值和该索引指向的记录的位置（文件号+页面+插槽号），因为这是唯一索引所以不需要额外的字段来记录重复值。

 

堆表上的非唯一非聚集索引

0_1289965973E1F2.gif

我们可以看到page(1:94)为该非唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于G1的指向叶子页面page(1:78)，小于G1的则指向叶子页面page(1:109)页面。

叶子页面中则包括每个索引条目的索引值和该索引指向的记录的位置（文件号+页面+插槽号）；存储结构与堆表上的唯一非聚集索引完全一致，我们可以看出在堆表中尽管索引值不唯一，但通过索引值+指针(文件号+页面+插槽号)的方式，也能够保证该索引条目的唯一性，所以不需要增加额外的辅助字段。

 

唯一聚集索引表

CREATE TABLE testUniqueClusterIndex

(

   name   CHAR(200),

   type1  CHAR(900),

   type2  CHAR(900)

)

CREATE UNIQUE CLUSTERED INDEX idx_testUniqueClusterIndex_cluster ON testUniqueClusterIndex(name)

CREATE UNIQUE INDEX idx_testUniqueClusterIndex1 ON testUniqueClusterIndex(type1)

CREATE INDEX idx_testUniqueClusterIndex2 ON testUniqueClusterIndex(type2)

INSERT INTO testUniqueClusterIndex VALUES('A','A1','A2')

INSERT INTO testUniqueClusterIndex VALUES('B','B1','B2')

INSERT INTO testUniqueClusterIndex VALUES('C','C1','B2')

INSERT INTO testUniqueClusterIndex VALUES('D','D1','B2')

INSERT INTO testUniqueClusterIndex VALUES('E','E1','C2')

INSERT INTO testUniqueClusterIndex VALUES('F','F1','F1')

INSERT INTO testUniqueClusterIndex VALUES('G','G1','G1')

INSERT INTO testUniqueClusterIndex VALUES('H','H1','G1')

INSERT INTO testUniqueClusterIndex VALUES('I','I1','G1')

INSERT INTO testUniqueClusterIndex VALUES('J','J1','J1')

 

唯一聚集索引表上的唯一非聚集索引

0_1289965981OJ0s.gif

我们首先可以看到page(1:192)为聚集索引的根节点，该根节点包括2个索引值和三个索引指针指向相应的叶子页面，而聚集索引的叶子页面则按照聚集索引的排序规则进行存储。

page(1:194)为该唯一聚集索引表上的唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于H1的指向叶子页面page(1:195)，小于H1的则指向叶子页面page(1:151)页面。

唯一非聚集索引的叶子页面中则包括每个索引条目的索引值和其聚集键值；因为都是唯一的，所以两者合起来能够保证该索引条目的唯一性，所以不需要增加额外的辅助字段。

那么当对唯一非聚集索引的字段进行查找的时候是如何处理的呢？SQL Server首先从唯一非聚集索引的根节点开始查起，直到找到合适的索引叶子页面，然后根据该索引条目中的聚集索引键值，去聚集索引根节点中进行查找，一直找到正确的聚集叶子页面为止。

 

唯一聚集索引表上的非唯一非聚集索引

0_1289965986AhH2.gif

page(1:196)为该唯一聚集索引表上的非唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于G1的指向叶子页面page(1:174)，小于G1的则指向叶子页面page(1:197)页面。之所以在196页面的索引条目中包括聚集索引的键值，是因为该索引条目不唯一造成的，从图中可以看出，Type2=G1的索引条目有2条，所以需要聚集索引键值的存在才能保证该索引条目的歧义和唯一性。

非唯一非聚集索引的叶子页面中则包括每个索引条目的索引值和其聚集键值；尽管非聚集索引不唯一，但因为聚集索引是唯一的，所以两者合起来能够保证该索引条目的唯一性，所以也不需要增加额外的辅助字段。

 

非唯一聚集索引表

CREATE TABLE testUnUniqueClusterIndex

(

   name   CHAR(200),

   type1  CHAR(900),

   type2  CHAR(900)

)

CREATE CLUSTERED INDEX idx_testUnUniqueClusterIndex_cluster ON testUnUniqueClusterIndex(name)

CREATE UNIQUE INDEX idx_testUnUniqueClusterIndex1 ON testUnUniqueClusterIndex(type1)

CREATE INDEX idx_testUnUniqueClusterIndex2 ON testUnUniqueClusterIndex(type2)

INSERT INTO testUnUniqueClusterIndex VALUES('A','A1','A2')

INSERT INTO testUnUniqueClusterIndex VALUES('B','B1','B2')

INSERT INTO testUnUniqueClusterIndex VALUES('B','C1','B2')

INSERT INTO testUnUniqueClusterIndex VALUES('D','D1','B2')

INSERT INTO testUnUniqueClusterIndex VALUES('E','E1','C2')

INSERT INTO testUnUniqueClusterIndex VALUES('F','F1','F1')

INSERT INTO testUnUniqueClusterIndex VALUES('F','G1','G1')

INSERT INTO testUnUniqueClusterIndex VALUES('F','H1','G1')

INSERT INTO testUnUniqueClusterIndex VALUES('F','I1','I1')

INSERT INTO testUnUniqueClusterIndex VALUES('J','J1','J1')

 

非唯一聚集索引表上的唯一非聚集索引

0_1289965989IICH.gif

我们首先可以看到page(1:205)为聚集索引的根节点，该根节点包括2个索引值和三个索引指针指向相应的叶子页面，其中第二个索引值后面还带了一个identifer为3的值，这是因为该聚集索引不唯一，所以必须增加一个唯一标识才能定位到相应的下级节点中。而聚集索引的叶子页面则按照聚集索引的排序规则进行存储；注意在叶子节点中重复键值的聚集索引的尾部也带有相应的唯一标识值。

page(1:207)为该非唯一聚集索引表上的唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于H1的指向叶子页面page(1:201)，小于H1的则指向叶子页面page(1:208)页面。

注意非唯一非聚集索引的叶子页面中则包括每个索引条目的索引值和其聚集键值，同时在重复聚集键值的后面增加了相应的唯一标识，因此三者结合起来就能够保证该索引条目的唯一性了。

 

非唯一聚集索引表上的非唯一非聚集索引

0_1289965993qLT9.gif

page(1:209)为该非唯一聚集索引表上的非唯一非聚集索引的根节点，因为数据量少实际上只有一个索引节点，即大于等于G1的指向叶子页面page(1:210)，小于G1的则指向叶子页面page(1:203)页面。

注意非唯一非聚集索引的叶子页面中则包括每个索引条目的索引值和其聚集键值，同时在重复聚集键值的后面增加了相应的唯一标识，虽然非唯一聚集索引的索引键值是重复的，但因为聚集索引键值和唯一标识已经是唯一的，所以三者结合起来依然能够保证该索引条目的唯一性。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6517/viewspace-678314/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/6517/viewspace-678314/

csy542339419
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
SQL Server 聚集索引和非聚集索引的区别分析
09-11
聚集索引:物理存储按照索引排序 非聚集索引:物理存储不按照索引排序
SQLSERVER的非聚集索引结构深度理解
12-15
我们知道SQLSERVER的数据行的存储有两种数据结构：A： 堆 B ：B树（binary 二叉树） 数据按照这种两种的其中一种来排序和存储，学过数据结构的朋友应该知道二叉树，为什麽用二叉树，因为方便用二分查找法来快速 ...
SQL Server 2008 创建非聚集索引
cuanji3287的博客
12-18 365
可以对表或索引视图创建多个非聚集索引。通常，创建非聚集索引是为了提高聚集索引未包含的常用查询的性能。 典型实现 可以通过下列方法实现非聚集索引： PRIMARY KEY 和 UNIQUE 约束在创建 PRIMARY KEY...
sql中创建主键非聚集索引和聚集索引
红叶锁清秋
09-17 1167
--排序(聚集索引)create clustered index inx_entry_stock_bi on entry_stock_d(entry_stock_bi)--创建非聚集索引create nonclustered index inx_entry_stock_on entry_stock_d(entry_stock_bi)--主键alter table entry_stock
SQL Server索引 (原理、存储)聚集索引、非聚集索引、堆 <第一篇>
ajwqb06628的专栏
06-12 440
SQL Server索引 (原理、存储)聚集索引、非聚集索引、堆 <第一篇> 一、存储结构 　　在SQL Server中，有许多不同的可用排列规则选项。 　　二进制：按字符的数字表示形式排序(ASCII码中，用数字32表示空格，用68表示字母"D")...
SQLSERVER2008R2 索引建立的几点建议
ju523756055的专栏
03-06 5125
1、不要把聚集索引浪费在主键上，除非你只按主键查询        虽然SQL SERVER默认是在主键上建立聚集索引的，但实际应用中，这样做比较浪费。通常，我们会在每个表中都建立一个ID列，以区分每条数据，并且这个ID列是自动增大的，步长一般为1。此时，如果我们将这个列设为主键，SQL SERVER会将此列默认为聚集索引。这样做有好处，就是可以让您的数据在数据库中按照ID进行物理排序，但这样做实
详解SQL Server的聚焦过滤索引
12-16
过滤索引是SQL 2008的新特性，被应用在表中的部分行，所以利用过滤索引能够提高查询，相对于全表扫描它能减少索引维护和索引存储的代价。当我们在索引上应用WHERE条件时就是过滤索引。也就是满足如下格式： CREATE ...
MySQL存储引擎的聚集索引与非聚集索引
zxwzz2的博客
12-14 160
前言 InnoDB表会用一个自增主键做聚集索引，没有的话MySQL会找唯一索引列，都没有会默认创建6字节的长整型行ID。 名词解释 聚集索引：不是一种索引类型，它是一种数据存储方式，也称聚簇索引； 非聚集索引：不是索引类型，也是一种数据存储方式，也称非聚簇索引； 辅助索引：工作中创建及使用的索引，也称二级索引； 存储文件InnoDB：frm是表定义文件，ibd是数据索引文件；MyISAM：frm是表定义文件，myd是数据文件，myi是索引文件； InnoDB索引实现 <1> InnoDB的
复合非聚集索引——列顺序重要么？
weixin_33795833的博客
06-28 108
当我谈论索引时，大家经常会问我在复合非聚集索引里，列的顺序是否重要？简单来说：“看情况”。我们来具体看下为啥“看情况”…… 单例查找（Singleton Lookups） 当在你的表上有进行单例查找的查询时，在复合非聚集索引里列的顺序真的不重要。假设下列查询： -- Without a supporting Non-Clustered Index we have to scan the ...
Sql Server中的表组织和索引组织（聚集索引结构，非聚集索引结构，堆结构）
weixin_33884611的博客
07-06 169
参考文献 表和索引数据结构体系结构 SqlServer存储结构组织其分区中的数据或索引页 漫谈数据库索引 正文 SqlServer用三种方法来组织其分区中的数据或索引页： 1、聚集索引结构 聚集索引是按B树结构进行组织的，B树中的每一页称为一个索引节点。每个索引行包含一个键值和一个指针。指针指向B树上的某一中间级页（比如根节点指向中间级节点中的索引页）或叶级索引中的某个数据行（比如中...
MySQL索引详解（聚集索引与非聚集索引以及数据结构）
Gothic的博客
09-30 1615
一、索引的简介 索引是存储引擎快速找到记录的一种数据结构，是数据库中专门用于帮助用户快速查询数据的一种数据结构，可以帮助用户快速寻找到需要的数据行，是数据库性能优化中最重要的工具。 使用索引的主要目的是为了优化查询速度，它们包含着对数据表里所有记录的引用指针。简单的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。 二、索引的分类 索引是在存储引擎中实现的，也就是说不同的存储...
聚集索引,非聚集索引,覆盖索引 原理
热门推荐
只有变秃 才能变强
08-28 1万+
「数据库」和「数据库索引」这两个东西是在服务器端开发领域应用最为广泛的两个概念，熟练使用数据库和数据库索引是开发人员在行业内生存的必备技能 使用索引很简单，只要能写创建表的语句，就肯定能写创建索引的语句，要知道这个世界上是不存在不会创建表的服务器端程序员的。然而， 会使用索引是一回事， 而深入理解索引原理又能恰到好处使用索引又是另一回事，这完全是两个天差地别的境界（我自己也还没有达到这层境界）。...
SQL Server2008存储结构之聚集索引和非聚集索引
yzd
11-22 278
聚集索引即基于数据行的键值在表内排序和存储这些数据行。每个表只能有一个聚集索引，因为数据行本身只能按一个顺序存储。从某种程度上，聚集索引即数据，这句话是有道理的；但正如同其他索引一样，聚集索引也是按 B 树结构进行组织的。既然是B树组织，那么就有叶子结点和非叶子节点之分。聚集索引B 树的顶端节点称为根节点；聚集索引中的底层节点称为叶节点。在根节点与叶节点之间的任何索引级别统称为中间级。在聚集索引中...
聚集索引和非聚集索引（整理） 实例讲解
gloryzyf
03-13 4682
很好的一篇博文，值得转载。 官方说法： 聚集索引 　　一种索引，该索引中键值的逻辑顺序决定了表中相应行的物理顺序。  　　聚集索引确定表中数据的物理顺序。聚集索引类似于电话簿，后者按姓氏排列数据。由于聚集索引规定数据在表中的物理存储顺序，因此一个表只能包含一个聚集索引。但该索引可以包含多个列（组合索引），就像电话簿按姓氏和名字进行组织一样。  　　　　 聚集索引对于那些经常要
SQLServer之创建非聚集索引
小子pk了的博客
09-17 7216
开始之前 典型实现 可以通过下列方法实现非聚集索引： UNIQUE 约束 在创建 UNIQUE 约束时，默认情况下将创建唯一非聚集索引，以便强制 UNIQUE 约束。 如果不存在该表的聚集索引，则可以指定唯一聚集索引。 有关详细信息，请参阅 Unique Constraints and Check Constraints。 独立于约束的索引 默认情况下，如果未指定聚集，...
一个基于C语言的简易学生管理系统.zip
最新发布
06-15
C语言是一种广泛使用的编程语言，它具有高效、灵活、可移植性强等特点，被广泛应用于操作系统、嵌入式系统、数据库、编译器等领域的开发。C语言的基本语法包括变量、数据类型、运算符、控制结构（如if语句、循环语句等）、函数、指针等。在编写C程序时，需要注意变量的声明和定义、指针的使用、内存的分配与释放等问题。C语言中常用的数据结构包括： 1. 数组：一种存储同类型数据的结构，可以进行索引访问和修改。 2. 链表：一种存储不同类型数据的结构，每个节点包含数据和指向下一个节点的指针。 3. 栈：一种后进先出（LIFO）的数据结构，可以通过压入（push）和弹出（pop）操作进行数据的存储和取出。 4. 队列：一种先进先出（FIFO）的数据结构，可以通过入队（enqueue）和出队（dequeue）操作进行数据的存储和取出。 5. 树：一种存储具有父子关系的数据结构，可以通过中序遍历、前序遍历和后序遍历等方式进行数据的访问和修改。 6. 图：一种存储具有节点和边关系的数据结构，可以通过广度优先搜索、深度优先搜索等方式进行数据的访问和修改。 这些数据结构在C语言中都有相应的实现方式，可以应用于各种不同的场景。C语言中的各种数据结构都有其优缺点，下面列举一些常见的数据结构的优缺点： 数组： 优点：访问和修改元素的速度非常快，适用于需要频繁读取和修改数据的场合。 缺点：数组的长度是固定的，不适合存储大小不固定的动态数据，另外数组在内存中是连续分配的，当数组较大时可能会导致内存碎片化。 链表： 优点：可以方便地插入和删除元素，适用于需要频繁插入和删除数据的场合。 缺点：访问和修改元素的速度相对较慢，因为需要遍历链表找到指定的节点。 栈： 优点：后进先出（LIFO）的特性使得栈在处理递归和括号匹配等问题时非常方便。 缺点：栈的空间有限，当数据量较大时可能会导致栈溢出。 队列： 优点：先进先出（FIFO）的特性使得
sqlserver查看表数据在非聚集索引中的存储信息
05-28
SQL Server 中可以使用以下查询语句查看表数据在非聚集索引中的存储信息： ```sql SELECT TableName = t.name, IndexName = ind.name, IndexType = ind.type_desc, ind.is_unique, ind.is_primary_key, ind....

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

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

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值