SQL Server 深入解析索引存储(上)
来源:互联网 发布:ipad绘画漫画软件 编辑:程序博客网 时间:2024/05/18 01:54
标签:SQL SERVER/MSSQL SERVER/数据库/DBA/索引体系结构/堆/聚集索引
概述
最近要分享一个课件就重新把这块知识整理了一遍出来,篇幅有点长,想要理解的透彻还是要上机实践。
正文
聚集索引
--创建测试数据库CREATE DATABASE IxdataGOUSE [Ixdata]GO---创建测试表CREATE TABLE Orders(ID INT PRIMARY KEY IDENTITY(1,1),NAME CHAR(80)NOT NULL,IDATE DATETIME NOT NULL DEFAULT(GETDATE()));GO---插入1000条测试数据DECLARE @ID INT=1WHILE(@ID<=1000)BEGININSERT INTO Orders(NAME)VALUES('商品'+CONVERT(NVARCHAR(20),@ID))SET @ID=@ID+1 ENDGOSELECT * FROM Orders GO
分析新创建的表的页的信息
---显示跟踪标志的状态DBCC TRACESTATUS---开启跟踪标志DBCC TRACEON(3604,2588)--DBCC TRACEOFF(3604,2588)---获取对象的数据页,结构:数据库、对象、显示DBCC IND(Ixdata,Orders,-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_pagefrom sys.objects soinner join sys.partitions sp on so.object_id = sp.object_idinner join sys.allocation_units sa on sa.container_id = sp.hobt_idinner join sys.system_internals_allocation_units internals on internals.container_id = sa.container_idwhere so.object_id = 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,1,89,3)
分析结果89页下面的子页总共有13页,每页80条记录,89索引页记录了每页的的键值的最小值,第一页就是id为1-80,第二页81-160,所以当你要找ID为150的数据的时候直接就可以去第90页里面找了。
PAGE HEADER
分析数据页
通过这些数据我们基本上可以知道90页的基本情况了,包括它的字段长度,上一页、下一页,还有该页的所以记录(这里没有截图出来).
插入20万条记录分析索引结构
--插入20万条记录分析索引结构 DECLARE @ID INT=1WHILE(@ID<=200000)BEGININSERT INTO Orders(NAME)VALUES('商品'+CONVERT(NVARCHAR(20),@ID))SET @ID=@ID+1 ENDCREATE TABLE Page( PageFID TINYINT, PagePID INT, IAMFID TINYINT, IAMPID INT, ObjectID INT, IndexID TINYINT, PartitionNumber TINYINT, PartitionID BIGINT, iam_chain_type VARCHAR(30), PageType TINYINT, IndexLevel TINYINT, NextPageFID TINYINT, NextPagePID INT, PrevPageFID TINYINT, PrevPagePID INT );GOINSERT INTO Page EXEC('DBCC IND(Ixdata,Orders,-1)')---查询索引页SELECT [PageFID] ,[PagePID] ,[IAMFID] ,[IAMPID] ,[ObjectID] ,[IndexID] ,[PartitionNumber] ,[PartitionID] ,[iam_chain_type] ,[PageType] ,[IndexLevel] ,[NextPageFID] ,[NextPagePID] ,[PrevPageFID] ,[PrevPagePID] FROM [Ixdata].[dbo].[Page] WHERE PageType=2 go select so.name, so.object_id, sp.index_id, internals.total_pages, internals.used_pages, internals.data_pages,first_iam_page, first_page, root_pagefrom sys.objects soinner join sys.partitions sp on so.object_id = sp.object_idinner join sys.allocation_units sa on sa.container_id = sp.hobt_idinner join sys.system_internals_allocation_units internals on internals.container_id = sa.container_idwhere so.object_id = object_id('orders')
通过两种方法查询到的索引页的数量是一样的,下面的这种计算方法是2524-2513-1(IAM页)=10,其中807页是root_page页它在第二级,其它的是中间级索引页页就是第一级;页可以通过下面的16进制计算出来,IAM=5*16=80,ROOT_PAGE=3*16*16+2*16+7=807
再分析89页
---DBCC page的格式为(数据库,文件id,页号,显示)DBCC page(Ixdata,1,89,3)
查询结果总共有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 分配单元。
---创建测试表CREATE TABLE Orderslob(ID INT PRIMARY KEY IDENTITY(1,1),NAME CHAR(80)NOT NULL,Product NVARCHAR(MAX) NOT NULL,IDATE DATETIME NOT NULL DEFAULT(GETDATE()));GO---插入1000条测试数据DECLARE @ID INT=1WHILE(@ID<=1000)BEGININSERT INTO Orderslob(NAME,Product)VALUES(CONVERT(NVARCHAR(20),@ID)+'商品',REPLICATE(@ID,2))SET @ID=@ID+1 END--REPLICATE(@ID,200)GODBCC IND(Ixdata,Orderslob,1)
--查看2719数据页的信息DBCC page(Ixdata,1,2719,1)
结果记录了每一条记录的偏移量。
每个人在自己的电脑上面测试页面id会不一样,但是反应的结果是一样的。
总结
本来想全部写完的,等写完这部分的时候发现篇幅已经有点长了,而且自己也有的吃不消熬到1点才写完,接下来还有中下两部分会尽快在几天内写完,欢迎关注。
备注:
作者:pursuer.chen
博客:http://www.cnblogs.com/chenmh
本站点所有随笔都是原创,欢迎大家转载;但转载时必须注明文章来源,且在文章开头明显处给明链接,否则保留追究责任的权利。
《欢迎交流讨论》
- SQL Server 深入解析索引存储(上)
- SQL Server 深入解析索引存储(二)
- SQL Server 深入解析索引存储(三)
- SQL Server 深入解析索引存储(中)
- SQL Server 深入解析索引存储(下)
- SQL Server 深入解析索引存储(下)
- SQL Server索引解析
- SQLServer 深入解析索引存储(一)
- SQL Server 表和索引存储结构
- SQL Server 表和索引存储结构
- SQL Server 2016:内存列存储索引
- SQL Server索引进阶第二篇:深入非聚集索引
- SQL Server索引进阶第二篇:深入非聚集索引
- SQL Server索引进阶第二篇:深入非聚集索引
- 解析SQL Server 的存储过程
- 解析 SQL Server 的存储过程
- SQL Server 存储过程解析XML传参
- SQL Server 2012新特性_列存储索引(1)
- MVC4 razor与aspx的区别以及用法1,2
- Android 的 PF_PACKET 限制
- MFC使用多字节字符集后界面变成老式风格(WIN95风格)
- 贪心算法 Problem E 1004
- SQL Server 系统数据库恢复
- SQL Server 深入解析索引存储(上)
- SQL Server 深入解析索引存储(中)
- SQL Server 深入解析索引存储(下)
- SQL Server 重新组织生成索引
- SQL Server 变更数据捕获(CDC)
- tcp/ip、http、socket区别
- forward与redirect区别
- SQL Server 执行计划缓存
- 搭建域服务器和DNS