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

本文涉及的产品
云数据库 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、产品图片、产品描述这样的表,通过外键约束的方式将大对象数据和长字符串数据放在另一个表中。
目录
相关文章
|
3月前
|
SQL 关系型数据库 PostgreSQL
CTE vs 子查询:深入拆解PostgreSQL复杂SQL的隐藏性能差异
本文深入探讨了PostgreSQL中CTE(公共表表达式)与子查询的选择对SQL性能的影响。通过分析两者底层机制,揭示CTE的物化特性及子查询的优化融合优势,并结合多场景案例对比执行效率。最终给出决策指南,帮助开发者根据数据量、引用次数和复杂度选择最优方案,同时提供高级优化技巧和版本演进建议,助力SQL性能调优。
320 1
|
6月前
|
SQL 关系型数据库 MySQL
如何优化SQL查询以提高数据库性能?
这篇文章以生动的比喻介绍了优化SQL查询的重要性及方法。它首先将未优化的SQL查询比作在自助餐厅贪多嚼不烂的行为,强调了只获取必要数据的必要性。接着,文章详细讲解了四种优化策略:**精简选择**(避免使用`SELECT *`)、**专业筛选**(利用`WHERE`缩小范围)、**高效联接**(索引和限制数据量)以及**使用索引**(加速搜索)。此外,还探讨了如何避免N+1查询问题、使用分页限制结果、理解执行计划以及定期维护数据库健康。通过这些技巧,可以显著提升数据库性能,让查询更高效流畅。
|
7月前
|
SQL 关系型数据库 OLAP
云原生数据仓库AnalyticDB PostgreSQL同一个SQL可以实现向量索引、全文索引GIN、普通索引BTREE混合查询,简化业务实现逻辑、提升查询性能
本文档介绍了如何在AnalyticDB for PostgreSQL中创建表、向量索引及混合检索的实现步骤。主要内容包括:创建`articles`表并设置向量存储格式,创建ANN向量索引,为表增加`username`和`time`列,建立BTREE索引和GIN全文检索索引,并展示了查询结果。参考文档提供了详细的SQL语句和配置说明。
184 2
|
8月前
|
SQL Oracle 关系型数据库
如何在 Oracle 中配置和使用 SQL Profiles 来优化查询性能?
在 Oracle 数据库中,SQL Profiles 是优化查询性能的工具,通过提供额外统计信息帮助生成更有效的执行计划。配置和使用步骤包括:1. 启用自动 SQL 调优;2. 手动创建 SQL Profile,涉及收集、执行调优任务、查看报告及应用建议;3. 验证效果;4. 使用 `DBA_SQL_PROFILES` 视图管理 Profile。
|
10月前
|
SQL 数据库 UED
SQL性能提升秘籍:5步优化法与10个实战案例
在数据库管理和应用开发中,SQL查询的性能优化至关重要。高效的SQL查询不仅可以提高应用的响应速度,还能降低服务器负载,提升用户体验。本文将分享SQL优化的五大步骤和十个实战案例,帮助构建高效、稳定的数据库应用。
846 3
|
10月前
|
SQL IDE 数据库连接
IntelliJ IDEA处理大文件SQL:性能优势解析
在数据库开发和管理工作中,执行大型SQL文件是一个常见的任务。传统的数据库管理工具如Navicat在处理大型SQL文件时可能会遇到性能瓶颈。而IntelliJ IDEA,作为一个强大的集成开发环境,提供了一些高级功能,使其在执行大文件SQL时表现出色。本文将探讨IntelliJ IDEA在处理大文件SQL时的性能优势,并与Navicat进行比较。
161 4
|
10月前
|
SQL 缓存 监控
SQL性能提升指南:五大优化策略与十个实战案例
在数据库性能优化的世界里,SQL优化是提升查询效率的关键。一个高效的SQL查询可以显著减少数据库的负载,提高应用响应速度,甚至影响整个系统的稳定性和扩展性。本文将介绍SQL优化的五大步骤,并结合十个实战案例,为你提供一份详尽的性能提升指南。
885 0
|
SQL 存储 关系型数据库
PolarDB产品使用合集之有的sql里面有自定义存储函数 如果想走列存有什么优化建议吗
PolarDB是阿里云推出的一种云原生数据库服务,专为云设计,提供兼容MySQL、PostgreSQL的高性能、低成本、弹性可扩展的数据库解决方案,可以有效地管理和优化PolarDB实例,确保数据库服务的稳定、高效运行。以下是使用PolarDB产品的一些建议和最佳实践合集。
403 0
|
SQL 存储 Perl
PL/SQL学习笔记_03_存储函数与存储过程
ORACLE 提供可以把 PL/SQL 程序存储在数据库中,并可以在任何地方来运行它。这样就叫存储过程或函数。 存储函数:有返回值,创建完成后,通过select function() from dual;执行 存储过程:由于没有返回值,创建完成后,不能使用select语句,只能使用pl/sql块执行   一.
1312 0
|
关系型数据库 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)")