【奥门永利402com】目录深入显出(5/10State of Qatar:非集中索引的B树构造在堆表

 

非集中索引深入浅出

我们用文章“目录深入浅出:聚焦索引的B树结构”用到的salesorderdetails创立一个非聚焦索引,那个表在salesorderdetailid列有八个集中索引。

1 CREATE UNIQUE INDEX Ix_ProductId ON SalesOrderDetail(ProductId,Salesorderid) 

搜集非集中索引相关信息:

 1 TRUNCATE TABLE dbo.sp_table_pages
 2 INSERT INTO sp_table_pages EXEC('DBCC IND(IndexDB,SalesOrderDetail,2)')
 3 GO
 4 
 5 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC --根节点/索引页
 6 DBCC TRACEON(3604)
 7 DBCC PAGE(IndexDB,1,3472,3)
 8 
 9 DBCC TRACEON(3604)
10 DBCC PAGE(IndexDB,1,3416,3)--叶子节点/索引页
11 
12 DBCC TRACEON(3604)
13 DBCC PAGE(IndexDB,1,3557,3)--叶子节点/索引页
14 SELECT * FROM dbo.sp_table_pages WHERE IndexLevel=0 --叶子节点/索引页

根据上述音讯进行非集中索引逻辑暗中提示图的绘图:

奥门永利402官方网站 1

现在大家来剖判下SQL
Server怎样存款和储蓄非聚焦索引,首先大家因此DBCC
IND命令查看非集中索引的页分配情形,最终叁个参数,2是Ix_ProductId的索引号。

1 DBCC IND(IndexDB,SalesOrderDetail,2)

奥门永利402官方网站 2

奥门永利402官方网站 3

 大家看到输出结果大器晚成共有229条记下,包涵1个IAM页和2贰17个索引页。我们得以经过找IndexLevel 列值最大的笔录,来找根页(root
page)。记住索引层级是从叶子层向根层拉长的。

1 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC --根节点/索引页

奥门永利402官方网站 4

在这里个表里,我们根层(root
leve)页号是3472,index
level是1,那正是说,那些非集中索引的B树构造独有根层(root
level)和叶子层(leaf level),未有中间层(intermediate
level)。大家来探视3472页。 

1 DBCC TRACEON(3604)
2 DBCC PAGE(IndexDB,1,3472,3)

奥门永利402官方网站 5

回来结果意气风发共有227条记下(2三十个叶子层的索引页)。部分结实如上所示。那和集中索引里的根层(root)/中间层(intermediate)的页布局是均等的。productid与salesorderid组合的值紧跟于或等于(707,51151)的保有记录,能够在子页3416里找到。productid与salesorderid组合的值在(707,51151)与(707,55920)之间的具备记录,能够在子页3417里找到,并依此类推。

咱俩来拜访3417页。

1 DBCC TRACEON(3604)
2 DBCC PAGE(IndexDB,1,3417,3)

奥门永利402官方网站 6

计算重回539条记下,都以product
id为707的记录。这里的目录只用2层,这些是B树布局的叶子层。你会专一到,这里未有子页ID列,但大家有salesorderdetailid列(聚焦索引键),SQL
Server用它来实行键或书签查找操作。

 大家来看看,SQL
Server怎么样使用那些目录进行一个SELECT操作。点击工具栏的奥门永利402官方网站 7展现满含实际的进行安排。

1 SET STATISTICS IO ON
2 GO
3 SELECT *  FROM SalesOrderDetail WHERE productid=707 AND SalesOrderid=51192 

奥门永利402官方网站 8

奥门永利402官方网站 9

能够见见进行安插的键查找操作。因为此地where条件适逢其会完全契合大家非集中索引定义,SQL
Server用那些目录来实施查询。首先SQL
Server读取B树构造的根页。大家的询问条件构成(707,51192)落在根页的第二条记下上,由此SQL
Server走到它的子页(页号3417)。在这一个页里,大家得以用标准构成(707,51192)定位到实际的笔录上,它的salesorderdetailid值是37793。从那边开端,SQL
Server使用salesorderdetailid值举办键查找(key look
up)操作。从上二个作品知晓,但大家开展任何集中索引键查找是,必要实践3个I/O。
由此这里,SQL
Server须要推行5个I/O操作(2个在非聚集索引,3个在集中索引的书签/键探索(bookmark/key
lookup),这一个和你的结果输出后生可畏致。

奥门永利402官方网站 10

为了越来越好的知情它,大家得以把非集中索引当做salesorderdetail 表的一个子表(大家把它称作Saleorderdetail_NC),有productid,salesorderid 和
SalesorderDetailid列,况且 ProductId与salesorderid列组合为聚焦索引。上述查询的结果能够经过以下2个查询来获取。

1 SELECT *  FROM SalesOrderDetail_nc WHERE productid=707 AND SalesOrderid=51192
2 GO
3 SELECT *  FROM SalesOrderDetail WHERE SalesOrderDetailid=37793

我们再来看二个询问:

1 SELECT *  FROM SalesOrderDetail WHERE productid=707

奥门永利402官方网站 11

询问再次回到3083条记下,查询条件与非聚焦索引的第一列相配。但是SQL
Server并没用非集中索引来实施这么些查询,查询安顿如下所示。

奥门永利402官方网站 12

如此做的原故是,借使利用非聚焦索引,就需求为3083条记下执楷体签查找(key
lookup)。那会发出92肆14个I/O操作(3083*3)。因而,SQL
Server使用了集中索引围观,它只要求1501(对于聚集索引树布局亟待的页数)个I/O操作。即使大家做叁个小的改动,只要Productid
,SalesOrderDetailid和SalesOrderId列,SQL
Server会使用非集中索引,因为它不要求开展书签查找(bookmark
lookup)操作。非集中索引的叶子层已经满含那个列了。

1 SELECT productid,salesorderdetailid,salesorderid  FROM SalesOrderDetail WHERE productid=707

奥门永利402官方网站 13

这篇随笔真的有一些长,何况笔者是讨厌的BING输入法出错,导致浏览器崩溃,错过多个夜晚3个钟头成果,重新写好的,希望大家看了随后能够不亦乐乎驾驭非聚焦索引了,晚安各位!!二〇一五-05-14
00:18:42 

 1 DECLARE @HeapRid BINARY(8) 2 SET @HeapRid = 0xB800000001003E00 3 SELECT    4    CONVERT (VARCHAR(5), 5           CONVERT(INT, SUBSTRING(@HeapRid, 6, 1) 6                + SUBSTRING(@HeapRid, 5, 1))) 7   + ':' 8   + CONVERT(VARCHAR(10), 9           CONVERT(INT, SUBSTRING(@HeapRid, 4, 1)10                + SUBSTRING(@HeapRid, 3, 1)11                + SUBSTRING(@HeapRid, 2, 1)12                + SUBSTRING(@HeapRid, 1, 1)))13   + ':'14      + CONVERT(VARCHAR(5),15           CONVERT(INT, SUBSTRING(@HeapRid, 8, 1)16                + SUBSTRING(@HeapRid, 7, 1)))17                AS 'Fileid:Pageid:Slot' 

集中索引与非集中索引

 非集中索引和集中索引有风流倜傥致的B树构造。非集中索引键不会对主体表的数据排序做其它变动,因为聚焦索引强迫SQL
Server将数据以集中索引键的顺序存款和储蓄。聚集索引的叶子层由包括表具体数据的数目页组成,而非集中索引的叶子层由索引页组成。

非聚焦索引能够定义在堆表或聚集表。在非集中索引的叶子层,每一个索引行富含非聚焦索引键值和行定位器。那一个定位器指向聚焦索引或堆表的数据行。在非聚焦索引行里的行定位器要么指向行,要么指向行聚集索引键。若是是堆表,它从不集中索引,行定位器是个指向行的指针。那么些指针由页里行的(文件号:页号:槽号,file
identifier :page number :slot number)组成。整个指针被叫作ROW
ID(EscortID)。借使表有聚焦索引,行定位器是行的集中索引键。

1 DBCC ind('IndexDB','SalesOrderDetailHeap',2)

非集中索引的逻辑突显

简短来讲,非聚焦索引是表的子集。当咱们定义了二个非聚集索引时,SQL
Server把整套非聚焦索引键存在不一致的页里。大家来看下叁个分包BusinessEntityID(PK卡塔尔(قطر‎,PersonType,FirstName,LastName那4列的表,那个表上有一个非集中索引定义。主身体表面按BusinessEntityID列(聚焦索引键)的顺序存储。非聚集索引的存款和储蓄是与本位表抽离的。假设您细心看非聚焦索引表,你会发觉,记录是按Firstname,lastname 列的顺序排列的。简单通晓下,非聚焦索引正是主体表的子集。

奥门永利402官方网站 14

假定今后大家要搜索first
name值为Michael的笔录。纵然您从实体表找的话,大家供给从头到脚把每条记下相称三回,因为记录并从未按first
name列排序保存。假使那一个表记录有上千条记下以来,那将是三个不行无聊且费时的做事。假诺大家在非聚焦索引表里找将会轻巧超多,因为这么些表是按first
name列以字母顺序排列的。大家相当的轻松定位到first
name是Michael的记录。我们并不供给再往下找,因为大家分明未有更加的多的first
name是Michael的记录了。

现今我们赢得了Firstname,lastname的值。这大家怎么样获得别的2列的值?让大家对非聚焦索引做一些更换,将BusinessEntityID列也充作非集中索引。

奥门永利402官方网站 15

近年来,豆蔻梢头旦大家一贯到记录,大家得以选择BusinessEntityID(集中索引键)列再次来到主体表,得到其余列的值,那些操作被称作书签查找(bookmark
lookups)或揽胜ID查找。

奥门永利402官方网站 16

三个表只可以有叁个集中索引,数据行以此聚焦索引的大器晚成黄金时代举办仓库储存,三个表却能有四个非集中索引。大家早就斟酌了聚集索引的构造奥门永利402官方网站,,那篇大家会看下非集中索引构造。

奥门永利402官方网站 17

www.402.com,参照作品:

http://www.sqlservercentral.com/blogs/practicalsqldba/2013/03/14/sql-server-part-4-explaining-the-non-clustered-index-structure-/

奥门永利402官方网站 18

 在聚焦表的非聚集索引的叶子层,聚焦键与非聚焦键一起参预了叶子层的页。这里我们从不集中索引,索引SQL
Server加了个行标志号(8 bytes大小),由文件号(2 bytes),页号(4
bytes)和槽号(2 bytes)组合而成。

大家来拜访叶子层的3608页。

能够看来,indexlevel列最大值1的页号是3270,这一个页便是根页,因为indexlevel列最大值是1,所以那几个堆表的非集中索引的B树布局唯有2层,即根层和叶子层,也正是说2八十七个索引页中,1个页是根层的根页(也是索引页),2捌十五个页是叶子层的索引页。我们来探视3270页的音讯。

1 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC 

从上海教室我们能够通晓看出,productid值为707,salesorderid值为43665的笔录完整音讯,能够在HeapOdysseyID
0xB800000001003E00岗位找到。下边包车型客车询问能够帮我们把LX570ID转为文件号:页号:槽号(FileId:PageId:SlotNo)格式。

奥门永利402官方网站 19

从输出我们能够看来,productid值为707,salesorderid值为43665的笔录全体列能够在槽号62找到,与1:184:62代表文件号:1
,页号:184 ,槽号:62完全蓬蓬勃勃致。

1 DBCC TRACEON(3604)2 DBCC PAGE(IndexDB,1,3720,3)
1 SET STATISTICS IO ON2 GO3 SELECT ProductId,SalesOrderid FROM SalesOrderDetailHeap WHERE productid=707 AND SalesOrderid=43665 

 奥门永利402官方网站 20

1:184:62意味着文件号:1 ,页号:184 ,槽号:62。大家来看看184页。 

奥门永利402官方网站 21

总括重返298条记下,包罗1个IAM页,2九十几个索引页,大家用下列语句找下根层的页号:

1 DBCC TRACEON(3604)2 DBCC PAGE(IndexDB,1,3608,3)--叶子节点/索引页
 1 TRUNCATE TABLE dbo.sp_table_pages 2 INSERT INTO sp_table_pages EXEC('DBCC IND(IndexDB,SalesOrderDetailHeap,2)') 3 GO 4  5 SELECT * FROM dbo.sp_table_pages ORDER BY IndexLevel DESC --根节点/索引页 6 DBCC TRACEON(3604) 7 DBCC PAGE(IndexDB,1,3720,3) 8  9 DBCC TRACEON(3604)10 DBCC PAGE(IndexDB,1,3608,3)--叶子节点/索引页11 12 DBCC TRACEON(3604)13 DBCC PAGE(IndexDB,1,3908,3)--叶子节点/索引页14 SELECT * FROM dbo.sp_table_pages WHERE IndexLevel=0 --叶子节点/索引页

咱俩通过下边包车型大巴查询看看SQL
Server如何使用非集中索引查找堆表上的数码,点击工具栏的奥门永利402官方网站 22奥门永利402com,展现包括实际的实践陈设。

出口结果,和聚焦表里的非聚焦索引的根页布局是大器晚成律的。

相关文章