SQLServer 深入解析索引存储(一)

发布时间：2020-12-12 12:45:02 所属栏目：MsSql教程来源：网络整理

导读：原文出处： http://www.cnblogs.com/chenmh/p/4356428.html 聚集索引 -- 创建测试数据库 CREATE DATABASE Ixdata GO USE [ Ixdata ] -创建测试表 TABLE Orders(ID INT PRIMARY KEY IDENTITY ( 1 , 1 ),NAME CHAR ( 80 ) NOT NULL ,IDATE DATETIME NULL DEFAU

原文出处： http://www.cnblogs.com/chenmh/p/4356428.html

聚集索引

复制代码

--创建测试数据库
CREATE DATABASE Ixdata
GO
USE [Ixdata]
-创建测试表
TABLE Orders
(ID INT PRIMARY KEY IDENTITY(1,1),NAME CHAR(80)NOT NULL,IDATE DATETIME NULL DEFAULT(GETDATE())
);
-插入1000条测试数据
DECLARE @ID INT=1
WHILE(@ID<=1000)
BEGIN
INSERT INTO Orders(NAME)VALUES('商品'+CONVERT(NVARCHAR(20),@ID))
SET =+1 
END
SELECT * FROM Orders 
GO

复制代码

分析新创建的表的页的信息

-显示跟踪标志的状态 DBCC TRACESTATUS -开启跟踪标志 DBCC TRACEON(3604,0); line-height:1.5!important">2588) DBCC TRACEOFF(3604,2588) ---获取对象的数据页，结构：数据库、对象、显示 DBCC IND(Ixdata,Orders,128); line-height:1.5!important">-1)

/*
1：显示所有分页的信息，包括IAM分页,数据分页,所有存在的LOB分页和行溢出页,索引分页
-1：显示所有IAM、数据分页、及指定对象上全部索引的索引分页.
-2：显示指定对象的所有IAM分页
0:显示所有IAM、数据分页.
*/

复制代码

DBCC IND的表结构

还可以通过另一种方法来测试：

select so.name,so.object_id,sp.index_id,internals.total_pages,internals.used_pages,internals.data_pages,first_iam_page,first_page,root_page from sys.objects so inner join sys.partitions sp on so.object_id = sp.object_id join sys.allocation_units sa on sa.container_id = sp.hobt_id join sys.system_internals_allocation_units internals on internals.container_id = sa.container_id where so.= object_id(orders')

复制代码

?最后三个字段分别是IAM页，根页，和第一个数据页；它们分别用16进制来表示，拿first_iam_page来分析，首先将编码从右往左一个字节接着一个字节反过来排行（0X代表16进制），结果就是0X,00 01,00 00 00 50;前两个字节代表文件组号，最后4个字节代表页号。16进制的0001转换成10进制就是1；16进制的00 00 00 50转换成10进制就是5*16的1次方=5*16=80，所以第一个数据页是4*16+15=79，根页是5*16+9=89?结果和前面的查询出来的结果是一样的。从表格的otal_pages,used_pages,data_pages得到的结果也和前面查询出来的结果是一致的，总分配了17个页，使用了15个页包括13个数据页+1个IAM页+1个索引页。

手绘一张当前表格的聚集索引体系结构图：

分析索引页

-DBCC page的格式为(数据库，文件id,页号，显示)
DBCC page(Ixdata,0); line-height:1.5!important">89,0); line-height:1.5!important">3)

?分析结果89页下面的子页总共有13页，每页80条记录，89索引页记录了每页的的键值的最小值，第一页就是id为1-80,第二页81-160，所以当你要找ID为150的数据的时候直接就可以去第90页里面找了。

PAGE HEADER

?分析数据页

通过这些数据我们基本上可以知道90页的基本情况了，包括它的字段长度，上一页、下一页，还有该页的所以记录（这里没有截图出来）.

插入20万条记录分析索引结构?

插入20万条记录分析索引结构 200000) END TABLE Page ( PageFID TINYINT,PagePID INT,IAMFID BIGINT,iam_chain_type VARCHAR(30),PageType INT ); INTO Page EXEC(DBCC IND(Ixdata,-1)') -查询索引页 SELECT PageFID],PagePIDIAMFIDIAMPIDObjectIDIndexIDPartitionNumberPartitionIDiam_chain_typePageTypeIndexLevelNextPageFIDNextPagePIDPrevPageFIDPrevPagePID] FROM ].dboPageWHERE PageType2 go ')

复制代码

通过两种方法查询到的索引页的数量是一样的，下面的这种计算方法是2524-2513-1(IAM页)=10，其中807页是root_page页它在第二级，其它的是中间级索引页页就是第一级；页可以通过下面的16进制计算出来，IAM=5*16=80，ROOT_PAGE=3*16*16+2*16+7=807

再分析89页

查询结果总共有269行，页就是269个数据页，orders表总共插入了201000条记录，一个页面存80条记录，就需要2513个页面和上面查询到的data_page是一样的。每个索引页存储269个数据页面就需要（‘select 2513*1.0/269’除不尽加1）10个索引页，查询最后一个索引页2698发现它还没分页共存储了361条记录，总共8*269+361=2513

手绘存储结构?

手绘的有点难看，但是意思差不多表达出来了。

大型对象 (LOB) 列

?根据聚集索引中的数据类型，每个聚集索引结构将有一个或多个分配单元，将在这些单元中存储和管理特定分区的相关数据。每个聚集索引的每个分区中至少有一个 IN_ROW_DATA 分配单元。如果聚集索引包含大型对象 (LOB) 列，则它的每个分区中还会有一个 LOB_DATA 分配单元。如果聚集索引包含的变量长度列超过 8,060 字节的行大小限制，则它的每个分区中还会有一个 ROW_OVERFLOW_DATA 分配单元。

TABLE Orderslob (ID NVARCHAR(MAX) INTO Orderslob(NAME,Product)@ID)',REPLICATE(@ID,0); line-height:1.5!important">2)) REPLICATE(@ID,200) GO 1)

复制代码

查看2719数据页的信息 2719,0); line-height:1.5!important">1)

结果记录了每一条记录的偏移量。

每个人在自己的电脑上面测试页面id会不一样，但是反应的结果是一样的。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!