數 據 庫 索 引 對 數 據 操 作 的 影 響

□付利軍，楊金勞

(運城農業職業技術學院，山西 運城 044000)

一、建立索引的理由

索引是對數據庫表中一列或多列的值進行排序的一種結構，創建索引可以大大提高系統的性能。首先，建立索引主要是為了加快表的查詢速度，因為對于創建了索引的列幾乎是立即響應，而不創建索引的列則需要等待較長的時間。這也是創建索引的最主要的原因。其次，創建索引還能加速連接、ORDER BY和GROUP BY語句的執行。因為連接、ORDER BY和GROUP BY都需要數據檢索，在建立索引后，其數據檢索速度就會加快，從而也就加速了連接等操作。再次，通過創建惟一索引，可以保證數據庫表中每一行數據的惟一性。除此之外，通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。

二、不合理的使用索引對數據操作的影響

在某些表中創建索引后，數據檢索的速度有所提高，但對表的操作(新增、修改、刪除)速度明顯變慢，而在某些表中創建索引后，數據檢索的速度降低，表的操作也受到影響。

SQL Server的索引數據保存在數據分頁中，在對表進行新增、修改、刪除時，SQL Server需要維護索引數據，因此在表中創建索引后，對數據的新增、修改、刪除會造成一定的影響，當然這種影響不一定是負面的，如果索引合理，對于帶條件的修改、刪除操作，則可以使用索引定位數據，所以合理的索引會提高帶條件的修改、刪除操作的處理效率。

一般來說，不合理的索引表現在以下幾個方面：

在表中創建了過多的索引。SQL Server 2000支持為每個表創建1個聚集索引，249個非聚集索引。不過，就一般情況而言，如果不是相對固定的表(很少做數據新增、修改、刪除)，建議每個表的索引不要超過15個，過多的索引會因為SQL Server維護索引的工作而大大影響表的新增、修改、刪除處理速度。索引應該只建立在經常用于排序和條件的列上。

過多的索引還會影響Transact-SQL 批處理語句的執行，SQL Server生成執行計劃時，會生成資源盡可能小的方案，過多的索引必須導致SQL Server評估更多的Transact-SQL批處理語句執行計劃，而這部分處理是最耗CPU資源的。

在鍵值大的列上建立索引。SQL Server 2000支持的單個索引最大字節數為900字節，不過，由于索引存放在數據頁中，因此越小的索引檢索的效率越高(越小的索引意味著每個數據頁包含的索引鍵值越多，檢索數據所需要讀取的數據頁就越少，大大降低了磁盤I/O)。對于大量字符數據的檢索，應該考慮配置全文檢索。

不合理地使用聚集索引。由于聚集索引決定表中記錄的存儲順序，因此，如果將聚集索引建立在不合適的字段上，會導致新增、修改、刪除記錄時，大量數據的移動。如聚集索引不適用于頻繁更改的行，因為數據必須按聚集索引的鍵值改變行的物理順序存儲，如果聚集索引列中的數據頻繁修改，將導致聚集索引頻繁修改，數據整行移動，消耗大量的系統資源。例如，在一個記錄用戶操作的日志記錄表中，由于日志是按時間順序記錄的，在用戶ID上建立聚集索引就不太合適。

不合理地使用填充因子。在創建聚集索引時，表中的數據按照索引列中的值的順序存儲在數據庫的數據頁中。在表中插入新的數據行或更改索引列中的值時，SQL Server可能必須重新組織表中的數據存儲，以便為新行騰出空間，保持數據的有序存儲。這同樣適用于非聚集索引。添加或更改數據時，SQL Server可能不得不重新組織非聚集索引頁中的數據存儲。向一個已滿的索引頁添加某個新行時，SQL Server把大約一半的行移到新頁中以便為新行騰出空間。這種重組稱為頁拆分。頁拆分會降低性能并使表中的數據存儲產生碎片。創建索引時，可以指定一個填充因子，以便在索引的每個葉級頁上留出額外的間隙和保留一定百分比的空間，供將來表的數據存儲容量進行擴充和減少頁拆分的可能性。填充因子的值是從0～100的百分比數值，指定在創建索引后對數據頁的填充比例。值越小則數據頁上的空閑空間越大，這樣可以減少在索引增長過程中對數據頁進行拆分的需要，但需要更多的存儲空間。提供填充因子選項是為了對性能進行微調。但是，使用sp_configure系統存儲過程指定的服務器范圍的默認填充因子，在大多數情況下都是最佳的選擇。

如果為經常做新增、修改、刪除記錄的表建立索引時，指定了過大的填充因子，則會因為頻繁的頁拆分操作而大大降低數據處理的效率。由于頁拆分也會導致表中的數據存儲產生碎片，所以查詢性能也會受到影響。一般來說，數據操作(新增、修改、刪除)越頻繁，填充因子的設置越小，以調高數據處理效率。反之，則填充因子應該設置得越大以節約存儲空間。

三、索引建立建議

雖然索引很重要，但也不是越多越好：一是因為創建索引要花費時間，這種時間隨著數據量的增加而增加。二是因為創建索引要占用存儲空間。除了數據表占數據空間之外，每一個索引還要占一定的物理空間，如果要建立聚簇索引，那么需要的空間就會更大。三是因為索引雖然加快了檢索速度，卻減慢了數據修改的速度。因為每當執行一次數據修改(包括插入、刪除、更新)時，就需要進行索引的維護，對建立了索引的列執行操作要比未建立索引的列執行修改操作所花的時間長，修改的數據越多，涉及維護索引的開銷也就越大。

過多的索引影響數據操作(新增、修改、刪除)，過少的索引影響查詢效率，所以索引的建立應該恰到好處。在確定是否應該建立索引的時候，除了索引合理性的考慮外，還應該參考下面幾方面：

選擇性。選擇性是符合查詢條件的記錄占總記錄數的百分比，百分比越小，選擇性高，越適合于建立索引。

數據密度。數據密度是1除以代表鍵惟一的記錄數。數據密度越小，該字段越適合建立索引。

數據的使用時機。索引可以建立，使用完成后再刪除。這適用于在某個時間要處理的數據。比如：每個月都要做一些月報，平時是不做的，那么在做月報的時候，可以根據取數的需要，建立適當的索引，在做完月報后刪除索引，以免影響平時的數據處理。

索引可以先刪除，等處理完后再建立以適應不同的操作要求。比如，要對表中的數據進行大批量的更新和插入時，應先刪除索引，等大批量數據插入或更新完成后，再重新建立索引。因為在更新或插入時需要花費代價來進行索引的維護。

多嘗試。由于更新索引結構不會對前段程序的數據存取，因此對于是否需要建立索引不確定的處理，可以分別測試建立索引與不建立索引后，對查詢及數據處理的影響，以根據實際測試的結果來確定是否建立索引，或者調整索引。

一般來說，可以在以下的列考慮建立索引：(1)用作查詢條件的列，如主鍵。一般而言，存取表的最常用的方法是通過主鍵來進行。因此，我們應該在主鍵上建立索引。(2)在按排序順序頻繁檢索的列和某一范圍內頻繁搜索的列。(3)連接中頻繁使用的列。因為用于連接的列如果按順序存放，系統可以很快執行連接。 在如下情況的列上一般不考慮建立索引：(1)很少或從來不在查詢中引用的列。因為系統很少或從來不根據這個列的值去查著行，因此有索引或者無索引，并不能提高查詢速度。相反，由于增加了索引，反而降低了系統的維護速度和增大了空間需求。(2)只有兩個或若干個值的列(如性別：男或女)，也得不到建立索引的好處。這是因為這些列的取值很少，在查詢的結果中，結果集的數據行占了表中數據行的很大比例，即需要在表中搜索的數據行的比例很大。增加索引，并不能明顯加快檢索速度。(3)小表(行數很少的表)一般也沒有必要創建索引。因為在小表中通過索引查找行可能會比簡單地進行全表掃描還慢。(4)當數據修改的性能比數據查詢的性能更重要時不應創建索引。這是因為，修改性能和檢索性能是互相矛盾的。當增加索引時，會提高檢索性能，但是會降低修改性能。當減少索引時，會提高修改性能，降低檢索性能。因此，當修改性能遠遠大于檢索性能時，不應該創建索引。

總之，創建索引是為了加速對表中數據的檢索，可以提高系統的性能。但不合理的使用索引會帶來負面的影響，使表的操作受到影響，所以在表中要創建合適的索引，以提高數據庫的性能。

參考文獻：

[1]劉世峰等.數據庫基礎與應用[M].北京: 中央廣播電視大學出版社, 2003.

[2]趙松濤，吳維元.SQL Server 2000 系統管理實錄[M]. 北京: 電子工業出版社, 2006.

[3]余金山.SQL Server 2000/2005 數據庫開發實例入門與提高[M].北京: 電子工業出版社, 2005.

[4]薩師煊，王珊. 數據庫系統概論[M]. 北京:高等教育出版社, 2000.

[5]鄒建.中文版SQL Server 2000 開發與管理應用實例[M].北京: 人民郵電出版社, 2005.