数据库引擎 - 表和索引数据结构体系结构
2013-02-18 11:00 BIWORK 阅读(1701) 评论(0) 编辑 收藏 举报SQL Server 数据库中的对象存储为 8 KB 页的集合, 本节说明如何组织, 存储和访问表和索引页.
表组织
下图显示了表的组织, 表包含在一个或多个分区中, 每个分区在一个堆或一个聚集索引结构包含数据行. 堆页或聚集索引页在一个或多个分配单元中进行管理，具体的分配单元数取决于数据行中的列类型.
分区
表页和索引页包含在一个或多个分区中, 分区是用户定义的数据组织单元.
默认情况下, 表或索引只有一个分区, 其中包含所有的表页或索引页. 该分区驻留在单个文件组中. 具有单个分区的表或索引相当于 SQL Server 早期版本中的表和索引的组织结构.
当表或索引使用多个分区时, 数据将被水平分区, 以便根据指定的列将行组映射到各个分区.
分区可以放在数据库中的一个或多个文件组中, 对数据进行查询或更新时, 表或索引将被视为单个逻辑实体. 有关详细信息, 请参阅已分区表和已分区索引
若要查看表或索引使用的分区, 请使用 sys.partitions (Transact-SQL) 目录视图.
聚集表, 堆和索引
SQL Server 表使用下列两种方法之一来组织其分区中的数据页
-
聚集表是有聚集索引的表
数据行基于聚集索引键按顺序存储. 聚集索引按 B 树索引结构实现, B 树索引结构支持基于聚集索引键值对行进行快速检索. 索引中每个级别的页（包括叶级别的数据页）链接在一个双向链接的列表中. 但是, 通过使用键值来执行从一个级别到另一级别的导航. 有关详细信息, 请参阅 聚集索引结构
-
堆是没有聚集索引的表
数据行不按任何特殊的顺序存储, 数据页也没有任何特殊的顺序. 数据页不在链接列表内链接. 有关详细信息, 请参阅 堆结构
索引视图与聚集表具有相同的存储结构
当堆或聚集表具有多个分区时, 每个分区都有一个堆或 B 树结构, 其中包含该指定分区的行组. 例如, 如果一个聚集表有 4 个分区, 那么将有 4 个 B 树, 每个分区一个.
非聚集索引
非聚集索引与聚集索引有一个相似的 B 树索引结构. 不同的是, 非聚集索引不影响数据行的顺序. 叶级别包含索引行, 每个索引行包含非聚集键值, 行定位符和任意包含列或非键列. 定位符指向包含键值的数据行. 有关详细信息,请参阅 非聚集索引结构.
XML 索引
可以对表中的每个 XML 列创建一个主 XML 索引和多个辅助 XML 索引, XML 索引是 xml 数据类型列中的 XML 二进制大型对象 (BLOB) 的拆分和持久化的表示形式. XML 索引以内部表的形式存储. 若要查看有关 XML 索引的信息, 请使用 sys.xml_indexes 或 sys.internal_tables 目录视图.
有关 XML 索引的详细信息, 请参阅 XML 数据类型列的索引
分配单元
分配单元是堆或 B 树内用于根据页类型管理数据的页集合,下表列出了用于管理表和索引中的数据的分配单元类型:
在堆或 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
sys.system_internals_allocation_units 中的 root_page 列指向指定分区内聚集索引 B 树的顶端, 服务器使用索引 B 树查找分区中的数据页.
-
为表或视图创建的每个非聚集索引在 sys.partitions 中有一行, 其 index_id > 1
sys.system_internals_allocation_units 中的 root_page 列指向指定分区内非聚集索引 B 树的顶端
-
至少有一个 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 页的管理方式与 LOB_DATA 分配单元中页的管理方式相同. 即，使用 IAM 页链管理 Text/Image 页.
LOB_DATA 分配单元
当表或索引有一个或多个 LOB 数据类型时, 将为每个分区分配一个 LOB_DATA 分配单元, 以管理该数据的存储, LOB 数据类型包括 text, ntext, image, xml, varchar(max), nvarchar(max), varbinary(max) 和 CLR 用户定义类型.
分配单元和分配单元示例
下列示例返回两个表的分区和分配单元数据:DatabaseLog, 具有 LOB 数据但没有非聚集索引的堆; Currency, 没有 LOB 数据但有一个非聚集索引的聚集表,两个表都有一个分区
USE AdventureWorks2008R2;
GO
SELECT o.name AS table_name,p.index_id, i.name AS index_name , au.type_desc AS allocation_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;
下面是结果集.请注意,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 row(s) affected)
堆结构
堆是不含聚集索引的表, 堆的 sys.partitions 中具有一行, 对于堆使用的每个分区, 都有 index_id = 0.
默认情况下,一个堆有一个分区. 当堆有多个分区时, 每个分区有一个堆结构, 其中包含该特定分区的数据. 例如,如果一个堆有四个分区,则有四个堆结构,每个分区有一个堆结构.
根据堆中的数据类型, 每个堆结构将有一个或多个分配单元来存储和管理特定分区的数据. 每个堆中的每个分区至少有一个 IN_ROW_DATA 分配单元. 如果堆包含大型对象 (LOB) 列, 则该堆的每个分区还将有一个 LOB_DATA 分配单元. 如果堆包含超过 8,060字节行大小限制的可变长度列,则该堆的每个分区还将有一个 ROW_OVERFLOW_DATA 分配单元. 有关分配单元的详细信息，请参阅表组织和索引组织.
sys.system_internals_allocation_units 系统视图中的列 first_iam_page 指向管理特定分区中堆的分配空间的一系列 IAM 页的第一页.
SQL Server 使用 IAM 页在堆中移动, 堆内的数据页和行没有任何特定的顺序, 也不链接在一起, 数据页之间唯一的逻辑连接是记录在 IAM 页内的信息.
可以通过扫描 IAM 页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区, 因为 IAM 按扩展盘区在数据文件内存在的顺序表示它们, 所以这意味着串行堆扫描连续沿每个文件进行. 使用 IAM 页设置扫描顺序还意味着堆中的行一般不按照插入的顺序返回.
下图说明 SQL Server 数据库引擎 如何使用 IAM 页检索具有单个分区的堆中的数据行.
聚集索引结构
在 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 中的 root_page 列指向该聚集索引某个特定分区的顶部. SQL Server 将在索引中向下移动以查找与某个聚集索引键对应的行. 为了查找键的范围, SQL Server 将在索引中移动以查找该范围的起始键值, 然后用向前或向后指针在数据页中进行扫描. 为了查找数据页链的首页, SQL Server 将从索引的根节点沿最左边的指针进行扫描.
下图显式了聚集索引单个分区中的结构:
非聚集索引结构
非聚集索引与聚集索引具有相同的 B 树结构,它们之间的显著差别在于以下两点:
-
基础表的数据行不按非聚集键的顺序排序和存储
-
非聚集索引的叶层是由索引页而不是由数据页组成
既可以使用聚集索引来为表或视图定义非聚集索引, 也可以根据堆来定义非聚集索引. 非聚集索引中的每个索引行都包含非聚集键值和行定位符, 此定位符指向聚集索引或堆中包含该键值的数据行.
非聚集索引行中的行定位器或是指向行的指针,或是行的聚集索引键, 如下所述:
-
如果表是堆(意味着该表没有聚集索引), 则行定位器是指向行的指针. 该指针由文件标识符 (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 指针定位.
下图说明了单个分区中的非聚集索引结构 :
包含列的索引
通过将包含列（称为非键列）添加到索引的叶级,可以扩展非聚集索引的功能. 键列存储在非聚集索引的所有级别,而非键列仅存储在叶级别,有关详细信息,请参阅 具有包含列的索引
您可以通过将非键列添加到非聚集索引的叶级,扩展非聚集索引的功能.通过包含非键列,可以创建覆盖更多查询的非聚集索引.这是因为非键列具有下列优点:
-
它们可以是不允许作为索引键列的数据类型.
-
在计算索引键列数或索引键大小时,数据库引擎不考虑它们.
当查询中的所有列都作为键列或非键列包含在索引中时, 带有包含性非键列的索引可以显著提高查询性能. 这样可以实现性能提升,因为查询优化器可以在索引中找到所有列值,不访问表或聚集索引数据,从而减少磁盘 I/O 操作.
当索引包含查询引用的所有列时,它通常称为“覆盖查询”.
键列存储在索引的所有级别中,而非键列仅存储在叶级别中,有关索引级别的详细信息,请参阅表组织和索引组织
使用包含列以免避免大小限制
可以将非键列包含在非聚集索引中,以避免超过当前索引大小的限制(最大键列数为16,最大索引键大小为900字节),数据库引擎计算索引键列数或索引键大小时,不考虑非键列.
例如,假设要为 AdventureWorks2008R2 示例数据库的 Document 表中的以下列建立索引:
Title nvarchar(50)
Revision nchar(5)
FileName nvarchar(400)
因为 nchar 和 nvarchar 数据类型的每个字符需要 2 个字节, 所以包含这三列的索引将超出 900 字节的大小限制 10 个字节 (455 * 2).
使用 CREATE INDEX 语句的 INCLUDE 子句, 可以将索引键定义为 (Title,Revision), 将 FileName 定义为非键列. 这样, 索引键大小将为 110 个字节 (55 * 2), 并且索引仍将包含所需的所有列. 下面的语句就创建了这样的索引:
USE AdventureWorks2008R2; GO CREATE INDEX IX_Document_Title ON Production.Document (Title, Revision) INCLUDE (FileName);
带有包含列的索引准则
设计带有包含列的非聚集索引时,请考虑下列准则:
-
在 CREATE INDEX 语句的 INCLUDE 子句中定义非键列.
-
只能对表或索引视图的非聚集索引定义非键列.
- 除 text, ntext 和 image之外,允许所有数据类型.
-
精确或不精确的确定性计算列都可以是包含列,有关详细信息,请参阅为计算列创建索引
-
与键列一样,只要允许将计算列数据类型作为非键索引列,从 image,ntext 和 text 数据类型派生的计算列就可以作为非键（包含性）列.
-
不能同时在 INCLUDE 列表和键列列表中指定列名.
- INCLUDE 列表中的列名不能重复.
列大小准则
-
必须至少定义一个键列, 最大非键列数为 1023 列, 也就是最大的表列数减 1.
-
索引键列（不包括非键）必须遵守现有索引大小的限制（最大键列数为 16, 总索引键大小为 900 字节）.
- 所有非键列的总大小只受 INCLUDE 子句中所指定列的大小限制; 例如, varchar(max) 列限制为 2 GB.
列修改准则
修改已定义为包含列的表列时, 要受下列限制 :
-
除非先删除索引, 否则无法从表中删除非键列.
- 除进行下列更改外, 不能对非键列进行其他更改:
-
将列的为空性从 NOT NULL 改为 NULL.
- 增加 varchar, nvarchar 或 varbinary 列的长度.
- 这些列修改限制也适用于索引键列.
-
设计建议
重新设计索引键大小较大的非聚集索引,以便只有用于搜索和查找的列为键列,将覆盖查询的所有其他列设置为包含性非键列. 这样,将具有覆盖查询所需的所有列, 但索引键本身较小, 而且效率高.
例如, 假设要设计覆盖下列查询的索引:
USE AdventureWorks2008R2;
GO
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
若要覆盖查询,必须在索引中定义每列. 尽管可以将所有列定义为键列, 但键大小为 334 字节. 因为实际上用作搜索条件的唯一列是 PostalCode 列（长度为 30 字节）,所以更好的索引设计应该将 PostalCode 定义为键列并包含作为非键列的所有其他列.
下面的语句创建了一个覆盖查询的带有包含列的索引:
USE AdventureWorks2008R2;
GO
CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);
性能注意事项
避免添加不必要的列, 添加过多的索引列（键列或非键列）会对性能产生下列影响:
-
一页上能容纳的索引行将更少, 这样会使 I/O 增加并降低缓存效率.
- 需要更多的磁盘空间来存储索引, 特别是, 将 varchar(max), nvarchar(max), varbinary(max) 或 xml数据类型添加为非键索引列会显著增加磁盘空间要求,这是因为列值被复制到了索引叶级别. 因此, 它们既驻留在索引中,也驻留在基表中.
-
索引维护可能会增加对基础表或索引视图执行修改, 插入,更新或删除操作所需的时间.
您应该确定修改数据时在查询性能上的提升是否超过了对性能的影响, 以及是否需要额外的磁盘空间要求. 有关评估查询性能的详细信息,请参阅查询优化
