学习散记 --表和索引体系结构
PS:MSDN上摘入
表组织
表-->一个或多个分区-->一个堆或一个聚集索引结构包含数据行-->堆页或聚集索引页在一个或多个分配单元中进行管理(具体的分配单元数取决于数据行中的列类型)
表中的分区：
效果：使用分区可以快速而有效地管理和访问数据子集，从而使大型表或索引更易于管理
概念：已分区表和已分区索引的数据划分为分布于一个数据库中多个文件组的单元。数据是按水平方式分区的，因此多组行映射到单个的分区。
对数据进行查询或更新时，表或索引将被视为单个逻辑实体。单个索引或表的所有分区都必须位于同一个数据库中。
决定分区的因素：要取决于表当前的大小或将来的大小、如何使用表以及对表执行用户查询和维护操作的完善程度
通常，如果某个大型表同时满足下列两个条件，则可能适于进行分区：
1该表包含（或将包含）以多种不同方式使用的大量数据。
2不能按预期对表执行查询或更新，或维护开销超过了预定义的维护期。
----------------------------例子--------------------------
例如，如果对当前月份的数据主要执行 INSERT、UPDATE、DELETE 和 MERGE 操作，而对以前月份的数据主要执行 SELECT 查询，则按月份对表进行分区可能会使表的管理工作更容易一些。
如果对表的常规维护操作只针对一个数据子集，那么此优点尤为明显。如果该表没有分区，那么就需要对整个数据集执行这些操作，这样就会消耗大量资源。
-----------------------------------------------------------
表的分类：
SQL Server 表使用下列两种方法之一来组织其分区中的数据页：
1.聚集表是有聚集索引的表：据行基于聚集索引键按顺序存储。聚集索引按 B 树索引结构实现，B 树索引结构支持基于聚集索引键值对行进行快速检索
2.堆是没有聚集索引的表：数据行不按任何特殊的顺序存储，数据页也没有任何特殊的顺序。数据页不在链接列表内链接.
表的分类详解：
堆结构：
堆和分区：对于堆使用的每个分区，都有 index_id = 0。默认情况下，一个堆有一个分区。当堆有多个分区时，每个分区有一个堆结构，其中包含该特定分区的数据。
堆和分配单元：个堆中的每个分区至少有一个 IN_ROW_DATA 分配单元。如果堆包含大型对象 (LOB) 列，则该堆的每个分区还将有一个 LOB_DATA 分配单元。如果堆包含超过 8,060 字节行大小限制的可变长度列，则该堆的每个分区还将有一个 ROW_OVERFLOW_DATA 分配单元。
堆和IAM页：可以通过扫描 IAM 页对堆进行表扫描或串行读操作来找到容纳该堆的页的扩展盘区。因为 IAM 按扩展盘区在数据文件内存在的顺序表示它们，所以这意味着串行堆扫描连续沿每个文件进行。使用 IAM 页设置扫描顺序还意味着堆中的行一般不按照插入的顺序返回。
聚集索引结构：
概念：索引是按 B 树结构进行组织的。索引 B 树中的每一页称为一个索引节点。
B 树的顶端节点称为根节点。索引中的底层节点称为叶节点。根节点与叶节点之间的任何索引级别统称为中间级。
在聚集索引中，叶节点包含基础表的数据页。根节点和中间级节点包含存有索引行的索引页。
每个索引行包含一个键值和一个指针，该指针指向 B 树上的某一中间级页或叶级索引中的某个数据行。
聚集索引和分区：索引使用的每个分区的 index_id = 1。默认情况下，聚集索引有单个分区。当聚集索引有多个分区时，每个分区都有一个包含该特定分区相关数据的 B 树结构。
聚集索引和分配单元：个聚集索引的每个分区中至少有一个 IN_ROW_DATA 分配单元。如果聚集索引包含大型对象 (LOB) 列，则它的每个分区中还会有一个 LOB_DATA 分配单元。如果聚集索引包含的变量长度列超过 8,060 字节的行大小限制，则它的每个分区中还会有一个 ROW_OVERFLOW_DATA 分配单元。
聚集索引查找数据：根据键值找到键值所在的索引页-》在索引页里面找到索引行-》然后根据指针找到数据页中的数据行;
非聚集索引结构：
于聚集索引的差别：
1）：基础表的数据行不按非聚集键的顺序排序和存储
2）：非聚集索引的叶层是由索引页而不是由数据页组成。
索引行的组成：包含非聚集键值和行定位符。此定位符指向聚集索引或堆中包含该键值的数据行。
关于定位符：
a.如果表是堆（意味着该表没有聚集索引），则行定位器是指向行的指针。该指针由文件标识符 (ID)、页码和页上的行数生成。整个指针称为行 ID (RID)。
b.果表有聚集索引或索引视图上有聚集索引，则行定位器是行的聚集索引键。
非聚集索引和分区：非聚集索引在 index_id >0 的 sys.partitions 中都有对应的一行。默认情况下，一个非聚集索引有单个分区。如果一个非聚集索引有多个分区，则每个分区都有一个包含该特定分区的索引行的 B 树结构。
非聚集索引和分配单元：每个非聚集索引至少有一个针对每个分区的 IN_ROW_DATA 分配单元（存储索引 B 树页）。如果非聚集索引包含大型对象 (LOB) 列，则还有一个针对每个分区的 LOB_DATA 分配单元。此外，如果非聚集索引包含的可变长度列超过 8,060 字节行大小限制，则还有一个针对每个分区的 ROW_OVERFLOW_DATA 分配单元。
非聚集索引查找数据：根据非聚集键值在索引页中找到行业索引行-》根据索引行中的行定位器找到数据页中的数据行（这里分堆和聚集表）
具有包含列的索引：
将非键列添加到非聚集索引的叶级，扩展非聚集索引的功能。
这样的非键列具有下列优点：
a.它们可以是不允许作为索引键列的数据类型。
b.在计算索引键列数或索引键大小时，数据库引擎不考虑它们。
当查询中的所有列都作为键列或非键列包含在索引中时，带有包含性非键列的索引可以显著提高查询性能。这样可以实现性能提升，因为查询优化器可以在索引中找到所有列值；不访问表或聚集索引数据，从而减少磁盘 I/O 操作。
例子：
表中的以下列：Title nvarchar(50) Revision nchar(5) FileName nvarchar(400) 建立索引
这三列的索引将超出 900 字节的大小限制 10 个字节 (455 * 2）,这个时候用包含列的索引就可以做到了：
CREATE INDEX IX_Document_Title
ON Production.Document (Title, Revision)
INCLUDE (FileName);
带有包含列的索引准则：
1.在 CREATE INDEX 语句的 INCLUDE 子句中定义非键列。
2.只能对表或索引视图的非聚集索引定义非键列。
3.除 text、ntext 和 image 之外，允许所有数据类型。
4.精确或不精确的确定性计算列都可以是包含列
5.不能同时在 INCLUDE 列表和键列列表中指定列名
6.INCLUDE 列表中的列名不能重复
修改带有包含列的表的限制：
1.除非先删除索引，否则无法从表中删除非键列
2.除进行下列更改外，不能对非键列进行其他更改： 将列的为空性从 NOT NULL 改为 NULL。 增加 varchar、nvarchar 或 varbinary 列的长度。
包含列用例：
SELECT AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
FROM Person.Address
WHERE PostalCode BETWEEN N'98000' and N'99999';
--对应的包含列中的非键列应该是select 后面的列：AddressLine1, AddressLine2, City, StateProvinceID, PostalCode
CREATE INDEX IX_Address_PostalCode
ON Person.Address (PostalCode)
INCLUDE (AddressLine1, AddressLine2, City, StateProvinceID);