SQL Server中的偽列
下午看QQ群有人在討論(非聚集)索引的存儲,說,對于聚集索引表,非聚集索引存儲的是索引鍵值+聚集索引鍵值;對于非聚集索引表,索引存儲的是索引鍵值+RowId,這應(yīng)該是一個常識,對此不作具體詳細(xì)闡述。
這里主要是提到的RowId引起了一點思考。
那么,這個RowId是個什么玩意?能不能更加直觀一點來看看RowId的信息?代表什么含義?這個當(dāng)然也是可以的。
Oracle中的表中有一個偽列的概念,就是在查詢表的時候加上select rowid,* from Table
,會查詢出來偽列。
SQL Server中同樣有這么一個偽列,在SQL Server中,這個偽列可以認(rèn)為是數(shù)據(jù)行的物理地址,下面簡單來觀察一下這個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個字節(jié),參考上圖的DATALENGTH(%%physloc%%) as Len
,%%physloc%%返回的記錄的物理地址,其中前四個字節(jié)表示頁號,中間兩個字節(jié)表示文件號,最后兩個字節(jié)表示槽號
為了更加方便地觀察偽列的含義,sqlserver提供了一個未公開的系統(tǒng)函數(shù)sys.fn_PhysLocFormatter,下面借助sys.fn_PhysLocFormatter
這個函數(shù)來繼續(xù)觀察這個偽列
如下圖,這里就可以清晰地看到偽列中的信息了。
比如第一行中的(1:73:0),上面說了,其中前四個字節(jié)表示頁號,中間兩個字節(jié)表示文件號,最后兩個字節(jié)表示槽號,(1:73:0)這種格式是經(jīng)過sys.fn_PhysLocFormatter
格式化顯式之后的結(jié)果。
把文件號1放在最前面,中間的73是頁號(page number),最后一位0是槽號(sloc number)。
下面粗略地說一下這幾個字段的含義。這里要求對SQL Server的存儲只是有一個基本的認(rèn)識,否則看的云里霧里。
1,首先說什么是文件號
如截圖,文件號就是數(shù)據(jù)庫的數(shù)據(jù)文件編號,這里只有一個數(shù)據(jù)文件,文件編號為1,建表的時候默認(rèn)(這里也只能建立)建立在fileid = 1 的文件上面,fileid=2的是日志文件,就不多說了。
2,其次是頁號,頁號就是分配給當(dāng)前這張表的數(shù)據(jù)頁面(8kb的最小分配單元)的頁號,我們看一下Test這個表的頁面情況
借助DBCC IND命令,查詢分配給這個表的頁面信息,其中77號頁面是IMA也面,至于什么事IMA頁面,不多解釋。
73號頁面才是真正存儲數(shù)據(jù)的頁,與上面的1:73:0中的73一樣,沒毛病。
3,最后看一下槽號,槽號的概念要對SQL Server的數(shù)據(jù)頁面有一個基本的認(rèn)識,這里盜用一張網(wǎng)友的圖。
所謂的槽號就是在數(shù)據(jù)頁面中,每個頁面存儲多行數(shù)據(jù),槽號用來標(biāo)記每一行數(shù)據(jù)的偏移量,用大白話說就是“存儲每一行數(shù)據(jù)的地址空間開始的位置”,因為每一行數(shù)據(jù)的總長度是不一樣的(存在可變長度列的情況下),每一行的占用的存儲空間也是不一樣的,槽號或者行偏移量就是說明每一行數(shù)據(jù)在頁內(nèi)的開始位置。
不過sys.fn_PhysLocFormatter
格式化顯式的槽號并不是如下截圖的偏移量,而是第N個數(shù)據(jù)行的這個N的信息,因此第1行的槽號就是1,第2行的槽號就是2,以此類推,當(dāng)?shù)谝粋€page存儲滿之后,從第二個page開始存儲,槽號又從0開始編號且累加
至此,對SQL Server的偽列,也就說經(jīng)常說的RowId有了一個簡單的認(rèn)識。
這里可以認(rèn)為,在SQL Server數(shù)據(jù)庫中,偽列RowId就是數(shù)據(jù)行的物理地址,至于別的數(shù)據(jù)庫中的偽列(RowId)是不是物理地址倒是不確定(很有可能也是的)
這里簡單提一下一開始說的一個問題:
為什么SQL Server的聚集表(有聚集索引的表)存儲數(shù)據(jù)的時候存儲的是“索引鍵值+聚集索引鍵值”,對于非聚集索引表,索引存儲的是索引鍵值+RowId?
或者反過來說,為什么聚集索引表的非聚集索引存儲的是“索引鍵值+聚集索引鍵值”而不是“索引存儲的是索引鍵值+RowId”
作為一個常識,聚集索引要按照聚集索引的順序存放,這就意味著聚集索引表的行數(shù)據(jù)物理位置有可能發(fā)生變化,比如在眾所周知的“頁拆分(page split)”中發(fā)生變化,在數(shù)據(jù)行的物理位置發(fā)生了變化的時候,如果非聚集索引存儲的是索引鍵值+RowId,那么這個RowId也勢必要發(fā)生變化,這個變化當(dāng)然要耗費一定的性能,為了防止此種情況的發(fā)生,聚集表中的非聚集索引存儲成相對不變的索引鍵值+聚集索引鍵值,因為在數(shù)據(jù)行的物理位置發(fā)生變化的時候,聚集索引鍵值是相對不變的,這一點也不難理解。
當(dāng)然有一種例外,當(dāng)對聚集索引表做更新的時候,直接更新聚集索引的鍵值,這樣的話,也有可能造成聚集索引表中當(dāng)前數(shù)據(jù)行的物理位置發(fā)生變化,這一點也比較有意思,就不展開敘述了。
這一點跟繞口令一樣,這里要求對SQL Server中的聚集索引和非聚集索引,以及存儲結(jié)構(gòu)有一個基礎(chǔ)的認(rèn)識才容易理解。
最后高能預(yù)警
高能預(yù)警,別說我瞎比比誤導(dǎo)人,上述解析偽列的函數(shù)sys.fn_PhysLocFormatter
是一個未公開的函數(shù),未公開的函數(shù)就有可能潛在一些問題,事實上這個函數(shù)有一個非常嚴(yán)重的bug。
該bug就是在解析物理存儲位置的時候有一定的邏輯錯誤,這個問題早有細(xì)心的人分析過了
參考:https://www.jb51.net/article/124109.htm
目前測試來看,在SQL Server 2014中仍然存在bug,N前年啃書的時候就了解到有這么一個函數(shù),但是一直不想提及sys.fn_PhysLocFormatter
這個函數(shù)的原因,因此對于未公開的函數(shù),請不要做驗證性測試,再次聲明:該函數(shù)有bug,請謹(jǐn)慎使用。
附上這個函數(shù)的源代碼,并參考原文的結(jié)論
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函數(shù)上。
reverse函數(shù)的作用是字符反轉(zhuǎn),而不是字節(jié)反轉(zhuǎn),當(dāng)遇到81-FE之間的字節(jié)時,被認(rèn)為是雙字節(jié)字符而組合在一起參與反轉(zhuǎn)操作,造成了錯誤。
總結(jié)
本文簡單闡述了SQL Server中的偽列,以及偽列的含義,通過偽列對非聚集索引以及數(shù)據(jù)行的存儲結(jié)構(gòu)有一個簡單的了解。
好了,以上就是這篇文章的全部內(nèi)容了,希望本文的內(nèi)容對大家的學(xué)習(xí)或者工作能帶來一定的幫助,如果有疑問大家可以留言交流,謝謝大家對腳本之家的支持。