再聊一下那SQL Server 行不能跨頁的事

作者：一線碼農(nóng)聊技術(shù) 2023-01-01 13:50:42

玩過 sqlserver 的朋友都知道，新一代的 sqlserver 版本已經(jīng)用 varchar(max)? 和 nvarchar(max)? 替代了早期的 text? 和 ntext?，理論上這種類型最大可存儲 2 的 31 次方 - 1?，大概就是 2G，接下來我們像 nvarchar(max) 插入 1w 個(gè)字符，大概 20k 的數(shù)據(jù)，向上取整的話應(yīng)該會用 3 個(gè)數(shù)據(jù)頁來承載。

一：背景

1. 講故事

上一篇寫完了之后，馬上就有朋友留言對記錄行的 8060byte 限制的疑惑，因?yàn)樗谋碛涗洿鎯α舜罅康奈恼拢鎯ξ恼碌淖侄晤愋陀玫氖?nbsp;nvarchar(max)，長度很顯然是超過 8060byte 的，請問這個(gè)底層是怎么破掉 8060byte 的限制的?

說實(shí)話這是一個(gè)好問題，本質(zhì)上來說 8060byte 的限制肯定是不能破掉的，如果讓我處理的話肯定是將文章的數(shù)據(jù)分?jǐn)傇诙鄠€(gè)數(shù)據(jù)頁上, 那是不是如我所想呢？我們觀察一下就好。

二：觀察大字段數(shù)據(jù)的布局

1. 對 nvarchar(max) 的理解

玩過 sqlserver 的朋友都知道，新一代的 sqlserver 版本已經(jīng)用 varchar(max) 和 nvarchar(max) 替代了早期的 text 和 ntext，理論上這種類型最大可存儲 2 的 31 次方 - 1，大概就是 2G，接下來我們像 nvarchar(max) 插入 1w 個(gè)字符，大概 20k 的數(shù)據(jù)，向上取整的話應(yīng)該會用 3 個(gè)數(shù)據(jù)頁來承載，測試代碼如下：

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 個(gè)數(shù)據(jù)頁，這就很奇怪了，等一會我們再解惑，先來簡單看一下，一個(gè)是 In-row data，也叫做行內(nèi)數(shù)據(jù)，是一個(gè)普通數(shù)據(jù)頁，三個(gè)是 LOB data ，即大值數(shù)據(jù)（ Large Object Data ），這是一種專門的LOB數(shù)據(jù)頁，看樣子這 1w 個(gè) x 應(yīng)該是分?jǐn)偟竭@ 3 個(gè) LOB data 數(shù)據(jù)頁上，是不是這樣我們用 DBCC PAGE 把四個(gè)數(shù)據(jù)頁的內(nèi)容導(dǎo)出來看一看便知。

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 號數(shù)據(jù)頁也有大量的 x，其實(shí)這些 x 算是垃圾數(shù)據(jù)，可以從 m_freeCnt = 8031 上便知，這個(gè)字段表示當(dāng)前數(shù)據(jù)頁的 Free 空間，所以那 1w 個(gè) x 都被 LOB 數(shù)據(jù)頁吃掉了，這和文章開頭的推測是一致的。

到這里算是解決了朋友的這個(gè)疑問，但你如果想打破沙鍋問到底的話，肯定想知道這 4 個(gè)數(shù)據(jù)頁在內(nèi)存中是如何組織的，或者說如何串聯(lián)的？接下來我們好好聊一聊。

2. 4 個(gè)數(shù)據(jù)頁是如何組織的

觀察 464號數(shù)據(jù)頁是如何與 LOB 數(shù)據(jù)頁發(fā)生關(guān)系的？這個(gè)就考驗(yàn)基礎(chǔ)知識了，在真正的行數(shù)據(jù)之前記錄了一個(gè) FID : PID : SID 的內(nèi)存存儲，即：文件ID : 數(shù)據(jù)頁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 是數(shù)據(jù)頁在內(nèi)存中的首地址， 000000482E3F8000+0x60 是數(shù)據(jù)頁內(nèi)第一個(gè)記錄的地址，再加上 +0x7 是為了內(nèi)存地址對齊。

仔細(xì)觀察內(nèi)存地址 000000482e3f8097 上的內(nèi)容是 00000001 000001c8，它就對應(yīng)著 SID (2byte), FID (2byte) ,PID (4byte) ，那 PID=0x000001c8 是多少呢？可以用 WinDbg 算一下是 456 號數(shù)據(jù)頁。

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

按照這個(gè)理論繼續(xù)往前看內(nèi)存地址，你會發(fā)現(xiàn) 00000001000001c9 和 00000001000001ca，對應(yīng)著 457 號數(shù)據(jù)頁和 458 號數(shù)據(jù)頁。

到這里腦子里就有了一張圖，大概像下面這樣。

三：總結(jié)

經(jīng)過本篇的分析，大家知道了 SQLSERVER 會用專門的 LOB數(shù)據(jù)頁來存儲這些大字段，由于數(shù)據(jù)被拆分到多個(gè)數(shù)據(jù)頁上，這讓 select 操作多了更多的邏輯，也會造成 C++ 代碼多次在 LOB 數(shù)據(jù)頁上游走，給查詢性能增加了巨大的開銷。

比如下面的 SQL 查詢。

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

可以發(fā)現(xiàn)在 LOB 數(shù)據(jù)頁上游走了 7 次，再加 2 條數(shù)據(jù)觀察下。

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