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

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

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

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

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

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

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

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