SQL Server中的偽列

下午看QQ群有人在討論（非聚集）索引的存儲，說，對于聚集索引表，非聚集索引存儲的是索引鍵值+聚集索引鍵值；對于非聚集索引表，索引存儲的是索引鍵值+RowId，這應該是一個常識，對此不作具體詳細闡述。

這里主要是提到的RowId引起了一點思考。

那么，這個RowId是個什么玩意？能不能更加直觀一點來看看RowId的信息？代表什么含義？這個當然也是可以的。

Oracle中的表中有一個偽列的概念，就是在查詢表的時候加上select rowid，* from Table，會查詢出來偽列。

SQL Server中同樣有這么一個偽列，在SQL Server中，這個偽列可以認為是數(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的存儲只是有一個基本的認識，否則看的云里霧里。

1，首先說什么是文件號

如截圖，文件號就是數(shù)據(jù)庫的數(shù)據(jù)文件編號，這里只有一個數(shù)據(jù)文件，文件編號為1，建表的時候默認（這里也只能建立）建立在fileid = 1 的文件上面，fileid=2的是日志文件，就不多說了。

2，其次是頁號，頁號就是分配給當前這張表的數(shù)據(jù)頁面（8kb的最小分配單元）的頁號，我們看一下Test這個表的頁面情況

借助DBCC IND命令，查詢分配給這個表的頁面信息，其中77號頁面是IMA也面，至于什么事IMA頁面，不多解釋。

73號頁面才是真正存儲數(shù)據(jù)的頁，與上面的1:73:0中的73一樣，沒毛病。

3，最后看一下槽號，槽號的概念要對SQL Server的數(shù)據(jù)頁面有一個基本的認識，這里盜用一張網(wǎng)友的圖。

所謂的槽號就是在數(shù)據(jù)頁面中，每個頁面存儲多行數(shù)據(jù)，槽號用來標記每一行數(shù)據(jù)的偏移量，用大白話說就是“存儲每一行數(shù)據(jù)的地址空間開始的位置”，因為每一行數(shù)據(jù)的總長度是不一樣的（存在可變長度列的情況下），每一行的占用的存儲空間也是不一樣的，槽號或者行偏移量就是說明每一行數(shù)據(jù)在頁內(nèi)的開始位置。

不過sys.fn_PhysLocFormatter格式化顯式的槽號并不是如下截圖的偏移量，而是第N個數(shù)據(jù)行的這個N的信息，因此第1行的槽號就是1，第2行的槽號就是2，以此類推，當?shù)谝粋€page存儲滿之后，從第二個page開始存儲，槽號又從0開始編號且累加

至此，對SQL Server的偽列，也就說經(jīng)常說的RowId有了一個簡單的認識。

這里可以認為，在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ā)生變化，這個變化當然要耗費一定的性能，為了防止此種情況的發(fā)生，聚集表中的非聚集索引存儲成相對不變的索引鍵值+聚集索引鍵值，因為在數(shù)據(jù)行的物理位置發(fā)生變化的時候，聚集索引鍵值是相對不變的，這一點也不難理解。

當然有一種例外，當對聚集索引表做更新的時候，直接更新聚集索引的鍵值，這樣的話，也有可能造成聚集索引表中當前數(shù)據(jù)行的物理位置發(fā)生變化，這一點也比較有意思，就不展開敘述了。

這一點跟繞口令一樣，這里要求對SQL Server中的聚集索引和非聚集索引，以及存儲結(jié)構(gòu)有一個基礎的認識才容易理解。

最后高能預警

高能預警，別說我瞎比比誤導人，上述解析偽列的函數(shù)sys.fn_PhysLocFormatter是一個未公開的函數(shù)，未公開的函數(shù)就有可能潛在一些問題，事實上這個函數(shù)有一個非常嚴重的bug。

該bug就是在解析物理存儲位置的時候有一定的邏輯錯誤，這個問題早有細心的人分析過了

參考：http://www.dbjr.com.cn/article/124109.htm

目前測試來看，在SQL Server 2014中仍然存在bug，N前年啃書的時候就了解到有這么一個函數(shù)，但是一直不想提及sys.fn_PhysLocFormatter這個函數(shù)的原因，因此對于未公開的函數(shù)，請不要做驗證性測試，再次聲明：該函數(shù)有bug，請謹慎使用。

附上這個函數(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)，當遇到81-FE之間的字節(jié)時，被認為是雙字節(jié)字符而組合在一起參與反轉(zhuǎn)操作，造成了錯誤。

總結(jié)

本文簡單闡述了SQL Server中的偽列，以及偽列的含義，通過偽列對非聚集索引以及數(shù)據(jù)行的存儲結(jié)構(gòu)有一個簡單的了解。

好了，以上就是這篇文章的全部內(nèi)容了，希望本文的內(nèi)容對大家的學習或者工作能帶來一定的幫助，如果有疑問大家可以留言交流，謝謝大家對腳本之家的支持。

最新評論