第一篇:通過維護表的索引來提高數據的訪問速度
大多數SQL Server表需要索引來提高數據的訪問速度,如果沒有索引,SQL Server要進行表格掃描讀取表中的每一個記錄才能找到索要的數據。索引可以分為簇索引和非簇索引,簇索引通過重排表中的數據來提高數據的訪問速度,而非簇索引則通過維護表中的數據指針來提高數據的索引。
索引的體系結構:
為什麼要不斷的維護表的索引?首先,簡單介紹一下索引的體系結構。SQL Server在硬盤中用8KB頁面在數據庫文件內存放數據。缺省情況下這些頁面及其包含的數據是無組織的。為了使混亂變為有序,就要生成索引。生成索引後,就有了索引頁和數據頁,數據頁保存用戶寫入的數據信息。索引頁存放用於檢索列的數據值清單(關鍵字)和索引表中該值所在紀錄的地址指針。索引分為簇索引和非簇索引,簇索引實質上是將表中的數據排序,就好像是字典的索引目錄。非簇索引不對數據排序,它只保存了數據的指針地址。向一個帶簇索引的表中插入數據,當數據頁達到100%時,由於頁面沒有空間插入新的的紀錄,這時就會發生分頁,SQL Server 將大約一半的數據從滿頁中移到空頁中,從而生成兩個半的滿頁。這樣就有大量的數據空間。簇索引是雙向鏈表,在每一頁的頭部保存了前一頁、後一頁地址以及分頁後數據移動的地址,由於新頁可能在數據庫文件中的任何地方,因此頁面的鏈接不一定指向磁盤的下一個物理頁,鏈接可能指向了另一個區域,這就形成了分塊,從而減慢了系統的速度。對於帶簇索引和非簇索引的表來說,非簇索引的關鍵字是指向簇索引的,而不是指向數據頁的本身。
為了克服數據分塊帶來的負面影響,需要重構表的索引,這是非常費時的,因此只能在需要時進行。可以通過DBCC SHOWCONTIG來確定是否需要重構表的索引。下面舉例來說明DBCC SHOWCONTIG和DBCC REDBINDEX的使用方法。以SQL Server自帶的northwind數據作為例子
帶開SQL Server的Query analyzer輸入命令:
use northwind
declare @table_id int
set @table_id=object_id('orders')
dbcc showcontig(@table_id)
這個命令顯示northwind數據庫中的orders表的分塊情況,結果如下:
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.
通過分析這些結果可以知道該表的索引是否需要重構。表1.1描述了每一行的意義
Extents Scanned 表或索引中的長區頁數
Extent Switches DBCC遍歷頁時從一個區域到另
一個區域的次數
Avg. Pages per Extent 相關區域中的頁數
Scan Density Best Count是連續鏈接時的理想區
[Best Count:Actual Count] 域改變數,Actual Count是實際區
域改變數,Scan Density為100%
表示沒有分塊。
Logical Scan Fragmentation 掃描索引頁中失序頁的百分比
Extent Scan Fragmentation 不實際相鄰和包含鏈路中所有鏈
接頁的區域數
Avg. Bytes Free per Page 掃描頁面中平均自由字節數
Avg. Page Density (full) 平均頁密度,表示頁有多滿
從上面命令的執行結果可以看的出來,Best count為3 而Actual Count為5這表明orders表有分塊需要重構表索引。下面通過DBCC DBREINDEX來重構表的簇索引。
同樣在Query Analyzer中輸入命令:
use northwind
dbcc dbreindex('northwind.dbo.orders',pk_orders,90)
執行結果:
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Dbcc dbreindex參數說明:第一個參數為要重構的表明。第二個參數為需要重構的索引表識,‘’表示所有的索引。第三個參數為頁的填充因子,填充因子越大,頁越滿。
然後再用DBCC SHOWCONTIG查看重構簇索引後的結果:
use northwind
declare @table_id int
set @table_id=object_id('orders')
dbcc showcontig(@table_id)
返回結果如下:
DBCC SHOWCONTIG scanning 'Or
您正在看的SQLserver教程是:如何提高SQLSERVER的性能。ders' 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.
通過結果我們可以看到Scan Denity為100%表沒有分塊不需要重構表索引了。如果重構表的簇索引Scan Denity還小於100%的話可以重構表的全部索引。命令如下:
--use northwind
--dbcc dbreindex('northwind.dbo.orders',’’,90)
使用作業定時重構索引:
如果你的數據庫訪問非常頻繁的話,非常容易出現數據分塊的現象,因此你可以利用作業來幫你在系統相對空閒的時候重構你的索引。