通過(guò)維護(hù)表的索引來(lái)提高數(shù)據(jù)的訪(fǎng)問(wèn)速度：
 

大多數(shù)SQL Server表需要索引來(lái)提高數(shù)據(jù)的訪(fǎng)問(wèn)速度，如果沒(méi)有索引，SQL Server要進(jìn)行表格掃描讀取表中的每一個(gè)記錄才能找到索要的數(shù)據(jù)。索引可以分為簇索引和非簇索引，簇索引通過(guò)重排表中的數(shù)據(jù)來(lái)提高數(shù)據(jù)的訪(fǎng)問(wèn)速度，而非簇索引則通過(guò)維護(hù)表中的數(shù)據(jù)指針來(lái)提高數(shù)據(jù)的索引。
索引的體系結(jié)構(gòu)：
 

為什么要不斷的維護(hù)表的索引？首先，簡(jiǎn)單介紹一下索引的體系結(jié)構(gòu)。SQL Server在硬盤(pán)中用8KB頁(yè)面在數(shù)據(jù)庫(kù)文件內(nèi)存放數(shù)據(jù)。缺省情況下這些頁(yè)面及其包含的數(shù)據(jù)是無(wú)組織的。為了使混亂變?yōu)橛行�，就要生成索引。生成索引后，就有了索引�?yè)和數(shù)據(jù)頁(yè)，數(shù)據(jù)頁(yè)保存用戶(hù)寫(xiě)入的數(shù)據(jù)信息。索引頁(yè)存放用于檢索列的數(shù)據(jù)值清單（關(guān)鍵字）和索引表中該值所在紀(jì)錄的地址指針。索引分為簇索引和非簇索引，簇索引實(shí)質(zhì)上是將表中的數(shù)據(jù)排序，就好像是字典的索引目錄。非簇索引不對(duì)數(shù)據(jù)排序，它只保存了數(shù)據(jù)的指針地址。向一個(gè)帶簇索引的表中插入數(shù)據(jù)，當(dāng)數(shù)據(jù)頁(yè)達(dá)到100%時(shí)，由于頁(yè)面沒(méi)有空間插入新的的紀(jì)錄，這時(shí)就會(huì)發(fā)生分頁(yè)，SQL Server 將大約一半的數(shù)據(jù)從滿(mǎn)頁(yè)中移到空頁(yè)中，從而生成兩個(gè)半的滿(mǎn)頁(yè)。這樣就有大量的數(shù)據(jù)空間。簇索引是雙向鏈表，在每一頁(yè)的頭部保存了前一頁(yè)、后一頁(yè)地址以及分頁(yè)后數(shù)據(jù)移動(dòng)的地址，由于新頁(yè)可能在數(shù)據(jù)庫(kù)文件中的任何地方，因此頁(yè)面的鏈接不一定指向磁盤(pán)的下一個(gè)物理頁(yè)，鏈接可能指向了另一個(gè)區(qū)域，這就形成了分塊，從而減慢了系統(tǒng)的速度。對(duì)于帶簇索引和非簇索引的表來(lái)說(shuō)，非簇索引的關(guān)鍵字是指向簇索引的，而不是指向數(shù)據(jù)頁(yè)的本身。

為了克服數(shù)據(jù)分塊帶來(lái)的負(fù)面影響，需要重構(gòu)表的索引，這是非常費(fèi)時(shí)的，因此只能在需要時(shí)進(jìn)行�？梢酝ㄟ^(guò)DBCC SHOWCONTIG來(lái)確定是否需要重構(gòu)表的索引。下面舉例來(lái)說(shuō)明DBCC SHOWCONTIG和DBCC REDBINDEX的使用方法。以SQL Server自帶的northwind數(shù)據(jù)作為例子

  帶開(kāi)SQL Server的Query analyzer輸入命令：

use northwind

declare @table_id int

set @table_id=object_id('orders')

dbcc showcontig(@table_id)

這個(gè)命令顯示northwind數(shù)據(jù)庫(kù)中的orders表的分塊情況，結(jié)果如下：
DBCC SHOWCONTIG scanning 'Orders' table...

Table: 'Orders' (21575115); index ID: 1, database ID: 6

TABLE level scan performed.

- Pages Scanned................................: 20

- Extents Scanned..............................: 5

- Extent Switches..............................: 4

- Avg. Pages per Extent........................: 4.0

- Scan Density [Best Count:Actual Count].......: 60.00% [3:5]

- Logical Scan Fragmentation ..................: 0.00%

- Extent Scan Fragmentation ...................: 40.00%

- Avg. Bytes Free per Page.....................: 146.5

- Avg. Page Density (full).....................: 98.19%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

通過(guò)分析這些結(jié)果可以知道該表的索引是否需要重構(gòu)。表1.1描述了每一行的意義

 

 

 

 

 

 

 

 信息                          描述

Pages Scanned                    表或索引中的長(zhǎng)頁(yè)數(shù)

Extents Scanned                               表或索引中的長(zhǎng)區(qū)頁(yè)數(shù)

Extent Switches                                DBCC遍歷頁(yè)時(shí)從一個(gè)區(qū)域到另

一個(gè)區(qū)域的次數(shù)

Avg. Pages per Extent                       相關(guān)區(qū)域中的頁(yè)數(shù)

Scan Density                                    Best Count是連續(xù)鏈接時(shí)的理想?yún)^(qū)

[Best Count:Actual Count]                域改變數(shù)，Actual Count是實(shí)際區(qū)

域改變數(shù)，Scan Density為100%

表示沒(méi)有分塊。

Logical Scan Fragmentation            掃描索引頁(yè)中失序頁(yè)的百分比

Extent Scan Fragmentation               不實(shí)際相鄰和包含鏈路中所有鏈

接頁(yè)的區(qū)域數(shù)

Avg. Bytes Free per Page                  掃描頁(yè)面中平均自由字節(jié)數(shù)

Avg. Page Density (full)                     平均頁(yè)密度，表示頁(yè)有多滿(mǎn)

 

從上面命令的執(zhí)行結(jié)果可以看的出來(lái)，Best count為3 而Actual Count為5這表明orders表有分塊需要重構(gòu)表索引。下面通過(guò)DBCC DBREINDEX來(lái)重構(gòu)表的簇索引。

同樣在Query Analyzer中輸入命令：

use northwind

dbcc dbreindex('northwind.dbo.orders',pk_orders,90)

執(zhí)行結(jié)果：

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Dbcc dbreindex參數(shù)說(shuō)明：第一個(gè)參數(shù)為要重構(gòu)的表明。第二個(gè)參數(shù)為需要重構(gòu)的索引表識(shí)，‘’表示所有的索引。第三個(gè)參數(shù)為頁(yè)的填充因子，填充因子越大，頁(yè)越滿(mǎn)。

然后再用DBCC SHOWCONTIG查看重構(gòu)簇索引后的結(jié)果：

use northwind

declare @table_id int

set @table_id=object_id('orders')

dbcc showcontig(@table_id)

返回結(jié)果如下：

DBCC SHOWCONTIG scanning 'Orders' table...

Table: 'Orders' (21575115); index ID: 1, database ID: 6

TABLE level scan performed.

- Pages Scanned................................: 22

- Extents Scanned..............................: 3

- Extent Switches..............................: 2

- Avg. Pages per Extent........................: 7.3

- Scan Density [Best Count:Actual Count].......: 100.00% [3:3]

- Logical Scan Fragmentation ..................: 0.00%

- Extent Scan Fragmentation ...................: 33.33%

- Avg. Bytes Free per Page.....................: 869.2

- Avg. Page Density (full).....................: 89.26%

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

通過(guò)結(jié)果我們可以看到Scan Denity為100%表沒(méi)有分塊不需要重構(gòu)表索引了。如果重構(gòu)表的簇索引Scan Denity還小于100%的話(huà)可以重構(gòu)表的全部索引。命令如下：

--use northwind

--dbcc dbreindex('northwind.dbo.orders',’’,90)
 

使用作業(yè)定時(shí)重構(gòu)索引：


如果你的數(shù)據(jù)庫(kù)訪(fǎng)問(wèn)非常頻繁的話(huà)，非常容易出現(xiàn)數(shù)據(jù)分塊的現(xiàn)象，因此你可以利用作業(yè)來(lái)幫你在系統(tǒng)相對(duì)空閑的時(shí)候重構(gòu)你的索引。
關(guān)鍵字：SQL SERVER、數(shù)據(jù)庫(kù)、索引

【 頂部 】 【 關(guān)閉 】