SQLSERVER 索引維護(hù)
更新時(shí)間：2012-03-06 | 發(fā)布人：本站 | 點(diǎn)擊率：304 次
Pages & Extents（頁(yè)和擴(kuò)展盤(pán)區(qū)）
SQL Server 2000最基本的數(shù)據(jù)存儲(chǔ)單元是data page，1個(gè)8K的存儲(chǔ)空間。在分配存儲(chǔ)空間時(shí)，SQL Server 2000并不是每次分配1個(gè)page，基本的存儲(chǔ)空間分配單元是8個(gè)page的連續(xù)空間，稱(chēng)為extent。
關(guān)于SQL Server 2000的page、extents和index結(jié)構(gòu)，參考：MSDN - Pages and Extents, MSDN - Table and Index Architecture。
Page Split（頁(yè)切分）
SQL Server在Insert/Update時(shí)，如果要更新的page已經(jīng)存儲(chǔ)滿(mǎn)，無(wú)法容納下新的數(shù)據(jù)，則SQL Server將這個(gè)page的一半數(shù)據(jù)切分出來(lái)，重新分配一個(gè)page存放，然后再進(jìn)行Insert/Update操作，將以滿(mǎn)的數(shù)據(jù)頁(yè)切分成兩個(gè)數(shù)據(jù)頁(yè)的操作叫做page split。
不管是data page還是index page，都會(huì)發(fā)生page split。在Insert操作時(shí)，如果page上的free space小于要插入的記錄大小，將進(jìn)行page split；在Update時(shí)，如果table中存在變寬字段，變寬字段的長(zhǎng)度變大導(dǎo)致原page上free space不夠，將進(jìn)行page split。
Index Fragmentation（索引碎片）
SQL Server的index fragmentation有兩種：external fragmentation和internal fragmentation。
External fragmentation:
Index page的邏輯順序不連續(xù)時(shí)，叫做external fragmentation。Index建立時(shí)，index page的存儲(chǔ)在邏輯上都是連續(xù)的。在進(jìn)行insert操作時(shí)，可能需要在兩個(gè)索引之間插入這個(gè)新的索引。如果在索引插入位置的index page還有足夠的空間，則會(huì)直接在這個(gè)index page中插入新的索引值；如果在這個(gè)index page上空間已滿(mǎn)或者不夠新的索引值所需空間，則SQL Server會(huì)進(jìn)行page split，將插入位置的index page一部分?jǐn)?shù)據(jù)移走，以釋放出空間來(lái)插入新的索引，被移走的數(shù)據(jù)在其它位置重新分配新的page存放。這樣，隨著insert操作的增加，index page在邏輯上的連續(xù)程度就越來(lái)越低。
下圖示例索引剛剛建立好之后邏輯上是連續(xù)時(shí)的索引結(jié)構(gòu)：
假如此時(shí)需要插入索引值為2的新索引，則插入之后的索引結(jié)構(gòu)如下圖：
插入之后index page結(jié)構(gòu)在邏輯上變得不連續(xù)。
在通過(guò)index返回特定記錄，或者返回不用指定排序的記錄集時(shí)，external fragmentation不會(huì)對(duì)查詢(xún)性能產(chǎn)生太大影響。當(dāng)需要返回指定排序的記錄集時(shí)，排序過(guò)程中需要對(duì)邏輯上非連續(xù)的index page進(jìn)行額外處理，對(duì)于大數(shù)據(jù)量的表，如果index page非常多，external fragmentation很?chē)?yán)重，就需要消耗高昂的查詢(xún)成本。另外，external fragmentation對(duì)緩存效率產(chǎn)生影響。
External fragmentation使用兩個(gè)方面的指標(biāo)來(lái)描述，page的連續(xù)程度和extent的連續(xù)程度。
Internal fragmentation：
Index page中如果存儲(chǔ)空間未達(dá)到最大存儲(chǔ)容量，叫做internal fragmentation。不考慮fill factor因素的影響，index建立時(shí)，索引結(jié)構(gòu)邏輯上連續(xù)，并且每個(gè)index page都存儲(chǔ)滿(mǎn)，被充分利用。Delete操作會(huì)造成index page出現(xiàn)空閑；External fragmentation的示例中，insert操作時(shí)的page split也造成index page出現(xiàn)空閑。
嚴(yán)重的internal fragmentation，造成index page占用比實(shí)際所需大得多的存儲(chǔ)空間。查詢(xún)中進(jìn)行index scan時(shí)，增加了logical READS、I/O等操作，產(chǎn)生性能問(wèn)題。
Internal fragmentation使用頁(yè)的平均頁(yè)的空閑程度/利用程度作為指標(biāo)。
Fill factor:
如果有設(shè)置或者是SQL Server自動(dòng)維護(hù)了一個(gè)fill factor值，則在創(chuàng)建索引時(shí)，每一個(gè)index page都不會(huì)存儲(chǔ)滿(mǎn)，而根據(jù)fill factor值預(yù)留一部分空閑空間。在external fragmentation的示例中，假如第一個(gè)index page沒(méi)有存儲(chǔ)滿(mǎn)，則在插入索引值為2的新索引時(shí)，就不需要將這個(gè)index page進(jìn)行split，從而可以改善insert操作。
Fill factor用于需要頻繁進(jìn)行insert/update操作的表中，避免大量的page split出現(xiàn)。顯然，fill factor的使用類(lèi)似于internal fragmentation，但對(duì)大量的insert操作以及各種data page的結(jié)構(gòu)帶來(lái)極大的改善。對(duì)于fill factor，不太方便準(zhǔn)確的評(píng)估什么樣的值最佳，Microsoft建議讓SQL Server自動(dòng)維護(hù)。不恰當(dāng)?shù)膄ill factor設(shè)置，同internal fragmentation一樣，影響SQL Server性能。
DBCC SHOWCONFIG
用于顯示數(shù)據(jù)、索引fragmentation信息。
DBCC SHOWCONTIG (TblUserItem,PK_TblUserItem)
顯示表TblUserItem中索引PK_TblUserItem的fragmentation信息。
DBCC SHOWCONTIG (TblUserItem) WITH ALL_INDEXES
顯示表TblUserItem所有索引的fragmentation信息。
DBCC SHOWCONTIG WITH ALL_INDEXES
顯示當(dāng)前數(shù)據(jù)庫(kù)中所有索引的fragmentation信息。
執(zhí)行的結(jié)果示例如下：
DBCC SHOWCONTIG scanning 'TblUserItem' table...
Table: 'TblUserItem' (1077578877); index ID: 1, database ID: 8
TABLE level scan performed.
- Pages Scanned................................: 56933
- Extents Scanned..............................: 7563
- Extent Switches..............................: 7565
- Avg. Pages per Extent........................: 7.5
- Scan Density [Best Count:Actual Count].......: 94.07% [7117:7566]
- Logical Scan Fragmentation ..................: 0.00%
- Extent Scan Fragmentation ...................: 0.03%
- Avg. Bytes Free per Page.....................: 114.3
- Avg. Page Density (full).....................: 98.59%
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Page Scanned:實(shí)際掃描的page數(shù)量�？梢詮拿總�(gè)數(shù)據(jù)行的大小、總的行數(shù)大致計(jì)算出實(shí)際所需的data page數(shù)量，如果Page Scanned數(shù)量遠(yuǎn)遠(yuǎn)超過(guò)計(jì)算出的實(shí)際data page數(shù)量，則internal fragmentation比較嚴(yán)重。
Extents Scanned:理想值為將Page Scanned/8圓整為最小整數(shù)。如果Extents Scanned大于理論值，則存在一定程度的external fragmentation。
Extent Switches:理想值為Extents Scanned減1，超過(guò)這個(gè)值說(shuō)明存在external fragmentation。
Avg. Pages per Extent:理想值為8，小于8則存在external fragmentation。
Scan Density [Best Count:Actual Count]:這是DBCC SHOWCONTIG返回的最有意義的一個(gè)值，為理想的extents數(shù)量比實(shí)際的extents數(shù)量，反應(yīng)external fragmentation的重要統(tǒng)計(jì)信息之一。理想值為100%，不能低于60%。
Logical Scan Fragmentation:另外一個(gè)非常有意義的值，指示page的非連續(xù)程度，反應(yīng)external fragmentation的重要統(tǒng)計(jì)信息之一。應(yīng)當(dāng)在0%-10%之間，不能高于15%。
Extent Scan Fragmentation:指示extents的非連續(xù)程度，理想值為0%。
Avg. Bytes Free per Page:平均每page上的空閑字節(jié)數(shù)。過(guò)高的值表明存在internal fragmentation，但是需要將fill factor因素排除。
Avg. Page Density (full):與Avg. Bytes Free per Page對(duì)立的一個(gè)百分比參數(shù)，較低的值表明存在internal fragmentation。
另外DBCC SHOWCONTIG還有幾個(gè)可選參數(shù)可以使用，具體參考Online Help。
Resolving fragmentation issues
1. Drop原來(lái)的索引再重建這些索引
這個(gè)過(guò)程中索引被drop和rebuild，會(huì)使這個(gè)期間所有的查詢(xún)阻塞；對(duì)所有的clustered index和non-clustered index使用該方法，可能會(huì)導(dǎo)致non-clustered index重建兩次。
優(yōu)點(diǎn)是索引徹底重建，達(dá)到最理想的狀況。如果external和internal fragmentation都相當(dāng)嚴(yán)重，應(yīng)當(dāng)使用該方法。
2. 使用DROP_EXISTING子句
使用DROP_EXISTING子句，可以避免non-clustered index被重建兩次。
3. DBCC DBREINDEX
可以?xún)H指定Table名字，而無(wú)須指定索引名稱(chēng)，該命令自動(dòng)將Table的所有索引進(jìn)行重建，這樣比寫(xiě)多條DROP INDEX和CREATE INDEX語(yǔ)句進(jìn)行操作要方便。這個(gè)命令同時(shí)將Table的PRIMARY KEY和UNIQUE約束、STATISTICS重建，無(wú)須額外對(duì)這些約束和STATISTICS進(jìn)行操作。
DBCC DBREINDEX能夠比較充分的利用多CPU進(jìn)行處理，對(duì)數(shù)據(jù)量相當(dāng)大和fragmentation非常嚴(yán)重的表操作時(shí)會(huì)比較快。
該方法在一個(gè)事務(wù)中完成操作，在數(shù)據(jù)文件中需要有足夠的free space來(lái)滿(mǎn)足將所有的索引及相關(guān)的一些對(duì)象進(jìn)行重建，否則操作可能失敗，或者是重建的不十分徹底，例如重建完后logical fragmentation可能仍大于0。對(duì)于數(shù)據(jù)量非常大的表，所需的free space也更多，應(yīng)當(dāng)特別注意這一點(diǎn)。
4. DBCC INDEXDEFRAG
DBCC INDEXDEFRAG分兩個(gè)步驟進(jìn)行操作，首先對(duì)各個(gè)index page進(jìn)行壓縮，釋放出多余的page；然后重組index page的各個(gè)根節(jié)點(diǎn)，使得index page的邏輯順序與物理存儲(chǔ)順序一致，即在物理存儲(chǔ)方向上保證邏輯順序是連續(xù)的。
4種方法中，其它三種都必須在數(shù)據(jù)庫(kù)offline情況下進(jìn)行，因?yàn)樵诓僮髌陂g會(huì)導(dǎo)致使用這些索引的所有查詢(xún)阻塞。DBCC INDEXDEFRAG可以在數(shù)據(jù)庫(kù)online的情況下執(zhí)行，但是整理不夠徹底。因?yàn)橐环矫�，在�?zhí)行期間會(huì)忽略被lock的 index page，另一方面它不會(huì)新分配page進(jìn)行重排序，只是在原來(lái)已分配的page空間里進(jìn)行重組。這個(gè)命令的目標(biāo)也就是使index page的邏輯順序與物理存儲(chǔ)順序一致，如果邏輯上相鄰的兩個(gè)page或extent的物理存儲(chǔ)之間存在間隔，DBCC INDEXDEFRAG不會(huì)采取操作消除這種物理存儲(chǔ)間隔。因此如果index page所占用的空間非常大時(shí)，訪問(wèn)index page空間可能會(huì)增加磁頭定位和移動(dòng)的開(kāi)銷(xiāo)，從而在一定程度上增加了I/O操作成本。
專(zhuān)家建議：60%<Scan Density<75%、10<Logical fragmentation<15時(shí)，使用DBCC INDEXDEFRAG；Scan Density<60%、Logical fragmentation>15時(shí)，使用DBCC DBREINDEX。
另外，在table設(shè)計(jì)方面，可以考慮以下幾點(diǎn)：
1. 對(duì)于Insert/Update操作頻繁的table，選擇一個(gè)合適的fill factor。
2. 將變寬字段設(shè)計(jì)為等寬字段。例如物料號(hào)、訂單號(hào)、客戶(hù)代碼、供應(yīng)商代碼等。
3. 設(shè)立刪除標(biāo)記而不是物理刪除數(shù)據(jù)。
例如有些大型系統(tǒng)，一個(gè)對(duì)象可能會(huì)有幾十個(gè)字段。通常情況下的做法是用一個(gè)table容納所有這些字段，但是出于系統(tǒng)訪問(wèn)效率方面考慮，可以使用兩個(gè) table來(lái)存儲(chǔ)。主表存放關(guān)鍵性、訪問(wèn)最頻繁的字段屬性，盡量不使用變寬字段；從表存放附加的、描述性的、訪問(wèn)比較少的字段屬性。這樣雖然是一對(duì)一關(guān)聯(lián)的表，但是對(duì)于大多數(shù)情況下對(duì)于只需要訪問(wèn)主表屬性的查詢(xún)，可以做到極大的提高訪問(wèn)性能。
域名注冊(cè)知識(shí)
虛擬主機(jī)知識(shí)
網(wǎng)站建設(shè)知識(shí)
網(wǎng)絡(luò)推廣知識(shí)
網(wǎng)絡(luò)營(yíng)銷(xiāo)知識(shí)
常見(jiàn)技術(shù)問(wèn)題
QQ空間
新浪微博
開(kāi)心網(wǎng)
人人網(wǎng)
