我们每天都在使用数据库,我们部门使用最多的关系数据库有Sqlserver,Oracle,有没有想过这些数据库是怎么存放到操作系统的文件中的?
有时候为了能够设计出最优的表结构,写出高性能的Sqlserver脚本,处理海量数据并发,我们必须解底层原理。
由于个人兴趣最近研究了下Sqlserver的文件存储,下面只讲解Sqlserver的最小存储单元——页。
什么是页
为什么会有一个页的概念。我们知道对于操作系统来说,文件可以认为是一个很大的线性空间,如果按地址空间顺序分配容量(也就是按段式存储),则有可能造成很多的外部碎片,导致很多的容量很难再次使用,只有移动合并空间才能腾出更多的空间。
例如:如下表所示,如果我现在要申请1024B字节的空间,显然下面的两个空间空间单个计算不够,合起来却是够用的的,只能移动合并空间。
8KB | 512B | 12KB | 512B | 8KB |
已分配空间 | 空闲 | 已分配空间 | 空闲 | 已分配空间 |
为了能够更好的利用磁盘空间,Sqlserver借鉴了操作系统的虚拟内存的概念,人为的将文件划分为了N个8KB的存储空间。
这样每次分配时,都是按照8KB空间申请,就解决了外部碎片的问题。
换句话说,页是Sqlserver 中数据存储的基本单位,每一页的大小为8KB。
实际数据 | 5kB | 12KB | 1KB | xxKB | |
分配的页单元 | 8KB | 8KB | 8KB | 8KB | 空闲 |
另外,每页的开头是96个字节的标头,存储了页的相关信息,包括:页码、页类型、页的可用空间以及拥有该页对象的分配单元ID等。
上述的例子分配就成为下表所示:这样就解决了外部碎片问题。
什么是区
为什么会有区的概念,已经有了页的单位难道不够吗?主要是为了更好的管理这些空间, Sqlserver 将每 8 个页划分为一个区(如下表所示)。
就像百元大钞代表着 100 个 10 元人民币一样,出去买很多东西时,用百元大钞比用100张 1 元纸币要方面。
一个分区 | |||||||
页1 | 页2 | 页3 | 页4 | 页5 | 页6 | 页7 | 页8 |
行是怎么在页中存储的?
数据库中的数据到底是以什么样的形式存储的呢?
Sqlserver是以“物理行”为单位存储的数据,也就是说表(Table)中的每条记录(Record,逻辑行)都是顺序存放在页中的。
一个物理行的宽度是8064字节,一个逻辑行的宽度不能超过这个值。
因此,一个物理行可以包括多个逻辑行。
那么怎么找到逻辑行?即如何找到一条记录的开始地址和结束地址。
Sqlserver在每页的末尾存放了每条记录的开始地址,这样我们就可以定位到某条记录的起始地址,通过下一条记录的开始位置能够知道该条记录的结束位置。
每一个这样的记录地址都占用2个字节。
如图所示,如果我想取第二条数据,那么先将一页数据都读到内存中,然后从最后读取偏移为第3开始读取2个字节,怎么可以找到行2的开始位置,同理可以读取出行2的结束位置。
![](http://up.2cto.com/2012/1210/20121210094604484.jpg)
列在页中是怎么存储的?
现在我们已经读取到行了并且已经在内存里了,接下来怎么解析出一行中的所有列?也就是这些列是怎么存放的?数据库表中的列无非就两种情况:定长列、变长列。
首先假设只有定长列,那么很容易想到一样中的每列的之顺序存放就行了,因为是定长的,完全可以将每列的偏移放到另外一个地方单独存储,如果要取某个特定的列,每个列的位置很容易定位:如下表所示:
2字节 | 3字节 | 6字节 | 10字节 | 3字节 | 2字节 |
1 | 23 | 55 | A | C | D |
如果要取红色的数据,那么它的
开始位置=(行开始位置)+ 2字节+3字节+6字节+10字节。
结束位置= 开始位置 + 3字节。
其中每个列的长度完全可以用另一张表存放
列 | 1 | 2 | 3 | 4 | 5 | 6 |
长度(字节) | 2 | 3 | 6 | 10 | 3 | 2 |
具体行结构的详细信息如下:
假如设计的表结构为 :
Col1 | Col2 | Col3 | Col4 |
Char(5)(not null) | Int (null) | Char(3)(null) | Char(6)(not null) |
在数据库中存放数据为:
Col1 | Col2 | Col3 | Col4 |
‘ABCDE’ | ‘123’ | ‘null’ | ‘ccc ‘ |
则数据在数据库文件中数据以如下形式存在:
如果其中有变长列呢,这个结构又是怎么存储的?有变长列最大的不同就是每个列的长度是不定的(同一列,每行长度都不一样),也就是不能用另外一张表存放。那么我们只能把列的长度放在行内了。这样就解决了实际长度定位的问题,上面已经说过,sqlserver有一个行偏移矩阵。
如果我们定义的表结构如下:
Col1 | Col2 | Col3 | Col4 | Col5 |
Char(2)(not null) | Varchar(250)(not null) | Varchar(5)(null) | Varchar(20)(not null) | Small int (null) |
表-8
假如这行数据为:
Col1 | Col2 | Col3 | Col4 | Col5 |
‘AAA’ | RELICATE(‘X’,250) | null | ‘ABC’ | 123 |
表-9
则数据在数据库中实际的存放形式为:
结论:
1.数据库列中尽量不用可空类型,当值为空时,实际不占用位置,并且也不能作为索引的键值。导致where语句中含有 is null 或者 is not null 时只能进行全表扫描,并且可空类型也容易导致空引用异常。
2.在设计列时,只有列长度确定的才用定长,比如身份证。其他情况基本上应该用varchar边长类型,不但节省空间的同时,一个页存放的数据会变多。导致同样的数据量读取页的次数变少,减少I/O,提高性能。
3.如图-1所示,聚簇索引不是按物理顺序存放的,是按逻辑物理顺序存放的(大多数人在这里会有误解。)
4.正常情况下不要使用varchar(max),因为这个列的数据肯定放不在一个页里,为了解决这个问题,sqlserver在列里只存放了一个指针。真正的数据放在了其他多个页里。每读取一行中的列都会至少多一次I/O,影响性能。
附注,参考资料:
(1) Microsoft SQL Server 2005技术内幕:存储引擎(中文)
(2)微软MSDN: http://msdn.microsoft.com/zh-cn/library/ms190969(v=sql.105).aspx