資源描述：
《T-SQL查詢高級—SQL Server索引中的碎片和填充因子》由會員上傳分享，免費在線閱讀，更多相關內容在教育資源-天天文庫。
1、T-SQL查詢高級—SQLServer索引中的碎片和填充因子??寫在前面:本篇文章需要你對索引和SQL中數(shù)據(jù)的存儲方式有一定了解.標題中高級兩個字僅僅是因為本篇文章需要我的T-SQL進階系列文章的一些內容作為基礎.簡介???在SQLServer中，存儲數(shù)據(jù)的最小單位是頁，每一頁所能容納的數(shù)據(jù)為8060字節(jié).而頁的組織方式是通過B樹結構（表上沒有聚集索引則為堆結構，不在本文討論之列)如下圖:??????在聚集索引B樹中，只有葉子節(jié)點實際存儲數(shù)據(jù)，而其他根節(jié)點和中間節(jié)點僅僅用于存放查找葉子節(jié)點的數(shù)據(jù).???每一個葉子節(jié)點為一頁，每頁是不可分割的.而SQLServer向
2、每個頁內存儲數(shù)據(jù)的最小單位是表的行(Row).當葉子節(jié)點中新插入的行或更新的行使得葉子節(jié)點無法容納當前更新或者插入的行時，分頁就產生了.在分頁的過程中，就會產生碎片.??理解外部碎片???首先，理解外部碎片的這個“外”是相對頁面來說的。外部碎片指的是由于分頁而產生的碎片.比如，我想在現(xiàn)有的聚集索引中插入一行，這行正好導致現(xiàn)有的頁空間無法滿足容納新的行。從而導致了分頁:??????因為在SQLSERVER中，新的頁是隨著數(shù)據(jù)的增長不斷產生的，而聚集索引要求行之間連續(xù)，所以很多情況下分頁后和原來的頁在磁盤上并不連續(xù).????這就是所謂的外部碎片.????由于分頁會導致
3、數(shù)據(jù)在頁之間的移動，所以如果插入更新等操作經(jīng)常需要導致分頁，則會大大提升IO消耗，造成性能下降.????而對于查找來說，在有特定搜索條件，比如where子句有很細的限制或者返回無序結果集時，外部碎片并不會對性能產生影響。但如果要返回掃描聚集索引而查找連續(xù)頁面時,外部碎片就會產生性能上的影響.????在SQLServer中，比頁更大的單位是區(qū)(Extent).一個區(qū)可以容納8個頁.區(qū)作為磁盤分配的物理單元.所以當頁分割如果跨區(qū)后，需要多次切區(qū)。需要更多的掃描.因為讀取連續(xù)數(shù)據(jù)時會不能預讀，從而造成額外的物理讀，增加磁盤IO.?理解內部碎片???和外部碎片一樣，內部碎
4、片的”內”也是相對頁來說的.下面我們來看一個例子:??????我們創(chuàng)建一個表，這個表每個行由int(4字節(jié)),char(999字節(jié))和varchar(0字節(jié)組成），所以每行為1003個字節(jié),則8行占用空間1003*8=8024字節(jié)加上一些內部開銷，可以容納在一個頁面中:??????當我們隨意更新某行中的col3字段后，造成頁內無法容納下新的數(shù)據(jù)，從而造成分頁:?????分頁后的示意圖:?????而當分頁時如果新的頁和當前頁物理上不連續(xù)，則還會造成外部碎片內部碎片和外部碎片對于查詢性能的影響???外部碎片對于性能的影響上面說過，主要是在于需要進行更多的跨區(qū)掃描，從而
5、造成更多的IO操作.???而內部碎片會造成數(shù)據(jù)行分布在更多的頁中，從而加重了掃描的頁樹，也會降低查詢性能.???下面通過一個例子看一下,我們人為的為剛才那個表插入一些數(shù)據(jù)造成內部碎片:??????通過查看碎片，我們發(fā)現(xiàn)這時碎片已經(jīng)達到了一個比較高的程度:??????通過查看對碎片整理之前和之后的IO，我們可以看出，IO大大下降了:????對于碎片的解決辦法???基本上所有解決辦法都是基于對索引的重建和整理，只是方式不同???1.刪除索引并重建??????這種方式并不好.在刪除索引期間，索引不可用.會導致阻塞發(fā)生。而對于刪除聚集索引，則會導致對應的非聚集索引重建兩次
6、(刪除時重建，建立時再重建).雖然這種方法并不好，但是對于索引的整理最為有效???2.使用DROP_EXISTING語句重建索引??????為了避免重建兩次索引，使用DROP_EXISTING語句重建索引，因為這個語句是原子性的，不會導致非聚集索引重建兩次，但同樣的，這種方式也會造成阻塞???3.如前面文章所示，使用ALTERINDEXREBUILD語句重建索引??????使用這個語句同樣也是重建索引，但是通過動態(tài)重建索引而不需要卸載并重建索引.是優(yōu)于前兩種方法的，但依舊會造成阻塞?？梢酝ㄟ^ONLINE關鍵字減少鎖，但會造成重建時間加長.???4.使用ALTERI
7、NDEXREORGANIZE??????這種方式不會重建索引，也不會生成新的頁，僅僅是整理，當遇到加鎖的頁時跳過，所以不會造成阻塞。但同時，整理效果會差于前三種.?理解填充因子?????重建索引固然可以解決碎片的問題.但是重建索引的代價不僅僅是麻煩，還會造成阻塞。影響使用.而對于數(shù)據(jù)比較少的情況下，重建索引代價并不大。而當索引本身超過百兆的時候。重建索引的時間將會很讓人蛋疼.?????填充因子的作用正是如此。對于默認值來說，填充因子為0（0和100表示的是一個概念）,則表示頁面可以100%使用。所以會遇到前面update或insert時，空間不足導致分頁.通過設置
8、填充因子，
