【SQLSERVER】数据内部存储结构简单探索
探索数据库内部存储数据" title="存储数据">存储数据存储结构,将从数据库内部如何存储数据,索引数据如何存储,操作数据对存储影响,最后总结。
数据库经常需要打交道,但是从来没想过数据库内部是如何存储数据。
今天探索一下数据库内部如何存储数据,从下面几个方面探索
- 数据库内部如何存储数据
- 索引数据如何存储
- 操作数据对存储影响
- 总结
数据库内部如何存储数据
1. 要验证,先准备数据,这里创建是一个表,并添加3条数据
createtable DataTable(Id intidentity(1,1), [Name]varchar(50), [Address]varchar(200), CreateTime datetime2)insertinto DataTableselect"Wilson","广州市天河区",GETDATE() unionallselect"Alice","北京市朝阳区",GETDATE() unionall
select"Key","广州市番禺区",GETDATE()
View Code
2. 利用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 BYTERecord Type
= PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSRecord Size
=43Memory
Dump@0x000000557C77A0600000000000000000: 3000100001000000 c0bb04c8 7fea400b 040000020.............@.....
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: 3000100002000000 c0bb04c8 7fea400b 040000020.............@.....
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: 3000100003000000 c0bb04c8 7fea400b 040000020.............@.....
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 BYTE0000000000000000: 3000100001000000 c0bb04c8 7fea400b 040000020.............@.....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. 创建非聚集索引
createindex 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 BYTERecord Type
= INDEX_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSRecord Size
=21Memory
Dump@0x00000055721FA0600000000000000000: 36000100000100010002000001001500 416c6963 6...............Alic
0000000000000014: 65 e
Slot 1, Offset 0x75, Length 22, DumpStyle BYTERecord Type = INDEX_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size =22
Memory Dump@0x00000055721FA075
0000000000000000: 36000100000100020002000001001600 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: 36000100000100000002000001001600 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 创建聚集索引
createclusteredindex 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: 3000100002000000 20d1565f 0deb400b 050000030....... .V_..@.....0000000000000014: 001b0020 002c0041 6c696365 b1b1bea9 cad0b3af ... .,.Alice........0000000000000028: d1f4c7f8
2.3 Page页面分析
这里数据大概格式行Id+定长字段+行信息+变长数据,这里就不展开验证。
值得注意是,这里的字段数量和可变长字段数量为 05000003
这里之所以会多了一个字段,是因为我们添加的聚集索引没有指定唯一,SQL SERVER会自动添加一个4字节的字段,确保聚集索引唯一。
操作数据对存储影响
1. INSERT
insertinto DataTable(Name,Address,CreateTime)select"Jack","广州市天河区",GETDATE()
1.1 查看Page情况
Slot 0, Offset 0x60, Length 44, DumpStyle BYTERecord Type
= PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNSRecord Size
=44Memory
Dump@0x0000005CD11FA0600000000000000000: 3000100002000000 40f61b57 1aeb400b 050000030.......@..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: 3000100004000000 350f285e 1aeb400b 050000030.......5.(^..@.....
0000000000000014: 001b001f 002b004a 61636bb9 e3d6ddca d0ccecba .....+.Jack.........
0000000000000028: d3c7f8 ...
可以看到在Alice后面槽位插入了数据(因为这个表的聚集索引是在Name,升序)
2. 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:
3000100003000000 40f61b57 1aeb400b 050000030.......@..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
0010000400000035 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.Wil0000005CD387A12C: 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:
3000100003000000 40f61b57 1aeb400b 050000030.......@..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
以上是 【SQLSERVER】数据内部存储结构简单探索 的全部内容, 来源链接: utcz.com/z/532963.html