本文講述了SQL SERVER中碎片產(chǎn)生的原理，內(nèi)部碎片和外部碎片的概念克婶。以及解決碎片的辦法和填充因子.在數(shù)據(jù)庫(kù)中，往往每一個(gè)對(duì)于某一方面性能增加的功能也會(huì)伴隨著另一方面性能的減弱。系統(tǒng)的學(xué)習(xí)數(shù)據(jù)庫(kù)知識(shí)磨取，從而根據(jù)具體情況進(jìn)行權(quán)衡，是dba和開發(fā)人員的必修課

本文需要你對(duì)索引和SQL中數(shù)據(jù)的存儲(chǔ)方式有一定了解

在SQL Server中柴墩，存儲(chǔ)數(shù)據(jù)的最小單位是頁(yè)忙厌，每一頁(yè)所能容納的數(shù)據(jù)為8060字節(jié).而頁(yè)的組織方式是通過(guò)B樹結(jié)構(gòu)（表上沒(méi)有聚集索引則為堆結(jié)構(gòu)，不在本文討論之列)如下圖:

image-20210720092628204.png

在聚集索引B樹中江咳，只有葉子節(jié)點(diǎn)實(shí)際存儲(chǔ)數(shù)據(jù)逢净，而其他根節(jié)點(diǎn)和中間節(jié)點(diǎn)僅僅用于存放查找葉子節(jié)點(diǎn)的數(shù)據(jù).

每一個(gè)葉子節(jié)點(diǎn)為一頁(yè)，每頁(yè)是不可分割的. 而SQL Server向每個(gè)頁(yè)內(nèi)存儲(chǔ)數(shù)據(jù)的最小單位是表的行(Row).當(dāng)葉子節(jié)點(diǎn)中新插入的行或更新的行使得葉子節(jié)點(diǎn)無(wú)法容納當(dāng)前更新或者插入的行時(shí)歼指，分頁(yè)就產(chǎn)生了.在分頁(yè)的過(guò)程中爹土，就會(huì)產(chǎn)生碎片.

理解外部碎片

首先，理解外部碎片的這個(gè)“外”是相對(duì)頁(yè)面來(lái)說(shuō)的东臀。外部碎片指的是由于分頁(yè)而產(chǎn)生的碎片.比如着饥，我想在現(xiàn)有的聚集索引中插入一行，這行正好導(dǎo)致現(xiàn)有的頁(yè)空間無(wú)法滿足容納新的行惰赋。從而導(dǎo)致了分頁(yè):

image-20210720092651188.png

因?yàn)樵赟QL SERVER中宰掉，新的頁(yè)是隨著數(shù)據(jù)的增長(zhǎng)不斷產(chǎn)生的，而聚集索引要求行之間連續(xù)赁濒，所以很多情況下分頁(yè)后和原來(lái)的頁(yè)在磁盤上并不連續(xù).

這就是所謂的外部碎片.

由于分頁(yè)會(huì)導(dǎo)致數(shù)據(jù)在頁(yè)之間的移動(dòng)轨奄，所以如果插入更新等操作經(jīng)常需要導(dǎo)致分頁(yè)，則會(huì)大大提升IO消耗拒炎，造成性能下降.

而對(duì)于查找來(lái)說(shuō)挪拟，在有特定搜索條件，比如where子句有很細(xì)的限制或者返回?zé)o序結(jié)果集時(shí)击你，外部碎片并不會(huì)對(duì)性能產(chǎn)生影響玉组。但如果要返回掃描聚集索引而查找連續(xù)頁(yè)面時(shí),外部碎片就會(huì)產(chǎn)生性能上的影響.

在SQL Server中谎柄，比頁(yè)更大的單位是區(qū)(Extent).一個(gè)區(qū)可以容納8個(gè)頁(yè).區(qū)作為磁盤分配的物理單元.所以當(dāng)頁(yè)分割如果跨區(qū)后，需要多次切區(qū)惯雳。需要更多的掃描.因?yàn)樽x取連續(xù)數(shù)據(jù)時(shí)會(huì)不能預(yù)讀朝巫，從而造成額外的物理讀，增加磁盤IO.

理解內(nèi)部碎片

和外部碎片一樣石景，內(nèi)部碎片的”內(nèi)”也是相對(duì)頁(yè)來(lái)說(shuō)的.下面我們來(lái)看一個(gè)例子:

image-20210720092716045.png

我們創(chuàng)建一個(gè)表劈猿，這個(gè)表每個(gè)行由int(4字節(jié)),char(999字節(jié))和varchar(0字節(jié)組成），所以每行為1003個(gè)字節(jié),則8行占用空間1003*8=8024字節(jié)加上一些內(nèi)部開銷潮孽，可以容納在一個(gè)頁(yè)面中:

image-20210720092741420.png

當(dāng)我們隨意更新某行中的col3字段后揪荣，造成頁(yè)內(nèi)無(wú)法容納下新的數(shù)據(jù)，從而造成分頁(yè):

image-20210720092758742.png

分頁(yè)后的示意圖:

image-20210720092807974.png

而當(dāng)分頁(yè)時(shí)如果新的頁(yè)和當(dāng)前頁(yè)物理上不連續(xù)往史，則還會(huì)造成外部碎片

內(nèi)部碎片和外部碎片對(duì)于查詢性能的影響

外部碎片對(duì)于性能的影響上面說(shuō)過(guò)仗颈，主要是在于需要進(jìn)行更多的跨區(qū)掃描，從而造成更多的IO操作.

而內(nèi)部碎片會(huì)造成數(shù)據(jù)行分布在更多的頁(yè)中怠堪，從而加重了掃描的頁(yè)樹揽乱，也會(huì)降低查詢性能.

下面通過(guò)一個(gè)例子看一下,我們?nèi)藶榈臑閯偛拍莻€(gè)表插入一些數(shù)據(jù)造成內(nèi)部碎片:

image-20210720092824007.png

通過(guò)查看碎片，我們發(fā)現(xiàn)這時(shí)碎片已經(jīng)達(dá)到了一個(gè)比較高的程度:

image-20210720092834247.png

通過(guò)查看對(duì)碎片整理之前和之后的IO粟矿，我們可以看出凰棉，IO大大下降了:

image-20210720092845879.png

對(duì)于碎片的解決辦法

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

1.刪除索引并重建

這種方式并不好.在刪除索引期間陌粹，索引不可用.會(huì)導(dǎo)致阻塞發(fā)生撒犀。而對(duì)于刪除聚集索引，則會(huì)導(dǎo)致對(duì)應(yīng)的非聚集索引重建兩次(刪除時(shí)重建掏秩，建立時(shí)再重建).雖然這種方法并不好或舞，但是對(duì)于索引的整理最為有效

2.使用DROP_EXISTING語(yǔ)句重建索引

為了避免重建兩次索引，使用DROP_EXISTING語(yǔ)句重建索引蒙幻，因?yàn)檫@個(gè)語(yǔ)句是原子性的映凳，不會(huì)導(dǎo)致非聚集索引重建兩次，但同樣的邮破，這種方式也會(huì)造成阻塞

3.如前面文章所示诈豌，使用ALTER INDEX REBUILD語(yǔ)句重建索引

使用這個(gè)語(yǔ)句同樣也是重建索引，但是通過(guò)動(dòng)態(tài)重建索引而不需要卸載并重建索引.是優(yōu)于前兩種方法的抒和，但依舊會(huì)造成阻塞矫渔。可以通過(guò)ONLINE關(guān)鍵字減少鎖摧莽，但會(huì)造成重建時(shí)間加長(zhǎng).

4.使用ALTER INDEX REORGANIZE

這種方式不會(huì)重建索引庙洼，也不會(huì)生成新的頁(yè)，僅僅是整理，當(dāng)遇到加鎖的頁(yè)時(shí)跳過(guò)油够，所以不會(huì)造成阻塞蚁袭。但同時(shí)，整理效果會(huì)差于前三種.

理解填充因子

重建索引固然可以解決碎片的問(wèn)題.但是重建索引的代價(jià)不僅僅是麻煩叠聋，還會(huì)造成阻塞撕阎。影響使用.而對(duì)于數(shù)據(jù)比較少的情況下，重建索引代價(jià)并不大碌补。而當(dāng)索引本身超過(guò)百兆的時(shí)候。重建索引的時(shí)間將會(huì)很讓人蛋疼.

填充因子的作用正是如此棉饶。對(duì)于默認(rèn)值來(lái)說(shuō)厦章，填充因子為0（0和100表示的是一個(gè)概念）,則表示頁(yè)面可以100%使用。所以會(huì)遇到前面update或insert時(shí)照藻，空間不足導(dǎo)致分頁(yè).通過(guò)設(shè)置填充因子袜啃，可以設(shè)置頁(yè)面的使用程度:

image-20210720092854184.png

下面來(lái)看一個(gè)例子:

還是上面那個(gè)表.我插入31條數(shù)據(jù)，則占4頁(yè):

image-20210720092900986.png

通過(guò)設(shè)置填充因子幸缕，頁(yè)被設(shè)置到了5頁(yè)上:

image-20210720092916773.png

這時(shí)我再插入一頁(yè)群发，不會(huì)造成分頁(yè):

image-20210720092925941.png

上面的概念可以如下圖來(lái)解釋:

image-20210720092932611.png

可以看出，使用填充因子會(huì)減少更新或者插入時(shí)的分頁(yè)次數(shù)发乔，但由于需要更多的頁(yè)熟妓，則會(huì)對(duì)應(yīng)的損失查找性能.

如何設(shè)置填充因子的值

如何設(shè)置填充因子的值并沒(méi)有一個(gè)公式或者理念可以準(zhǔn)確的設(shè)置。使用填充因子雖然可以減少更新或者插入時(shí)的分頁(yè)栏尚，但同時(shí)因?yàn)樾枰嗟捻?yè)起愈，所以降低了查詢的性能和占用更多的磁盤空間.如何設(shè)置這個(gè)值進(jìn)行trade-off需要根據(jù)具體的情況來(lái)看.

具體情況要根據(jù)對(duì)于表的讀寫比例來(lái)看,我這里給出我認(rèn)為比較合適的值:

1.當(dāng)讀寫比例大于100：1時(shí)，不要設(shè)置填充因子译仗，100%填充

2.當(dāng)寫的次數(shù)大于讀的次數(shù)時(shí)抬虽，設(shè)置50%-70%填充

3.當(dāng)讀寫比例位于兩者之間時(shí)80%-90%填充

上面的數(shù)據(jù)僅僅是我的看法，具體設(shè)置的數(shù)據(jù)還要根據(jù)具體情況進(jìn)行測(cè)試才能找到最優(yōu).