数据库经常需要打交道, 但是从来没想过数据库内部是如何存储数据.
今天探索一下数据库内部如何存储数据, 从下面几个方面探索
数据库内部如何存储数据
索引数据如何存储
操作数据对存储影响
总结
数据库内部如何存储数据
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()
- 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 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
来源: https://www.cnblogs.com/WilsonPan/p/12605299.html