一个字节造成的巨大性能差异——SQL Server存储结构

编程技术  /  houtizong 发布于 3年前   79

今天同事问了我一个SQL的问题，关于SQL Server内部存储结构的，我觉得挺有意思，所以写下这篇博客讨论并归纳了一下。问题是这样的：

首先我们创建两张表，一张表的列长度是4039字节，另一张表的长度是4040字节，他们就只有一个字节的差距，比如以下创建表的SQL：


<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->CREATE TABLE tb4039(c1INT IDENTITY,c2 char(4035)not null)
CREATE TABLE tb4040(c1 INT IDENTITY,c2char(4036)not null)

由于INT类型是4个字节，所以我们创建的tb4039表有4+4035=4039个字节的长度，tb4040中的c2字段比tb4039中的c2字段多了一个字节，总长度是4040字节，其他没有区别了。接下来是向这两个表中插入数据，比如插入100条数据，SQL语句是：


<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->DECLARE @i INT
SET @i=1
WHILE @i<=100
BEGIN
INSERT INTO tb4039 (c2) VALUES('test'+CONVERT(VARCHAR(5),@i));
INSERT INTO tb4040 (c2) VALUES('test'+CONVERT(VARCHAR(5),@i));
SET @i=@i+1
END

好，现在我们使用SSMS来查看一下这两个表的空间占用量，如果是SQL2005，那么可以使用SSMS自带的报表查看，如果是SQL2008，那么直接使用对象资源管理器详细信息界面进行查看（如果使用的是SQL2008而不知道怎么查看表空间使用量那么请查看我以前写的一篇博客：SQL Server 2008新特性之SSMS增强）。我这里使用的是SQL2008，查看到的情况如图：



当然，我们也可以使用T-SQL来查询系统视图，得出这两个表的数据占用的空间，查询代码为：


<!--

Code highlighting produced by Actipro CodeHighlighter (freeware)
http://www.CodeHighlighter.com/

-->SELECT OBJECT_NAME(i.object_id)AS TableName,data_pages*8AS DataSize--这里返回的是数据页个数，1页是8K，所以乘以8
FROM sys.indexesas i
JOIN sys.partitionsas p ON p.object_id= i.object_idand p.index_id= i.index_id
JOIN sys.allocation_unitsas a ON a.container_id = p.partition_id
where i.object_id=OBJECT_ID('tb4039')OR i.object_id=OBJECT_ID('tb4040')

系统返回结果：

TableName DataSize
tb4039 400
tb4040 800

和我们通过报表或者SSMS查看到的结果相同，两个表只相差了一个字节，可是一个占用了400K的存储空间，另一个却占用了800K的存储空间，是另一个表的双倍！！！

一个字节的差距就造成了存储空间成倍的增加，为什么会这样呢？这就要从SQL Server存储结构讲起。

------------------------------------------------华丽的分割线，进入主题-----------------------------------------------------------------

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个字节 具体变长数据字节

不管我们对表的定义是多么的简单，一行数据除了数据自身占用的空间外，至少还要占用1+1+2+2+1=7个字节。如果定义的数据列很多，或者里面有变长数据列，那么占用的空间可能会更多。

现在回到我们前面讲到的2个表tb4039和tb4040，要存储tb4039中的一行数据需要1+1+2+4039+2+1=4046字节，所以正好可以在一个页中保存2行数据。所以插入了100行数据，实际上是保存在50个数据页中，大小就是8K*50=400K。而对于tb4040表，要存储一行数据需要4047个字节，没法在一个页中保存2行数据，所以一行数据就占用一个数据页，100行数据占用了100个数据页，大小就是8K*100=800K。

--------------------------------------------做了一堆加减乘除，下面总结下--------------------------------------------

这里只是举了一个极端的例子，所以造成了一个字节的偏差而使占用的存储空间翻倍，在实际应用中很少会出现这么极端的情况，但是很有可能使一个页存储5条数据的因为某个列多了1个2个字节所以只能存储4条数据。也许大家认为少存一条数据并没有什么，但是在数据量变的非常庞大以后一页4条数据和一页5条数据将会产生明显的性能差异。使得一页中存放更多的数据并不是为了节约存储成本，现在的硬盘已经很便宜了很多服务器都是几百个G的硬盘，本来5G的数据现在变长了10G，相对几百个G上T的硬盘来说又算得了什么。

实际上我们要让一个数据页中存放更多行的数据主要是出于性能的考虑。SQL Server进行数据库读写操作的基本单位是页，如果一页中存放了更多的数据，那么对表进行扫描和查找时进行的IO操作将减少，毕竟IO操作是非常消耗时间影响性能的。假设tb4039中有100W条数据，那么进行全表扫描就要读取50W个数据页，如果读取10W个数据页花费1秒钟，那么对表tb4039进行扫描需要花费5秒钟时间，而如果是使用tb4040存储这100W条数据，进行全表扫描则需要读取100W个数据页，总共花费10秒钟时间。就一个字节的差别，一个是5秒另一个是10秒，对性能的影响非常明显。




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

1、主键尽可能的短，能用tinyint的就不要用int，能用char(5)的就不要用成varchar(50)。

2、计算好表列的长度，能够在一个页中存放5条数据的，那就不要将字段设置的太长使得一个页中只能存放3条或者4条数据。

3、尽量将字段设置为不允许为NULL，因为NULL值在存储和数据处理时系统需要专门的处理，降低了性能。

4、能够用固定长度的就不要用变长字段，比如身份证号就可以使用CHAR(18)，而不应该使用VARCHAR(18)。

5、不要在一个表中建立太多的列，如果一个实体的属性太多时可以考虑进行垂直分割，将常用的字段放在一个表，不常用的字段放另外的表，这样可以减小常用字段表中数据列占用的空间，使得一个数据页中存储更多的数据行。

6、不要将大对象、长字符串和常用的字段放在同一个表中。同样还是出于性能上的考虑，比如有个产品表，里面有产品ID、产品名字、产品售价、产品图片、产品描述等字段，那么我们可以将产品ID、产品名字、产品售价这几个常用的而且占用空间小的列放在一个表，然后建立产品ID、产品图片、产品描述这样的表，通过外键约束的方式将大对象数据和长字符串数据放在另一个表中。