《SQL Server企業級平台管理實踐》讀書筆記——SQL Server中資料檔案空間使用與管理

標籤：blog   http   io   ar   使用   sp   strong   檔案   資料   

1、表和索引儲存結構

在SQL Server2005以前，一個表格是以一個B樹或者一個堆（heap)存放的。每個B樹或者堆，在sysindexes裡面都有一條記錄相對應。SQL Server2005以後，引入了分區表的概念（Table Partition），在儲存群組織上，現有的分區基本上替代了原來表格的概念，原先表的概念成為了一個邏輯概念。一個分區就是一個B樹或者一個堆。而一張表格則是一個到多個分區的組合。

1.1用B樹儲存於叢集索引的表資料頁

如果一個表格上有叢集索引（Clustered Index)，資料行將基於叢集索引鍵按順序儲存。叢集索引按B樹索引結構來實現，B樹索引結構支援基於叢集索引索引值對行快速檢索。資料頁面之間用雙向鏈表，緊密相連。

 1.2堆是沒有叢集索引的表

如果表格上沒有叢集索引，資料行將不按任何的順序儲存，資料頁也沒有任何特殊的順序。資料頁之間沒有鏈錶鏈接。

1.3非叢集索引

非叢集索引與叢集索引有一個相似的B樹索引結構。不同的是，非叢集索引不影響資料行的順序。分葉層級僅包含索引行，沒有完整的資料。每個索引行包含非叢集索引索引值和行定位器。定位器指向（在另一個B樹或者堆中）包含索引值的資料行。非叢集索引本身也會佔用一些資料頁。這些頁面以雙向鏈表相連。

sys.partitions為表和索引提供一個對象，每個對象佔據一行資訊，其中分為以下幾類：

1、index_id=0，這種行記錄的是堆表資訊

sys.system_internals_allocation_units中的first_iam_page列指向指定分區中堆資料頁i彙總的IAM鏈。因為這些頁沒有串連，不可能從第一頁找到下一頁，所以SQL Server只能使用IAM頁尋找資料頁集合中的每一頁。

2、index_id=1,表示表或視圖的叢集索引

sys.system_internals_allocation_units中的root_page列指向指定分區內叢集索引B樹的頂端。SQL Server使用索引B樹鏈表能夠從頂端頁面尋找到分頁中的每個資料頁。

3、index_id>1,為表或視圖建立的非叢集索引

sys.system_internals_allocation_units中的root_page列指向指定分區內非叢集索引B樹的頂端。

如果存在LOB列（image、varchar(max)、text)的每個表在sys.partitions中也另外再有一行，其index_id>250，用以管理LOB頁面。

first_iam_page列指向管理LOB_DATA配置單位中的頁的IAM頁鏈。

總之，從一個對象Index_id就能判斷出它是什麼類型的儲存方式，如果是0，據說明這張表沒有叢集索引；如果是1，就是叢集索引頁面；如果大於250，就是text或image欄位；如果在2——250之間，就是非叢集索引頁面。

堆結構

堆是不包含叢集索引的表。SQL Server使用“索引配置對應(IAM)”頁將堆的頁面聯絡在一起。堆的特點有以下幾個：

1、堆內的資料頁和行沒有任何特定的順序。

在一個堆裡的資料完全是隨機存放的。而且SQL Server也假設資料之間沒有任何聯絡。

2、頁面也不串連在一起

資料頁之間唯一的邏輯串連是記錄在IAM頁內的資訊。頁面與頁面之間也沒有什麼緊密的聯絡。

3、堆中的行一般不按照插入的順序返回

因為IAM按資料頁在資料檔案記憶體在的順序標示他們，所以這意味著堆掃描會沿每個檔案進行。而不是按這些行的插入順序，或者任何邏輯上的順序。

，表現了SQL Server資料庫引擎如何使用IAM頁檢索具有單個分區的堆中的資料行。

可以看到，SQL Server對堆的管理師比較簡單的。在演算法能力上也是比較脆弱的。不談效能，光從資料存放區管理上來講，用堆去管理一個超大的表格是比較吃力的。所以在SQL Server裡對於所有大的、經常使用的表格上都建立叢集索引。因為叢集索引會避免很多問題。

叢集索引結構

在SQL Server中，索引是按B樹結構進行組織的。索引B樹種的每一頁稱為一個索引節點。B樹的頂端節點稱為根節點。索引中的底層節點稱為分葉節點。根節點與分葉節點之間的任何索引層級統稱為中間級。每個索引行包含一個索引值和一個指標，該指標指向B樹上某一中間頁或頁級索引中的某個資料行。每級索引張的頁均被連結在雙向鏈表中。

資料鏈內的頁和行將按叢集索引索引值進行排序。所有插入操作都在所插入行中的索引值與現有行中的排序次序相匹配時執行。B樹頁集合由sys.system_internals_allocation_units系統檢視表中的頁指標來定位。

對於某個叢集索引，sys.system_internals_allocation_units中的root_page列指向該叢集索引某個特定分區的頂部。SQL Server將在索引中向下移動以尋找與某個叢集索引鍵對應的行。為了尋找鍵的範圍，SQL Server將在索引中移動以尋找該範圍的起始索引值，然後用向前或向後指標在資料頁中進行掃描。為了尋找資料頁鏈的首頁，SQL Server將從索引的根節點沿最左邊的指標進行掃描。

相對於堆，叢集索引的特點有以下幾個：

1、堆內的資料頁和行有嚴格的順序。

叢集索引保證了表格的資料按照索引行的順序排列。而且SQL Server知道這種循序關聯性。

2、頁面連結在一起。頁面與頁面聯絡緊密。

3、樹種的行一般能夠按照索引列的順序返回。

所以從這幾點來看，建立B樹以後，SQL Sever對資料頁的管理能夠更加快速有效，有些發生在堆上的問題就不容易發生在B樹上，效能高出很多

非叢集索引結構

非叢集索引與叢集索引具有相同的B樹結構，他們之間的顯著差別在於以下兩點：

1、基礎資料表的資料行不按非聚集鍵的順序排序和儲存

2、非叢集索引的頁層是由索引頁面不是由資料頁組成

3、建立非叢集索引的表可以是一個B樹，也可以是一個堆。

4、如果表示堆（意味著該表沒有叢集索引），則行定位器是指向行的指標。該指標由檔案標識符（ID）、頁碼和頁上的行數產生。整個指標稱為行ID(RID)。

5、如果表沒有叢集索引或索引檢視表上有叢集索引，則行定位器是行的叢集索引鍵。如果叢集索引不是唯一的索引，SQL Server將添加在內部產生的值（稱為唯一值）以使所有重複鍵唯一。SQL Server通過使用儲存在非叢集索引的葉行內的叢集索引鍵搜尋叢集索引來檢索資料行。

所以非叢集索引不會去改變或改善資料頁的儲存方式。它的B數結構只針對自己的索引頁面。如果問題是堆的特性導致的，加一個非叢集索引不能帶來根本的改善。

 對於表格建立叢集索引不會增加表格的大小，而增加非叢集索引就會增加空間，但是如果在一個經常變化的表格建立叢集索引，會容易遇到頁面分割（page split)，所以儘力叢集索引會影響效能。基於這種考慮，很多設計者不願意在SQL Server的表格中建立叢集索引，但是一張不建立索引的表格效能是非常差的，所以就增加了非叢集索引，以期望得到好的效能。

但是這這種方案是一種浪費空間、效能也不一定好的設計，為此SQL Server2005做了個比較，得出的結論是：有叢集索引的表格在Select 、Update、Delete這些動作效能有很大的提升，更重要的是在Insert這一項上，兩者沒有什麼差別。並沒有出現叢集索引影響Insert速度的現象。所以再次強烈建議，在一個大的表格上一定要建立一個叢集索引。

DELETE和TRUNCATE之間的區別

1、DELETE命令並不能完全釋放表格或索引的資料結構以及他們申請的頁面，尤其在堆表上。在SQL Server2005以後的版本中樹結構的資料對於頁釋放做的更好一點，但是也不能完全釋放。而TRUNCATE就能完全釋放掉

2、所用交易記錄空間較少，因為DELETE語句每次刪除一行，並在交易記錄中為所刪除的每行記錄一個項，TRUNCATE TABLE通過釋放用於儲存資料的資料頁來刪除資料，並且在交易記錄中只記錄這個動作，而不記錄每一行。

3、使用的鎖通常較少，當使用行鎖執行Delete語句時，將鎖定表中各行以便刪除，TRUNCATE TABLE始終鎖定表和頁，而且速度更快

4、表中將毫無例外的不保留任何頁，執行DELETE語句後，表仍會包含空頁。例如，必須至少一個排他（LCK_M_X）表鎖，才能釋放堆中的空頁。如果執行刪除操作時沒有使用表鎖，表（堆）中將包含許多空頁。對於索引、刪除操作會留下一些空頁，儘管這些頁會通過後台清楚迅速釋放。

TRUNCATE TABLE刪除表中的行的所有行，但表結構以及其列、約束、索引等保持不變。若刪除表定義以及其資料，使用Drop table語句。

所以對於像及時的刪除資料，然後釋放空間，可以採用的方法有：

1、在表格建立叢集索引

2、如果所有資料都不要了，要使用TRUNCATE TABLE而不是delete

Delete 不能完全釋放空間，會不會造成空間泄露了呢？其實不用擔心，雖然沒有將這些頁面釋放掉，但是當表格插入新的資料的時候，這些頁面會被重新使用的。所以這些頁面並沒有“泄露”掉，會留給SQL Server重用。

如果真的非要用DELETE語句，如果表有叢集索引，重建一下索引能釋放掉出這些空間，但是如果沒有，可以重建一張新表，把資料從舊錶中倒過去，然後刪除舊錶，釋放空間；也可以在這種表上建立叢集索引，這樣有點折騰，當然有時候也沒必要非要把空間釋放出來，可以等著新資料插入時，直接利用就行。

《SQL Server企業級平台管理實踐》讀書筆記——SQL Server中資料檔案空間使用與管理