SQL Server 索引基礎知識(1)— 記錄資料的基本格式

SQL Server 索引基礎知識(1)--- 記錄資料的基本格式 [來自]http://blog.joycode.com/ghj/archive/2008/01/02/113290.aspx

由於需要給同事培訓資料庫的索引知識，就收集整理了這個系列的部落格。發表在這裡，也是對索引知識的一個總結回顧吧。通過總結，我發現自己以前很多很模糊的概念都清晰了很多。

不論是緩衝的資料資訊，還是物理儲存的資訊，他們的基本單位都是資料頁。所以理解資料頁是最最基礎的知識點，本篇部落格就介紹跟索引有關的資料頁的一些基礎知識。

資料頁的基礎知識

SQL Server 中資料存放區的基本單位是頁（Page）。資料庫中的資料檔案（.mdf 或 .ndf）分配的磁碟空間可以從邏輯上劃分成頁（從 0 到 n 連續編號）。磁碟 I/O 操作在頁級執行。也就是說，SQL Server 每次讀取或寫入資料的最少資料單位是資料頁。

注意：記錄檔不是用這種方式儲存的，而是一系列日誌記錄。

資料庫被分成邏輯頁面（每個頁面8KB），並且在每個檔案中，所有頁面都被連續地從0到x編號，其中x是由檔案的大小決定的。我們可以通過指定一個資料庫ID、一個檔案ID、一個頁碼來引用任何一個資料頁。當我們使用ALTER DATABASE命令來擴大一個檔案時，新的空間會被加到檔案的末尾。也就是說，我們所擴大檔案的新空間第一個資料頁的頁碼是x+1。當我們使用DBCC SHRINKDATABASE或DBCC SHRINKFILE命令來收縮一個資料庫時，將會從資料庫中頁碼最高的頁面（檔案末尾）開始移除頁面，並向頁碼較低的頁面移動。這保證了一個檔案中的頁碼總是連續的。

在 SQL Server 中，頁的大小為 8 KB。這意味著 SQL Server 資料庫中每 MB 有 128 頁。依次類推。根據資料庫的檔案大小，我們可以算出資料庫有多少資料頁。

SQL Server 2005 有以下幾種頁類型：

頁類型	內容
Data	當 text in row 設定為 ON 時，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 資料之外的所有資料的資料行。
Index	索引條目。
Text/Image	大型物件資料類型： text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 資料。 資料行超過 8 KB 時為可變長度資料類型列： varchar、nvarchar、varbinary 和 sql_variant
Global Allocation Map、Shared Global Allocation Map	有關區是否分配的資訊。
Page Free Space	有關頁分配和頁的可用空間的資訊。
Index Allocation Map	有關每個配置單位中表或索引所使用的區的資訊。
Bulk Changed Map	有關每個配置單位中自最後一條 BACKUP LOG 語句之後的大容量操作所修改的區的資訊。
Differential Changed Map	有關每個配置單位中自最後一條 BACKUP DATABASE 語句之後更改的區的資訊。

資料頁（Data 類型頁）的結構：

每頁的開頭是 96 位元組的標題，用於儲存有關頁的系統資訊。此資訊包括頁碼、頁類型、頁的可用空間以及擁有該頁的對象的配置單位 ID。

在資料頁上，資料行緊接著標題按順序放置。頁的末尾是行位移表，對於頁中的每一行，每個行位移表都包含一個條目。每個條目記錄對應行的第一個位元組與頁首的距離。行位移表中的條目的順序與頁中行的順序相反。

有關資料頁的更多知識，可以通過下面這篇文章獲得更詳細的瞭解：

估計在堆中儲存資料所需的空間量
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

另外也可以看我收集的資料：怎樣查看錶的資料頁的結構
http://blog.joycode.com/ghj/articles/113108.aspx

對大型行的支援

在 SQL Server 2005 中，行不能跨頁，但是行的部分可以移出行所在的頁，因此行實際可能非常大。
（比如：一行多列時，這一行的部分列在資料頁A，部分列在資料頁B）
頁的單個行中的最大資料量和開銷是 8,060 位元組 (8 KB)。但是，這不包括用 Text/Image 頁類型儲存的資料。
在 SQL Server 2005 中，包含 varchar、nvarchar、varbinary 或 sql_variant 列的表不受此限制的約束。
當表中的所有固定列和可變列的行的總大小超過限制的 8,060 位元組時，SQL Server 將從最大長度的列開始動態將一個或多個可變長度列移動到 ROW_OVERFLOW_DATA 配置單位中的頁。
每當插入或更新操作將行的總大小增大到超過限制的 8,060 位元組時，將會執行此操作。
將列移動到 ROW_OVERFLOW_DATA 配置單位中的頁後，將在 IN_ROW_DATA 配置單位中的原始頁上維護 24 位元組的指標。
如果後續操作減小了行的大小，SQL Server 會動態將列移回到未經處理資料頁。

 

SQL Server 的資料頁緩衝

SQL Server 資料庫的主要用途是儲存和檢索資料，因此密集型磁碟 I/O 是資料庫引擎的一大特點。此外，完成磁碟 I/O 操作要消耗許多資源並且耗時較長，所以 SQL Server 側重於提高 I/O 效率。緩衝區管理是實現高效 I/O 操作的關鍵環節。SQL Server 2005 的緩衝區管理組件由下列兩種機制組成：用於訪問及更新資料庫頁的緩衝區管理器和用於減少資料庫檔案 I/O 的緩衝區快取（又稱為“緩衝池”）。

 

緩衝區管理的工作原理
一個緩衝區就是一個 8KB 大小的記憶體頁，其大小與一個資料頁或索引頁相當。因此，緩衝區快取被劃分為多個 8KB 頁。緩衝區管理器負責將資料頁或索引頁從資料庫磁碟檔案讀入緩衝區快取中，並將修改後的頁寫回磁碟。頁一直保留在緩衝區快取中，直到已有一段時間未對其進行引用或者緩衝區管理器需要緩衝區讀取更多資料。資料只有在被修改後才重新寫入磁碟。在將緩衝區快取中的資料寫回磁碟之前，可對其進行多次修改。

實驗

下面做一個簡單的實驗來看你是否已經掌握的上面的知識點：

準備測試環境

在一個SQL 2005資料庫中，執行下面指令碼。

簡單來說，就是建立了2個表，注意這兩個表，一個是儲存的 nchar(2019) 的欄位，一個是儲存的 nchar(2020) 的欄位。 我們將來看這兩個表在同樣資料下，儲存所花費的空間大小。由於緩衝和實體儲存體的基本單位都是資料頁，這個表實體儲存體的大小跟全部緩衝的大小會是一樣的。

然後我們每個表填充20個資料。

-- 建立2個測試表CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL)CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL)go-- 填充資料declare @i intset @i = 0while(@i < 20)begininsert Table_2019(Data) values('')insert Table_2020(Data) values('')select @i = @i + 1endgo 

這裡我們用 nchar 資料類型，是因為：
當指定了 NOT NULL 子句時，nchar 資料類型是一種長度固定的資料類型。
如果插入值的長度比 nchar NOT NULL 列的長度小，將在值的右邊填補空格直到達到列的長度。
例如，如果某列定義為 nchar(10)，而要儲存的資料是“music”，則 SQL Server 將資料存放區為“music_____”，這裡“_”表示空格。
http://technet.microsoft.com/zh-cn/library/ms175055.aspx
這樣我們填充測試資料的指令碼就非常簡單。
而且計算資料行所佔的空間也非常簡單。
另外，我們建立的這兩個表都沒有索引，所以他們都是堆，有關估計在堆中儲存資料所需的空間量請參看以下文章：
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

完成準備工作後，我們來查看這兩個所佔空間的大小。在 SQL Server Management Studio 中，我們選擇測試資料庫， 然後在右鍵菜單中依次選擇 
Reports --> Standard Reports --> Disk Usage by Top Tables 或者Disk Usage by Table 就可以看到下面統計資料。 

Disk Usage by Top Tables: [ghj_Demo]
on GHJ1976-PC\SQLEXPRESS at 2007/12/27 9:21:33
This report provides detailed data on the utilization of disk space by top 1000 tables within the Database.
Table Name	# Records	Reserved (KB)	Data (KB)	Indexes (KB)	Unused (KB)
dbo.Table_2020	20	200	160	8	32
dbo.Table_2019	20	136	80	8	48

 

這兩個表同樣20條記錄。Table_2020 表資料佔了 160kb ，即 20 個資料頁。Table_2019 表資料佔了 80 kb，即 10 個資料頁。
為何會這樣呢？
Table_2020  表的1個資料頁只能放下1個資料行。
Table_2019  表的1個資料頁只能放下2個資料行。
這兩個表的欄位長度只差2個位元組，但是實體儲存體卻是一倍的差距。

 

參考資料：

SQL Server資料庫中儲存引擎深入探討
http://tech.ccidnet.com/art/1106/20070320/1040665_3.html

《Microsoft SQL Server 2005技術內幕：儲存引擎》 這本書電子版的一部分
http://book.csdn.net/bookfiles/504/10050417350.shtml

MSDN 中關於“頁和區”的描述
http://technet.microsoft.com/zh-cn/library/ms190969.aspx 

叢集索引結構
http://technet.microsoft.com/zh-cn/library/ms177443.aspx

資料列溢位資料超過 8 KB
http://technet.microsoft.com/zh-cn/library/ms186981.aspx

緩衝區管理
http://technet.microsoft.com/zh-cn/library/aa337525.aspx

估計堆的大小
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

nchar 和 nvarchar (Transact-SQL)
http://technet.microsoft.com/zh-cn/library/ms186939.aspx

Teched 2007 上 吳家震 主講的"微軟SQL伺服器Always-On Tech-nologies: 進階索引策略"  錄影:
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032364059&Culture=zh-CN
注意, 這個頁面標示的是 "SharePoint 2007 網站效能調優" ,但是其實是進階索引策略,微軟弄錯檔案了,害得我一個個下下來看,哪個是需要的錄影.