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

 数据库经常需要打交道,但是从来没想过数据库内部是如何存储数据。

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

  1. 数据库内部如何存储数据
  2. 索引数据如何存储
  3. 操作数据对存储影响

数据库内部如何存储数据

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

create table datatable(id int identity(1,1), [name] varchar(50), [address] varchar(200), createtime datetime2)
    
insert into datatable
select 'wilson','广州市天河区',getdate() union all
select 'alice','北京市朝阳区',getdate() union all
select 'key','广州市番禺区',getdate()

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 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,升序)

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:   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

 

 

 

(0)
上一篇 2022年3月21日
下一篇 2022年3月21日

相关推荐