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

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
RDS SQL Server Serverless,2-4RCU 50GB 3个月
推荐场景:
简介:

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

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

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

 

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

复制代码
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,查看到的情况如图:

image

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

SELECT   OBJECT_NAME (i. object_id AS  TableName,data_pages * 8   AS  DataSize  -- 这里返回的是数据页个数,1页是8K,所以乘以8
FROM  sys.indexes  as  i
JOIN  sys.partitions  as  p  ON  p. object_id   =  i. object_id   and  p.index_id  =  i.index_id
JOIN  sys.allocation_units  as  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部分组成:页头、数据行和行偏移矩阵,具体结构如图:

image

页头保存了页的编号、上一页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秒,对性能的影响非常明显。

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

  • 主键尽可能的短,能用tinyint的就不要用int,能用char(5)的就不要用成varchar(50)。
  • 计算好表列的长度,能够在一个页中存放5条数据的,那就不要将字段设置的太长使得一个页中只能存放3条或者4条数据。
  • 尽量将字段设置为不允许为NULL,因为NULL值在存储和数据处理时系统需要专门的处理,降低了性能。
  • 能够用固定长度的就不要用变长字段,比如身份证号就可以使用CHAR(18),而不应该使用VARCHAR(18)。
  • 不要在一个表中建立太多的列,如果一个实体的属性太多时可以考虑进行垂直分割,将常用的字段放在一个表,不常用的字段放另外的表,这样可以减小常用字段表中数据列占用的空间,使得一个数据页中存储更多的数据行。
  • 不要将大对象、长字符串和常用的字段放在同一个表中。同样还是出于性能上的考虑,比如有个产品表,里面有产品ID、产品名字、产品售价、产品图片、产品描述等字段,那么我们可以将产品ID、产品名字、产品售价这几个常用的而且占用空间小的列放在一个表,然后建立产品ID、产品图片、产品描述这样的表,通过外键约束的方式将大对象数据和长字符串数据放在另一个表中。
相关实践学习
使用SQL语句管理索引
本次实验主要介绍如何在RDS-SQLServer数据库中,使用SQL语句管理索引。
SQL Server on Linux入门教程
SQL Server数据库一直只提供Windows下的版本。2016年微软宣布推出可运行在Linux系统下的SQL Server数据库,该版本目前还是早期预览版本。本课程主要介绍SQLServer On Linux的基本知识。 相关的阿里云产品:云数据库RDS&nbsp;SQL Server版 RDS SQL Server不仅拥有高可用架构和任意时间点的数据恢复功能,强力支撑各种企业应用,同时也包含了微软的License费用,减少额外支出。 了解产品详情:&nbsp;https://www.aliyun.com/product/rds/sqlserver
目录
相关文章
|
26天前
|
SQL 存储 缓存
如何优化SQL查询性能?
【10月更文挑战第28天】如何优化SQL查询性能?
84 10
|
27天前
|
SQL 关系型数据库 MySQL
惊呆:where 1=1 可能严重影响性能,差了10多倍,快去排查你的 sql
老架构师尼恩在读者交流群中分享了关于MySQL中“where 1=1”条件的性能影响及其解决方案。该条件在动态SQL中常用,但可能在无真实条件时导致全表扫描,严重影响性能。尼恩建议通过其他条件或SQL子句命中索引,或使用MyBatis的`&lt;where&gt;`标签来避免性能问题。他还提供了详细的执行计划分析和优化建议,帮助大家在面试中展示深厚的技术功底,赢得面试官的青睐。更多内容可参考《尼恩Java面试宝典PDF》。
|
2月前
|
SQL 监控 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
2月前
|
SQL 关系型数据库 PostgreSQL
遇到SQL 子查询性能很差?其实可以这样优化
遇到SQL 子查询性能很差?其实可以这样优化
99 2
|
2月前
|
SQL Oracle 关系型数据库
Oracle SQL:了解执行计划和性能调优
Oracle SQL:了解执行计划和性能调优
59 1
|
2月前
|
SQL 存储 数据库
慢SQL对数据库写入性能的影响及优化技巧
在数据库管理系统中,慢SQL(即执行缓慢的SQL语句)不仅会影响查询性能,还可能对数据库的写入性能产生显著的不利影响
|
2月前
|
SQL 数据处理 数据库
SQL语句优化与查询结果优化:提升数据库性能的实战技巧
在数据库管理和应用中,SQL语句的编写和查询结果的优化是提升数据库性能的关键环节
|
3月前
|
关系型数据库 MySQL 网络安全
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
|
5月前
|
SQL 存储 监控
SQL Server的并行实施如何优化?
【7月更文挑战第23天】SQL Server的并行实施如何优化?
117 13
|
5月前
|
SQL
解锁 SQL Server 2022的时间序列数据功能
【7月更文挑战第14天】要解锁SQL Server 2022的时间序列数据功能,可使用`generate_series`函数生成整数序列,例如:`SELECT value FROM generate_series(1, 10)。此外,`date_bucket`函数能按指定间隔(如周)对日期时间值分组,这些工具结合窗口函数和其他时间日期函数,能高效处理和分析时间序列数据。更多信息请参考官方文档和技术资料。