sql server 查詢優(yōu)化(4)_索引的碎片與管理

1、一、索引碎片一、索引碎片無論是索引組織表（IOT）還是堆表（HEAP），隨著數(shù)據(jù)的增刪改，都會或多或多的產(chǎn)生碎片。碎片的存在，主要對于數(shù)據(jù)掃描效率有著較大的影響，對于數(shù)據(jù)查找效率幾乎沒有影響或者說影響很小，如果想要改善數(shù)據(jù)查找的效率，進(jìn)行索引碎片整理并沒有什么效果。以下的碎片分類也主要是從數(shù)據(jù)掃描著眼。1、內(nèi)部碎片、內(nèi)部碎片內(nèi)部指的是頁內(nèi)，即頁面的空閑空間。其實填充因子就是一種碎片，為了減少頁拆分，寧愿適當(dāng)?shù)厝ブ圃爝@種碎片。但在大量內(nèi)部

2、碎片一直處于無法被數(shù)據(jù)填充的情況下，是沒有益處的，它會導(dǎo)致掃描過程中讀取額外的頁面。對于LOB和ROW_OVERFLOW_DATA頁面，這是唯一的碎片形式，因為在這兩種列上無法建立B樹索引。2、外部碎片、外部碎片（1）邏輯碎片索引葉子節(jié)點頁的邏輯順序與物理順序不一致，比如：有頁號1，2的兩個頁面，此時1頁面發(fā)生頁拆分，這時新申請的頁面頁號為3，此時邏輯順序為132，但物理順序是123，1頁面沒有直接指向磁盤的下一個物理頁，這就造成了不一

3、致，即邏輯碎片；（2）擴(kuò)展碎片SQLSERVER通常給表或索引分配新的空間是以EXTENT（區(qū)或擴(kuò)展）的形式，一個區(qū)是8個頁面，所以區(qū)的第一個頁號應(yīng)該是8的倍數(shù)，比如：一個包含有序區(qū)的表，第一個頁面的頁號應(yīng)該是81624，這樣下去，如果是824，那么說明第一個頁面頁號為16的區(qū)被分配給了另一個表，那么824的表在物理上就存在一個間隙，即擴(kuò)展碎片；外部碎片是對數(shù)據(jù)連續(xù)性的度量，擴(kuò)展碎片是堆表數(shù)據(jù)連續(xù)性的度量，數(shù)據(jù)的連續(xù)性越差，掃描的成本也

4、會越大。二、查看與管理二、查看與管理對于索引樹的管理主要考慮兩方面：一是B樹的平衡性，這一點數(shù)據(jù)庫系統(tǒng)會自動維護(hù)；二是索引碎片，這需要手動去維護(hù)。1、碎片的查看、碎片的查看SQLSERVER支持兩種碎片查看方式，至于這兩種碎片查看方式的使用方法，幫助文檔里有很詳細(xì)的說明。（1）dbccshowcontig是SQLSERVER2000中的唯一碎片查看方式，在SQLSERVER2005中無法支持LOB類型、ROW_OVERFLOW_DATA

5、及整個分區(qū)表的碎片查看；（2）sys.dm_db_index_physical_stats是SQLSERVER2005新的碎片查看方式；對于碎片的檢測以及是否需要進(jìn)行碎片處理，主要從以下幾個參數(shù)來看：D：dbccdbreindex；這是SQLSERVER2000時重建索引的方法，它默認(rèn)使用表上原來createindex的參數(shù)重建索引。填充因子可重新指定。（2）、索引重組將索引樹的葉節(jié)點頁重新排序，以消除外部碎片。這是一個聯(lián)機(jī)操作。但不同

6、于索引重建的是，索引重組后統(tǒng)計信息不會得到更新，而且索引重組使用的是冒泡排序法，效率比較低。同時，索引重組只能在單個文件內(nèi)進(jìn)行，無法跨文件重組索引。索引重組的方式有兩種：A：alterindex的regnize選項這是SQLSERVER20052008新的重組索引的方式，和dbccindexdefrag一樣，它默認(rèn)對表上所有分區(qū)進(jìn)行索引重組，不可以重新指定填充因子，因為只是索引葉子節(jié)點的重新排序。但它比dbccindexdefrag的選

7、項更豐富些；B：dbccindexdefrag這是SQLSERVER2000時重組索引的方法。注意：（1）碎片是不可避免，但并不是說一旦檢測到碎片就進(jìn)行碎片整理，有時少量碎片的整理工作反而會帶來更大的成本消耗，所以在不能確定索引整理能否帶來良好性能時，不要輕易進(jìn)行索引整理。（2）不管是內(nèi)部碎片還是外部碎片，即便進(jìn)行了碎片整理，也并不一定能夠完全消除，對于外部碎片，可以將表獨立存放在一個文件組上，并在文件初始化時分配足夠的磁盤空間，這樣的