备案控制台
探索云世界
开发者社区 数据库 文章 正文

【SQL SERVER重新认识】数据内部存储结构简单探索

2020-04-01 605
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
本文涉及的产品
云数据库 RDS SQL Server，独享型 2核4GB
推荐场景：
SQL Server性能诊断案例分析
简介：

【SQL SERVER重新认识】数据内部存储结构简单探索
数据库经常需要打交道，但是从来没想过数据库内部是如何存储数据。

今天探索一下数据库内部如何存储数据，从下面几个方面探索

数据库内部如何存储数据
索引数据如何存储
操作数据对存储影响
总结
数据库内部如何存储数据

  1. 要验证，先准备数据，这里创建是一个表，并添加3条数据

 View Code

  1. 利用DBCC查看页数据，数据库名称Demo

DBCC TRACEON(2588,3604) --打开追踪
DBCC IND(Demo,DataTable,-1) --查看分配情况，这里查到的PageFID,PagePID，用于PAGE查询，PageType = 1 是数据页
DBCC PAGE(Demo,1,224,1) --查看页槽位情况
PAGE是查看cha内容太多，截取部分数据，

Slot 0, Offset 0x60, Length 43, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 43
Memory Dump @0x000000557C77A060

0000000000000000: 30001000 01000000 c0bb04c8 7fea400b 04000002 0.............@.....
0000000000000014: 001f002b 0057696c 736f6eb9 e3d6ddca d0ccecba ...+.Wilson.........
0000000000000028: d3c7f8 ...

Slot 1, Offset 0x8b, Length 42, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 42
Memory Dump @0x000000557C77A08B

0000000000000000: 30001000 02000000 c0bb04c8 7fea400b 04000002 0.............@.....
0000000000000014: 001e002a 00416c69 6365b1b1 bea9cad0 b3afd1f4 ...*.Alice..........
0000000000000028: c7f8 ..

Slot 2, Offset 0xb5, Length 40, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 40
Memory Dump @0x000000557C77A0B5

0000000000000000: 30001000 03000000 c0bb04c8 7fea400b 04000002 0.............@.....
0000000000000014: 001c0028 004b6579 b9e3d6dd cad0b7ac d8aec7f8 ...(.Key............

OFFSET TABLE:

Row - Offset
2 (0x2) - 181 (0xb5)
1 (0x1) - 139 (0x8b)
0 (0x0) - 96 (0x60)

上面是16进制，拿第一条数据来分析，分析完再跟另外两条数据来验证

Slot 0, Offset 0x60, Length 43, DumpStyle BYTE
0000000000000000: 30001000 01000000 c0bb04c8 7fea400b 04000002 0.............@.....
0000000000000014: 001f002b 0057696c 736f6eb9 e3d6ddca d0ccecba ...+.Wilson.........
0000000000000028: d3c7f8
30001000：字段的标志位，没找到官方说明，目前了解是，有可变字段和无可变字段不一致，已经删除的行会从30->3c
01000000：这个明显代表是Id，从另外两条数据可以猜到
c0bb04c8 7fea400b：因为看到Name在后面，这里刚好8个字符，这个应该是时间datetime2，写了个代码将16进制转换datetime2，现在就用代码验证，代码在文章最后放出来，因为时间转换有精度丢失，所以两个时间不是完全一直

04000002：04代表一共有4个字段，02代表一共有连个可变长字段
001f002b： 这里是两个可变字符的偏移量
0057696c 736f6eb9 e3d6ddca d0ccecba d3c7f8：这一串保存是姓名和地址，前面两个00，应该是上面长度的一个分隔符，下面也是用代码验证一下

 到这里也大概清楚SQL如何存储数据，它不是按字段顺序存储，先存固定长度的字段，然后插入分隔的符号，然后存可变长的字段，这样做可能为了减少移动数据带来的成本，后面操作数据会有讲到。

索引数据如何存储

到目前为止，是没有涉及到索引，因为我们上面的数据是没有创建索引，是一个堆表。

索引分非聚集索引和聚集索引

  1. 创建非聚集索引

create index IX_DataTable_Name on DataTable(Name ASC)
1.1 查看数据页分配情况

DBCC IND(Demo,DataTable,-1) --查看分配情况
DBCC PAGE(Demo,1,280,1) --查看页分配情况
1.2 Page分配情况

Slot 0, Offset 0x60, Length 21, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 21
Memory Dump @0x00000055721FA060

0000000000000000: 36000100 00010001 00020000 01001500 416c6963 6...............Alic
0000000000000014: 65 e

Slot 1, Offset 0x75, Length 22, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 22
Memory Dump @0x00000055721FA075

0000000000000000: 36000100 00010002 00020000 01001600 4b657920 6...............Key
0000000000000014: 4c69 Li

Slot 2, Offset 0x8b, Length 22, DumpStyle BYTE

Record Type = INDEX_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 22
Memory Dump @0x00000055721FA08B

0000000000000000: 36000100 00010000 00020000 01001600 57696c73 6...............Wils
0000000000000014: 6f6e on

1.3 Page页面分析

0100 00010001：这个是行Id，转义过来是（1:256:1），因为现在还是堆表，所以指向行Id

00020000：这个也是正文开始标记

01001500：这里记录索引的长度

后面的就是索引内容

可以看到，索引存储大概结构

指针 -> 索引信息（长度）-> 索引内容

查看行Id，可以使用sys.fn_PhysLocFormatter(%%physloc%%)

select sys.fn_PhysLocFormatter(%%physloc%%),* from DataTable

2  创建聚集索引

create clustered index IX_DatTaable_Name on DataTable(Name ASC)
2.1 查看数据页分配情况

DBCC IND(Demo,DataTable,-1) --IndexID=1就是聚集索引
DBCC PAGE(Demo,1,234,3)
2.2 Page分配情况

0000000000000000: 30001000 02000000 20d1565f 0deb400b 05000003 0....... .V_..@.....
0000000000000014: 001b0020 002c0041 6c696365 b1b1bea9 cad0b3af ... .,.Alice........
0000000000000028: d1f4c7f8
2.3 Page页面分析

这里数据大概格式行Id+定长字段+行信息+变长数据，这里就不展开验证。

值得注意是，这里的字段数量和可变长字段数量为 05000003

这里之所以会多了一个字段，是因为我们添加的聚集索引没有指定唯一，SQL SERVER会自动添加一个4字节的字段，确保聚集索引唯一。

操作数据对存储影响

  1. INSERT

insert into DataTable(Name,Address,CreateTime)
select 'Jack','广州市天河区',GETDATE()
1.1 查看Page情况

Slot 0, Offset 0x60, Length 44, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 44
Memory Dump @0x0000005CD11FA060

0000000000000000: 30001000 02000000 40f61b57 1aeb400b 05000003 0.......@..W..@.....
0000000000000014: 001b0020 002c0041 6c696365 b1b1bea9 cad0b3af ... .,.Alice........
0000000000000028: d1f4c7f8 ....

Slot 1, Offset 0xe3, Length 43, DumpStyle BYTE

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 43
Memory Dump @0x0000005CD11FA0E3

0000000000000000: 30001000 04000000 350f285e 1aeb400b 05000003 0.......5.(^..@.....
0000000000000014: 001b001f 002b004a 61636bb9 e3d6ddca d0ccecba .....+.Jack.........
0000000000000028: d3c7f8 ...

可以看到在Alice后面槽位插入了数据（因为这个表的聚集索引是在Name，升序）

  1. UPDATE

update DataTable set Address = '广州市白云区黄边路8号' where Id = 1
2.1 查看Pag情况，用2，查看整个Page

0000005CD387A064: 02000000 40f61b57 1aeb400b 05000003 001b0020 ....@..W..@........
0000005CD387A078: 002c0041 6c696365 b1b1bea9 cad0b3af d1f4c7f8 .,.Alice............
0000005CD387A08C: 30001000 03000000 40f61b57 1aeb400b 05000003 0.......@..W..@.....
0000005CD387A0A0: 001b001e 002a004b 6579b9e3 d6ddcad0 b7acd8ae .....*.Key..........
0000005CD387A0B4: c7f83000 10000100 000040f6 1b571aeb 400b0500 ..0.......@..W..@...
0000005CD387A0C8: 0003001b 0021002d 0057696c 736f6eb9 e3d6ddca .....!.-.Wilson.....
0000005CD387A0DC: d0b0d7d4 c6c7f830 00100004 00000035 0f285e1a .......0.......5.(^.
0000005CD387A0F0: eb400b05 00000300 1b001f00 2b004a61 636bb9e3 .@..........+.Jack..
0000005CD387A104: d6ddcad0 ccecbad3 c7f83000 10000100 000040f6 ..........0.......@.
0000005CD387A118: 1b571aeb 400b0500 0003001b 00210036 0057696c .W..@........!.6.Wil
0000005CD387A12C: 736f6eb9 e3d6ddca d0b0d7d4 c6c7f8bb c6b1dfc2 son.................

可以看到有2个Wilson的记录，因为更新的字段比原来的长，原来地方放不下，在当前页空闲的地方重新整条记录复制过去，然后偏移量指向新的地址。实际上字段变短了也会发生这种迁移。

这样原来的地方就变成碎片。事实上索引页的维护也是一样道理。

3.DELETE

delete DataTable where Id = 2
3.1 查看Pag情况，用2，查看整个Page

3c001000 ................<...
0000005CD627A064: 02000000 40f61b57 1aeb400b 05000003 001b0020 ....@..W..@........
0000005CD627A078: 002c0041 6c696365 b1b1bea9 cad0b3af d1f4c7f8 .,.Alice............
0000005CD627A08C: 30001000 03000000 40f61b57 1aeb400b 05000003 0.......@..W..@.....
可以看到Alice这条记录还存在页上，原来行头的4个标志位从30001000 -> 3c001000

总结

其实数据库如何存储对平常开发没什么影响，只是无聊研究一下。

其实还是有点影响，我能想到如下，未必准确和完整。

  1. 尽量选择定长的字段，例如状态，类型这种字段定义int
  2. char 是用空间换时间，经常更新字段且比较短的字段可以考虑定义char
  3. 超长的varchar字段可以考虑不放在主表，不然一页存不下，又会发生行溢出问题
  4. 索引有利有弊，要尽量合理使用索引，特别是聚集索引，非常要谨慎使用。

转发请标明出处：https://www.cnblogs.com/WilsonPan/p/12605299.html

示例代码：https://github.com/WilsonPan/Net.Demos/tree/master/Demo.SQLTools

文章标签：
云数据库 RDS SQL Server 版
SQL
索引
数据库
存储
关键词：
SQL server
云数据库RDS SQLServer版数据
SQL数据
SQL存储
云数据库RDS SQLServer版结构
相关实践学习
使用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
幸运券发放
目录
相关文章
SarPro
|
7天前
|
SQL 人工智能 算法
【SQL server】玩转SQL server数据库：第二章 关系数据库
【SQL server】玩转SQL server数据库：第二章 关系数据库
SarPro
44 10
changwoo
|
19天前
|
SQL 存储 关系型数据库
一文搞懂SQL优化——如何高效添加数据
**SQL优化关键点：** 1. **批量插入**提高效率，一次性建议不超过500条。 2. **手动事务**减少开销，多条插入语句用一个事务。 3. **主键顺序插入**避免页分裂，提升性能。 4. **使用`LOAD DATA INFILE`**大批量导入快速。 5. **避免主键乱序**，减少不必要的磁盘操作。 6. **选择合适主键类型**，避免UUID或长主键导致的性能问题。 7. **避免主键修改**，保持索引稳定。 这些技巧能优化数据库操作，提升系统性能。
changwoo
213 4
一文搞懂SQL优化——如何高效添加数据
w风雨无阻w
|
1月前
|
SQL 数据库 数据安全/隐私保护
Sql Server数据库Sa密码如何修改
Sql Server数据库Sa密码如何修改
w风雨无阻w
23 0
whisper1
|
1月前
|
SQL 存储 数据库
SQL实践篇（二）：为什么微信用SQLite存储聊天记录
SQL实践篇（二）：为什么微信用SQLite存储聊天记录
whisper1
57 1
whisper1
|
1月前
|
存储 SQL Web App开发
SQL实践篇（一）：使用WebSQL在H5中存储一个本地数据库
SQL实践篇（一）：使用WebSQL在H5中存储一个本地数据库
whisper1
43 2
傻啦嘿哟
|
1月前
|
SQL 数据可视化 数据处理
使用SQL和Python处理Excel文件数据
使用SQL和Python处理Excel文件数据
傻啦嘿哟
51 0
风水道人
|
17天前
|
SQL
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
启动mysq异常The server quit without updating PID file [FAILED]sql/data/***.pi根本解决方案
风水道人
15 0
SarPro
|
7天前
|
SQL 算法 数据库
【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
SarPro
61 6
1436047922066202
|
7天前
|
SQL 存储 数据挖掘
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
服务器数据恢复环境： 一台安装windows server操作系统的服务器。一组由8块硬盘组建的RAID5，划分LUN供这台服务器使用。 在windows服务器内装有SqlServer数据库。存储空间LUN划分了两个逻辑分区。 服务器故障&初检： 由于未知原因，Sql Server数据库文件丢失，丢失数据涉及到3个库，表的数量有3000左右。数据库文件丢失原因还没有查清楚，也不能确定数据存储位置。 数据库文件丢失后服务器仍处于开机状态，所幸没有大量数据写入。 将raid5中所有磁盘编号后取出，经过硬件工程师检测，没有发现明显的硬件故障。以只读方式将所有磁盘进行扇区级的全盘镜像，镜像完成后将所
1436047922066202
18 1
数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
hacker707
|
11天前
|
SQL 安全 Java
SQL server 2017安装教程
SQL server 2017安装教程
hacker707
14 1

热门文章

最新文章

  • 1
    阿里云数据库内核 Apache Doris 兼容 Presto、Trino、ClickHouse、Hive 等近十种 SQL 方言，助力业务平滑迁移
  • 2
    一文搞懂SQL优化——如何高效添加数据
  • 3
    Seata常见问题之服务端 error日志没有输出,客户端执行sql报错如何解决
  • 4
    【软件设计师备考 专题 】数据库语言（SQL）
  • 5
    TiDB支持的SQL语法概述
  • 6
    SQL常见面试题总结2
  • 7
    MyBatis中的9种常用动态sql标签精妙用法
  • 8
    【MySQL技术之旅】（7）总结和盘点优化方案系列之常用SQL的优化
  • 9
    SQL，Group By 真扎心，原来是这样
  • 10
    Star 4.7k！高效SQL Parser！纯Python开发！自称目前最快的纯Python SQL解析器！
  • 1
    【SQL server】玩转SQL server数据库：第三章 关系数据库标准语言SQL（二）数据查询
    61
  • 2
    【SQL server】玩转SQL server数据库：第二章 关系数据库
    44
  • 3
    数据库数据恢复—RAID5上层Sql Server数据库数据恢复案例
    18
  • 4
    SQL server 2017安装教程
    14
  • 5
    Microsoft SQL Server 编写汉字转拼音函数
    21
  • 6
    sql server 2012远程链接的方法及步骤
    17
  • 7
    高效的SQLSERVER分页查询
    16
  • 8
    sql server链接查询
    17
  • 9
    sql server模糊查询、分组
    12
  • 10
    sql server简单查询
    14

    • 相关课程

    更多
  • 如何在 PolarDB-X 中优化慢 SQL
  • SQL完全自学手册
  • SQL Server on Linux入门教程
  • SQL入门与实践
  • 数据库及SQL/MySQL基础
  • SQL进阶及查询

    • 相关电子书

    更多
  • SQL Server 2017
  • GeoMesa on Spark SQL
  • 原生SQL on Hadoop引擎- Apache HAWQ 2.x最新技术解密malili

    • 相关实验场景

    更多
  • PolarDB NL2SQL：自然语言驱动的数据智能
  • MySQL引擎及架构优化
  • PolarDB for AI：在数据库中通过SQL实现AI能力
  • 玩转MaxCompute SQL! 30分钟搞定数据分析挖掘
  • 使用SQL语句实现数据查询操作
  • 使用SQL语句管理索引
    • 下一篇
    阿里云oss简介和使用流程
    为什么选择阿里云什么是云计算全球基础设施技术领先稳定可靠安全合规分析师报告
    产品和定价全部产品免费试用产品动态产品定价价格计算器云上成本管理
    解决方案技术解决方案
    文档与社区文档开发者社区天池大赛培训与认证
    权益中心免费试用高校计划企业扶持计划推荐返现计划
    支持与服务基础服务企业增值服务迁云服务官网公告健康看板信任中心
    关注阿里云
    关注阿里云公众号或下载阿里云APP，关注云资讯，随时随地运维管控云服务
    阿里云APP阿里云微信
    联系我们：4008013260
    法律声明Cookies政策廉正举报安全举报联系我们加入我们
    阿里巴巴集团淘宝网天猫全球速卖通阿里巴巴国际交易市场1688阿里妈妈飞猪阿里云计算AliOS万网高德UC友盟优酷钉钉支付宝达摩院淘宝海外阿里云盘饿了么
    © 2009-2024 Aliyun.com 版权所有 增值电信业务经营许可证： 浙B2-20080101 域名注册服务机构许可： 浙D3-20210002 京D3-20220015
    浙公网安备 33010602009975号浙B2-20080101-4