来自:http://blog.csdn.net/herowang/article/details/4436605
/***************************************************
作者:herowang(让你望见影子的墙)
日期:2009.11.4
注: 转载请保留此信息
更多内容,请访问我的博客:blog.csdn.net/herowang
****************************************************/
一个字节引发的存储空间问题
今天在偶然看到了一篇文章,忽然想起疯子的一个帖子中,一直没有想明白的问题,可能和这个有关系,验证了一下,果然如此。
一、起因:
-- 创建2个测试表
CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL)
CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL)
go
-- 填充数据
declare @i int
set @i = 0
while(@i < 20)
begin
insert Table_2019(Data) values('')
insert Table_2020(Data) values('')
select @i = @i + 1
end
go
查看每个表所占用的空间,结果:
Table_2020 表数据占了 160kb ,Table_2019 表数据占了 80 kb
二、问题
两张表中的字符长度相差为1,(因为数据类型为nchar,所以数据所占用的空间实际相差为2),但是两张表所占用的空间确相差一倍,原因何在?
三、分析
要解释这个问题,首先先要搞清楚sql server中表的存储结构:
那么在表中存储一行数据,除了本身的数据之外,至少还需要1+1+2+2+1=7个字节。
所以对于Table_2019来说,需要7+2019*2=4045个字节的空间,
对于Table_2020来说,需要7+2020*2=4047个字节的空间。
对于数据库的一页而言(最小的存储单位),一页的空间为8K(8192B)。一个数据页是由3部分组成:页头、数据行和行偏移矩阵,页头保存了页的编号、上一页ID、下一页ID、可以字节数等等关于该页的基本信息。页头的大小是固定的96个字节,所以剩下8192-96=8096个字节用于存储数据行和行偏移矩阵。
行偏移矩阵在页的最后面,而且是倒序排列的,使用2个字节来表示数据行在页面内部的偏移量,有1行数据则行偏移矩阵的大小是2字节,有2行数据则行偏移矩阵的大小是4字节,以此类推。
下面我们再来分析下这两张表:
1、table_2019:存储数据需要的空间为4045B,加上也最后面的行偏移,为4047B,而一个数据库页剩余的空间为8096B,这样的话,一个数据能够存储两行的数据。8096>4047*2=8094
2、table_2020:存储数据需要的空间为4047B,加上也最后面的行偏移,为4049B,而一个数据库页剩余的空间为8096B,这样的话,一个数据只能够存储一行的数据。8096<4049*2=8098。
这样,我们就会发现,同样的数据,对于table_2020所需要的存储空间是table_2019的二倍。
四、建议
从上面就可以发现,在对表的设计过程当中,为某一列选择适当的数据类型是非常重要的。尤其是对字符类型,长度的设置一定要合适,尽量让一页能够存储更多行的数据,来减少I/O,提高数据库的效率。