創(chuàng)建索引是為了在檢索數(shù)據(jù)時(shí)能夠減少時(shí)間，提高檢索效率。創(chuàng)建好了索引，并且所有索引都在工作，但性能卻仍然不好，那很可能是產(chǎn)生了索引碎片，你需要進(jìn)行索引碎片整理。1、什么是索引碎片？由于表上有過(guò)度地插入、修改和刪除操作，索引頁(yè)被分成多塊就形成了索引碎片，如果索引碎片嚴(yán)重，那掃描索引的時(shí)間就會(huì)變長(zhǎng)，甚至導(dǎo)致索引不可用，因此數(shù)據(jù)檢索操作就慢下來(lái)了。有兩種類型的索引碎片：內(nèi)部碎片和外部碎片。內(nèi)部碎片：為了有效的利用內(nèi)存，使內(nèi)存產(chǎn)生更少的碎片，要對(duì)內(nèi)存分頁(yè)，內(nèi)存以頁(yè)為單位來(lái)使用，最后一頁(yè)往往裝不滿，于是形成了內(nèi)部碎片。外部碎片：為了共享要分段，在段的換入換出時(shí)形成外部碎片，比如5K的段換出后，有一個(gè)4k的段進(jìn)來(lái)放到原來(lái)5k的地方，于是形成1k的外部碎片。2、 如何知道是否發(fā)生了索引碎片？執(zhí)行下面的SQL語(yǔ)句就知道了(下面的語(yǔ)句可以在SQLServer2005及后續(xù)版本中運(yùn)行，用你的數(shù)據(jù)庫(kù)名替換掉這里的AdventureWorks)：SELECTobject_name(dt.object_id)Tablename,IndexName,dt.avg_fragmentation_in_percentASExternalFragmentation,dt.avg_page_space_used_in_percentASInternalFragmentationFROM(SELECTobject_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percentFROMsys.dm_db_index_physical_stats(db_id('AdventureWorks'),null,null,null,'DETAILED')WHEREindex_id<>0)ASdtINNERJOINsys.indexessiONsi.object_id=dt.object_idANDsi.index_id=dt.index_idANDdt.avg_fragmentation_in_percent>10ANDdt.avg_page_space_used_in_percent<75ORDERBYavg_fragmentation_in_percentDESC執(zhí)行后顯示AdventureWorks數(shù)據(jù)庫(kù)的索引碎片信息。圖3索引碎片信息使用下面的規(guī)則分析結(jié)果，你就可以找出哪里發(fā)生了索引碎片：ExternalFragmentation的值＞10表示對(duì)應(yīng)的索引發(fā)生了外部碎片；InternalFragmentation的值＜75表示對(duì)應(yīng)的索引發(fā)生了內(nèi)部碎片。如何整理索引碎片？有兩種整理索引碎片的方法：重組有碎片的索引：執(zhí)行下面的命令A(yù)LTERINDEXALLONTableNameREORGANIZE重建索引：執(zhí)行下面的命令A(yù)LTERINDEXALLONTableNameREBUILDWITH(FILLFACTOR=90,ONLINE=ON)也可以使用索引名代替這里的“ALL”關(guān)鍵字，重組或重建單個(gè)索引，也可以使用SQLServer管理工作臺(tái)進(jìn)行索引碎片的整理。3、什么時(shí)候用重組，什么時(shí)候用重建呢？當(dāng)對(duì)應(yīng)索引的外部碎片值介于10-15之間，內(nèi)部碎片值介于60-75之間時(shí)使用重組，其它情況就應(yīng)該使用重建。值得注意的是重建索引時(shí)，索引對(duì)應(yīng)的表會(huì)被鎖定，但重組不會(huì)鎖表，因此在生產(chǎn)系統(tǒng)中，對(duì)大表重建索引要慎重，因?yàn)樵诖蟊砩蟿?chuàng)建索引可能會(huì)花幾個(gè)小時(shí)，幸運(yùn)的是，MSQLServer2005開(kāi)始，微軟提出了一個(gè)解決辦法，在重建索引時(shí)，將ONLINE選項(xiàng)設(shè)置為ON，這樣可以保證重建索引時(shí)表仍然可以正常使用。雖然索引可以提高查詢速度，但如果你的數(shù)據(jù)庫(kù)是一個(gè)事務(wù)型數(shù)據(jù)庫(kù)，大多數(shù)時(shí)候都是更新操作，更新數(shù)據(jù)也就意味著要更新索引，這個(gè)時(shí)候就要兼顧查詢和更新操作了，因?yàn)樵贠LTP數(shù)據(jù)庫(kù)表上創(chuàng)建過(guò)多的索引會(huì)降低整體數(shù)據(jù)庫(kù)性能。我給大家一個(gè)建議：如果你的數(shù)據(jù)庫(kù)是事務(wù)型的，平均每個(gè)表上不能超過(guò)5個(gè)索引，如果你的數(shù)據(jù)庫(kù)是數(shù)據(jù)倉(cāng)庫(kù)型，平均每個(gè)表可以創(chuàng)建10個(gè)索引都沒(méi)問(wèn)題?？稍凇具x項(xiàng)】對(duì)窗口模式進(jìn)行合并/分離設(shè)置。5、數(shù)據(jù)庫(kù)磁盤碎片整理相關(guān)腳本/*讀取磁盤分區(qū)信息*/CREATEPROCEDURESP_ExtentInfoASDBCCExtentInfo(0)GO/*SQLServer磁盤碎片整理*/CREATEPROCEDURESP_ShrinkSpaces(@UsagePercentnumeric(2,2)=0.60--整理小于指定使用率的表空間，1為100%使用率無(wú)需整理)ASBEGIN--創(chuàng)建保存分區(qū)信息的臨時(shí)表CreateTable#ExtentInfo(fileidsmallint,pageidint,pg_allocint,ext_sizeint,obj_idint,index_idint,partition_numberint,partition_idbigint,iam_chain_typevarchar(50),pfs_bytesvarbinary(10))insertinto#ExtentInfoexecSP_ExtentInfo--使用游標(biāo)，對(duì)小于指定空間使用率的表進(jìn)行整理declare@Tablesysnamedeclare@Indexsysnamedeclare@IdentityNamesysnamedeclare@sqlvarchar(1000)declarecscursorforselect(selectnamefromsysobjectswhereid=obj_idandxtype='u'),--xtype='u的記錄為數(shù)據(jù)表(selectnamefromsysindexeswhereid=obj_idandindid=1)--indid=1的記錄為聚集索弓Ifrom#ExtentInfogroupbyobj_idhavingsum(pg_alloc)*1.0/max(ext_size)/count(*)<=@UsagePercentopencsfetchnextfromcsinto@Table,@Indexwhile@@FETCH_STATUS=0beginif@Tableisnotnullbeginif@Indexisnotnullbegin--重建聚集索引set@sql='alterindex'+@Index+'on'+@Table+'rebuild'print@sqlexec(@sql)endelsebegin--對(duì)于堆，清空并重新寫表或給自增列加聚集索引(128代表自增列)select@IdentityName=namefromsyscolumnswhereid=OBJECT_ID(@Table)andstatus=128if@@ROWCOUNT=0set@sql='select*into#ExtentTablefrom'+@Table+'truncatetable'+@Table+'insert'+@Table+'select*from#ExtentTable'elseset@sql='createclusteredindexExtentOperaPrimaryKeyon'+@Table+'('+@IdentityName+')dropindex'+@Table+'.ExtentOperaPrimaryKey'print@sqlexec(@sql)endendfetchnextfromcsinto@Table,@Indexendclosecsdeallocatecs--收縮當(dāng)前數(shù)據(jù)庫(kù)DBCCSHRINKDATABASE(0)--重新獲取分區(qū)信息truncatetable#ExtentInfoinsertinto#ExtentInfoexecSP_ExtentInfo--顯示當(dāng)前分區(qū)信息selectfileid,obj_id,index_id,partition_id,ext_size,object_name(obj_id)as對(duì)象名'，count(*)as'實(shí)際區(qū)數(shù)',sum(pg_alloc)as'實(shí)際頁(yè)數(shù)'，ceiling(sum(pg_alloc)*1.00/ext_size)*ext_sizeas最大可用頁(yè)數(shù)'，ceiling(sum(pg_all