热门标签 | HotTags
当前位置:  开发笔记 > 编程语言 > 正文

SQLServer存储(1/8):理解数据页结构

我们都很清楚SQLServer用8KB的页来存储数据,并且在SQLServer里磁盘IO操作在页级执行。也就是说,SQLServer读取或写入所有数据页

我们都很清楚SQL Server用8KB 的页来存储数据,并且在SQL Server里磁盘 I/O 操作在页级执行。也就是说,SQL Server 读取或写入所有数据页。页有不同的类型,像数据页,GAM,SGAM等。在这文章里,让我们一起来理解下数据页结构。

SQL Server把数据记录存在数据页(Data Page)里。数据记录是堆表里、聚集索引里叶子节点的行。

数据页由3个部分组成。页头(标头),数据区(数据行和可用空间)及行偏移数组。

在我们讨论在SQL Server里,数据页内部结构具体是什么样之前,我们来创建一个表并插入一些记录。

 

1 USE [InternalStorageFormat]
2 GO
3
4 IF EXISTS ( SELECT *
5 FROM sysobjects
6 WHERE id = OBJECT_ID(N'[dbo].[Customers]')
7 AND OBJECTPROPERTY(id, N'IsUserTable') = 1 )
8 DROP TABLE dbo.Customers
9
10 CREATE TABLE Customers
11 (
12 FirstName CHAR(50) NOT NULL,
13 LastName CHAR(50) NOT NULL,
14 Address CHAR(100) NOT NULL,
15 ZipCode CHAR(5) NOT NULL,
16 Rating INT NOT NULL,
17 ModifiedDate DATETIME NOT NULL,
18 )
19 GO
20
21
22 INSERT INTO dbo.Customers
23 ( FirstName ,
24 LastName ,
25 Address ,
26 ZipCode ,
27 Rating ,
28 ModifiedDate
29 )30 VALUES ( 'Woody' , -- FirstName - char(50)
31 'Tu' , -- LastName - char(50)
32 'ZUOQIAO YOUXI TOWN LINHAI CITY' , -- Address - char(50)
33 '0000' , -- ZipCode - char(5)
34 1 , -- Rating - int
35 '2015-05-07 10:09:51' -- ModifiedDate - datetime
36 )
37 go 2

现在我们要找出SQL Server给这个表分配的页有哪些,这个就要用到非文档的命令DBCC IND。
它的语法如下:

DBCC IND 命令用于查询一个存储对象的内部存储结构信息,该命令有4个参数, 前3个参数必须指定。语法如下:
DBCC IND ( { 'dbname' | dbid }, { 'objname' | objid },{ nonclustered indid | 1 | 0 | -1 | -2 } [, partition_number] )
第一个参数是数据库名或数据库ID。
第二个参数是数据库中的对象名或对象ID,对象可以是表或者索引视图。
第三个参数是一个非聚集索引ID或者 1, 0, 1, or 2. 值的含义:
 0: 只显示对象的in-row data页和 in-row IAM 页。
 1: 显示对象的全部页, 包含IAM 页, in-row数据页, LOB 数据页row-overflow 数据页 . 如果请求的对象含有聚集所以则索引页也包括。
 -1: 显示全部IAM页,数据页, 索引页 也包括 LOB 和row-overflow 数据页。
 -2: 显示全部IAM页。
 Nonclustered index ID:显示索引的全部 IAM页, data页和索引页,包含LOB和 row-overflow数据页。
为了兼容sql server 2000,第四个参数是可选的,该参数用于指定一个分区号.如果不给定值或者给定0, 则显示全部分区数据。
和DBCC PAGE不同的是, SQL Server运行DBCC IND不需要开启3604跟踪标志.

我们来执行下列的命令:

1 DBCC IND('InternalStorageFormat','Customers',-1)

SQL Server会给我们如下的输出结果:

可以看到有2条记录,一条记录为页面类型(PageType)为10的页和一条记录为页面类型(PageType)为1的页。页面类型(PageType)10是IAM页,页面类型(PageType)1是数据页,它的页ID是79.

关于数据库页类型如下所示:

  • 1 Data page 堆表和聚集索引的叶子节点数据
  • 2 Index page 聚集索引的非叶子节点和非聚集索引的所有索引记录

  • 3 Text mixed page A text page that holds small chunks of LOB values plus internal parts of text tree. These can be shared between LOB values in the same partition of an index or heap.

  • 4 Text tree page A text page that holds large chunks of LOB values from a single column value.

  • 7 Sort page 排序时所用到的临时页,排序中间操作存储数据用的。

  • 8 GAM page 全局分配映射(Global Allocation Map,GAM)页面 这些页面记录了哪些区已经被分配并用作何种用途。

  • 9 SGAM page 共享全局分配映射(Shared Global Allocation Map,GAM)页面 这些页面记录了哪些区当前被用作混合类型的区,并且这些区需含有至少一个未使用的页面。

  • 10 IAM page  有关每个分配单元中表或索引所使用的区的信息

  • 11 PFS page  有关页分配和页的可用空间的信息

  • 13 boot page 记录了关于数据库的信息,仅存于每个数据库的第9页

  • 15 file header page 记录了关于数据库文件的信息,存于每个数据库文件的第0页

  • 16 DCM page 记录自从上次全备以来的数据改变的页面,以备差异备份

  • 17 BCM page 有关每个分配单元中自最后一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息

现在我们来看看79号类型为1的数据页里存放的数据,这个就要用到DBCC PAGE命令,它的语法如下:

dbcc page 命令读取数据页结构的命令DBCC Page。
该命令为非文档化的命令,具体如下:
  DBCC Page ({dbid|dbname},filenum,pagenum[,printopt])
  具体参数描述如下:
  dbid 包含页面的数据库ID
  dbname 包含页面的数据库的名称
  filenum 包含页面的文件编号
  pagenum 文件内的页面
  printopt 可选的输出选项;选用其中一个值:
  0:默认值,输出缓冲区的标题和页面标题
  1:输出缓冲区的标题、页面标题(分别输出每一行),以及行偏移量表
  2:输出缓冲区的标题、页面标题(整体输出页面),以及行偏移量表
  3:输出缓冲区的标题、页面标题(分别输出每一行),以及行偏移量表;每一行
  后跟分别列出的它的列值
  要想看到这些输出的结果,还需要设置DBCC TRACEON(3604)。

我们来执行下列的命令:

1 DBCC TRACEON(3604)
2 DBCC PAGE(InternalStorageFormat,1,79,3)
3 GO

SQL Server会给我们包含4个部分的输出。第1部分是BUFFER,里面是一些内存分配信息,对此我们没多少兴趣。下一部分是固定96 bytes大小的页头(page header),页头(page header)会类似如下显示:

页头相关字段的含义:

  • Page @0x08F84000            同BUFFER中的bpage地址
  • m_pageId = (1:79)              数据页号     
  • m_headerVersion = 1         头文件版本号,一直为1          
  • m_type = 1                          页面类型,1为数据页面
  • m_typeFlagBits = 0x4         数据页和索引页为4,其他页为0        
  • m_level = 0                         该页在索引页(B树)中的级数
  • m_flagBits = 0x8000          页面标志
  • m_objId (AllocUnitId.idObj) = 46                       同Metadata: ObjectId             
  • m_indexId (AllocUnitId.idInd) = 256                  同Metadata: IndexId
  • Metadata: AllocUnitId = 72057594040942592  存储单元的ID,sys.allocation_units.allocation_unit_id                              
  • Metadata: PartitionId = 72057594039304192   数据页所在的分区号,sys.partitions.partition_id                             
  • Metadata: IndexId = 0                                        页面的索引号,sys.objects.object_id&sys.indexes.index_id
  • Metadata: ObjectId = 277576027                      该页面所属的对象的id,sys.objects.object_id
  • m_prevPage = (0:0)                  该数据页的前一页面;主要用在数据页、索引页和IAM页
  • m_nextPage = (0:0)                  该数据页的后一页面;主要用在数据页、索引页和IAM页
  • pminlen = 221                          定长数据所占的字节数
  • m_slotCnt = 2                           页面中的数据的行数
  • m_freeCnt = 7644                    页面中剩余的空间
  • m_freeData = 544                    从第一个字节到最后一个字节的空间字节数
  • m_reservedCnt = 0                   活动事务释放的字节数
  • m_lsn = (255:8406:2)                日志记录号
  • m_xactReserved = 0                 最新加入到m_reservedCnt领域的字节数
  • m_xdesId = (0:0)                       添加到m_reservedCnt的最近的事务id
  • m_ghostRecCnt = 0                 幻影数据的行数
  • m_tornBits = 0                         页的校验位或者被由数据库页面保护形式决定分页保护位取代

再来看下页面相关分配情况:

 

  • GAM (1:2) = ALLOCATED                                                   在GAM页上的分配情况
  • SGAM (1:3) = ALLOCATED                                                 在SGAM页上的分配情况
  • PFS (1:1) = 0x61 MIXED_EXT ALLOCATED  50_PCT_FULL 在PFS页上的分配情况,该页为50%满,                       
  • DIFF (1:6) = CHANGED
  • ML (1:7) = NOT MIN_LOGGED   

接下来就是用于存放实际数据的槽(slot),每条记录存放一个槽(slot)里。0号槽在页里拥有第1条数据,1号槽拥有第2条数据,以此类推。通过下面的图片,你可以看到我们记录大小是224 bytes,217 bytes(50+50+100+5+4+8) 的定长和7 bytes 的系统行开销。

页的最后一部分是行偏移数组表,我们可以用参数为1的DBCC PAGE命令来,在输出信息的底部获得。

执行如下的命令:

1 DBCC TRACEON(3604)
2 DBCC PAGE(InternalStorageFormat,1,79,3)
3 GO

SQL Server在输出信息的底部,给我们如下的信息:

这个行偏移表,应该从下往上读。每条槽条目是一个2 bytes长的指针指向页里槽偏移量。这里我们插入了2条记录,所以表里有2个槽条目。第1条记录指向第96 bytes,刚好在页头后。这个行偏移表可以帮助我们管理页面的记录。在页里的行偏移表里,每条记录需要2 bytes的大小来存储。于此类似,在堆表上建立的非聚集索引,每个非聚集索引行里都包含一个物理指针映射回堆表里的行记录。这个物理指针是[文件号:页号:槽号](file:page:solt)的结构,因此在读取页的时候,可以找到堆表里的对应行,再通过行偏移表里槽号里的偏移量,就可以在页里读取到对应的行记录。如果我们要修改页中间的记录,我们并不一定需要重组整个页,我们只要修改偏移表里偏移量即可。

在页头我们看到当前页面还有7644 bytes可以用,我们一起来验证下。

(8 * 1024) - 96 - (217 * 2)-(7 * 2)-(2 * 2)=7644 bytes

8 * 1024 = 页的总大小,8K

         96 = 页头大小 96 bytes       

 217 * 2 = 每条记录的总长 * 记录数

     7 * 2 = 每条记录的系统行开销 * 记录数

     2 * 2 = 行偏移表里每槽占用字节数 * 记录数

现在我们已经知道了页的结构,我们一起来小结下。

页是 8KB 的大小,即 8192 bytes,固定 96 bytes的大小给页头使用,接下来是具体的数据以槽的方式存储。数据记录的最大长度是 8060 bytes(包括 7 bytes的系统行开销),因此一条记录中你拥有的最大字节数是 8053 bytes。下列的表创建语句会失败。

1 CREATE TABLE Maxsize(
2 id CHAR(8000) NOT NULL,
3 id1 CHAR(54) NOT NULL
4 )

剩下的 36 bytes (8192-96-8060)保留给槽数组(Slot array)或者任何转发行返回指针(forwarding row back pointer)(每条10 bytes)。这就意味一个页不一定就能保存18(36/2)条记录。槽数组(Slot array)根据你的记录数从下往上增长。如果记录长度小,页里就可以存储更多的记录,偏移表也会自下而上占用更多的空间。 

参考文章:

http://www.sqlservercentral.com/blogs/practicalsqldba/2012/08/12/sql-server-understanding-the-data-page-structure/

转:https://www.cnblogs.com/woodytu/p/4484328.html



推荐阅读
  • 在将Excel数据导入MySQL数据库的过程中,如何确保不会生成重复记录?本文介绍了一种方法,通过PHP脚本检查数据库中是否存在相同的“Code”字段值,从而避免重复记录的产生。该方法不仅提高了数据导入的准确性,还增强了系统的健壮性。 ... [详细]
  • 在深入掌握Spring框架的事务管理之前,了解其背后的数据库事务基础至关重要。Spring的事务管理功能虽然强大且灵活,但其核心依赖于数据库自身的事务处理机制。因此,熟悉数据库事务的基本概念和特性是必不可少的。这包括事务的ACID属性、隔离级别以及常见的事务管理策略等。通过这些基础知识的学习,可以更好地理解和应用Spring中的事务管理配置。 ... [详细]
  • 在Java分层设计模式中,典型的三层架构(3-tier application)将业务应用细分为表现层(UI)、业务逻辑层(BLL)和数据访问层(DAL)。这种分层结构不仅有助于提高代码的可维护性和可扩展性,还能有效分离关注点,使各层职责更加明确。通过合理的设计和实现,三层架构能够显著提升系统的整体性能和稳定性。 ... [详细]
  • 本文详细介绍了使用 Python 进行 MySQL 和 Redis 数据库操作的实战技巧。首先,针对 MySQL 数据库,通过 `pymysql` 模块展示了如何连接和操作数据库,包括建立连接、执行查询和更新等常见操作。接着,文章深入探讨了 Redis 的基本命令和高级功能,如键值存储、列表操作和事务处理。此外,还提供了多个实际案例,帮助读者更好地理解和应用这些技术。 ... [详细]
  • SQLite数据库CRUD操作实例分析与应用
    本文通过分析和实例演示了SQLite数据库中的CRUD(创建、读取、更新和删除)操作,详细介绍了如何在Java环境中使用Person实体类进行数据库操作。文章首先阐述了SQLite数据库的基本概念及其在移动应用开发中的重要性,然后通过具体的代码示例,逐步展示了如何实现对Person实体类的增删改查功能。此外,还讨论了常见错误及其解决方法,为开发者提供了实用的参考和指导。 ... [详细]
  • MySQL索引详解及其优化策略
    本文详细解析了MySQL索引的概念、数据结构及管理方法,并探讨了如何正确使用索引以提升查询性能。文章还深入讲解了联合索引与覆盖索引的应用场景,以及它们在优化数据库性能中的重要作用。此外,通过实例分析,进一步阐述了索引在高读写比系统中的必要性和优势。 ... [详细]
  • 在Python中,是否可以通过使用Tkinter或ttk库创建一个具有自动换行功能的多行标签,并使其宽度能够随着父容器的变化而动态调整?例如,在调整NotePad窗口宽度时,实现类似记事本的自动换行效果。这种功能在设计需要显示长文本的对话框时非常有用,确保文本内容能够完整且美观地展示。 ... [详细]
  • Java 零基础入门:SQL Server 学习笔记(第21篇)
    Java 零基础入门:SQL Server 学习笔记(第21篇) ... [详细]
  • SQL 查询实体优化与实战技巧分享 ... [详细]
  • 深入解析 Android 中 EditText 的 getLayoutParams 方法及其代码应用实例 ... [详细]
  • 本文全面解析了JavaScript中的DOM操作,并提供了详细的实践指南。DOM节点(Node)通常代表一个标签、文本或HTML属性,每个节点都具有一个nodeType属性,用于标识其类型。文章深入探讨了DOM节点的创建、查询、修改和删除等操作,结合实际案例,帮助读者更好地理解和掌握DOM编程技术。 ... [详细]
  • 如何使用mysql_nd:Python连接MySQL数据库的优雅指南
    无论是进行机器学习、Web开发还是爬虫项目,数据库操作都是必不可少的一环。本文将详细介绍如何使用Python通过 `mysql_nd` 库与 MySQL 数据库进行高效连接和数据交互。内容涵盖以下几个方面: ... [详细]
  • 在Oracle数据库中,若需更新特定列的数据,可以通过联接两张表来实现。例如,假设我们有两张表:`sales` 和 `goods`。为了更新 `sales` 表中的某些列,可以使用 `UPDATE` 语句结合 `JOIN` 操作,确保数据的准确性和一致性。具体操作步骤包括选择需要更新的目标列,定义联接条件,并指定更新后的值。这种方法不仅提高了数据处理的效率,还保证了数据的完整性。 ... [详细]
  • 在实际开发中,连接池是最常使用的数据库管理技术之一。连接池通过创建和管理一组预初始化的数据库连接,使得这些连接可以被多个线程复用,从而显著提高应用程序的性能和资源利用率。本文将详细介绍如何从零开始构建一个自定义的 MySQL 连接池,并将其与 Spring Data 进行集成,以实现高效、可靠的数据库操作。 ... [详细]
  • PHP 数组逆序排列方法及常用排序函数详解 ... [详细]
author-avatar
mobiledu2502872883
这个家伙很懒,什么也没留下!
PHP1.CN | 中国最专业的PHP中文社区 | DevBox开发工具箱 | json解析格式化 |PHP资讯 | PHP教程 | 数据库技术 | 服务器技术 | 前端开发技术 | PHP框架 | 开发工具 | 在线工具
Copyright © 1998 - 2020 PHP1.CN. All Rights Reserved | 京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有