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

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

今天同事问了我一个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
目录
相关文章
|
2月前
|
SQL 数据处理 数据库
专坑同事的SQL写法:性能杀手揭秘
【8月更文挑战第29天】在日常的数据库开发与维护工作中,编写高效、清晰的SQL语句是每位数据工程师的必修课。然而,不当的SQL编写习惯不仅能降低查询效率,还可能给同事的工作带来不必要的困扰。今天,我们就来揭秘八种常见的“专坑同事”SQL写法,助你避免成为那个无意间拖慢整个团队步伐的人。
31 1
|
2月前
|
SQL 关系型数据库 MySQL
【MySQL 慢查询秘籍】慢SQL无处遁形!实战指南:一步步教你揪出数据库性能杀手!
【8月更文挑战第24天】本文以教程形式深入探讨了MySQL慢SQL查询的分析与优化方法。首先介绍了如何配置MySQL以记录执行时间过长的SQL语句。接着,利用内置工具`mysqlslowlog`及第三方工具`pt-query-digest`对慢查询日志进行了详细分析。通过一个具体示例展示了可能导致性能瓶颈的查询,并提出了相应的优化策略,包括添加索引、缩小查询范围、使用`EXPLAIN`分析执行计划等。掌握这些技巧对于提升MySQL数据库性能具有重要意义。
62 1
|
2月前
|
前端开发 C# 设计模式
“深度剖析WPF开发中的设计模式应用:以MVVM为核心,手把手教你重构代码结构,实现软件工程的最佳实践与高效协作”
【8月更文挑战第31天】设计模式是在软件工程中解决常见问题的成熟方案。在WPF开发中,合理应用如MVC、MVVM及工厂模式等能显著提升代码质量和可维护性。本文通过具体案例,详细解析了这些模式的实际应用,特别是MVVM模式如何通过分离UI逻辑与业务逻辑,实现视图与模型的松耦合,从而优化代码结构并提高开发效率。通过示例代码展示了从模型定义、视图模型管理到视图展示的全过程,帮助读者更好地理解并应用这些模式。
59 0
|
2月前
|
Java XML Maven
跨越时代的飞跃:Struts 2 升级秘籍——从旧版本无缝迁移到最新版,焕发应用新生!
【8月更文挑战第31天】随着软件技术的发展,Struts 2 框架也在不断更新。本文通过具体案例指导开发者如何从旧版平滑升级到 Struts 2.6.x。首先更新 `pom.xml` 中的依赖版本,并执行 `mvn clean install`。接着检查 `struts.xml` 配置,确保符合新版本要求,调整包扫描器等设置。审查 Action 类及其注解,检查配置文件中的弃用项及插件。更新自定义拦截器实现,并验证日志配置。最后,通过一系列测试确保升级后的系统正常运行。通过这些步骤,可以顺利完成 Struts 2 的版本升级,提升应用的安全性和性能。
97 0
|
2月前
|
SQL 测试技术 数据处理
|
2月前
|
SQL 关系型数据库 MySQL
SQL性能调优的神奇之处:如何用优化技巧让你的数据库查询飞起来,实现秒级响应?
【8月更文挑战第31天】在现代软件开发中,数据库性能至关重要。本文通过一个实战案例,展示了从慢查询到秒级响应的全过程。通过对查询的详细分析与优化,包括创建索引、改进查询语句及数据类型选择等措施,最终显著提升了性能。文章还提供了示例代码及最佳实践建议,帮助读者掌握SQL性能调优的核心技巧。
48 0
|
2月前
|
SQL 关系型数据库 MySQL
SQL Server、MySQL、PostgreSQL:主流数据库SQL语法异同比较——深入探讨数据类型、分页查询、表创建与数据插入、函数和索引等关键语法差异,为跨数据库开发提供实用指导
【8月更文挑战第31天】SQL Server、MySQL和PostgreSQL是当今最流行的关系型数据库管理系统,均使用SQL作为查询语言,但在语法和功能实现上存在差异。本文将比较它们在数据类型、分页查询、创建和插入数据以及函数和索引等方面的异同,帮助开发者更好地理解和使用这些数据库。尽管它们共用SQL语言,但每个系统都有独特的语法规则,了解这些差异有助于提升开发效率和项目成功率。
140 0
|
2月前
|
SQL NoSQL 关系型数据库
性能与扩展性的考量:SQL vs NoSQL
【8月更文第24天】在选择数据库系统时,开发者和架构师面临着一个关键决策:是选择传统的SQL(结构化查询语言)数据库还是现代的NoSQL(非关系型)数据库。这两种类型各有优劣,尤其是在性能和扩展性方面。本文将深入探讨SQL和NoSQL数据库在这两个方面的差异,并通过具体的代码示例来展示它们各自的优势。
49 0
|
4月前
|
SQL 存储 关系型数据库
PolarDB产品使用合集之有的sql里面有自定义存储函数 如果想走列存有什么优化建议吗
PolarDB是阿里云推出的一种云原生数据库服务,专为云设计,提供兼容MySQL、PostgreSQL的高性能、低成本、弹性可扩展的数据库解决方案,可以有效地管理和优化PolarDB实例,确保数据库服务的稳定、高效运行。以下是使用PolarDB产品的一些建议和最佳实践合集。
330 0
|
SQL 存储 Perl
PL/SQL学习笔记_03_存储函数与存储过程
ORACLE 提供可以把 PL/SQL 程序存储在数据库中,并可以在任何地方来运行它。这样就叫存储过程或函数。 存储函数:有返回值,创建完成后,通过select function() from dual;执行 存储过程:由于没有返回值,创建完成后,不能使用select语句,只能使用pl/sql块执行   一.
1228 0
下一篇
无影云桌面