SQL存储原理及聚集索引、非聚集索引、唯一索引、主键约束的关系

部分内容来自：http://dufei.blog.51cto.com/382644/1377630

暂时只针对SQL Server

一、SQL的存储结构

每个 SQL Server 数据库至少具有两个操作系统文件：一个数据文件和一个日志文件。  数据文件包含数据和对象，例如表、索引、存储过程和视图。日志文件包含恢复数据库中的所有事务所需的信息。 为了便于分配和管理，可以将数据文件集合起来，放到文件组中。

在SQL Server中数据存储的基本单位称为页。每页是8KB，SQL Server读取或者是写入数据的最小单位也是页，那么1MB就有128页。行不能跨页（页的单个行最大数量是8,060字节8kb 1024*8)，不包括Text/Image类型的页数据，对于可变长类型的列，如果行超过8060,则从最大长度的列开始，将一个或多个可变长度列移动到ROW_OVERFLOW_DATA分配单元中的页，在原始页上维护一个24字节的指针，如果行的总大小小于8060,就再移回来.执行查询等操作时将延长处理时间，因为这些记录将同步处理，而不是异步。

但还有另一个概念，大家要知道，叫做区，区是指8个物理上连续的页的集合，如果这8个物理上连续的页属于同一个表，则这种区称为统一区，如果这8个页分别属于至少两个不同的表。则这种区称为混合区。

虽然每个页有8KB，但并不是说这8KB都用来存放具体数据，每页的开头有一个96字节的页头，用来存储有关页的系统信息，例如：页码、页类型、页的可用空间以及拥有该页的对象ID（也就是这个页是哪个对象在用）。不同类型的数据，存放在不同类型的页里面。如下图所示，就显示了数据文件中各种页类型以及它们里面存放的内容：

（PS:具体存储：

SQL Server最小的存储单位是页（Page），一个页的大小是8K=8192字节。一个数据页是由3部分组成：页头、数据行和行偏移矩阵，具体结构如图：

页头保存了页的编号、上一页ID、下一页ID、可以字节数等等关于该页的基本信息。页头的大小是固定的96个字节，所以剩下8192-96=8096个字节用于存储数据行和行偏移矩阵。

行偏移矩阵在页的最后面，而且是倒序排列的，使用2个字节来表示数据行在页面内部的偏移量，有1行数据则行偏移矩阵的大小是2字节，有2行数据则行偏移矩阵的大小是4字节，以此类推。

除了页头占用的空间和行偏移矩阵占用的空间，中间剩下的空间就是给数据行使用的。假设我们要在一个页中保存2行数据，那么这2行数据可以使用8096-4=8092个字节的空间，也就是说1行数据可以使用8092/2=4046个字节的空间。这里的4046个字节并不是完全都用来保存数据行，一个数据行中还存在其他的信息用于表示该行数据，具体的结构是这样的：

状态位A	状态位B	定长数据类型的长度	定长数据的内容	列数	NULL位图	变长列的个数	变长列的偏移矩阵	变长列的数据
1字节	1字节	2字节	具体定长数据字节	2字节	列数/8个字节	2字节	变长列个数*2个字节	具体变长数据字节

为了提高数据库查询的性能，在表设计时可以遵循以下建议：

• 主键尽可能的短，能用tinyint的就不要用int，能用char(5)的就不要用成varchar(50)。
• 计算好表列的长度，能够在一个页中存放5条数据的，那就不要将字段设置的太长使得一个页中只能存放3条或者4条数据。
• 尽量将字段设置为不允许为NULL，因为NULL值在存储和数据处理时系统需要专门的处理，降低了性能。
• 能够用固定长度的就不要用变长字段，比如身份证号就可以使用CHAR(18)，而不应该使用VARCHAR(18)。
• 不要在一个表中建立太多的列，如果一个实体的属性太多时可以考虑进行垂直分割，将常用的字段放在一个表，不常用的字段放另外的表，这样可以减小常用字段表中数据列占用的空间，使得一个数据页中存储更多的数据行。
• 不要将大对象、长字符串和常用的字段放在同一个表中。同样还是出于性能上的考虑，比如有个产品表，里面有产品ID、产品名字、产品售价、产品图片、产品描述等字段，那么我们可以将产品ID、产品名字、产品售价这几个常用的而且占用空间小的列放在一个表，然后建立产品ID、产品图片、产品描述这样的表，通过外键约束的方式将大对象数据和长字符串数据放在另一个表中。

）

在这里面，我们用到较多的页是Data和Text/Image类型，而在一个数据文件的开头则分布很多管理页面如：GM、SGAM、PFS。SQL Server通过这些页面知道这个数据文件中的哪些页面已经使用，哪些页面还没有使用等。

      当一张表或一个索引需要更多的空间时，SQL Server需要找到能够用来分配的空间。如果该表或索引整体仍然少于8个页面，SQL Server必须找到能够用来分配的混合类型区构成的空间。如果表或索引有8个页面或更大，SQL Server必须找到一个自由的统一类型的区。那么SQL Server就需要知道区已经分配出去，哪些区可以使用，这就要用到全局分配映射页面和共享全局分配映射页面，简称为：GAM/SGAM。

     GAM记录了哪些区已经被分配并用作何种用途。一个GAM页面在它所覆盖空间里针对每一个区都有一个数据位。如果为1，则为空闲区，可以用来分配，如果为0则该区已经被使用。GAM大约能标识64000个区，也就是4G的空间。如果超过4G，则再启用一个GAM页来标识下一个4G空间。

       SGAM记录了哪些区当前是混合区，并且至少有一个未使用的页面。它也能标识64000个区，大约4G空间。如果bit位标识为1，则说明它使用的是混合区并且至少有一个页可用来分配 。如果为0，则说明是统一区，或者是混合区，但已经没有空闲页面。

       那么SQL Server可以很方便地查找需要的页面，如需要一个新的完全没有使用的区，那么可以使用任何一个在GAM页面中对应的比特位值为1的区。如果需要找到一个有着可用空间，如一个或多个自由页面的混合类型的区，那么它可以寻找一个对应的GAM中的值为0、SGAM中的值为1的区。如果不存在有可用空间的混合类型的区，SQL Server会使用GAM页面来寻找一个全新的区并将其分配为混合类型的区，然后使用该区中的一页。如果根本没有自由区，那么这个文件已经满了。   

SQL Server能够迅速地锁定一个文件中的GAM页面，因为它总是位于任何数据库文件的第三页上(页码为2)。SGAM页面是在第四页上(页码为3)。下一个GAM页面出现在第一个GAM页面(页码为2)以后的每511 230个页面中，并且下一个SGAM页面出现在第一个SGAM页面(页码为3)以后的每511 230个页面中。每一个数据库文件的页码为0的页面是文件头页面，并且每个文件仅有一页。页码0是头文件页，页码1是页面自由空间页(Page Free Space，PFS)。并且每一个数据库的前八个页面是固定不变的。

.

下面，我们可以通过DBCC Page命令查看某一个数据库的页面信息，此命令的语法是:

       dbcc page(数据库名称|数据库ID，文件编号，页面编号，输出选项)

输出选项：0:默认值，输出缓冲区的标题和页面标题；1:输出缓冲区的标题、页面标题(分别输出每一行)，以及行偏移量表；2:输出缓冲区的标题、页面标题(整体输出页面)，以及行偏移量表。3：输出标题的同时，显示列值。

       但需要此命令之前，必须启用DBCC TRACEON(3604)。

一个完整的页面包含四个部分BUFFER、PAGE HEADER、DATA、OFFSET TABLE，分别表示缓存、页面的头部信息、数据和偏移表。

BUFFER:缓存部分，用于标识页在内存中的位置。

PAGE HEADER:页面头部信息，包括一些重要属性，如：m_pageid为页编号；m_headerversion为页头格式的版本；m_type为页的类型，如：1，表明是数据页，2为索引页，8为GAM页，9为SGAM页等；m_typeflagebits:类型标识位，基本上用不到；m_level为在B树结构中的层级，最底层的层级为0；m_flagbits为页的属性，如0x200表明页有checksum检查；m_prevpage和m_nextpage，在B树结构的同一层级数据页之间，互相通过m_prevpage和m_nextpage连接起来；m_slotcnt表明当前页中有多少条记录；m_freecnt表明当前页中还剩余多少 空间，以字节为单位。m_lsn表明当前页中的所有记录中，最后一个改变相对应的日志记录号。

DATA部分一般分为若干插槽号(Slot)，如果是数据页或索引页的话，可以理解为一行记录，SQLServer通过文件号+页面号+插槽号用来唯一标识表中的每一条记录。但在GAM页中我们可以把Slot 0理解为GAM页的保留页，共计94个字节。

页的末尾是行偏移表，对于页中的每一行，每个行偏移表都包含一个条目。每个条目记录对应行的第一个字节与页首的距离。行偏移表中的条目的顺序与页中行的顺序相反。如下图所示：

更详细的内容可参考：MSDN数据库大小的估计

 

二、索引的区别

SQL Server数据文件的页面类型，系统通过96个字节的头部信息和系统表从逻辑层面上将表的存储结构管理起来，具体到表的存储结构上，SQL Server引入对象、分区、堆或B树、分配单元等概念。

下图显示了表的存储组织，每张表有一个对应的对象ID，并且包含一个或多个分区，每个分区会有一个堆或者多个B树，堆或者B树的结构是预留的。每个堆或者是B树都有三个分配单元用来存放数据，分别是数据、LOB（Large Object）、行溢出，使用最多的分配单元是数据。如果有LOB数据或者是长度超过8000字节的记录，则可能有另外的LOB分配单元和行溢出分配单元。

 

上面的存储结构是SQL文件的存储结构，所以不管是表，索引，还是存储过程、视图，都是以这种方式存储的，一般最长提到的是表和索引的这种存储结构。

关于一个表，有两种组织方式：

1、堆

在没有聚集索引的表中，数据是以堆的方式组织的。所谓堆（heap）,就是不含聚集索引的表。堆的 sys.partitions 中具有一行，对于堆使用的每个分区，都有index_id = 0。只有一个分区，在系统表里，对于这个分区下面的每个分配单元都有一个连接指向Index Allocation Map页（IAM）,在IAM页里，描述了区的信息。

sys.system_internals_allocation_units 系统视图中的列 first_iam_page 指向管理特定分区中堆的分配空间的一系列 IAM 页的第一页。SQL Server  使用 IAM 页在堆中移动。堆内的数据页和行没有任何特定的顺序，也不链接在一起。数据页之间唯一的逻辑连接是记录在 IAM 页内的信息。

2. 具有非聚集索引的表

如果有一个表只有非聚集索引而没有聚集索引，对应的索引号是2--250。那么针对每个非聚集索引，都有一个对应的分区，在系统表进而，对于这个分区下面的每个分配单元，都有一个连接指向根页。数据页之间通过前后指针互相联系，是一个完整的树形结构。在树的底层，会有一个连接指向真正的数据，连接的形式是文件号+页号+行号，而真正的数据是以堆的形式存放的。如下图所示：

3. 具有聚集索引的表

   表中的聚集索引，对应的索引号是1。它有一个对应的分区，该分区下的每个分配单元都有一个连接指向根页。对于聚集索引来说，叶子结点里存放的是真正的数据，而不是非聚集索引那样的连接。如下图所示：

非聚集索引与聚集索引具有相同的 B 树结构，它们之间的显著差别在于以下两点：

• 基础表的数据行不按非聚集键的顺序排序和存储。

• 非聚集索引的叶层是由索引页而不是由数据页组成

在同样表结构的情况下，建立聚集索引不会增加表格的大小，但是建立非聚集索引反而会增加不少空间，在性能方面，SQL Server产品组做过测试，在select、update、delete操作下，聚集索引性能较高，在插入记录时，聚集索引和非聚集索引性能相同，没有出现聚集索引影响插入速度的现象，但在生产环境中，还是要谨慎行事。

 

PS：关于IAM（索引分配映射）的补充

向空表中插入数据：

insert into TestTable values('testInRow', 'InRow', 'InRowData', 1, null)

再次查询allocation：

SELECT *  FROM [TestDB].[sys].[allocation_units] WHERE container_id='72057594038779904'

得到如下结果：

Total_pages,used_pages,data_pages的值发生改变。

为什么插入了一行数据，会增加两页呢？其中一页是数据页，用来存放用户的数据，另外一页叫做IAM（索引分配映射）页，用来将数据页链接起来。结构如图：

Sytem_internals_allocation_units表存放第一个数据页和第一个IAM页的指针。IAM按照数据页的顺序存放数据页的指针。数据页之间并无直接链接。

索引分配映射(Index Allocation Map，IAM)页面在4  GB的区间中跟踪被一个分配单元所使用的区。一个分配单元就是一组页面，这些页面属于一个数据表或索引的单个分区。它由下面三种类型页面中的一种组成：含 有常规的行内数据的页面、含有大型对象(Large Object，LOB)数据的页面和含有行溢出数据的页面。 其实SQL  Server的数据页面类型与Oracle的段的概念有些类似，一个对象包含若干段，而一个段只能属于一个对象。

　　假如一张在四个分区上 的含有所有三种类型的数据(行内数据、LOB数据和行溢出数据)的表将会有至少12个IAM页面。单张IAM页面也是仅仅覆盖单个文件的4GB区间，所以 如果分区跨越多个文件，那么就会有多个IAM页面，同时如果文件大小超过4GB，并且分区使用了一个4  GB区间以外的数据页，那么也将会有额外的IAM数据页。

　　一个IAM数据页包含一个页头(IAM页头)，该页头包含有8个页面指针槽， 还有一组比特位用来将一个范围内的区映射到一个文件，这个文件并不必一定就是IAM页面所在的那个文件。页头包含有在IAM映射范围内的第一个区的地址。 8个页面指针槽可能包含指向某些属于相关对象页面的指针，这些对象被包含在混合类型的区中，对一个对象来说，只有第一个IAM页面含有这些指针的值。一旦 一个对象占用的页面超过8个，它所有的区都会是统一类型的区——这意味着一个对象决不会需要超过8个指针来指向处于混合类型区中的页面。如果一张表中的数 据行已被删除，该表实际上可以使用的指针数不到8个。比特位映射中的每一个比特位代表了该范围内的一个区，而不论该区是否被分配给了拥有该IAM的对象。 如果一个比特位是打开的，那么在此范围内相关的区就是被分配给拥有  IAM的对象的;如果一个比特位是关闭的，那么此范围内相关的区没有被分配给拥有该IAM的对象。

　　IAM页面在需要的时候被分配给每一个对象，并且位于数据库中的随机位置。每一个IAM页面覆盖的可能范围大约是512 000个页面。

虽然IAM是用来维持堆的数据结构的，但是在聚集索引（即B树）上其实也是有IAM的，

IAM页不是只有堆表才有也不只是维护堆表中的数据页的连续，有索引的表都有，所以IAM页不只维护数据页，也维护索引页的连续，在下篇说到非聚集索引的时候

我会给出MSDN的解释和IAM页在聚集索引表，非聚集索引表中的情况

PageType  分页类型： 1：数据页面；2：索引页面；3：Lob_mixed_page；4：Lob_tree_page；10：IAM页面

IndexID    索引ID：   0 代表堆, 1 代表聚集索引, 2-250 代表非聚集索引 ，大于250就是text或image字段

第二个：每个数据页的IndexID都是1，不是说数据页变成了索引页，而是说现在数据页已经属于聚集索引的一部分，不在堆里了

第三个：每个数据页的IndexLevel都是0，就是说数据页在聚集索引的最下层

第四个:索引页和数据页,前一页和后一页是首尾相连的,但是数据页和索引页不是首尾相连的，也就是说没有一个数据页的[PrevPagePID]指向14464页或3528页

【有点乱了，先放着http://www.cnblogs.com/lyhabc/archive/2013/07/20/3196479.html】

 

关于非聚集索引指向聚集索引的数据，其实是指向聚集索引的键值，这个键值是使用IAM维护的

聚集索引的最底层是按物理顺序排列的，所以查找效率较高，这个顺序是有一个键值来维护的

 

 

总结：上面的有点乱，来看看这篇文章http://www.cnblogs.com/lwzz/archive/2012/08/05/2620824.html