SQL server 容易讓人誤解的問題之 聚集表的物理順序問題

      對於MS SQL server 資料庫，有幾個容易讓人產生誤解的問題，對於這幾個問題，即使很多 SQL server DBA 都有錯誤認識或者認識不充分，所以我想撰文幾篇，把這些容易理解錯誤的問題前前後後深入闡述一下,也希望糾正一下網上對這幾個問題的訛傳(我也可能有認識不對的地方，歡迎批評指正)。

      第一個問題：聚集表的物理順序問題。這個問題很有迷惑性，因為很多教科書在講到叢集索引的時候都會出現“叢集索引是按照聚集鍵的排序次序物理地儲存資料” 類似的說法，因此我們很容易產生以下幾種誤解：

      誤解一：“按順序物理地儲存”就是磁碟本身；

      誤解二：聚集表的頁在物理上是順序的；

      誤解三：聚集表的頁在物理上是順序的，並且是連續的。

 

     下面我們分別說明上面三種觀點確實是誤解。

     對於誤解一，我們必須要瞭解資料行如何被儲存在資料頁上。

              

是我從MSDN上截來的，從可以看到，在每個資料頁的末尾，都有一個“行位移量”的數組，這個數組記錄了每一個資料行的開頭在頁面中的起始位置，即每行資料開頭應該從頁頭位移多少個位元組。我將改了一下，可能更便於正確理解：

           

我改編的這個圖中表示，從這個資料頁頭位移96個位元組即是第一行資料的開始位置，位移200位元組即是第二行資料的開始位置，位移300個位元組即是第三行資料的開始位置。

 

下面我們舉個實際的例子來說明聚集表的資料在一個資料頁上可能的樣子。

CREATE TABLE test(   RowId   int not null primary key ,   Column1 char(100)) INSERT INTO test(RowId,Column1)Select 1, ' 'UnionSelect 2,' 'UnionSelect 10,' '

對於上表，資料第一次插入時，這三行資料在資料頁上的表現如下：

       

而如若我再往裡面插入如下資料：

INSERT INTO test(RowId,Column1)Select 7, ' '

因為這個表的RowId 列有叢集索引（primary key 預設建立叢集索引），而數字7大約2，且小於10，那麼SQL server 在執行INSERT 操作時，是不是會把RowId = 10 這行資料往下挪呢？顯然SQL server 不會這麼笨，而只會將RowId = 7這行資料資料附加到RowId = 10 這行資料的後面，然後再修改行位移量數組，如下：

          

      現在我們應該可以明白：一個叢集索引表資料頁上資料行的物理順序，僅依靠行位移列表來決定，並不取決於在磁碟上的物理位置。

 

     對於誤解二和誤解三可以放在一起論述，在論述之前，我們需要先瞭解一下SQL server 儲存引擎中頁跟區的概念。
         1. SQL server 中資料存放區的基本單位是頁。
         2. 區是八個邏輯上連續的頁的集合，用來有效管理頁，這也說明，所有的資料頁一定屬於某個區。
         3. 區分為混合區和統一區。混合區中的頁可以被分配給多個資料庫物件；統一區中的頁一定是被分配給了某一個資料庫物件。
         4. SQL server 在為某個資料庫物件申請空間時，需要使用GAM,SGAM,PFS 系統頁的資訊，同時在空間被分配後，也會維護好GAM,SGAM,PFS系統頁的息                      。

         上面我簡單列出了幾個要點，更多詳細資料大家可以參考下面的連結資訊：
         http://msdn.microsoft.com/zh-cn/library/cc280360(v=sql.100).aspx 

         然後我將列舉具體的情況來證明二跟三確實是誤解：
         1. 當我們建立一個聚集表，並且往表裡插資料，當這個表所佔用空間不夠8個資料頁時，SQL server儲存引擎都將從混合區尋找閒置頁面分配給表。而在這個階段內，很容易出現一個混合區被同時分配給多個資料庫物件（最多可達8個資料庫物件）。那麼，如果我們的聚集表需要再次申請磁碟空間，就很可能在起初的混合區內分配到不連續的頁(相對於已指派給這個聚集表的頁來說)，或者要從另外一個混合區尋找空閑頁面，這樣，是無論如何也保證不了“聚集表的頁在物理上是順序的”，更保證不了“頁是連續的”。

         2. 如果一個聚集表滿8個頁，那麼，後續所有的空間申請都將分配完整的統一區，這後續的統一區是否可以順序，或者連續呢？ 當然不行，當一個申請空間的動作發出後，儲存引擎都會從表所在資料檔案的第一個GAM去遍曆尋找閒置統一區，如果某個統一區在之前已經分配給某個其它的資料庫物件，但當前這個區已經被釋放，那麼，SQL server 就會將這個閒置區分配給我們的聚集表，這樣也自然保證不了頁在物理上的“順序和連續”；另外，即使SQL server每次都分配從來沒有分配給任何資料庫物件的區給我們的聚集表，也沒有辦法保證頁在物理上的“順序和連續”。

         3. 如果聚集表出現分頁的情況，那麼新申請頁的頁面鏈的“上一頁”會指向被分割的資料頁，“下一頁”會指向被分割頁在分割前指向的下一頁。顯然，這種情況也沒有辦法保證頁在物理上的“順序和連續”，而且它是索引片段情況中的一種。

 

結論：對於叢集索引表資料行的物理順序問題，在頁與頁的角度來看，唯一能指明叢集索引表資料頁順序的是資料頁上的頁面鏈表。因為頁面鏈表清楚的指明了本頁的上一頁，及下一頁的頁面號分別是啥，而頁面號就決定了上一頁及下一頁的物理位置；如果將眼光縮小到一個資料頁的範圍內，決定資料行物理位置的因素只有頁尾的行位移量數數組。