SQL Server 即 Microsoft SQL Server SQL 是英文 Structured Query Language 的缩写, 意思为结构化查询语言 SQL 语言的主要功能就是同各种数据库建立联系, 进行沟通按照 ANSI(美国国家标准协会)的规定, SQL 被作为关系型数据库管理系统的标准语言
这篇文章主要给大家介绍了关于 SQL Server 数据库中伪列及伪列含义的相关资料, 文中通过示例代码介绍的非常详细, 对大家的学习或者工作具有一定的参考学习价值, 需要的朋友们下面随着小编来一起学习学习吧
SQL Server 中的伪列
下午看 QQ 群有人在讨论 (非聚集) 索引的存储, 说, 对于聚集索引表, 非聚集索引存储的是索引键值 + 聚集索引键值; 对于非聚集索引表, 索引存储的是索引键值 + RowId, 这应该是一个常识, 对此不作具体详细阐述
这里主要是提到的 RowId 引起了一点思考
那么, 这个 RowId 是个什么玩意? 能不能更加直观一点来看看 RowId 的信息? 代表什么含义? 这个当然也是可以的
Oracle 中的表中有一个伪列的概念, 就是在查询表的时候加上
select rowid,* from Table
, 会查询出来伪列
SQL Server 中同样有这么一个伪列, 在 SQL Server 中, 这个伪列可以认为是数据行的物理地址, 下面简单来观察一下这个 RowId 以及 RowId 的含义
伪列的测试
建一张简单的表, 下面借助这个表来查看说明伪列
- CREATE TABLE Test
- (
- id int identity(1,1),
- name varchar(50)
- )
- GO
- INSERT INTO Test VALUES (NEWID())
- GO 100
SQL Server 中有一个未公开的伪列 %%physloc%%, 也就是在查询的时候, 对于任何一张表, 可以加上这个字段, 比如如下, 就可以查到表中每一行的伪列
这个伪列的类型是 binary(8) , 也就是有 8 个字节, 参考上图的
DATALENGTH(%%physloc%%) as Len
,%%physloc%% 返回的记录的物理地址, 其中前四个字节表示页号, 中间两个字节表示文件号, 最后两个字节表示槽号
为了更加方便地观察伪列的含义, sqlserver 提供了一个未公开的系统函数 sys.fn_PhysLocFormatter, 下面借助
sys.fn_PhysLocFormatter
这个函数来继续观察这个伪列
如下图, 这里就可以清晰地看到伪列中的信息了
比如第一行中的 (1:73:0), 上面说了, 其中前四个字节表示页号, 中间两个字节表示文件号, 最后两个字节表示槽号,(1:73:0) 这种格式是经过
sys.fn_PhysLocFormatter
格式化显式之后的结果
把文件号 1 放在最前面, 中间的 73 是页号(page number), 最后一位 0 是槽号(sloc number)
下面粗略地说一下这几个字段的含义这里要求对 SQL Server 的存储只是有一个基本的认识, 否则看的云里雾里
1, 首先说什么是文件号
如截图, 文件号就是数据库的数据文件编号, 这里只有一个数据文件, 文件编号为 1, 建表的时候默认 (这里也只能建立) 建立在 fileid = 1 的文件上面, fileid=2 的是日志文件, 就不多说了
2, 其次是页号, 页号就是分配给当前这张表的数据页面 (8kb 的最小分配单元) 的页号, 我们看一下 Test 这个表的页面情况
借助 DBCC IND 命令, 查询分配给这个表的页面信息, 其中 77 号页面是 IMA 也面, 至于什么事 IMA 页面, 不多解释
73 号页面才是真正存储数据的页, 与上面的 1:73:0 中的 73 一样, 没毛病
3, 最后看一下槽号, 槽号的概念要对 SQL Server 的数据页面有一个基本的认识, 这里盗用一张网友的图
所谓的槽号就是在数据页面中, 每个页面存储多行数据, 槽号用来标记每一行数据的偏移量, 用大白话说就是存储每一行数据的地址空间开始的位置, 因为每一行数据的总长度是不一样的(存在可变长度列的情况下), 每一行的占用的存储空间也是不一样的, 槽号或者行偏移量就是说明每一行数据在页内的开始位置
不过
sys.fn_PhysLocFormatter
格式化显式的槽号并不是如下截图的偏移量, 而是第 N 个数据行的这个 N 的信息, 因此第 1 行的槽号就是 1, 第 2 行的槽号就是 2, 以此类推, 当第一个 page 存储满之后, 从第二个 page 开始存储, 槽号又从 0 开始编号且累加
至此, 对 SQL Server 的伪列, 也就说经常说的 RowId 有了一个简单的认识
这里可以认为, 在 SQL Server 数据库中, 伪列 RowId 就是数据行的物理地址, 至于别的数据库中的伪列 (RowId) 是不是物理地址倒是不确定(很有可能也是的)
这里简单提一下一开始说的一个问题:
为什么 SQL Server 的聚集表 (有聚集索引的表) 存储数据的时候存储的是索引键值 + 聚集索引键值, 对于非聚集索引表, 索引存储的是索引键值 + RowId?
或者反过来说, 为什么聚集索引表的非聚集索引存储的是索引键值 + 聚集索引键值而不是索引存储的是索引键值 + RowId
作为一个常识, 聚集索引要按照聚集索引的顺序存放, 这就意味着聚集索引表的行数据物理位置有可能发生变化, 比如在众所周知的页拆分 (page split) 中发生变化, 在数据行的物理位置发生了变化的时候, 如果非聚集索引存储的是索引键值 + RowId, 那么这个 RowId 也势必要发生变化, 这个变化当然要耗费一定的性能, 为了防止此种情况的发生, 聚集表中的非聚集索引存储成相对不变的索引键值 + 聚集索引键值, 因为在数据行的物理位置发生变化的时候, 聚集索引键值是相对不变的, 这一点也不难理解
当然有一种例外, 当对聚集索引表做更新的时候, 直接更新聚集索引的键值, 这样的话, 也有可能造成聚集索引表中当前数据行的物理位置发生变化, 这一点也比较有意思, 就不展开叙述了
这一点跟绕口令一样, 这里要求对 SQL Server 中的聚集索引和非聚集索引, 以及存储结构有一个基础的认识才容易理解
最后高能预警
高能预警, 别说我瞎比比误导人, 上述解析伪列的函数
sys.fn_PhysLocFormatter
是一个未公开的函数, 未公开的函数就有可能潜在一些问题, 事实上这个函数有一个非常严重的 bug
该 bug 就是在解析物理存储位置的时候有一定的逻辑错误, 这个问题早有细心的人分析过了
参考:/article/17/1108/352239.html
目前测试来看, 在 SQL Server 2014 中仍然存在 bug,N 前年啃书的时候就了解到有这么一个函数, 但是一直不想提及
sys.fn_PhysLocFormatter
这个函数的原因, 因此对于未公开的函数, 请不要做验证性测试, 再次声明: 该函数有 bug, 请谨慎使用
附上这个函数的源代码, 并参考原文的结论
- create function sys.fn_PhysLocFormatter (@physical_locator binary (8))
- returns varchar (128)
- as
- begin
- declare @page_id binary (4)
- declare @file_id binary (2)
- declare @slot_id binary (2)
- -- Page ID is the first four bytes, then 2 bytes of page ID, then 2 bytes of slot
- --
- select @page_id = convert (binary (4), reverse (substring (@physical_locator, 1, 4)))
- select @file_id = convert (binary (2), reverse (substring (@physical_locator, 5, 2)))
- select @slot_id = convert (binary (2), reverse (substring (@physical_locator, 7, 2)))
- return '(' + cast (cast (@file_id as int) as varchar) + ':'
- + cast (cast (@page_id as int) as varchar) + ':'
- + cast (cast (@slot_id as int) as varchar) + ')'
- end
问题出在 reverse 函数上
reverse 函数的作用是字符反转, 而不是字节反转, 当遇到 81-FE 之间的字节时, 被认为是双字节字符而组合在一起参与反转操作, 造成了错误
总结
本文简单阐述了 SQL Server 中的伪列, 以及伪列的含义, 通过伪列对非聚集索引以及数据行的存储结构有一个简单的了解
好了, 以上就是这篇文章的全部内容了, 希望本文的内容对大家的学习或者工作能带来一定的帮助, 如果有疑问大家可以留言交流, 谢谢大家对 PHPERZ 的支持
来源: http://www.phperz.com/article/18/0315/352240.html