SQL Server性能优化（7）数据库文件组织

浏览数：23 / 时间：2015年06月12日

一、基本单位“页”

SQL Server是用8KB的页来存储数据。物理I/O操作也是在页级执行。页的种类有很多，具体参考（msdn）。我们关注更多的是数据页的结构，包括三部分：页头（96bytes）、数据区（数据行和可用空间）以及行偏移数组（槽，最少是7bytes）：

为什么数据页的大小是8k，这有什么优缺点，有两篇文章解释的很好（数据页结构，坏的一面）。总结下，

a. 数据区大小 =（8096 - 页头96bytes+偏移数组7byte） = 7 993bytes，这是一个页能分配给我们的数据使用的最大空间。我们的数据就一行一行的放在这写数据内部。

b. 如果我们一行数据包含两个int列，那一共可以存储 7993%(4+4)= 999.125,最后余7byte空间无法存入，也就是这这个页里浪费了7bytes。如

c. 果我们一行数据包含两列（一个char(4000)和一个int，共4004byte），那每个页只能存储一行，浪费了3 989bytes，很明显这不是一个好的数据库设计。

d. 可以利用free_space_in_bytes来查看页面空闲情况。

SELECT
   DB_NAME(database_id),
   SUM(free_space_in_bytes) / 1024 AS ‘Free_KB‘
FROM sys.dm_os_buffer_descriptors
WHERE database_id <> 32767
GROUP BY database_id
ORDER BY SUM(free_space_in_bytes) DESC
GO

e. 包含 varchar、nvarchar、varbinary 或 sql_variant 列的表不受此限制的约束。MSDN上有进一步解释行溢出数据超过 8 KB。

二、页的上层“区”

区是管理空间的基本单位。一个区是八个物理上连续的页（即 64 KB）。这意味着 SQL Server 数据库中每 MB 有 16 个区。

由于历史原因，SQL Serve有两种类型的区：同一区、混合区。

混合区：区内的8个页，可以是不同的表、索引等。

统一区：区内的8个页，是同一个表、索引。

新建表的时候，先在混合区插入一条记录，当其占用的这个混合区的表或索引增长到 8 页时，将变成使用统一区进行后续分配，每次扩展84kb（8页）。问题是如何管理这两种分区？

1. 全局分配映射表（GAM: Global Allocation Map Pages）---为统一区管理服务

这是一个页，共64000位（8000bytes）的掩码位图（参考位图算法），用一个位管理一个byte管理4Gbyte。所以每隔4G的数据文件都要有一个GAM页进行管理（64000 * 64 /1024 /1024)。

2. 共享全局分配映射表（SGAM: Shared Global Allocation Map Pages）---为混合区管理服务

原理同全局分配映射表。

二、没有索引的表的结构

下图是SQL Server中表的组织形式（其中分区1、分区2是为了便于管理，把表进行分区，放到不同的硬盘数据文件里。默认情况下，表只有一个分区。）。表在硬盘上的存放形式，有堆和B树两种形式。

技术分享

图最下方的三个叶子节点，数据、LOB、行溢出是数据在硬盘上存放数据的集合。可以这么理解，SQL Server在硬盘上一个数据页是8k，页有三种类型、分别为：数据、LOB、行溢出。关于页的结构，可参考页和区：https://technet.microsoft.com/zh-cn/library/ms190969(v=sql.105).aspx

数据（IN_ROW_DATA）：包含除大型对象 (LOB) 数据以外的所有数据的数据行或索引行。页的类型是data或者index。

LOB(LOB_DATA)：一些大型对象数据，如：text、ntext、image、xml、varchar(max)、nvarchar(max)、varbinary(max) 等。页的类型为 Text/Image。

行溢出（ROW_OVERFLOW_DATA）：如果某些数据太大以至于超过1个数据页。

二、聚集索引的结构

要想使用聚集索引，必须了解聚集索引的概念以及它的原理。网上对聚集索引描述的资料有很多，最通俗的解释就是拿字典举例：如我们的在新华字典里用拼音查一个汉字“索”的时候，是先从字典最开始几页的字母“s”开始，然后查“suo”，然后找到“索”这个字，根据它的页数，一下翻到这个字所在的页数。

当然这要求，

1. 所有的新华字典里的汉字都按照拼音字母顺序排列。

2. 新华字典前面都有一个拼音的查找附录。

聚集索引也是这个道理，

1. 有一个所有数据的“拼音”列表，即索引。

2. 所有数据根据这个索引在硬盘上有序排列，这个排列顺序就是B树。

B树的概念：http://www.baidu.com/link?url=5cmF2QROgNE6JUpvbu_9oqwYhdtGvn-1WsCae98nB6eK70H6tc4S3s3sKGfvalbOiwHEUblJIAI6mOivaWk5jq&wd=&eqid=ae27fb0e0003091b00000004555ae6b3&ie=UTF-8