SQLServer中的頁如何影響資料庫效能 (轉)

標籤：http   使用   strong   資料   ar   問題   工作   時間   

無論是哪一個資料庫，如果要對資料庫的效能進行最佳化，那麼必須要瞭解資料庫內部的儲存結構。否則的話，很多資料庫的最佳化工作無法展開。對於對於資料庫管理員來說，雖然學習資料庫的記憶體儲存結構比較單調，但是卻是我們必須攻下的一個堡壘。在SQLServer資料庫中，資料頁是其儲存的最基本單位。系統無論是在儲存資料還是在讀取資料的時候，都是以頁為單位來進行操作的。

　　

　　一、資料頁的基本組成。

　　如所示，是SQLServer資料庫中頁的主要組成部分。從這個圖中可以看出，一個資料頁基本上包括三部分內容，分別為標題、資料行和行位移量。其中資料行儲存的是資料本身，其他的標題與位移量都是一些輔助的內容。對於這個資料頁來說，筆者認為資料庫管理員必須要瞭解如下的內容。

　　一是要瞭解資料頁的大小。在SQLServer資料庫中資料頁的大小基本上是固定的，即每個資料頁的大小都為8KB，8192個位元組。其中每頁開頭都有一個標題，其佔據了96個位元組，用於儲存有關頁的資訊。如這個頁被分配到頁碼、頁的類型、頁的可用空間以及擁有這個頁的對象的配置單位ID等等資訊。不過值得慶幸的是，這些內容資料庫都會自動管理與更新，不需要資料庫管理員擔心。資料庫管理員只需要知道的是，這個資料頁中最多可以用來儲存資料的空間。每個頁的大小是8192個位元組，扣除掉一些必要的開銷(如標題資訊或者位移量所佔用的空間)，一般其可以用來實際儲存資料的空間只有8000位元組左右。牢記這個數字，對於後續資料庫效能的最佳化具有很大的作用。詳細的內容筆者在後續行溢出的部分會進行說明。

　　二是需要注意行的放置順序。在每個資料頁上，資料行緊接著標題按順序放置。在頁的末尾有一張行位移表。對於頁中的每一行，每個行位移表都包含有一個條目。即如果業中的資料行達到100條的話，則在這個行位移表中就對英100個條目。每個條目記錄中記錄對應行的第一個位元組與頁首的距離。如第二個跳就記錄著第二個資料行的行首字母到資料頁頁首的位置。由於每個資料行的大小都是不同的，為此這個行位移表中記錄的內容也是沒有規律的。這裡需要注意的是，行位移表中的條目順序與頁中行的順序是相反的。這主要是為了更方便資料庫定位元據行。

　　二、大資料類型與行。

　　根據SQLServer資料庫定義的規則，行是不能夠跨頁的。如所示，如果一個欄位的資料值非常大，其超過8000位元組。此時一個頁已經不能夠容納這個資料。此時資料庫會如何處理呢?雖然說在SQLServer資料庫中，行是不能夠跨頁的。但是可以將行分成兩部分，分別儲存在不同的行中。所以說，對於大資料類型來說，是不受到這個頁大小(或者說行大小)的限制的。根據上面的分析可以看出，一個資料頁其最大可以用的儲存空間在8KB。如果扣掉一些必要的開銷，其只有8000位元組左右。當某條記錄的所有列(包括固定長度的列與可變長度的列其大小超過這個限制的時候，資料庫就會將其進行分行處理，分別儲存在兩個不同的頁中。當某張表格中列的總大小超過限制的8KB(實際上還還不到一點)位元組時，資料庫系統會從最大長度的列開始動態將一個或多個可變長度列移動到另外一個頁中。簡單的說，就是將某個列超過的部分單獨存放在另一個頁中。並且同時還會儲存一些指標之類的資訊，以便在不同頁的記錄中建立關聯。這種現象在SQLServer資料庫中給其取了一個名字，叫做行溢出。

　　三、行溢出對於資料庫效能的不利影響。

　　掌握了上面關於資料頁的基本工作原理後，資料庫管理員需要重點理解行溢出對於資料庫效能的不利影響。即需要瞭解，當所有列(包括固定長度的列與可變長度的列)的累積長度超過一個資料頁(或者一個資料行)的最大承受限度時，會將列的內容分行來進行存放。資料庫如此處理，對資料庫的效能會有不利的影響嗎?如果有的話，該如何避免?

　　一般來說，每行的記錄超過頁的最大容量時，肯定會對資料庫的效能造成不利的影響。這是毋庸置疑的。因為當超過這個容量時，資料庫系統就需要對這個資料行進行分頁處理。而分頁處理需要資料庫額外的開銷。如在分頁儲存時，需要給資料庫添加額外的指標;在查詢資料的時候，由於分頁情況的存在，為了讀取一條完整的記錄，資料庫系統可能不得不讀取多頁的內容;當進行更新操作，將某個欄位的內容變短，導致整行的內容在頁的最大範圍之內，則相關的記錄會被儲存在同一個行中。這些操作都需要資料庫額外的開銷。當在同一個時間處理這些作業多了，那麼積累起來，對資料庫效能的影響就會很顯著。同理，此時如果對相關的記錄進行排序、統計等操作，由於涉及到多個頁，會延長這些作業的執行時間，即降低資料庫的效能。

其次需要注意的是對一些變長欄位的限制。在SQLServre資料庫中，也含有varchar等變長的資料類型。在SQLServer資料庫中對此有最大長度的限制。一般情況下，其最大長度不能夠超過不能夠超過8000位元組的限制。不過他們的總寬度可以超過這個8KB的限制。如果單列的資料長度超過這個限制，那麼就不能夠使用普通的資料類型。如對於那些用來儲存圖片或者多媒體的資料，必須要使用大對象資料類型。因為只有這些大對象資料類型不受這個長度的限制。資料庫對對於這些大型資料庫類型對象有特殊的處理方法。

　　四、資料庫設計時的注意事項。

　　在資料庫運行時，如果存在比較多的行溢出現象，會在很大程度上影響資料庫的效能。所以在資料庫設計時，需要考慮到這種情況。一般的資料類型不會造成行溢出的情況。只有一些varchar nvarchar或者CLR使用者自訂類型的列，比較容易造成這個行溢出現象。所以在設計資料庫時，資料庫管理員應該根據使用者提供的樣板資料分析可能發生行溢出現象的百分比，以及評估會發生溢出現象的頻率。如果溢出現象發生的百分比或者頻率比較高的話，那麼資料庫管理員就需要考慮對錶格進行正常化處理，以提高資料庫的效能，減少溢出現象對於資料庫的不利影響。

　　一般來說，有兩種方法可以顯著的降低這個行溢出現象對資料庫效能的影響。一是假設列定義了varchar或者使用者自訂資料類型等資料類型的時候，如果其長度比較長，很有可能引起行溢出現象的話，那麼就乾脆使用大對象資料類型。對於大對象資料類型SQLServer資料庫會採取特殊的管理方法，會講這個資料與普通資料分開來管理。所以可以在很大程度上降低行溢出現象對資料庫效能的影響。不過需要注意的是，管理這些大對象資料類型，資料庫本身就需要花費更多的精力與資源。所以採用這種方式帶來的收益，與行溢出現象帶來的損失就會有一個輕重之分的問題。資料庫管理員要評估由此帶來的收益能夠彌補行溢出對象帶來的損失。如果可以彌補的話，那麼可以採用這個方案。如果不可以的話，那就得不償失了。故筆者並不是很推薦使用這種方法。筆者現在採用的是下面要介紹的這種方式。

　　第二種方法執行起來比較簡單，具有比較強的可執行性。即如果某個表格中有varchar或則使用者自訂的資料類型，而且其最大長度也比較長，很容易造成行溢出現象。此時最好將這些列與表中的其他列分開來存放。即將他們放在兩張不同的表中。然後再通過join語句來進行串連。由於資料頁對單個列的最大長度有限制，所以如此處理的話，就不怎麼會發生行溢出的現象。此時如果需要查詢完整的記錄，也需要訪問多個頁。但是在實際工作中，往往不需要訪問全部的資訊。如在更新或者統計操作時，不需要更新varchar資料類型的欄位，那麼資料庫的效率就會有很大的提升。即使需要訪問完整的記錄，需要訪問多個頁。但是採取join操作也要比行溢出操作效能來的好。如在更新資料時將varchar的列縮短了，此時由於在兩個不同的表中，也不會出現合并行的問題。所以可以在很大程度上節省資料庫的開銷。顯然，這種分表處理的方式更加簡單，很容易操作。所以筆者強烈建議採用這種方式來避免行溢出對SQLServer資料庫造成的不利影響。