sqlserver体系结构基础知识

1、名师精编优秀资料SQL SERVER体系结构袁旭佳目录SQL SERVE体系结构1袁旭佳1聚集索引结构2非聚集索引结构 3堆结构5表组织和索引组织6表组织6分区7聚集表、堆和索引 7非聚集索引8XML索弓| 8IN_ROW_DATA 分配单元9ROW_OVERFLOW_DATA 分配单元10LOB_DATA分配单元10分区和分配单元示例 10页和区11页11大型行支持13区13行溢出数据超过 8 KB 14行溢出注意事项 14聚集索引结构在SQL Server中，索引是按 B树结构进行组织的。索引B树中的每一页称为一个索引节点。B树的顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节

2、点之间的任何索引级别统称为中间级。在聚集索引中，叶节点包含基础表的数据页。根节点和中间级节点包含存有索引行的索引页。每个索引行 包含一个键值和一个指针，该指针指向B树上的某一中间级页或叶级索引中的某个数据行。每级索引中的页均被链接在双向链接列表中。聚集索引在sys.partitions中有一行，其中，索引使用的每个分区的 index_id = 1。默认情况下，聚集索引 有单个分区。当聚集索引有多个分区时，每个分区都有一个包含该特定分区相关数据的B树结构。例如，如果聚集索引有四个分区，就有四个 B树结构，每个分区中有一个 B树结构。根据聚集索引中的数据类型，每个聚集索引结构将有一个或多个分配单元

3、，将在这些单元中存储和管理特定分区的相关数据。每个聚集索引的每个分区中至少有一个IN_ROW_DATA分配单元。如果聚集索引包含大型对象（LOB）列，则它的每个分区中还会有一个LOB_DATA分配单元。如果聚集索引包含的变量长度列超过8,060字节的行大小限制，则它的每个分区中还会有一个R0W_0VERFL0W_DATA分配单元。有关分配单元的详细信息，请参阅表组织和索引组织。数据链内的页和行将按聚集索引键值进行排序。所有插入操作都在所插入行中的键值与现有行中的排序顺序相匹配时执行。B树页集合由sys.system_internals_allocation_units系统视图中的页指针来定位。

4、重要提示sys.system_internals_allocation_units系统视图保留为仅供 Microsoft SQL Server 内部使用不保证将来的兼容性。对于某个聚集索引，sys.system_internals_allocation_units 中的root_page 列指向该聚集索引某个特定 分区的顶部。SQL Server将在索引中向下移动以查找与某个聚集索引键对应的行。为了查找键的范围，SQLServer将在索引中移动以查找该范围的起始键值，然后用向前或向后指针在数据页中进行扫描。为了查找 数据页链的首页，SQL Server将从索引的根节点沿最左边的指针进行扫描。下

5、图显式了聚集索引单个分区中的结构。K叶书点禎辰帀Idld= 1非聚集索引结构非聚集索引与聚集索引具有相同的B树结构，它们之间的显著差别在于以下两点：* 基础表的数据行不按非聚集键的顺序排序和存储。* 非聚集索引的叶层是由索引页而不是由数据页组成。既可以使用聚集索引来为表或视图定义非聚集索引，也可以根据堆来定义非聚集索引。非聚集索引中的每个索引行都包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。非聚集索引行中的行定位器或是指向行的指针，或是行的聚集索引键，如下所述：该指针由文件标*如果表是堆（意味着该表没有聚集索引），则行定位器是指向行的指针 识符（ID）、页码和页上的行

6、数生成。整个指针称为行ID （RID） 如果表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键。如果聚集索引不是唯一的索引，SQL Server将添加在内部生成的值（称为唯一值）以使所有重复键唯一。此四字节的值对于用户不可见。仅当需要使聚集键唯一以用于非聚集索引中时，才添加该值。SQL Server通过使用存储在非聚集索引的叶行内的聚集索引键搜索聚集索引 来检索数据行。对于索引使用的每个分区，非聚集索引在index_id >0的sys.partitions中都有对应的一行。默认情况下，一个非聚集索引有单个分区。如果一个非聚集索引有多个分区，则每个分区都有一个包含该特定分区的索

7、引行的B树结构。例如，如果一个非聚集索引有四个分区，那么就有四个B树结构，每个分区中一个。根据非聚集索引中数据类型的不同，每个非聚集索引结构会有一个或多个分配单元，在其中存储和管理特定分区的数据。每个非聚集索引至少有一个针对每个分区的IN_ROW_DATA分配单元（存储索引 B树页）。如果非聚集索引包含大型对象（LOB）列，则还有一个针对每个分区的LOB_DATA分配单元。此外，如果非聚集索引包含的可变长度列超过8,060字节行大小限制，则还有一个针对每个分区的ROW_OVERFLOW_DATA 分配单元。有关分配单元的详细信息，请参阅表组织和索引组织 。B树的页集合由sys.system_i

8、nternals_allocation_units系统视图中的root_page 指针定位。重要提示sys.systemnternals_allocation_units系统视图保留为仅供 MicrosoftSQL Server内部使用。不保证以后的兼容性。下图说明了单个分区中的非聚集索引结构。”丰舉律盍引 it!或離味察引包含列的索引通过将包含列（称为非键列）添加到索引的叶级，可以扩展非聚集索引的功能。键列存储在非聚集索引的所有级别，而非键列仅存储在叶级别。有关详细信息，请参阅具有包含列的索引。堆结构堆是不含聚集索引的表。堆的sys.partitio ns中具有一行，对于堆使用的每个分区，都

9、有index_id = 0。默认情况下，一个堆有一个分区。 当堆有多个分区时， 每个分区有一个堆结构， 其中包含该特定分区的数据。例如，如果一个堆有四个分区，则有四个堆结构；每个分区有一个堆结构。根据堆中的数据类型，每个堆结构将有一个或多个分配单元来存储和管理特定分区的数据。每个堆中的每个分区至少有一个IN_ROW_DATA 分配单元。如果堆包含大型对象（LOB）列，则该堆的每个分区还将有一个LOB_DATA分配单元。如果堆包含超过8,060字节行大小限制的可变长度列，则该堆的每个分区还将有一个ROW_OVERFLOW_DATA分配单元。有关分配单元的详细信息，请参阅 表组织和索引组织。sys

10、.system_ intern als_allocati on_un its系统视图中的列 first_iam_page指向管理特定分区中堆的分配空间的一系列IAM页的第一页。SQL Server使用IAM页在堆中移动。堆内的数据页和行没有任何特定的顺序，也不链接在一起。数据页之间唯一的逻辑连接是记录在IAM页内的信息。重要提示sys.system_internals_allocation_units系统视图保留为仅供 Microsoft SQL Server 内部使用不保证将来的兼容性。可以通过扫描IAM 页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区。因为 IAM按扩展盘区在数据

11、文件内存在的顺序表示它们，所以这意味着串行堆扫描连续沿每个文件 进行。使用IAM 页设置扫描顺序还意味着堆中的行一般不按照插入的顺序返回。下图说明SQL Server数据库引擎如何使用IAM页检索具有单个分区的堆中的数据行。idindex id = 0fi rst ia m pa9e表组织和索引组织表和索引作为8 KB页的集合存储。本主题介绍表页和索引页的组织方式。表组织名师精编优秀资料下图显示了表的组织。表包含在一个或多个分区中，每个分区在一个堆或一个聚集索引结构包含数据行 堆页或聚集索引页在一个或多个分配单元中进行管理，具体的分配单元数取决于数据行中的列类型。分区表页和索引页包含在一个或多

12、个分区中。分区是用户定义的数据组织单元。默认情况下，表或索引只有一个分区，其中包含所有的表页或索引页。该分区驻留在单个文件组中。具有单个分区的表或索引相当于SQLServer早期版本中的表和索引的组织结构。当表或索引使用多个分区时，数据将被水平分区，以便根据指定的列将行组映射到各个分区。分区可以放 在数据库中的一个或多个文件组中。对数据进行查询或更新时，表或索引将被视为单个逻辑实体。有关详 细信息，请参阅 已分区表和已分区索引。若要查看表或索引使用的分区，请使用sys.partitions仃ransact-SQL）目录视图。聚集表、堆和索引SQL Server表使用下列两种方法之一来组织其分区

13、中的数据页：* 聚集表是有聚集索引的表。数据行基于聚集索引键按顺序存储。聚集索引按B树索引结构实现，B树索引结构支持基于聚集索引键值对行进行快速检索。索引中每个级别的页（包括叶级别的数据页）链接在一个双向链接的列表中。但是，通过使用键值来执行从一个级别到另一级别的导航。有关详细信息，请参阅聚集索引结构。« 堆是没有聚集索引的表。数据行不按任何特殊的顺序存储，数据页也没有任何特殊的顺序。数据页不在链接列表内链接。有关详细信息，请参阅堆结构。索引视图与聚集表具有相同的存储结构。当堆或聚集表具有多个分区时，每个分区都有一个堆或B树结构，其中包含该指定分区的行组。例如，如果一个聚集表有4个分

14、区，那么将有4个B树，每个分区一个。非聚集索引非聚集索引与聚集索引有一个相似的B树索引结构。不同的是，非聚集索引不影响数据行的顺序。叶级别包含索引行。每个索引行包含非聚集键值、行定位符和任意包含列或非键列。定位符指向包含键值的数据行。有关详细信息，请参阅 非聚集索引结构。XML索引可以对表中的每个 xml列创建一个主XML索引和多个辅助 XML索引。XML索引是 xml数据类型列 中的XML二进制大型对象(BLOB)的拆分和持久化的表示形式。 XML索引以内部表的形式存储。 若要查看 有关 XML 索引的信息，请使用 sys.xml_indexes 或 ernal_tables目

15、录视图。有关XML索引的详细信息，请参阅 XML数据类型列的索引。分配单元分配单元是堆或B树内用于根据页类型管理数据的页集合。下表列岀了用于管理表和索引中的数据的分配单元类型。分配单元类型用于管理IN_ROW_DATA包含除大型对象(LOB)数据以外的所有数据的数据行或索引行。页的类型为 Data 或Index。LOB_DATA以下列一种或多种数据类型存储的大型对象数据：text、ntext、image、xml、varchar(max)、nvarchar(max)、varbinary(max)或 CLR 用户定义类型(CLR UDT)。页的类型为Text/Image。ROW_OVERFLOW_

16、DATA存储在超过 8,060 字节行大小限制的varchar、nvarchar、varbinarysql_variant列中的可变长度数据。页的类型为Text/lmage有关页类型的详细信息，请参阅页和区。在堆或B树的特定分区中，每种类型只能有一个分配单元。若要查看表或索引的分配单元信息，请使用 sys.allocation_units目录视图。IN_ROW_DATA分配单元对于表（堆或聚集表）、索引或索引视图使用的每个分区，只有一个IN_ROW_DATA分配单元，它由一个数据页集合构成。此分配单元还包含其他页集合，这些集合用来实现为表或视图定义的每个非聚集索引和XML索引。表、索引或索引视

17、图的每个分区中的页集合由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之间具有一对一的映射，用于维护分区中存储的表、索引或索引视

18、图数据与用来管理分区内数据的分配单元之间的关系。表、索引或索引视图分区的页分配由一个IAM 页链管理。sys.systemnternals_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链。服

19、务器使用IAM页查找数据页集合中的页，因为这些页没有链接。*表或视图的聚集索引在sys.partitions中有一行，其index_id = 1。sys.system_internals_allocation_units中的 root_page列指向指定分区内聚集索弓丨B 树的顶端。服务器使用索引 B树查找分区中的数据页。为表或视图创建的每个非聚集索引在sys.partitions中有一行，其 index_id > 1中的 root_page列指向指定分区内非聚集索引sys.system_internals_allocation_units 树的顶端。« 至少有一个LOB列的每

20、个表在sys.partitions中也有一行，其 index_id > 250first_iam_page列指向管理LOB_DATA分配单元中的页的IAM页链R0W_0VERFL0W_DATA 分配单元对于表(堆或聚集表)、索引或索引视图使用的每个分区，都有一个ROW_OVERFLOW_DA分配单元。此分配单元包含零(0) 页，直到IN_ROW_DATA分配单元中带有可变长度列( varchar、nvarchar、varbinary 或 sql_variant )的数据行超过 8 KB的行大小限制。达到大小限制后，SQLServer将把最宽的列从该行移动到 ROW_OVERFLOW_DA

21、分配单元中的页。原始页上将维护一个指向此行外数据的24字节指针。ROW_OVERFLOW_DAT配单元中Text/Image 页的管理方式与LOB_DATA分配单元中页的管理方式相同。 即，使用IAM 页链管理Text/Image 页。LOB_DATA分配单元当表或索引有一个或多个 LOB数据类型时，将为每个分区分配一个 LOB_DATA分配单元，以管理该数据的 存储。LOB 数据类型包括 text、ntext、image、xml、varchar(max)、nvarchar(max)、varbinary(max) 和 CLR用户定义类型。分区和分配单元示例下列示例返回两个表的分区和分配单元数据

22、：DatabaseLog ，具有LOB数据但没有非聚集索引的堆;Currency ，没有LOB数据但有一个非聚集索引的聚集表。两个表都有一个分区。USE AdventureWorks2008R2;GOSELECT AS table_name,p.index_id, AS index_name , au.type_desc AS alloc ation_type, au.data_pages, partition_numberFROM sys.allocation_units AS auJOIN sys.partitions AS p ON au.container_

23、id = p.partition_idJOIN sys.objects AS o ON p.object_id = o.object_idJOIN sys.indexes AS i ON p.index_id = i.index_id AND i.object_id = p.object_idWHERE = N'DatabaseLog' OR = N'Currency'ORDER BY , p.index_id;下面是结果集。请注意，DatabaseLog表使用所有三个分配单元类型，因为表中包含Data和Text/lmag

24、e页类型。Currency 表没有LOB数据，但具有管理数据页所需的分配单元。如果以后 将 Currency表修改为包含LOB数据类型列，将创建一个LOB_DATA分配单元来管理该数据。table_name index_id index_nameallocation_type data_pages partition_numberCurrency 1 PK_Currency_CurrencyCode IN_ROW_DATA 11Currency 3 AK_Currency_Name IN_ROW_DATA 11DatabaseLog 0NULLIN_ROW_DATA1601DatabaseLo

25、g 0NULLROW_OVERFLOW_DATA 01DatabaseLog 0NULLLOB_DATA491(5 row(s) affected)页和区SQLServer中数据存储的基本单位是页。为数据库中的数据文件( .mdf或.ndf )分配的磁盘空间可以从 逻辑上划分成页(从 0到 n连续编号)。磁盘I/O 操作在页级执行。也就是说，SQL Server读取或写入所有数据页。区是八个物理上连续的页的集合，用来有效地管理页。所有页都存储在区中在SQL Server中，页的大小为8 KB这意味着SQL Server数据库中每 MB有128页。每页的开头是96字节的标头，用于存储有关页的系统

26、信息。此信息包括页码、页类型、页的可用空间以及拥有该页的对象的分配单元ID 。下表说明了 SQL Server数据库的数据文件中所使用的页类型。页类型内容Data当 text in row设置为ON时，包含除 text、ntext、image、nvarchar(max) 、varchar(max) 、varbinary(max) 和xml数据之外的所有数据的数据行。Index索引条目。Text/lmage大型对象数据类型： text、 ntext、image、nvarchar(max) 、varchar(max) 、varbinary(max) 和 xml数据。数据行超过8 KB时为可变长度数

27、据类型列： varchar、nvarchar、varbinary和 sql_variantGlobal Allocation MapShared GlobalAllocation Map有关区是否分配的信息。Page Free Space有关页分配和页的可用空间的信息。Index Allocation Map有关每个分配单元中表或索引所使用的区的信息。Bulk Changed Map有关每个分配单元中自最后一条BACKUP LOG语句之后的大容量操作所修改的区的信息。Differential ChangedMap有关每个分配单元中自最后一条 BACKUP DATABASES句之后更改的区的信息

28、。注意日志文件不包含页， 而是包含一系列日志 记录。在数据页上，数据行紧接着标头按顺序放置。页的末尾是行偏移表，对于页中的每一行，每个行偏移表都 包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺 序相反。大型行支持行不能跨页，但是行的部分可以移岀行所在的页，因此行实际可能非常大。页的单个行中的最大数据量和开销是8,060 字节(8 KB)。但是，这不包括用Text/lmage 页类型存储的数据。包含 varchar、nvarchar、 varbinary 或 sql_variant列的表不受此限制的约束。当表中的所有固定列和可变列的行的总大小超过限制

29、的8,060字节时，SQL Server将从最大长度的列开始动态将一个或多个可变长度列移动到 ROW_OVERFLOW_DAT配单元中的页。每当插入或更新操作将行的总大小增大到超过限制的 8,060字节时, 将会执行此操作。将列移动到ROW_OVERFLOW_DA分配单元中的页后，将在IN_ROW_DATA分配单元中的原始页上维护24字节的指针。如果后续操作减小了行的大小，SQLServer会动态将列移回到原始数据页。 有关详细信息，请参阅行溢岀数据超过8 KB。区区是管理空间的基本单位。一个区是八个物理上连续的页(即64 KB )。这意味着SQL Server数据库中每MB有16个区。为了使

30、空间分配更有效，SQL Server不会将所有区分配给包含少量数据的表。SQL Server有两种类型的区： 统一区，由单个对象所有。区中的所有8页只能由所属对象使用。« 混合区，最多可由八个对象共享。区中八页的每页可由不同的对象所有。通常从混合区向新表或索引分配页。当表或索引增长到8页时，将变成使用统一区进行后续分配。如果对现有表创建索引，并且该表包含的行足以在索引中生成8页，则对该索引的所有分配都使用统一区进行。t 葩佢 2 index 1iabl2 UbleJ mdex3 labl2 tabledtable! Lab lei tablel table! table 1 tabl

31、e 1 Labial la blel行溢出数据超过KB一个表中的每一行最多可以包含8,060字节。在 SQL Server 2008 中，对于包含 varchar、nvarchar、varb in ary 、sql_varia nt 或CLR用户定义类型列的表，可以放宽此限制。其中每列 的长度仍必须在8,000字节的限制内，但是它们的总宽可以超过8,060字节的限制。创建和修改varchar、nvarchar、varbinary 、sql_variant 或 CLR用户定义类型的列以及更新或插入数 据时，此限制适用于上述列。注意此限制不适用于varchar(max) 、nvarchar(max) 、varbinary(max) 、text、image 或 xml 歹农有关这些列的存储的详细信息，请参阅使用大值数据类型、使用Text和Image数据和使用XML数据行溢出注意事项当合并每行超过8060 字节的varchar、n varchar、varb in ary 、sql_varia nt 或CLR用户定义类型的列时，请注意下列事项：* 超