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

不論是緩存的數(shù)據(jù)信息，還是物理保存的信息，他們的基本單位都是數(shù)據(jù)頁。所以理解數(shù)據(jù)頁是最最基礎(chǔ)的知識點(diǎn)，本篇博客就介紹跟索引有關(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ù)庫被分成邏輯頁面（每個(gè)頁面8KB），并且在每個(gè)文件中，所有頁面都被連續(xù)地從0到x編號，其中x是由文件的大小決定的。我們可以通過指定一個(gè)數(shù)據(jù)庫ID、一個(gè)文件ID、一個(gè)頁碼來引用任何一個(gè)數(shù)據(jù)頁。當(dāng)我們使用ALTER DATABASE命令來擴(kuò)大一個(gè)文件時(shí)，新的空間會(huì)被加到文件的末尾。也就是說，我們所擴(kuò)大文件的新空間第一個(gè)數(shù)據(jù)頁的頁碼是x+1。當(dāng)我們使用DBCC SHRINKDATABASE或DBCC SHRINKFILE命令來收縮一個(gè)數(shù)據(jù)庫時(shí)，將會(huì)從數(shù)據(jù)庫中頁碼最高的頁面（文件末尾）開始移除頁面，并向頁碼較低的頁面移動(dòng)。這保證了一個(gè)文件中的頁碼總是連續(xù)的。

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

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

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

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

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

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

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

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

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

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

實(shí)驗(yàn)

下面做一個(gè)簡單的實(shí)驗(yàn)來看你是否已經(jīng)掌握的上面的知識點(diǎn)：

準(zhǔn)備測試環(huán)境

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

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

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

-- 創(chuàng)建2個(gè)測試表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 intset @i = 0while(@i < 20)begin    insert Table_2019(Data) values('')    insert Table_2020(Data) values('')    select @i = @i + 1endgo 

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

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

這兩個(gè)表同樣20條記錄。Table_2020 表數(shù)據(jù)占了 160kb ，即 20 個(gè)數(shù)據(jù)頁。Table_2019 表數(shù)據(jù)占了 80 kb，即 10 個(gè)數(shù)據(jù)頁。
為何會(huì)這樣呢？
Table_2020 表的1個(gè)數(shù)據(jù)頁只能放下1個(gè)數(shù)據(jù)行。
Table_2019 表的1個(gè)數(shù)據(jù)頁只能放下2個(gè)數(shù)據(jù)行。
這兩個(gè)表的字段長度只差2個(gè)字節(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

估計(jì)堆的大小
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服務(wù)器Always-On Tech-nologies: 高級索引策略" 錄像下載地址:
http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032364059&Culture=zh-CN
注意, 這個(gè)頁面標(biāo)示的是 "SharePoint 2007 網(wǎng)站性能調(diào)優(yōu)" ,但是其實(shí)是高級索引策略,微軟弄錯(cuò)文件了,害得我一個(gè)個(gè)下下來看,哪個(gè)是需要的錄像.

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