【SQL Server2005页面存储5之--非聚集索引行在非叶级别存储】

feixianxxx 2010-07-17 10:23:54
加精
距离上一篇很久很久了。。这个是最后一篇 补上

博客地址:http://blog.csdn.net/feixianxxx/archive/2010/07/17/5741364.aspx

非聚集索引的非叶级的索引行通过非聚集键值和指针去遍历到下层的非聚集索引页来查找实际数据.

这里有种情况,

一种是这个非聚集索引是具有唯一性的,

还有一种则相反(当然也可以再细分,比如聚集索引是否存在,聚集索引是否唯一等)



先来说说当非聚集索引定义有唯一性的情况



/*----------------------------------------------------------------------



*auther:Poofly



*date:2010.3.14



*VERSION:



Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 



Jul  9 2008 14:43:34 



Copyright (c) 1988-2008 Microsoft Corporation



Enterprise Evaluation Edition on Windows NT 6.1 <X86> (Build 7600: )



*转载请注明出处



*更多精彩内容,请进http://blog.csdn.net/feixianxxx



------------------------------------------------------------------------*/



--建表(表源来自技术内幕)



GO



CREATE TABLE NC_JNodes (



  id int NOT NULL ,



  str1 char (5) NOT NULL ,



  str2 varchar (10) NULL 



); 



GO



--在str2上建立唯一性的聚集索引,在str1上建立具有唯一性的非聚集索引



CREATE UNIQUE CLUSTERED INDEX idxcl_str2 on NC_JNodes (str2);



CREATE UNIQUE INDEX idxNC ON NC_JNodes (str1); 



GO



--插入数据



DECLARE @i int;



SET @i = 1240;



WHILE @i < 13000 BEGIN



  INSERT INTO NC_JNodes



   SELECT @i, cast(@i AS char), 



       cast(cast(@i * rand() AS int) as char);   --打乱了str1的排序



  SET @i = @i + 1;



 END; 



GO



--查看页面分布和页号文件号



TRUNCATE TABLE sp_table_pages;



INSERT INTO sp_table_pages



    EXEC ('dbcc ind (poofly,NC_JNodes, -1)');



SELECT PageFID, PagePID, IndexID, IndexLevel, PageType 



FROM sp_table_pages



WHERE IndexLevel >= 0;



/*



PageFID PagePID     IndexID IndexLevel PageType



------- ----------- ------- ---------- --------



5       967         1       0          1                --该条是聚集索引的叶级别的数据页面



5       969         2       0          2                --该条是非聚集索引的叶级别的索引页面



5       971         1       1          2                --该条是聚集索引的子结点的索引页面



5       972         1       0          1



5       973         2       1          2                --该条是非聚集索引的子结点的索引页面



5       974         2       0          2



5       975         1       0          1



*/



dbcc traceon(3604)



dbcc page(poofly,5,973,1)--非聚集索引的子结点的索引页面



/*



Slot 0, Offset 0x60, Length 12, DumpStyle BYTE



 



00000000:   06000000 1000c903 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?............  



 



Slot 1, Offset 0x138, Length 12, DumpStyle BYTE



 



00000000:   06313036 33310304 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?.10631......             



  



 



Slot 2, Offset 0x144, Length 12, DumpStyle BYTE



 



00000000:   06313132 34340404 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?.11244......      



 



*/



--我们来看下第一个索引行



06->状态位A 



000000 1000->无效的键值



c903 0000->Ox03c9==969 表示指向下一层的第一个非聚集索引页面号



0500->文件号



--接下来看第二个索引行



06->状态位A 



313036 3331->'10631' 这个是str1 非聚集索引值,是下一层非聚集索引页的第一个值



0304 0000->Ox0403==1027 表示指向下一层的非聚集索引页面号



0500->文件号



 



--可以发现这个非聚集索引的结点行的结构好像跟聚集索引的结点行很像,第一行的所在键值都是无效的,只有从第二行开始有效



--我们来验证下这个假设吧



dbcc page(poofly,5,1027,1)



/*



Slot 0, Offset 0x60, Length 23, DumpStyle BYTE



 



00000000:   36313036 33310200 00010017 00363238 †10631.......628         



00000010:   38202020 202020†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?†8224 ?8



*/



注意看这里的第个到第个字节 3130363331='10631' 正好对应上我们在叶级别存储的那个非聚集值



这条记录下面的存储我在以前也说过了就是一般的拥有聚集索引的表的非聚集索引叶级别的存储了,不再多描述



结论:

在搜索记录的时候,我们可能通过非聚集索引列的值在非叶级别的索引行中找到对应的非聚集键值,然后遍历到叶级别的索引行中

然后利用叶级别的索引行中的书签键值,去遍历聚集索引树,然后找到我们要的数据.



接下来说说非聚集索引的非叶级的索引行在其不定义为UNIQUE的情况下的存储


 



--建表(表源技术内幕系列)



CREATE TABLE NC_NU_JNodes (



  id int NOT NULL ,



  str1 char (5) NOT NULL ,



  str2 varchar (10) NULL 



); 



GO



--在str2建立



CREATE UNIQUE CLUSTERED INDEX idxcl_str2 on NC_NU_JNodes (str2);



CREATE  INDEX idxNC ON  NC_NU_JNodes (str1); 



GO



SET NOCOUNT ON;



GO



DECLARE @i int;



SET @i = 1240;



WHILE @i < 13000 BEGIN



  INSERT INTO NC_NU_JNodes



   SELECT @i, cast(@i AS char), 



       cast(cast(@i * rand() AS int) as char);   --打乱了str1的排序



  SET @i = @i + 1;



 END; 



GO



--查看页面分布和页号文件号



TRUNCATE TABLE sp_table_pages;



INSERT INTO sp_table_pages



    EXEC ('dbcc ind (poofly,NC_NU_JNodes, -1)');



SELECT PageFID, PagePID, IndexID, IndexLevel, PageType 



FROM sp_table_pages



WHERE IndexLevel >= 0;



/*



PageFID PagePID     IndexID IndexLevel PageType



------- ----------- ------- ---------- --------



5       1051        1       0          1



5       1053        2       0          2



5       1055        1       1          2



5       1104        1       0          1



5       1105        2       1          2



5       1106        2       0          2



5       1107        1       0          1



。。。。。



*/



dbcc traceon(3604)



dbcc page(poofly,5,1105,1)



/*



Slot 0, Offset 0x60, Length 12, DumpStyle BYTE



 



00000000:   06066161 de001d04 00000500 †8224 ?†8224 ?†8224 ?†8224 ?†8224 ?..aa.......           



 



Slot 1, Offset 0x259, Length 29, DumpStyle BYTE



 



00000000:   36313036 33318304 00000500 02000001 †10631..........         



00000010:   001d0037 37393920 20202020 20†8224 ?†8224 ?†8224 ?†8224 ?...7799                    



 



Slot 2, Offset 0x276, Length 29, DumpStyle BYTE



 



00000000:   36313133 30318404 00000500 02000001 †11301..........         



00000010:   001d0031 30303832 20202020 20†8224 ?†8224 ?†8224 ?†8224 ?...10082                      



 



*/



 



--让我们来分析下索引行的第一行



06->状态位A 



066161 de00->无效的键值



1d04 0000->Ox041d==1053 表示指向下一层的第一个非聚集索引页面号



0500->文件号 



--第二条索引行



36->状态位A 



313036 3331->=='10631'这个是str1 非聚集索引值,是下一层非聚集索引页的第一个值



8304 0000->Ox0483==1155  表示指向下一层的第一个非聚集索引页面号



0500->==5 文件号



0200->==2该页上的列数



00->NULL位图



0100->==1变长列的数目



1d00->00011101=13+16=29 第一列变长列的结束位置即该索引行的长度



37 37393920 20202020 20->=='7799      '聚集键值



 



--验证下：



dbcc page(poofly,5,1155,3)--STYLE 3 可以以表格形式显示一些信息



/*



FileId PageId      Row    Level  str1 (key) str2 (key) KeyHashValue



------ ----------- ------ ------ ---------- ---------- ----------------



5      1155        0      0      10631      7799       (f90044db974c)



5      1155        1      0      10641      3241       (f500488c99d3)



5      1155        2      0      10644      8469       (fd006debfe58)



....



*/



看这里的第一条 str1='10631' str2='7799'  就是上面非聚集索引非叶级别的第二条索引行读出的值




结论:

其实这里跟我在非聚集索引在结点行的存储中说的行结构区别就是后面多了做为聚集索引的变长列所带来的多余字节

其作用也是显而易见的,就是以防止我们的非聚集键值因为不唯一性而带来的遍历不唯一

这里还有一点：如果你的索引没有定义成唯一性,即使所有的列值都是唯一的(就像本例中的str1不可能重复),但是它还是会包含书签。





PS：这个存储情况有很多种 读者可自行尝试

有关这方面的专题 可参看

1:http://blog.csdn.net/feixianxxx/archive/2010/03/17/5390317.aspx

2: http://blog.csdn.net/feixianxxx/archive/2010/04/05/5452759.aspx

3: http://blog.csdn.net/feixianxxx/archive/2010/04/14/5486513.aspx

4: http://blog.csdn.net/feixianxxx/archive/2010/05/08/5569945.aspx

...全文
1376 147 打赏 收藏 转发到动态 举报
写回复
用AI写文章
147 条回复
切换为时间正序
请发表友善的回复…
发表回复
jackwei057 2010-07-26
  • 打赏
  • 举报
 回复
楼主绝对厉害
doushilaji111 2010-07-26
  • 打赏
  • 举报
 回复
全是高手啊
haming 2010-07-25
  • 打赏
  • 举报
 回复
顶了啊
侯俊航 2010-07-25
  • 打赏
  • 举报
 回复
刚学完数据库，上来看看~~·
laneagle 2010-07-24
  • 打赏
  • 举报
 回复
学习一下吧！拿点小分！
blueclair 2010-07-24
  • 打赏
  • 举报
 回复
受益~~
mihoutao718 2010-07-23
  • 打赏
  • 举报
 回复
看看，不错的~
feitianhu112 2010-07-22
  • 打赏
  • 举报
 回复
增加一点下载积分
caomeng2010 2010-07-22
  • 打赏
  • 举报
 回复
学习记号
a233530058 2010-07-21
  • 打赏
  • 举报
 回复
很好，很不错
menghuantk 2010-07-21
  • 打赏
  • 举报
 回复
路过学习？？？？？？？？？？？？？？？？？？？
AND1982 2010-07-21
  • 打赏
  • 举报
 回复
不错啊，值得回复！顶上！
dershen 2010-07-21
  • 打赏
  • 举报
 回复
今天来这里来对了。
dershen 2010-07-21
  • 打赏
  • 举报
 回复
太好了，用处真大。
feixianxxx 2010-07-21
  • 打赏
  • 举报
 回复
[Quote=引用 132 楼 getstudymessages 的回复:]

EXEC ('dbcc ind (poofly,NC_NU_JNodes, -1)');

有些东西不知道什么意思，看来要学的还很多。
[/Quote]

你可以看下 我下面给的链接 里面有
x3235849 2010-07-21
  • 打赏
  • 举报
 回复
OK!!!
GetStudyMessages 2010-07-21
  • 打赏
  • 举报
 回复
EXEC ('dbcc ind (poofly,NC_NU_JNodes, -1)');

有些东西不知道什么意思，看来要学的还很多。
GetStudyMessages 2010-07-21
  • 打赏
  • 举报
 回复
thank you
study
skysea88 2010-07-21
  • 打赏
  • 举报
 回复
学习中
智星 2010-07-20
  • 打赏
  • 举报
 回复
写的非常的好,学习学习,支持.
加载更多回复（108）
SQL Server 聚集索引和非聚集索引的区别分析
聚集索引:物理存储按照索引排序 非聚集索引:物理存储不按照索引排序
Sql Server 数据库视图 索引等
视图索引存储过程 事务显示事务 隐式事务 --索引 作用:提高查询效率 降低新增修改和删除的效率 --主键索引 唯一索引 聚集索引非聚集索引 --创建索引 --索引index 默认创建的是不唯一，非聚集索引
详解SQL Server索引
全面重新认识SQL Server的索引。详细讲解索引的组织结构，数据的写入过程，sql语句的执行计划等... 本课程的索引是SQL Server各个版本的通用技术，所以适用于SQL Server2008，SQL Server2012， SQL Server2016等版本。
SQLSERVER的非聚集索引结构深度理解
我们知道SQLSERVER的数据行的存储有两种数据结构：A： 堆 B ：B树（binary 二叉树） 数据按照这种两种的其中一种来排序和存储，学过数据结构的朋友应该知道二叉树，为什麽用二叉树，因为方便用二分查找法来快速 ...
SQL Server 数据库索引其索引的小技巧
一、什么是索引 减少磁盘I/O和逻辑读次数的最佳方法之一就是使用【索引】 索引允许SQL Server在表中查找数据而不需要扫描整个表。...所以SQLServer能从一个非聚集索引的页面中读到比包含该列的表也
新技术前沿

6,129

社区成员

4,460

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 新技术前沿
社区管理员
  • 新技术前沿社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章