T-SQL查詢進階—SQL Server索引中的片段和填滿因數

    寫在前面:本篇文章需要你對索引和SQL中資料的儲存方式有一定瞭解.標題中進階兩個字僅僅是因為本篇文章需要我的T-SQL進階系列文章的一些內容作為基礎.

簡介

    在SQL Server中，儲存資料的最小單位是頁，每一頁所能容納的資料為8060位元組.而頁的組織方式是通過B樹結構（表上沒有叢集索引則為堆結構，不在本文討論之列)如:

   

    在叢集索引B樹中，只有葉子節點實際儲存資料，而其他根節點和中間節點僅僅用於存放尋找葉子節點的資料.

    每一個葉子節點為一頁，每頁是不可分割的. 而SQL Server向每個頁記憶體儲資料的最小單位是表的行(Row).當葉子節點中新插入的行或更新的行使得葉子節點無法容納當前更新或者插入的行時，分頁就產生了.在分頁的過程中，就會產生片段.

 

 

理解外部片段

    首先，理解外部片段的這個“外”是相對頁面來說的。外部片段指的是由於分頁而產生的片段.比如，我想在現有的叢集索引中插入一行，這行正好導致現有的頁空間無法滿足容納新的行。從而導致了分頁:

  

     因為在SQL SERVER中，新的頁是隨著資料的增長不斷產生的，而叢集索引要求行之間連續，所以很多情況下分頁後和原來的頁在磁碟上並不連續.

     這就是所謂的外部片段.

     由於分頁會導致資料在頁之間的移動，所以如果插入更新等操作經常需要導致分頁，則會大大提升IO消耗，造成效能下降.

     而對於尋找來說，在有特定搜尋條件，比如where子句有很細的限制或者返回無序結果集時，外部片段並不會對效能產生影響。但如果要返回掃描叢集索引而尋找連續頁面時,外部片段就會產生效能上的影響.

     在SQL Server中，比頁更大的單位是區(Extent).一個區可以容納8個頁.區作為磁碟分配的物理單元.所以當頁分割如果跨區後，需要多次切區。需要更多的掃描.因為讀取連續資料時會不能預讀，從而造成額外的物理讀，增加磁碟IO.

 

理解內部片段

    和外部片段一樣，內部片段的”內”也是相對頁來說的.下面我們來看一個例子:

   

    我們建立一個表，這個表每個行由int(4位元組),char(999位元組)和varchar(0位元組組成），所以每行為1003個位元組,則8行佔用空間1003*8=8024位元組加上一些內部開銷，可以容納在一個頁面中:

   

    當我們隨意更新某行中的col3欄位後，造成頁內無法容納下新的資料，從而造成分頁:

   

   分頁後的:

  

    而當分頁時如果新的頁和當前頁物理上不連續，則還會造成外部片段

內部片段和外部片段對於查詢效能的影響

    外部片段對於效能的影響上面說過，主要是在於需要進行更多的跨區掃描，從而造成更多的IO操作.

    而內部片段會造成資料行分布在更多的頁中，從而加重了掃描的頁樹，也會降低查詢效能.

    下面通過一個例子看一下,我們人為的為剛才那個表插入一些資料造成內部片段:

   

    通過查看片段，我們發現這時片段已經達到了一個比較高的程度:

   

    通過查看對磁碟重組之前和之後的IO，我們可以看出，IO大大下降了:

   

 

對於片段的解決辦法

    基本上所有解決辦法都是基於對索引的重建和整理，只是方式不同

    1.刪除索引並重建

       這種方式並不好.在刪除索引期間，索引不可用.會導致阻塞發生。而對於刪除叢集索引，則會導致對應的非叢集索引重建兩次(刪除時重建，建立時再重建).雖然這種方法並不好，但是對於索引的整理最為有效

    2.使用DROP_EXISTING語句重建索引

       為了避免重建兩次索引，使用DROP_EXISTING語句重建索引，因為這個語句是原子性的，不會導致非叢集索引重建兩次，但同樣的，這種方式也會造成阻塞

    3.如前面文章所示，使用ALTER INDEX REBUILD語句重建索引

       使用這個語句同樣也是重建索引，但是通過動態重建索引而不需要卸載並重建索引.是優於前兩種方法的，但依舊會造成阻塞。可以通過ONLINE關鍵字減少鎖，但會造成重建時間加長.

    4.使用ALTER INDEX REORGANIZE

       這種方式不會重建索引，也不會產生新的頁，僅僅是整理，當遇到加鎖的頁時跳過，所以不會造成阻塞。但同時，整理效果會差於前三種.

 

理解填滿因數

      重建索引固然可以解決片段的問題.但是重建索引的代價不僅僅是麻煩，還會造成阻塞。影響使用.而對於資料比較少的情況下，重建索引代價並不大。而當索引本身超過百兆的時候。重建索引的時間將會很讓人蛋疼.

      填滿因數的作用正是如此。對於預設值來說，填滿因數為0（0和100表示的是一個概念）,則表示頁面可以100%使用。所以會遇到前面update或insert時，空間不足導致分頁.通過設定填滿因數，可以設定頁面的使用程度:

    

      下面來看一個例子:

      還是上面那個表.我插入31條資料，則佔4頁:

     

     通過設定填滿因數，頁被設定到了5頁上:

    

     這時我再插入一頁，不會造成分頁:

    

     上面的概念可以如來解釋:

    

      可以看出，使用填滿因數會減少更新或者插入時的分頁次數，但由於需要更多的頁，則會對應的損失尋找效能.

     

如何設定填滿因數的值

    如何設定填滿因數的值並沒有一個公式或者理念可以準確的設定。使用填滿因數雖然可以減少更新或者插入時的分頁，但同時因為需要更多的頁，所以降低了查詢的效能和佔用更多的磁碟空間.如何設定這個值進行trade-off需要根據具體的情況來看.

    具體情況要根據對於表的讀寫比例來看,我這裡給出我認為比較合適的值:

    1.當讀寫比例大於100：1時，不要設定填滿因數，100%填充

    2.當寫的次數大於讀的次數時，設定50%-70%填充

    3.當讀寫比例位於兩者之間時80%-90%填充

    上面的資料僅僅是我的看法，具體設定的資料還要根據具體情況進行測試才能找到最優.

 

總結

     本文講述了SQL SERVER中片段產生的原理，內部片段和外部片段的概念。以及解決片段的辦法和填滿因數.在資料庫中，往往每一個對於某一方面效能增加的功能也會伴隨著另一方面效能的減弱。系統的學習資料庫知識，從而根據具體情況進行權衡，是dba和開發人員的必修課.