sql服务器存储结构分析与数据表优化.docxVIP

sql服务器存储结构分析与数据表优化

数据库是由计算机和计算机管理的相关数据的集合。数据库可以同时集成不同的用户，具有冗余性低、数据相关性好、数据库独立性好等特点。数据表是数据库中最基本、最重要、最重要的对象。它不仅是组织数据的方法，也是存储数据的地方。对于许多数据库对象，如索引和视图，数据表结构的设计质量直接影响到数据库中数据的使用效率。优秀的数据表结构可以为提高系统的用户体验提供保障。然而，在系统开发和设计过程中，开发人员很少关注数据库数据表结构的设计。本文以移动图像程序管理数据库为例，探讨了数据表的设计。

1sdms的插入表

为方便研究和论述,本文在创建数据表时进行了简化字段设计.首先创建两个表,在第一个表Books1中,第一个字段为int类型,长4个字节;第二个字段为字符型,长度为4035字节,所以Books1表的列长度为4+4035=4039个字节;另一个表Books2表中的Title字段比Books1表中的Title字段多了一个字节,其列长度为4040字节,其他没有区别.

接下来向这两个表中插入数据,如插入1000条数据:

然后使用SSMS查看这两个表的空间占用量.如果是SQLServer2005,可使用SSMS自带的报表查看;如果是SQLServer2008,则直接使用“对象资源管理器详细信息”查看.图1是在SQLServer2008中查看到的情况:Books1表占仅用了4000KB的存储空间,Books2表却占用了8000KB的存储空间,是Books1表的2倍.

在两个表中分别查询前500条记录,并在SQLServer2008“客户端统计信息”中查看对两个表的查询执行情况,如图2所示.其中“查询试验1”为Books1表中执行查询的情况,“查询试验2”为Books2表中执行查询的情况,可明显观察到Books1表的数据查询性能优于Books2表,它们的网络统计信息数据也明显地反映出基于表Books1的网络传输数据量要小一些.尽管两个表在设计时仅相差一个字节,但在占用存储空间、查询性能和数据传输量上却相去甚远,主要原因是SQLServer的数据存储结构.

2表目标数据的存储

SQLServer最小的存储单位是页(Page),一个页的大小是8KB.一个数据页是由3部分组成:Pageheader(页头)、Datarow(数据行)和Rowoffsets(行偏移矩阵),具体结构如图3所示.

页头保存了页的编号、上一页ID、下一页ID、可用字节数等关于该页的基本信息.页头的大小是固定的,为96个字节,剩下4*1024-97=8096个字节用于存储数据行和行偏移矩阵.

行偏移矩阵存储在页的最后面,而且是倒序排列的,使用2个字节来表示数据行在页面内部的偏移量.如果有1行数据,则行偏移矩阵的大小是2字节;有2行数据,则行偏移矩阵的大小是4字节,以此类推.

除去页头和行偏移矩阵占用的空间,中间剩下的空间就是供数据行使用的.假设在一个页中保存2行数据,那么这2行数据就可以使用8092个字节的空间,也就是说1行数据可以使用4046个字节的空间,而这4046个字节并不是完全都用来保存数据行中用户数据信息的,因为一个数据行中还存在其他的信息用于表示该行数据,具体的结构如表1所示.

不管数据表结构的定义是多么简单,数据行除了数据自身占用的空间外,至少还要占用状态位A、状态位B、定长数据类型的长度等共7个字节.如果定义的数据列较多,或者包含有变长数据列,那么占用的空间就会更多.

再来考查表Books1和表Books2.对于表Books1,要存储表Books1中的一行数据需要4039+7=4046字节,正好可以在一页中保存2行数据,所以插入了1000行数据,实际上是保存在500个数据页中,大小为8K*500=4000K;对于表Books2,要存储一行数据需要4047个字节,一页中保存不了2行数据,所以一行数据就占用一个数据页,1000行数据就占用了1000个数据页,大小为8K*1000=8000K.

虽然前面所举的例子有些极端,造成了一个字节的偏差而使占用的存储空间翻倍,在实际应用中很少会出现这种情况,但是很有可能会使一页存储5条数据的(因为某个列多了1或2个字节)而只能存储4条数据.对于数据量较少的数据表少存一条数据并没有什么,但是在数据量非常庞大的数据表中,一页4条数据和一页5条数据将会产生明显的性能差异.

SQLServer进行数据库读写操作的基本单位是页,如果一页中存放了更多的数据行,那么对表进行扫描和查找时进行的IO操作将减少,这样就会节省时间,从而提高数据库的性能.例如