SQL Server數(shù)據(jù)庫中的頁影響數(shù)據(jù)庫性能的表現(xiàn)
此文章主要向大家描述的是SQL Server數(shù)據(jù)庫中的頁影響數(shù)據(jù)庫性能的表現(xiàn)，我們大家都知道無論哪個數(shù)據(jù)庫，如果你需要對其性能進(jìn)行優(yōu)化的話，那么你必須要了解數(shù)據(jù)庫內(nèi)部的存儲結(jié)構(gòu)。
否則的話，很多數(shù)據(jù)庫的優(yōu)化工作無法展開。對于對于數(shù)據(jù)庫管理員來說，雖然學(xué)習(xí)數(shù)據(jù)庫的內(nèi)存存儲結(jié)構(gòu)比較單調(diào)，但是卻是我們必須攻下的一個堡壘。在SQL Server數(shù)據(jù)庫中，數(shù)據(jù)頁是其存儲的最基本單位。系統(tǒng)無論是在保存數(shù)據(jù)還是在讀取數(shù)據(jù)的時候，都是以頁為單位來進(jìn)行操作的。
一、數(shù)據(jù)頁的基本組成。
如上圖所示，是SQL Server數(shù)據(jù)庫中頁的主要組成部分。從這個圖中可以看出，一個數(shù)據(jù)頁基本上包括三部分內(nèi)容，分別為標(biāo)頭、數(shù)據(jù)行和行偏移量。其中數(shù)據(jù)行存儲的是數(shù)據(jù)本身，其他的標(biāo)頭與偏移量都是一些輔助的內(nèi)容。對于這個數(shù)據(jù)頁來說，筆者認(rèn)為數(shù)據(jù)庫管理員必須要了解如下的內(nèi)容。
一是要了解數(shù)據(jù)頁的大小。在SQL Server數(shù)據(jù)庫中數(shù)據(jù)頁的大小基本上是固定的，即每個數(shù)據(jù)頁的大小都為8KB，8192個字節(jié)。其中每頁開頭都有一個標(biāo)頭，其占據(jù)了96個字節(jié)，用于存儲有關(guān)頁的信息。如這個頁被分配到頁碼、頁的類型、頁的可用空間以及擁有這個頁的對象的分配單元ID等等信息。不過值得慶幸的是，這些內(nèi)容數(shù)據(jù)庫都會自動管理與更新，不需要數(shù)據(jù)庫管理員擔(dān)心。數(shù)據(jù)庫管理員只需要知道的是，這個數(shù)據(jù)頁中最多可以用來保存數(shù)據(jù)的空間。
每個頁的大小是8192個字節(jié)，扣除掉一些必要的開銷(如標(biāo)頭信息或者偏移量所占用的空間)，一般其可以用來實際存儲數(shù)據(jù)的空間只有8000字節(jié)左右。牢記這個數(shù)字，對于后續(xù)數(shù)據(jù)庫性能的優(yōu)化具有很大的作用。詳細(xì)的內(nèi)容筆者在后續(xù)行溢出的部分會進(jìn)行說明。
二是需要注意行的放置順序。在每個數(shù)據(jù)頁上，數(shù)據(jù)行緊接著標(biāo)頭按順序放置。在頁的末尾有一張行偏移表。對于頁中的每一行，每個行偏移表都包含有一個條目。即如果業(yè)中的數(shù)據(jù)行達(dá)到100條的話，則在這個行偏移表中就對英100個條目。每個條目記錄中記錄對應(yīng)行的第一個字節(jié)與頁首的距離。
如第二個跳就記錄著第二個數(shù)據(jù)行的行首字母到數(shù)據(jù)頁頁首的位置。由于每個數(shù)據(jù)行的大小都是不同的，為此這個行偏移表中記錄的內(nèi)容也是沒有規(guī)律的。這里需要注意的是，行偏移表中的條目順序與頁中行的順序是相反的。這主要是為了更方便數(shù)據(jù)庫定位數(shù)據(jù)行。
二、大數(shù)據(jù)類型與行。
根據(jù)SQLServer數(shù)據(jù)庫定義的規(guī)則，行是不能夠跨頁的。如上圖所示，如果一個字段的數(shù)據(jù)值非常大，其超過8000字節(jié)。此時一個頁已經(jīng)不能夠容納這個數(shù)據(jù)。此時數(shù)據(jù)庫會如何處理呢?雖然說在SQL Server數(shù)據(jù)庫中，行是不能夠跨頁的。但是可以將行分成兩部分，分別存儲在不同的行中。所以說，對于大數(shù)據(jù)類型來說，是不受到這個頁大小(或者說行大小)的限制的。根據(jù)上面的分析可以看出，一個數(shù)據(jù)頁其最大可以用的存儲空間在8KB。
如果扣掉一些必要的開銷，其只有8000字節(jié)左右。當(dāng)某條記錄的所有列(包括固定長度的列與可變長度的列其大小超過這個限制的時候，數(shù)據(jù)庫就會將其進(jìn)行分行處理，分別存儲在兩個不同的頁中。
當(dāng)某張表格中列的總大小超過限制的8KB(實際上還還不到一點)字節(jié)時，數(shù)據(jù)庫系統(tǒng)會從最大長度的列開始動態(tài)的將一個或多個可變長度列移動到另外一個頁中。簡單的說，就是將某個列超過的部分單獨存放在另一個頁中。并且同時還會存儲一些指針之類的信息，以便在不同頁的記錄中建立關(guān)聯(lián)。這種現(xiàn)象在SQL Server數(shù)據(jù)庫中給其取了一個名字，叫做行溢出。
三、行溢出對于數(shù)據(jù)庫性能的不利影響。
掌握了上面關(guān)于數(shù)據(jù)頁的基本工作原理后，數(shù)據(jù)庫管理員需要重點理解行溢出對于數(shù)據(jù)庫性能的不利影響。即需要了解，當(dāng)所有列(包括固定長度的列與可變長度的列)的累積長度超過一個數(shù)據(jù)頁(或者一個數(shù)據(jù)行)的最大承受限度時，會將列的內(nèi)容分行來進(jìn)行存放。數(shù)據(jù)庫如此處理，對數(shù)據(jù)庫的性能會有不利的影響嗎?如果有的話，該如何避免?
一般來說，每行的記錄超過頁的最大容量時，肯定會對數(shù)據(jù)庫的性能造成不利的影響。這是毋庸置疑的。因為當(dāng)超過這個容量時，數(shù)據(jù)庫系統(tǒng)就需要對這個數(shù)據(jù)行進(jìn)行分頁處理。而分頁處理需要數(shù)據(jù)庫額外的開銷。
如在分頁保存時，需要給數(shù)據(jù)庫添加額外的指針;在查詢數(shù)據(jù)的時候，由于分頁情況的存在，為了讀取一條完整的記錄，數(shù)據(jù)庫系統(tǒng)可能不得不讀取多頁的內(nèi)容;當(dāng)進(jìn)行更新操作，將某個字段的內(nèi)容變短，導(dǎo)致整行的內(nèi)容在頁的最大范圍之內(nèi)，則相關(guān)的記錄會被保存在同一個行中。這些操作都需要數(shù)據(jù)庫額外的開銷。當(dāng)在同一個時間處理這些作業(yè)多了，那么積累起來，對數(shù)據(jù)庫性能的影響就會很顯著。同理，此時如果對相關(guān)的記錄進(jìn)行排序、統(tǒng)計等操作，由于涉及到多個頁，會延長這些作業(yè)的執(zhí)行時間，即降低數(shù)據(jù)庫的性能。
其次需要注意的是對一些變長字段的限制。在SQLServre數(shù)據(jù)庫中，也含有varchar等變長的數(shù)據(jù)類型。在SQLServer數(shù)據(jù)庫中對此有最大長度的限制。一般情況下，其最大長度不能夠超過不能夠超過8000字節(jié)的限制。不過他們的總寬度可以超過這個8KB的限制。
如果單列的數(shù)據(jù)長度超過這個限制，那么就不能夠使用普通的數(shù)據(jù)類型。如對于那些用來保存圖片或者多媒體的數(shù)據(jù)，必須要使用大對象數(shù)據(jù)類型。因為只有這些大對象數(shù)據(jù)類型不受這個長度的限制。數(shù)據(jù)庫對對于這些大型數(shù)據(jù)庫類型對象有特殊的處理方法。
四、數(shù)據(jù)庫設(shè)計時的注意事項。
在數(shù)據(jù)庫運行時，如果存在比較多的行溢出現(xiàn)象，會在很大程度上影響數(shù)據(jù)庫的性能。所以在數(shù)據(jù)庫設(shè)計時，需要考慮到這種情況。一般的數(shù)據(jù)類型不會造成行溢出的情況。只有一些varchar nvarchar或者CLR用戶自定義類型的列，比較容易造成這個行溢出現(xiàn)象。
所以在設(shè)計數(shù)據(jù)庫時，數(shù)據(jù)庫管理員應(yīng)該根據(jù)用戶提供的樣板數(shù)據(jù)分析可能發(fā)生行溢出現(xiàn)象的百分比，以及評估會發(fā)生溢出現(xiàn)象的頻率。如果溢出現(xiàn)象發(fā)生的百分比或者頻率比較高的話，那么數(shù)據(jù)庫管理員就需要考慮對表格進(jìn)行規(guī)范化處理，以提高數(shù)據(jù)庫的性能，減少溢出現(xiàn)象對于數(shù)據(jù)庫的不利影響。
一般來說，有兩種方法可以顯著的降低這個行溢出現(xiàn)象對數(shù)據(jù)庫性能的影響。一是假設(shè)列定義了varchar或者用戶自定義數(shù)據(jù)類型等數(shù)據(jù)類型的時候，如果其長度比較長，很有可能引起行溢出現(xiàn)象的話，那么就干脆使用大對象數(shù)據(jù)類型。對于大對象數(shù)據(jù)類型SQL Server數(shù)據(jù)庫會采取特殊的管理方法，會講這個數(shù)據(jù)與普通數(shù)據(jù)分開來管理。所以可以在很大程度上降低行溢出現(xiàn)象對數(shù)據(jù)庫性能的影響。
不過需要注意的是，管理這些大對象數(shù)據(jù)類型，數(shù)據(jù)庫本身就需要花費更多的精力與資源。所以采用這種方式帶來的收益，與行溢出現(xiàn)象帶來的損失就會有一個輕重之分的問題。數(shù)據(jù)庫管理員要評估由此帶來的收益能夠彌補行溢出對象帶來的損失。如果可以彌補的話，那么可以采用這個方案。如果不可以的話，那就得不償失了。故筆者并不是很推薦使用這種方法。筆者現(xiàn)在采用的是下面要介紹的這種方式。
第二種方法執(zhí)行起來比較簡單，具有比較強的可執(zhí)行性。即如果某個表格中有varchar或則用戶自定義的數(shù)據(jù)類型，而且其最大長度也比較長，很容易造成行溢出現(xiàn)象。此時最好將這些列與表中的其他列分開來存放。即將他們放在兩張不同的表中。然后再通過join語句來進(jìn)行連接。由于數(shù)據(jù)頁對單個列的最大長度有限制，所以如此處理的話，就不怎么會發(fā)生行溢出的現(xiàn)象。此時如果需要查詢完整的記錄，也需要訪問多個頁。
但是在實際工作中，往往不需要訪問全部的信息。如在更新或者統(tǒng)計操作時，不需要更新varchar數(shù)據(jù)類型的字段，那么數(shù)據(jù)庫的效率就會有很大的提升。即使需要訪問完整的記錄，需要訪問多個頁。但是采取join操作也要比行溢出操作性能來的好。如在更新數(shù)據(jù)時將varchar的列縮短了，此時由于在兩個不同的表中，也不會出現(xiàn)合并行的問題。所以可以在很大程度上節(jié)省數(shù)據(jù)庫的開銷。顯然，這種分表處理的方式更加簡單，很容易操作。所以筆者強烈建議采用這種方式來避免行溢出對SQL Server數(shù)據(jù)庫造成的不利影響。
【編輯推薦】
