由于需要給同事培訓數(shù)據(jù)庫的索引知識，就收集整理了這個系列的博客。發(fā)表在這里，也是對索引知識的一個總結(jié)回顧吧。通過總結(jié)，我發(fā)現(xiàn)自己以前很多很模糊的概念都清晰了很多。

不論是緩存的數(shù)據(jù)信息，還是物理保存的信息，他們的基本單位都是數(shù)據(jù)頁。所以理解數(shù)據(jù)頁是最最基礎(chǔ)的知識點，本篇博客就介紹跟索引有關(guān)的數(shù)據(jù)頁的一些基礎(chǔ)知識。

數(shù)據(jù)頁的基礎(chǔ)知識

SQL Server 中數(shù)據(jù)存儲的基本單位是頁（Page）。數(shù)據(jù)庫中的數(shù)據(jù)文件（.mdf 或 .ndf）分配的磁盤空間可以從邏輯上劃分成頁（從 0 到 n 連續(xù)編號）。磁盤 I/O 操作在頁級執(zhí)行。也就是說，SQL Server 每次讀取或?qū)懭霐?shù)據(jù)的最少數(shù)據(jù)單位是數(shù)據(jù)頁。

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

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

在 SQL Server 中，頁的大小為 8 KB。這意味著 SQL Server 數(shù)據(jù)庫中每 MB 有 128 頁。依次類推。根據(jù)數(shù)據(jù)庫的文件大小，我們可以算出數(shù)據(jù)庫有多少數(shù)據(jù)頁。

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

頁類型	內(nèi)容
Data	當 text in row 設(shè)置為 ON 時，包含除 text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 數(shù)據(jù)之外的所有數(shù)據(jù)的數(shù)據(jù)行。
Index	索引條目。
Text/Image	大型對象數(shù)據(jù)類型： text、 ntext、image、nvarchar(max)、varchar(max)、varbinary(max) 和 xml 數(shù)據(jù)。 數(shù)據(jù)行超過 8 KB 時為可變長度數(shù)據(jù)類型列： varchar、nvarchar、varbinary 和 sql_variant
Global Allocation Map、Shared Global Allocation Map	有關(guān)區(qū)是否分配的信息。
Page Free Space	有關(guān)頁分配和頁的可用空間的信息。
Index Allocation Map	有關(guān)每個分配單元中表或索引所使用的區(qū)的信息。
Bulk Changed Map	有關(guān)每個分配單元中自最后一條 BACKUP LOG 語句之后的大容量操作所修改的區(qū)的信息。
Differential Changed Map	有關(guān)每個分配單元中自最后一條 BACKUP DATABASE 語句之后更改的區(qū)的信息。

數(shù)據(jù)頁（Data 類型頁）的結(jié)構(gòu)示意圖：

每頁的開頭是 96 字節(jié)的標頭，用于存儲有關(guān)頁的系統(tǒng)信息。此信息包括頁碼、頁類型、頁的可用空間以及擁有該頁的對象的分配單元 ID。

在數(shù)據(jù)頁上，數(shù)據(jù)行緊接著標頭按順序放置。頁的末尾是行偏移表，對于頁中的每一行，每個行偏移表都包含一個條目。每個條目記錄對應行的第一個字節(jié)與頁首的距離。行偏移表中的條目的順序與頁中行的順序相反。

有關(guān)數(shù)據(jù)頁的更多知識，可以通過下面這篇文章獲得更詳細的了解：

估計在堆中存儲數(shù)據(jù)所需的空間量
http://technet.microsoft.com/zh-cn/library/ms189124.aspx

另外也可以看我收集的資料：怎樣查看表的數(shù)據(jù)頁的結(jié)構(gòu)
http://blog.joycode.com/ghj/articles/113108.aspx

對大型行的支持

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

SQL Server 的數(shù)據(jù)頁緩存

SQL Server 數(shù)據(jù)庫的主要用途是存儲和檢索數(shù)據(jù)，因此密集型磁盤 I/O 是數(shù)據(jù)庫引擎的一大特點。此外，完成磁盤 I/O 操作要消耗許多資源并且耗時較長，所以 SQL Server 側(cè)重于提高 I/O 效率。緩沖區(qū)管理是實現(xiàn)高效 I/O 操作的關(guān)鍵環(huán)節(jié)。SQL Server 2005 的緩沖區(qū)管理組件由下列兩種機制組成：用于訪問及更新數(shù)據(jù)庫頁的緩沖區(qū)管理器和用于減少數(shù)據(jù)庫文件 I/O 的緩沖區(qū)高速緩存（又稱為“緩沖池”）。

 

緩沖區(qū)管理的工作原理
一個緩沖區(qū)就是一個 8KB 大小的內(nèi)存頁，其大小與一個數(shù)據(jù)頁或索引頁相當。因此，緩沖區(qū)高速緩存被劃分為多個 8KB 頁。緩沖區(qū)管理器負責將數(shù)據(jù)頁或索引頁從數(shù)據(jù)庫磁盤文件讀入緩沖區(qū)高速緩存中，并將修改后的頁寫回磁盤。頁一直保留在緩沖區(qū)高速緩存中，直到已有一段時間未對其進行引用或者緩沖區(qū)管理器需要緩沖區(qū)讀取更多數(shù)據(jù)。數(shù)據(jù)只有在被修改后才重新寫入磁盤。在將緩沖區(qū)高速緩存中的數(shù)據(jù)寫回磁盤之前，可對其進行多次修改。

實驗

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

準備測試環(huán)境

在一個SQL 2005數(shù)據(jù)庫中，執(zhí)行下面腳本。

簡單來說，就是創(chuàng)建了2個表，注意這兩個表，一個是存儲的 nchar(2019) 的字段，一個是存儲的 nchar(2020) 的字段。 我們將來看這兩個表在同樣數(shù)據(jù)下，存儲所花費的空間大小。由于緩存和物理存儲的基本單位都是數(shù)據(jù)頁，這個表物理存儲的大小跟全部緩存的大小會是一樣的。

然后我們每個表填充20個數(shù)據(jù)。

-- 創(chuàng)建2個測試表
CREATE TABLE [dbo].[Table_2019]([Data] [nchar](2019) NOT NULL) 
CREATE TABLE [dbo].[Table_2020]([Data] [nchar](2020) NOT NULL) 
go
-- 填充數(shù)據(jù)
declare @i int
set @i = 0
while(@i < 20)
begin
    insert Table_2019(Data) values('')
    insert Table_2020(Data) values('')
    select @i = @i + 1
end
go 

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

完成準備工作后，我們來查看這兩個所占空間的大小。在 SQL Server Management Studio 中，我們選擇測試數(shù)據(jù)庫， 然后在右鍵菜單中依次選擇 
Reports --> Standard Reports --> Disk Usage by Top Tables 或者Disk Usage by Table 就可以看到下面統(tǒng)計數(shù)據(jù)。 

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 表數(shù)據(jù)占了 160kb ，即 20 個數(shù)據(jù)頁。Table_2019 表數(shù)據(jù)占了 80 kb，即 10 個數(shù)據(jù)頁。
為何會這樣呢？
Table_2020 表的1個數(shù)據(jù)頁只能放下1個數(shù)據(jù)行。
Table_2019 表的1個數(shù)據(jù)頁只能放下2個數(shù)據(jù)行。
這兩個表的字段長度只差2個字節(jié)，但是物理存儲卻是一倍的差距。

參考資料：

SQL Server數(shù)據(jù)庫中存儲引擎深入探討
http://tech.ccidnet.com/art/1106/20070320/1040665_3.html

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

MSDN 中關(guān)于“頁和區(qū)”的描述
http://technet.microsoft.com/zh-cn/library/ms190969.aspx

聚集索引結(jié)構(gòu)
http://technet.microsoft.com/zh-cn/library/ms177443.aspx

行溢出數(shù)據(jù)超過 8 KB
http://technet.microsoft.com/zh-cn/library/ms186981.aspx

緩沖區(qū)管理
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 網(wǎng)站性能調(diào)優(yōu)" ,但是其實是高級索引策略,微軟弄錯文件了,害得我一個個下下來看,哪個是需要的錄像.

來源：http://blog.joycode.com/ghj/