【SQL SERVER重新认识】数据内部存储结构简单探索
数据库内部如何存储数据
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