今天同事问了我一个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、产品图片、产品描述这样的表,通过外键约束的方式将大对象数据和长字符串数据放在另一个表中。
分享到:
相关推荐
SQL储存过程等的解密,破解字节不受限制,适用于SQLSERVER2000存储过程,函数,视图,触发器
数据库中,英文字符只需要一个字节存储就足够了,但汉字和其他众多非英文字符,则需要两个字节存储。如果英文与汉字同时存在,由于占用空间数不同,容易造成混乱,导致读取出来的字符串是乱码。Unicode字符集就是...
字节跳动青训营——抖音项目.zip
Microsoft SQL Server 2005 Express Edition (SQL Server Express) 是一个免费且易于使用的 SQL Server 2005 版本,它替换了 Microsoft Desktop Engine (MSDE)。与 Microsoft Visual Studio 2005 集成之后,SQL ...
开发软件时候 经常要改动数据库结构,如何快速地对比和修改数据库呢,并且能快速对比样本数据,并同步数据库的结构,约束,主键,字节,小数点 ,以及存储过程,本工具就是为这个目的开发。界面比较粗糙但挺实用, ...
破解字节不受限制,适用于SQLSERVER2000存储过程,函数,视图,触发器
【赛迪网-IT技术报道】SQL Server数据库查询速度慢的原因有很多,常见的有以下几种: ...其次,看一下SQL SERVER的数据存放的结构:一个页面的大小为8K(8060)字节,8个页面为一个盘区,按照B树存放。
第六届字节跳动青训营——后端进阶班.zip
SQLSERVER2008_ASAMO10
这是注册一个Sql Server实例所要使用的注册表,把里面的$name,$path,$port替换成你定义的实例名,当前程序所有路径,监听端口,然后导入注册表就可以了。4. 启动Sql Server iPath:=ExtractFilePath(Application....
解决两个问题:vs自带数据库sql server的中文插入乱码问题、查找当前数据库名 (查找数据库名在文章中间,自己往下翻吧,不单拎出来了) vs自带数据库sql server的中文插入乱码问题: 如图所示,我插入表的中文变成...
企业管理器->工具->选项->在弹出的"SQL Server企业管理器属性"窗口中,点击"高级"选项卡->连接设置->在 登录超时(秒) 后面的框里输入一个较大的数字 查询分析器->工具->选项->连接->在 登录超时(秒) 后面的框里...
面向字节_Memcached.pdf
Microsoft Access、MySQL 以及 SQL Server 所使用的数据类型和范围。 SQL Server 数据类型 Character 字符串 数据类型 描述 存储 char(n) 固定长度的字符串。最多 8,000 个字符。 n varchar(n) 可变长度的...
SQL Server存储引擎把DateTime类型存储为2个int32类型,共8个字节,第一个int32 整数(前4个字节)存储的是日期相对于基准日期(1900-01-01)的偏移量。基准日期是1900-01-01,当前4 字节为0 时,表示的日期是1900 ...
一个牛逼的 Java 字节码类库!(csdn)————程序
我想谈下SQL Server里的一个特别话题——即时文件初始化(Instant File Initialization)。对于你的SQL Server实例,如果你启用了即时文件初始化,在特定情况下,你会获得巨大的性能提升。即时文件初始化定义了当在...
我们在使用SqlServer2005的时候,当操作某数据库很久以后,日志文件会越来越大,在此,该文档里就整理了几种清理日志文件的方法。
(20080318_4)SQL Server 2000安全及高级应用技巧 【SQLServer.rar】文件大小:165 KB (169,094 字节) 解压后含有ppt,sql脚本。
b) 表:每个数据库最多20亿个表,表中每行最大字节数为8060 c) 列:每个表最多1024个列 d) 索引:每个表一个簇式索引,最多249个非簇式索引,一个复合索引最多有16个索引关 键字 e) 存储过程:一个存储过程最多可以有...