首页 > 数据库 >再聊一下那 SQLSERVER 行不能跨页的事

再聊一下那 SQLSERVER 行不能跨页的事

时间:2023-01-01 09:44:12浏览次数:39  
标签:21212121 ... 00000000 SQLSERVER 再聊 62626262 78007800 跨页 数据

一:背景

1. 讲故事

上一篇写完了之后,马上就有朋友留言对记录行的 8060byte 限制的疑惑,因为他的表记录存储了大量的文章,存储文章的字段类型用的是 nvarchar(max),长度很显然是超过 8060byte 的,请问这个底层是怎么破掉 8060byte 的限制的?

说实话这是一个好问题,本质上来说 8060byte 的限制肯定是不能破掉的,如果让我处理的话肯定是将文章的数据分摊在多个数据页上, 那是不是如我所想呢? 我们观察一下就好。

二:观察大字段数据的布局

1. 对 nvarchar(max) 的理解

玩过 sqlserver 的朋友都知道,新一代的 sqlserver 版本已经用 varchar(max)nvarchar(max) 替代了早期的 textntext,理论上这种类型最大可存储 2 的 31 次方 - 1, 大概就是 2G,接下来我们像 nvarchar(max) 插入 1w 个字符,大概 20k 的数据,向上取整的话应该会用 3 个数据页来承载,测试代码如下:


USE MyTestDB
GO
CREATE TABLE t7 (a INT IDENTITY, b NVARCHAR(MAX))
GO

INSERT INTO t7 VALUES(REPLICATE(CAST( 'x' AS NVARCHAR(max)),10000))

SELECT LEN(b) FROM t7;

DBCC TRACEON(3604)
DBCC IND(MyTestDB,t7,-1)

从图中看居然有 4 个数据页,这就很奇怪了,等一会我们再解惑,先来简单看一下,一个是 In-row data,也叫做行内数据,是一个普通数据页,三个是 LOB data ,即大值数据( Large Object Data ),这是一种专门的LOB数据页,看样子这 1w 个 x 应该是分摊到这 3 个 LOB data 数据页上,是不是这样我们用 DBCC PAGE 把四个数据页的内容导出来看一看便知。


PAGE: (1:464)

Page @0x00000175CBB46000

m_pageId = (1:464)                  m_headerVersion = 1                 m_type = 1
m_typeFlagBits = 0x0                m_level = 0                         m_flagBits = 0x8000
m_objId (AllocUnitId.idObj) = 202   m_indexId (AllocUnitId.idInd) = 256 
Metadata: AllocUnitId = 72057594051166208                                
Metadata: PartitionId = 72057594044022784                                Metadata: IndexId = 0
Metadata: ObjectId = 1637580872     m_prevPage = (0:0)                  m_nextPage = (0:0)
pminlen = 8                         m_slotCnt = 1                       m_freeCnt = 8031
m_freeData = 159                    m_reservedCnt = 0                   m_lsn = (38:2936:61)
m_xactReserved = 0                  m_xdesId = (0:0)                    m_ghostRecCnt = 0
m_tornBits = 0                      DB Frag ID = 1        

DATA:

000000482E3F8000:   01010000 00800001 00000000 00000800 00000000  ....................
000000482E3F8014:   00000100 ca000000 5f1f9f00 d0010000 01000000  ........_...........
...
000000482E3F808C:   01000001 00000020 4e0000c8 01000001 00000000  ....... N...........
000000482E3F80A0:   00007800 78007800 78007800 78007800 78007800  ..x.x.x.x.x.x.x.x.x.
000000482E3F80B4:   78007800 78007800 78007800 78007800 78007800  x.x.x.x.x.x.x.x.x.x.
...
000000482E3F9FCC:   78007800 78007800 78000000 21212121 21212121  x.x.x.x.x...!!!!!!!!
000000482E3F9FE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
000000482E3F9FF4:   21212121 21212121 21216000                    !!!!!!!!!!`.

OFFSET TABLE:

Row - Offset                        
0 (0x0) - 96 (0x60)   


PAGE: (1:456)
DATA:
Memory Dump @0x00000048355F8000

000000483A478000:   01030000 00800001 00000000 00000000 00000000  ....................
000000483A478014:   00000100 cb000000 4010be0f c8010000 01000000  ........@...........
000000483A478028:   26000000 780b0000 24000000 00000000 00000000  &...x...$...........
000000483A47803C:   00000000 01000000 00000000 00000000 00000000  ....................
000000483A478050:   00000000 00000000 00000000 00000000 08005e0f  ..................^.
000000483A478064:   0000f306 00000000 03007800 78007800 78007800  ..........x.x.x.x.x.
...
000000483A479FA4:   00780078 00780078 00780000 00626262 62626262  .x.x.x.x.x...bbbbbbb
000000483A479FB8:   62626262 62626262 62626262 62626262 62626262  bbbbbbbbbbbbbbbbbbbb
000000483A479FCC:   62626262 62626262 62626262 62020000 00002121  bbbbbbbbbbbbb.....!!
000000483A479FE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
000000483A479FF4:   21212121 21212121 21216000                    !!!!!!!!!!`.

PAGE: (1:457)
DATA:
Memory Dump @0x000000483BA78000

000000483BA78000:   01030000 00800001 00000000 00000000 00000000  ....................
000000483BA78014:   00000100 cb000000 2800d61f c9010000 01000000  ........(...........
...
000000482EDF8050:   00000000 00000000 00000000 00000000 0800761f  ..................v.
000000482EDF8064:   0000f306 00000000 03007800 78007800 78007800  ..........x.x.x.x.x.
000000483BA79FE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
000000483BA79FF4:   21212121 21212121 21216000                    !!!!!!!!!!`.

PAGE: (1:458)
DATA:
Memory Dump @0x000000483BA78000
...
000000483BA78050:   00000000 00000000 00000000 00000000 0800761f  ..................v.
000000483BA78064:   0000f306 00000000 03007800 78007800 78007800  ..........x.x.x.x.x.
...
000000483BA79FCC:   78007800 78007800 78000000 21212121 21212121  x.x.x.x.x...!!!!!!!!
000000483BA79FE0:   21212121 21212121 21212121 21212121 21212121  !!!!!!!!!!!!!!!!!!!!
000000483BA79FF4:   21212121 21212121 21216000                    !!!!!!!!!!`.

我相信有很多朋友很奇怪,为什么 464 号 数据页也有大量的 x, 其实这些 x 算是垃圾数据,可以从 m_freeCnt = 8031 上便知,这个字段表示当前数据页的 Free 空间,所以那 1w 个 x 都被 LOB 数据页吃掉了,这和文章开头的推测是一致的。

到这里算是解决了朋友的这个疑问,但你如果想打破沙锅问到底的话,肯定想知道这 4 个数据页在 内存中是如何组织的,或者说如何串联的? 接下来我们好好聊一聊。

2. 4 个数据页是如何组织的

观察 464号 数据页是如何与 LOB 数据页 发生关系的?这个就考验基础知识了,在真正的行数据之前记录了一个 FID : PID : SID 的内存存储,即:文件ID : 数据页ID : 槽位ID,可以用 WinDbg 来观察。


0:125> dp 000000482E3F8000+0x60+0x7
00000048`2e3f8067  803f0001`78000200 00000001`35000004
00000048`2e3f8077  00001f68`000006f3 00000001`000001c9
00000048`2e3f8087  000001ca`00003ed0 00004e20`00000001
00000048`2e3f8097  00000001`000001c8 78007800`78000000
00000048`2e3f80a7  78007800`78007800 78007800`78007800
00000048`2e3f80b7  78007800`78007800 78007800`78007800
00000048`2e3f80c7  78007800`78007800 78007800`78007800
00000048`2e3f80d7  78007800`78007800 78007800`78007800

简单解释一下: 000000482E3F8000 是数据页在内存中的首地址, 000000482E3F8000+0x60 是数据页内第一个记录的地址,再加上 +0x7 是为了内存地址对齐。

仔细观察内存地址 000000482e3f8097 上的内容是 00000001 000001c8,它就对应着 SID (2byte), FID (2byte) ,PID (4byte) ,那 PID=0x000001c8 是多少呢?可以用 WinDbg 算一下是 456 号 数据页。


0:125> ? 0x1c8
Evaluate expression: 456 = 00000000`000001c8

按照这个理论继续往前看内存地址,你会发现 00000001000001c900000001000001ca,对应着 457 号数据页458 号数据页

到这里脑子里就有了一张图,大概像下面这样。

三:总结

经过本篇的分析,大家知道了 SQLSERVER 会用专门的 LOB数据页 来存储这些大字段,由于数据被拆分到多个数据页上,这让 select 操作多了更多的逻辑,也会造成 C++ 代码多次在 LOB 数据页上游走,给查询性能增加了巨大的开销。

比如下面的 SQL 查询。


SET STATISTICS IO ON
SELECT * FROM t7;
SET STATISTICS IO OFF

可以发现在 LOB 数据页上游走了 7 次,再加 2 条数据观察下。


INSERT INTO t7 VALUES(REPLICATE(CAST( 'y' AS NVARCHAR(max)),10000))
INSERT INTO t7 VALUES(REPLICATE(CAST( 'z' AS NVARCHAR(max)),10000))

SET STATISTICS IO ON
SELECT * FROM t7;
SET STATISTICS IO OFF

这次由 7 次变成了 23 次,总的来说还是尽量不要将大字段存放在数据库吧。

标签:21212121,...,00000000,SQLSERVER,再聊,62626262,78007800,跨页,数据
From: https://www.cnblogs.com/huangxincheng/p/17017740.html

相关文章

  • 一次SQL调优 聊一聊 SQLSERVER 数据页
    一:背景1.讲故事最近给一位朋友做​​SQL慢语句​​优化,花了些时间调优,遗憾的是SQLSERVER非源码公开,玩起来不是那么顺利,不过从这次经历中我觉得明年的一个重大任务就是......
  • 聊一聊 SQLSERVER 的行不能跨页
    一:背景1.讲故事相信有很多朋友在学习SQLSERVER的时候都听说过这句话,但大多都是记忆为主,最近在研究SQLSERVER,所以我们从底层存储的角度来深入理解下。二:理解数据页......
  • Sqlserver 用户数据库权限
    第一步打开数据库管理界面---》安全性--》登录名,“新建登录名”这个界面,只要填写登录名和密码即可。其他的都不用管。第二步执行sql: --将所有数据库的查看权限......
  • sqlserver 获取汉字拼音的首字母(大写)函数
    USE[test]GO/******对象:UserDefinedFunction[dbo].[GetFirstChar]脚本日期:02/22/201916:39:06******/SETANSI_NULLSONGOSETQUOTED_IDENTIFIERONG......
  • 一次SQL调优 聊一聊 SQLSERVER 数据页
    一:背景1.讲故事最近给一位朋友做SQL慢语句优化,花了些时间调优,遗憾的是SQLSERVER非源码公开,玩起来不是那么顺利,不过从这次经历中我觉得明年的一个重大任务就是好好研......
  • sqlserver MD5加密
    一、示例及分析例如对人力资源表(hrmresource)的身份证号(certificatenum)进行md5加密对于mysql数据库直接查询md5函数即可,但sqlServer没有内置md5函数,处理起来相......
  • ef6不支持sqlserver 2008的offset语句分页解决
    一、问题项目中如果使用了EF6,数据库是2008,而分页会收到类似如下的错误提示:{"Depth":0,"ClassName":"","Message":"Incorrectsyntaxnear'OFFSET'.\r\nIn......
  • sqlserver/System.Data.SqlClient.SqlException (0x80131904): 用户 'NT AUTHORITY\I
    本人最近学习XAF框架项目,在本地运行可以连接到数据库,发布后无法连接,抛出用户'NTAUTHORITY\IUSR'登录失败问题截图:  问题原因:数据库连接使用的window身份验证时,W......
  • SQLServer2017管理工具的数据库还原操作
    原文链接:https://blog.csdn.net/qq_21209307/article/details/104992642还原数据库选择数据库点击右键选择还原数据库。在弹出的框内选择设备,点击输入框后面的“…”按钮,......
  • C# SQLServer数据库连接并执行类
    SQLHelper.cs usingSystem;usingSystem.Collections.Generic;usingSystem.Configuration;usingSystem.Data;usingSystem.Data.SqlClient;usingSystem.Linq;......