一：背景
1. 講故事
上一篇寫完了之后，馬上就有朋友留言對記錄行的 8060byte 限制的疑惑，因為他的表記錄存盤了大量的文章，存盤文章的欄位型別用的是 nvarchar(max)，長度很顯然是超過 8060byte 的，請問這個底層是怎么破掉 8060byte 的限制的?
說實話這是一個好問題，本質上來說 8060byte 的限制肯定是不能破掉的，如果讓我處理的話肯定是將文章的資料分攤在多個資料頁上, 那是不是如我所想呢？ 我們觀察一下就好，
二：觀察大欄位資料的布局
1. 對 nvarchar(max) 的理解
玩過 sqlserver 的朋友都知道，新一代的 sqlserver 版本已經用 varchar(max) 和 nvarchar(max) 替代了早期的 text 和 ntext，理論上這種型別最大可存盤 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
按照這個理論繼續往前看記憶體地址，你會發現 00000001000001c9 和 00000001000001ca，對應著 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 次，總的來說還是盡量不要將大欄位存放在資料庫吧，
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/541055.html
標籤：SQL Server