T-SQL查詢高級(jí)—SQL Server索引中的碎片和填充因子

《T-SQL查詢高級(jí)—SQL Server索引中的碎片和填充因子》由會(huì)員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在教育資源-天天文庫。

1、T-SQL查詢高級(jí)—SQLServer索引中的碎片和填充因子??寫在前面:本篇文章需要你對(duì)索引和SQL中數(shù)據(jù)的存儲(chǔ)方式有一定了解.標(biāo)題中高級(jí)兩個(gè)字僅僅是因?yàn)楸酒恼滦枰业腡-SQL進(jìn)階系列文章的一些內(nèi)容作為基礎(chǔ).簡(jiǎn)介???在SQLServer中，存儲(chǔ)數(shù)據(jù)的最小單位是頁，每一頁所能容納的數(shù)據(jù)為8060字節(jié).而頁的組織方式是通過B樹結(jié)構(gòu)（表上沒有聚集索引則為堆結(jié)構(gòu)，不在本文討論之列)如下圖:??????在聚集索引B樹中，只有葉子節(jié)點(diǎn)實(shí)際存儲(chǔ)數(shù)據(jù)，而其他根節(jié)點(diǎn)和中間節(jié)點(diǎn)僅僅用于存放查找葉子節(jié)點(diǎn)的數(shù)據(jù).???每一個(gè)葉子節(jié)點(diǎn)為一頁，每頁是不可分割的.而SQLServer向

2、每個(gè)頁內(nèi)存儲(chǔ)數(shù)據(jù)的最小單位是表的行(Row).當(dāng)葉子節(jié)點(diǎn)中新插入的行或更新的行使得葉子節(jié)點(diǎn)無法容納當(dāng)前更新或者插入的行時(shí)，分頁就產(chǎn)生了.在分頁的過程中，就會(huì)產(chǎn)生碎片.??理解外部碎片???首先，理解外部碎片的這個(gè)“外”是相對(duì)頁面來說的。外部碎片指的是由于分頁而產(chǎn)生的碎片.比如，我想在現(xiàn)有的聚集索引中插入一行，這行正好導(dǎo)致現(xiàn)有的頁空間無法滿足容納新的行。從而導(dǎo)致了分頁:??????因?yàn)樵赟QLSERVER中，新的頁是隨著數(shù)據(jù)的增長不斷產(chǎn)生的，而聚集索引要求行之間連續(xù)，所以很多情況下分頁后和原來的頁在磁盤上并不連續(xù).????這就是所謂的外部碎片.????由于分頁會(huì)導(dǎo)致

3、數(shù)據(jù)在頁之間的移動(dòng)，所以如果插入更新等操作經(jīng)常需要導(dǎo)致分頁，則會(huì)大大提升IO消耗，造成性能下降.????而對(duì)于查找來說，在有特定搜索條件，比如where子句有很細(xì)的限制或者返回?zé)o序結(jié)果集時(shí)，外部碎片并不會(huì)對(duì)性能產(chǎn)生影響。但如果要返回掃描聚集索引而查找連續(xù)頁面時(shí),外部碎片就會(huì)產(chǎn)生性能上的影響.????在SQLServer中，比頁更大的單位是區(qū)(Extent).一個(gè)區(qū)可以容納8個(gè)頁.區(qū)作為磁盤分配的物理單元.所以當(dāng)頁分割如果跨區(qū)后，需要多次切區(qū)。需要更多的掃描.因?yàn)樽x取連續(xù)數(shù)據(jù)時(shí)會(huì)不能預(yù)讀，從而造成額外的物理讀，增加磁盤IO.?理解內(nèi)部碎片???和外部碎片一樣，內(nèi)部碎

4、片的”內(nèi)”也是相對(duì)頁來說的.下面我們來看一個(gè)例子:??????我們創(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è)頁面中:??????當(dāng)我們隨意更新某行中的col3字段后，造成頁內(nèi)無法容納下新的數(shù)據(jù)，從而造成分頁:?????分頁后的示意圖:?????而當(dāng)分頁時(shí)如果新的頁和當(dāng)前頁物理上不連續(xù)，則還會(huì)造成外部碎片內(nèi)部碎片和外部碎片對(duì)于查詢性能的影響???外部碎片對(duì)于性能的影響上面說過，主要是在于需要進(jìn)行更多的跨區(qū)掃描，從而

5、造成更多的IO操作.???而內(nèi)部碎片會(huì)造成數(shù)據(jù)行分布在更多的頁中，從而加重了掃描的頁樹，也會(huì)降低查詢性能.???下面通過一個(gè)例子看一下,我們?nèi)藶榈臑閯偛拍莻€(gè)表插入一些數(shù)據(jù)造成內(nèi)部碎片:??????通過查看碎片，我們發(fā)現(xiàn)這時(shí)碎片已經(jīng)達(dá)到了一個(gè)比較高的程度:??????通過查看對(duì)碎片整理之前和之后的IO，我們可以看出，IO大大下降了:????對(duì)于碎片的解決辦法???基本上所有解決辦法都是基于對(duì)索引的重建和整理，只是方式不同???1.刪除索引并重建??????這種方式并不好.在刪除索引期間，索引不可用.會(huì)導(dǎo)致阻塞發(fā)生。而對(duì)于刪除聚集索引，則會(huì)導(dǎo)致對(duì)應(yīng)的非聚集索引重建兩次

6、(刪除時(shí)重建，建立時(shí)再重建).雖然這種方法并不好，但是對(duì)于索引的整理最為有效???2.使用DROP_EXISTING語句重建索引??????為了避免重建兩次索引，使用DROP_EXISTING語句重建索引，因?yàn)檫@個(gè)語句是原子性的，不會(huì)導(dǎo)致非聚集索引重建兩次，但同樣的，這種方式也會(huì)造成阻塞???3.如前面文章所示，使用ALTERINDEXREBUILD語句重建索引??????使用這個(gè)語句同樣也是重建索引，但是通過動(dòng)態(tài)重建索引而不需要卸載并重建索引.是優(yōu)于前兩種方法的，但依舊會(huì)造成阻塞。可以通過ONLINE關(guān)鍵字減少鎖，但會(huì)造成重建時(shí)間加長.???4.使用ALTERI

7、NDEXREORGANIZE??????這種方式不會(huì)重建索引，也不會(huì)生成新的頁，僅僅是整理，當(dāng)遇到加鎖的頁時(shí)跳過，所以不會(huì)造成阻塞。但同時(shí)，整理效果會(huì)差于前三種.?理解填充因子?????重建索引固然可以解決碎片的問題.但是重建索引的代價(jià)不僅僅是麻煩，還會(huì)造成阻塞。影響使用.而對(duì)于數(shù)據(jù)比較少的情況下，重建索引代價(jià)并不大。而當(dāng)索引本身超過百兆的時(shí)候。重建索引的時(shí)間將會(huì)很讓人蛋疼.?????填充因子的作用正是如此。對(duì)于默認(rèn)值來說，填充因子為0（0和100表示的是一個(gè)概念）,則表示頁面可以100%使用。所以會(huì)遇到前面update或insert時(shí)，空間不足導(dǎo)致分頁.通過設(shè)置

8、填充因子，