2022年sqlserver体系结构基础知识.docx

名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师精编 优秀资料SQL SERVER体系结构袁旭佳目录SQL SERVER体系结构 . 1 袁旭佳 . 1 集合索引结构 . 2 非集合索引结构 . 3 堆结构 . 5表组织和索引组织 . 6 表组织 . 6 分区 . 7 集合表、堆和索引 . 7 非集合索引 . 8XML 索引 . 8 IN_ROW_DATA 安排单元 . 9 ROW_OVERFLOW_DATA 安排单元 . 10 LOB_DATA 安排单元 . 10 分区和安排单元示例 . 10 页和区 . 11 页 . 11 大型行支持 . 13 区 . 13 行溢出数据超过8 KB . 14 第 1 页,共 15 页 - - - - - - - - - 行溢出留意事项. 14 细心整理归纳 精选学习资料 - - - - - - - - - - - - - - -名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师精编 优秀资料集合索引结构在 SQL Server 中,索引是按B 树结构进行组织的；索引B 树中的每一页称为一个索引节点；B 树的顶端节点称为根节点；索引中的底层节点称为叶节点；根节点与叶节点之间的任何索引级别统称为中间级；在集合索引中,叶节点包含基础表的数据页；根节点和中间级节点包含存有索引行的索引页；每个索引行包含一个键值和一个指针,该指针指向B 树上的某一中间级页或叶级索引中的某个数据行；每级索引中的页均被链接在双向链接列表中；集合索引在 sys.partitions 中有一行,其中,索引使用的每个分区的 index_id = 1 ；默认情形下,集合索引有单个分区；当集合索引有多个分区时,每个分区都有一个包含该特定分区相关数据的 B 树结构；例如,假如集合索引有四个分区,就有四个 B 树结构,每个分区中有一个 B 树结构；依据集合索引中的数据类型,每个集合索引结构将有一个或多个安排单元,将在这些单元中储备和治理特定分区的相关数据；每个集合索引的每个分区中至少有一个 IN_ROW_DATA 安排单元；假如集合索引包含大型对象 LOB 列,就它的每个分区中仍会有一个 LOB_DATA 安排单元；假如集合索引包含的变量长度列超过 8,060 字节的行大小限制,就它的每个分区中仍会有一个 ROW_OVERFLOW_DATA 安排单元；有关安排单元的具体信息,请参阅 表组织和索引组织；数据链内的页和行将按集合索引键值进行排序；全部插入操作都在所插入行中的键值与现有行中的排序顺序相匹配时执行；B 树页集合由sys.system_internals_allocation_units系统视图中的页指针来定位；重要提示sys.system_internals_allocation_units系统视图保留为仅供 Microsoft SQL Server 内部使用；不保证将来的兼容性；对于某个集合索引,sys.system_internals_allocation_units 中的 root_page 列指向该集合索引某个特定分区的顶部； SQL Server 将在索引中向下移动以查找与某个集合索引键对应的行；为了查找键的范畴, SQL Server 将在索引中移动以查找该范畴的起始键值,然后用向前或向后指针在数据页中进行扫描；为了查找数据页链的首页,SQL Server 将从索引的根节点沿最左边的指针进行扫描；下图显式了集合索引单个分区中的结构；细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 2 页,共 15 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师精编 优秀资料非集合索引结构非集合索引与集合索引具有相同的 B 树结构,它们之间的显著差别在于以下两点：基础表的数据行不按非集合键的次序排序和储备；非集合索引的叶层是由索引页而不是由数据页组成；既可以使用集合索引来为表或视图定义非集合索引,也可以依据堆来定义非集合索引；非集合索引中的每个索引行都包含非集合键值和行定位符；据行；此定位符指向集合索引或堆中包含该键值的数非集合索引行中的行定位器或是指向行的指针,或是行的集合索引键,如下所述：细心整理归纳 精选学习资料 假如表是堆（意味着该表没有集合索引）,就行定位器是指向行的指针；该指针由文件标 第 3 页,共 15 页 识符 ID 、页码和页上的行数生成；整个指针称为行ID RID ； - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师精编优秀资料假如集合索假如表有集合索引或索引视图上有集合索引,就行定位器是行的集合索引键；引不是唯独的索引,SQL Server 将添加在内部生成的值（称为 唯独值 ）以使全部重复键唯独； 此四字节的值对于用户不行见；仅当需要使集合键唯独以用于非集合索引中时,才添加该值； SQL Server 通过使用储备在非集合索引的叶行内的集合索引键搜寻集合索引来检索数据行；对于索引使用的每个分区,非集合索引在 index_id >0 的 sys.partitions 中都有对应的一行；默认情形下, 一个非集合索引有单个分区；假如一个非集合索引有多个分区,就每个分区都有一个包含该特定分区的索引行的 B 树结构；例如,假如一个非集合索引有四个分区,那么就有四个B 树结构,每个分区中一个；依据非集合索引中数据类型的不同,每个非集合索引结构会有一个或多个安排单元,在其中储备和治理特定分区的数据；每个非集合索引至少有一个针对每个分区的 IN_ROW_DATA 安排单元（储备索引 B 树页）；假如非集合索引包含大型对象 LOB 列,就仍有一个针对每个分区的LOB_DATA 安排单元；此外,假如非集合索引包含的可变长度列超过8,060 字节行大小限制,就仍有一个针对每个分区的 ROW_OVERFLOW_DATA 安排单元；有关安排单元的具体信息,请参阅 表组织和索引组织；B 树的页集合由 sys.system_internals_allocation_units 系统视图中的 root_page 指针定位；重要提示sys.system_internals_allocation_units系统视图保留为仅供 Microsoft SQL Server 内部使用；不保证以后的兼容性；下图说明白单个分区中的非集合索引结构；细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 4 页,共 15 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师精编 优秀资料包含列的索引通过将包含列（称为非键列）添加到索引的叶级,可以扩展非集合索引的功能；键列储备在非聚集索引的全部级别,而非键列仅储备在叶级别；有关具体信息,请参阅具有包含列的索引；堆结构堆是不含集合索引的表；堆的 sys.partitions 中具有一行,对于堆使用的每个分区,都有 index_id = 0 ；默认情形下, 一个堆有一个分区；当堆有多个分区时,每个分区有一个堆结构,其中包含该特定分区的数据；例如,假如一个堆有四个分区,就有四个堆结构；每个分区有一个堆结构；依据堆中的数据类型,每个堆结构将有一个或多个安排单元来储备和治理特定分区的数据；每个 第 5 页,共 15 页 - - - - - - - - - 堆中的每个分区至少有一个IN_ROW_DATA 安排单元；假如堆包含大型对象LOB 列,就该堆的每个分区仍将有一个LOB_DATA 安排单元；假如堆包含超过8,060 字节行大小限制的可变长度列,就该堆的每个分区仍将有一个ROW_OVERFLOW_DATA 安排单元；有关安排单元的具体信息,请参阅表组织和索引组织；细心整理归纳 精选学习资料 - - - - - - - - - - - - - - -名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -sys.system_internals_allocation_units名师精编优秀资料first_iam_page指向治理特定分区中系统视图中的列堆的安排空间的一系列IAM 页的第一页； SQL Server 使用IAM 页在堆中移动；堆内的数据页和行没有任何特定的次序,IAM 页内的也不链接在一起；数据页之间唯独的规律连接是记录在信息；重要提示sys.system_internals_allocation_units系统视图保留为仅供 Microsoft SQL Server 内部使用；不保证将来的兼容性；可以通过扫描 IAM 页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区；由于IAM 按扩展盘区在数据文件内存在的次序表示它们,所以这意味着串行堆扫描连续沿每个文件进行；使用 IAM 页设置扫描次序仍意味着堆中的行一般不依据插入的次序返回；下图说明 SQL Server 数据库引擎 如何使用 IAM 页检索具有单个分区的堆中的数据行；表组织和索引组织表和索引作为 8 KB 页的集合储备；本主题介绍表页和索引页的组织方式；表组织细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 6 页,共 15 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师精编 优秀资料下图显示了表的组织；表包含在一个或多个分区中,每个分区在一个堆或一个集合索引结构包含数据行；堆页或集合索引页在一个或多个安排单元中进行治理,具体的安排单元数取决于数据行中的列类型；分区表页和索引页包含在一个或多个分区中；分区是用户定义的数据组织单元；默认情形下,表或索引只有一个分区, 其中包含全部的表页或索引页；该分区驻留在单个文件组中；具有单个分区的表或索引相当于 SQL Server 早期版本中的表和索引的组织结构；当表或索引使用多个分区时,数据将被水平分区,以便依据指定的列将行组映射到各个分区；分区可以放 在数据库中的一个或多个文件组中；对数据进行查询或更新时,表或索引将被视为单个规律实体；有关详细信息,请参阅 已分区表和已分区索引；sys.partitions Transact-SQL目录视图；如要查看表或索引使用的分区,请使用集合表、堆和索引SQL Server 表使用以下两种方法之一来组织其分区中的数据页：集合表是有集合索引的表；数据行基于集合索引键按次序储备；集合索引按 B 树索引结构实现,B 树索引结构支持基于集合索引键值对行进行快速检索；索引中每个级别的页（包括叶级别的数据页）链接在一个双向链接的细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 7 页,共 15 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师精编 优秀资料列表中；但是,通过使用键值来执行从一个级别到另一级别的导航；有关具体信息,请参阅 集合索引结构 ；堆是没有集合索引的表；数据行不按任何特别的次序储备,数据页也没有任何特别的次序；数据页不在链接列表内链接；有关具体信息,请参阅 堆结构 ；索引视图与集合表具有相同的储备结构；当堆或集合表具有多个分区时,每个分区都有一个堆或 B 树结构,其中包含该指定分区的行组；例如,如果一个集合表有 4 个分区,那么将有 4 个 B 树,每个分区一个；非集合索引非集合索引与集合索引有一个相像的 B 树索引结构； 不同的是, 非集合索引不影响数据行的次序；叶级别包含索引行；每个索引行包含非集合键值、行定位符和任意包含列或非键列；定位符指向包含键值的数据行；有关具体信息,请参阅 非集合索引结构 ；XML 索引可以对表中的每个 xml 列创建一个主 XML 索引和多个帮助 XML 索引； XML 索引是 xml 数据类型列中的 XML 二进制大型对象 BLOB 的拆分和长久化的表示形式；XML 索引以内部表的形式储备；如要查看有关 XML 索引的信息,请使用 sys.xml_indexes 或 sys.internal_tables 目录视图；有关 XML 索引的具体信息,请参阅 XML 数据类型列的索引 ；安排单元安排单元是堆或 B 树内用于依据页类型治理数据的页集合；单元类型；安排单元类型 用于治理下表列出了用于治理表和索引中的数据的安排IN_ROW_DATA 包含除大型对象 LOB 数据以外的全部数据的数据行或索引行；页的类型为 Data 或 Index ；LOB_DATA 以以下一种或多种数据类型储备的大型对象数据：text 、ntext 、image、 xml、细心整理归纳 精选学习资料 varcharmax、nvarcharmax 、 varbinarymax或 CLR 用户定义类型 CLR UDT ；页的类型为 Text/Image； 第 8 页,共 15 页 - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -ROW_OVERFLOW_DATA 名师精编优秀资料varchar 、nvarchar 、varbinary或储备在超过 8,060 字节行大小限制的sql_variant 列中的可变长度数据；页的类型为 Text/Image；有关页类型的具体信息,请参阅 页和区 ；在堆或 B 树的特定分区中,每种类型只能有一个安排单元；如要查看表或索引的安排单元信息,请使用sys.allocation_units目录视图；IN_ROW_DATA 安排单元对于表（堆或集合表）、索引或索引视图使用的每个分区,只有一个 IN_ROW_DATA 安排单元,它由一个数据页集合构成； 此安排单元仍包含其他页集合,这些集合用来实现为表或视图定义的每个非集合索引和 XML 索引；表、索引或索引视图的每个分区中的页集合由 sys.system_internals_allocation_units 系统视图中的页指针定位；重要提示sys.system_internals_allocation_units系统视图保留为仅供 Microsoft SQL Server 内部使用；不保证将来的兼容性；每个表、索引和索引视图分区在 sys.system_internals_allocation_units 中有一行,该行由容器 ID container_id 唯独标识；容器 ID 与 sys.partitions 目录视图中的 partition_id 之间具有一对一的映射,用于保护分区中储备的表、索引或索引视图数据与用来治理分区内数据的安排单元之间的关系；表、索引或索引视图分区的页安排由一个 IAM 页链治理； sys.system_internals_allocation_units 中的 first_iam_page 列指向 IAM 页链（用于治理安排给 IN_ROW_DATA 安排单元中的表、索引或索引视图的空间）中的第一个 IAM 页；sys.partitions 为表或索引中每个分区返回一行；堆在 sys.partitions 中有一行,其 index_id = 0 ；sys.system_internals_allocation_units 中的 first_iam_page 列指向指定分区中堆数据页集合的 IAM 链；服务器使用 IAM 页查找数据页集合中的页,由于这些页没有链接；细心整理归纳 精选学习资料 表或视图的集合索引在sys.partitions中有一行,其index_id= 1 ； B 树 第 9 页,共 15 页 sys.system_internals_allocation_units中的root_page列指向指定分区内集合索引的顶端；服务器使用索引 B 树查找分区中的数据页；为表或视图创建的每个非集合索引在sys.partitions中有一行,其index_id> 1 ； - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师精编优秀资料列指向指定分区内非集合索引 B sys.system_internals_allocation_units中的root_page树的顶端；至少有一个 LOB 列的每个表在 sys.partitions 中也有一行,其 index_id > 250 ；first_iam_page 列指向治理 LOB_DATA 安排单元中的页的 IAM 页链；ROW_OVERFLOW_DATA 安排单元对于表（堆或集合表）、索引或索引视图使用的每个分区,都有一个 ROW_OVERFLOW_DATA 安排单元；此分配单元包含零 0 页,直到 IN_ROW_DATA 安排单元中带有可变长度列（varchar 、nvarchar 、varbinary 或 sql_variant）的数据行超过 8 KB 的行大小限制；达到大小限制后,SQL Server 将把最宽的列从该行移动到 ROW_OVERFLOW_DATA 安排单元中的页； 原始页上将保护一个指向此行外数据的 24 字节指针；ROW_OVERFLOW_DATA 安排单元中 Text/Image 即,使用 IAM 页链治理 Text/Image 页；LOB_DATA 安排单元页的治理方式与 LOB_DATA 安排单元中页的治理方式相同；当表或索引有一个或多个 LOB 数据类型时, 将为每个分区安排一个 LOB_DATA 安排单元, 以治理该数据的储备； LOB 数据类型包括text 、ntext 、image、xml 、varcharmax、nvarcharmax、varbinarymax和CLR 用户定义类型；分区和安排单元示例以下示例返回两个表的分区和安排单元数据：DatabaseLog,具有 LOB 数据但没有非集合索引的堆；Currency,没有 LOB 数据但有一个非集合索引的集合表；两个表都有一个分区；USE AdventureWorks2022R2; GO SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS alloc ation_type, au.data_pages, partition_number FROM sys.allocation_units AS au JOIN sys.partitions AS p ON au.container_id = p.partition_id JOIN sys.objects AS o ON p.object_id = o.object_id JOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_id WHERE o.name = N'DatabaseLog' OR o.name = N'Currency' ORDER BY o.name, p.index_id; 细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 10 页,共 15 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -下面是结果集；请留意,DatabaseLog名师精编优秀资料 Data 和表使用全部三个安排单元类型,由于表中包含Text/Image 页类型； Currency 表没有 LOB 数据,但具有治理数据页所需的安排单元；假如以后将 Currency 表修改为包含 LOB 数据类型列,将创建一个 LOB_DATA 安排单元来治理该数据；table_name index_id index_name allocation_type data_pages partition_number - - - - - - Currency 1 PK_Currency_CurrencyCode IN_ROW_DATA 1 1 Currency 3 AK_Currency_Name IN_ROW_DATA 1 1 DatabaseLog 0 NULL IN_ROW_DATA 160 1 DatabaseLog 0 NULL ROW_OVERFLOW_DATA 0 1 DatabaseLog 0 NULL LOB_DATA 49 1 5 rows affected 页和区SQL Server 中数据储备的基本单位是页；为数据库中的数据文件（.mdf 或 .ndf）安排的磁盘空间可以从规律上划分成页（从 0 到 n 连续编号）；磁盘 I/O 操作在页级执行；也就是说,SQL Server 读取或写入全部数据页；区是八个物理上连续的页的集合,用来有效地治理页；全部页都储备在区中；页在 SQL Server 中,页的大小为 8 KB；这意味着 SQL Server 数据库中每 MB 有 128 页；每页的开头是 96 字节的标头,用于储备有关页的系统信息；此信息包括页码、页类型、页的可用空间以及拥有该页的对象的安排单元 ID ；下表说明白 SQL Server 数据库的数据文件中所使用的页类型； 第 11 页,共 15 页 页类型内容Data 当text in row设置为 ON 时,包含除text 、Index ntext 、 image、nvarcharmax、 varcharmax、varbinarymax和xml数据之外的全部数据的数据行；索引条目；细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -Text/Image 名师精编优秀资料大型对象数据类型：text 、ntext 、 image、nvarcharmax、varcharmax、varbinarymax和xml数据；数据行超过 8 KB 时为可变长度数据类型列：Global Allocation Map、varchar 、nvarchar 、varbinary和sql_variant有关区是否安排的信息；Shared Global Allocation Map Page Free Space 有关页安排和页的可用空间的信息；Index Allocation Map 有关每个安排单元中表或索引所使用的区的信息；Bulk Changed Map 有关每个安排单元中自最终一条 BACKUP LOG 语句之后的大容量操作所修改的区的信息；Differential Changed 有关每个安排单元中自最终一条 BACKUP DATABASE 语句之后更换的区的信息；Map 留意日志文件不包含页,而是包含一系列日志记录；在数据页上,数据行紧接着标头按次序放置；页的末尾是行偏移表,对于页中的每一行,每个行偏移表都包含一个条目；每个条目记录对应行的第一个字节与页首的距离；行偏移表中的条目的次序与页中行的顺序相反；细心整理归纳 精选学习资料 - - - - - - - - - - - - - - - 第 12 页,共 15 页 - - - - - - - - - 名师归纳总结 精品学习资料 - - - - - - - - - - - - - - -名师精编 优秀资料大型行支持行不能跨页,但是行的部分可以移出行所在的页,因此行实际可能特别大；页的单个行中的最大数据量和开销是 8,060 字节 8 KB；但是,这不包括用 Text/Image 页类型储备的数据； 包含 varchar 、nvarchar 、varbinary 或 sql_variant 列的表不受此限制的约束；当表中的全部固定列和可变列的行的总大小超过限制的 8,060 字节时, SQL Server 将从最大长度的列开头动态将一个或多个可变长度列移动到ROW_OVERFLOW_DATA 安排单元中的页； 每当插入或更新操作将行的总大小增大到超过限制的 8,060 字节时,将会执行此操作；将列移动到 ROW_OVERFLOW_DATA