一、簡介

自從你和你的團隊成功的開發和部署了一個INTERNET網站,已經過去數月了,這個網站在很短的時間內吸引了數千用戶前來注冊和使用,因此你有了一個非常滿意的客戶。包括你和你的團隊、管理層、客戶，每個人都非常高興。

生活并不總是一帆風順的。當站點的用戶開始日均高速增長的時候，問題隨即出現了，客戶發來郵件開始抱怨網站性能太慢，同時稱網站正在丟失客戶。

你開始調查這個系統，很快你發現當系統訪問或更新數據的時候，速度非常慢。打開數據庫一看，數據庫的記錄增加的很快，有些表的記錄達到了成千上萬行，測試團隊在產品數據庫上做了一個測試，結果發現在測試服務器上僅2/3秒就能完成的一個處理過程，現在需要5分鐘。”

這個古老的故事發生在全球范圍內的數以千計的系統身上。包括我在內，幾乎每個開發人員在他或她的開發過程中會碰到同樣的事情。我知道為什么這樣的情形會發生，同時我也知道如何去克服它。

二、閱讀范圍

請注意本一系列文章討論的主要的焦點是“事務性的SQLServer數據庫數據訪問性能優化”，但大部分優化技術同樣適用于其他的數據庫。

我將要討論的優化技術僅僅適用于軟件開發人員。作為一個開發者，你需要跟隨我關注的問題，確認你已經作了所有能做的事情，去優化你已經寫的或將要寫的數據訪問代碼。數據庫管理人員(DBA)同樣在優化和提高性能上扮演了很重要的角色，但是DBA領域的優化將不屬于這篇文章討論的范圍。

三、開始優化一個數據庫

當基于數據庫的應用系統放慢的時候，99%的可能是系統的數據訪問過程沒有優化，或者沒有使用***的方式。所以你需要回顧和優化你的數據訪問/操作過程，提高系統的全局性能。接下來我們通過一步一步的方式開始我們的優化任務。

***步：在列上采用正確的索引

有些人可能爭論實施正確的索引是否是數據庫優化過程的***步。但是我認為在數據庫應用正確的索引是***位的。原因有2點：

1.在一個產品系統里，它將使你在很快的時間內提高盡可能大的性能。

2.創建數據庫索引不需要你做任何的系統修改，因此不需要任何重新編譯和部署

如果你發現有當前的數據庫沒有很好的處理索引，你建了索引，結果就是性能的快速提升。然而，如果索引已經處理了，我們進入下面的步驟。

什么是索引

我相信你已經明白了什么是索引，但是，我仍舊看到很多人對索引不太清楚。讓我們再一次弄明白什么是索引，請看下面的小故事。

很久以前，在一個古城市里有一個很大的圖書館，里面有數以千計的圖書，圖書凌亂的存放在書架上。因此，一旦有讀者向圖書員索要一本圖書，圖書員除了一本一本的檢查圖書,看是否匹配讀者索要的圖書，其它沒有更好的辦法。發現一本渴望的圖書往往需要花費圖書員數個小時。同時讀者也不得不等很長的時間。

[這看起來象一個沒有主鍵的表，當在表里進行搜索數據的時候，數據庫引擎需要遍歷全部的數據來查找相關的記錄，所以運行起來非常慢。]

當讀者和圖書每天都在大量增加的時候，圖書員的工作越來越繁重。有一天，有一個智者來到圖書館，看到圖書員的繁重的工作，建議他給每一本書編號，同時按順序碼放在書架上。“我可以從中得得什么好處?”圖書員問，那個智者回答到:“如果有讀者通過給你一個書號來索要圖書，你很快就能發現在哪個書架上存放了包含該書號的圖書，然后在這個書架上，你同樣能很快的找到需要的圖書”

[給書編號就象在數據表里創建一個主鍵,當你在一個表里創建了一個主健后,系統就創建了一個聚集索引樹,所有的包含記錄的數據頁按照主鍵的值在文件系統中進行排序.每一個數據頁內部也同樣按照主鍵的值進行排序.所以,當你向數據庫請求任何一個數據行的時候,首先數據庫服務器使用聚焦索引找到合適的頁(象首先發現書架一樣),接著在頁里查找包含主鍵值的記錄(象在書架發現一本書)]

“這正是我所需要的”,興奮的圖書員開始給書編號,接著把它們排列在不同的書架上,他花費了一天的時間來排序.在那天快結束的時候,他做了測試,結果發現幾乎不用花費時間就能找到一本書.圖書員高興極了.

[這正是你創建了主鍵后所發生的事情.首先,創建了聚焦索引,接著數據頁在物理文件里按照主鍵的值被排序.有一點我想你應該很容易理解,因為數據僅僅只能使用一列的值作為憑證來排序,所以一個表只能創建一個聚焦索引.就象圖書只能使用一個標準即書號來排序一樣.]

等一等,問題還沒有被完全解決,在接下來的時間里,有個讀者沒有圖書的編號,只有圖書的名字,他想通過書名索要圖書,如何辦呢?可憐的圖書員只能按照從1到N來查遍所有已經編號的圖書.如果圖書存放在67號書架上,他可能需要20分鐘,相比早間圖書沒有被排序的時候,他所花費的2-3個小時.這確實有一個進步.但是和花費30秒通過書號查找一本書比較起來,,20分鐘仍舊是一個不短的時間.還有沒有更好的辦法呢?他問那個智者。

[假設你有一個產品表,如果你只有一個ProductID主鍵而沒有其它的索引,上述的情況同樣會發生,所以,當使用產品名字來搜索的時候,數據引擎只能遍歷文件里所有物理排序的數據頁,沒有其它的辦法.]

那個智者告訴圖書員:因為你已經按照書號對圖書做了排序,你不能使用其它的憑證重新排序,所以,較好的方法是創建一個包含書名和與之對應的編號的目錄或索引,在這個目錄上,按照圖書的字母順序排序,并使用阿拉伯字母進行分組,例如,當有人想查找DatabaseManagementSystem這本書的時候,你使用下列的規則就能發現這本書

1.在書名目錄里跳到D章,找到包含你的書名的圖書.

2.得到這本書的書號,然后用書號去查找這本書

“你真是一個天才”,圖書員喊到,他立即花費了一些時間創建了書名的目錄,通過一個快速的測試,他發現使用書名來查詢僅僅需要1分鐘,其中30秒查找書的編號,30秒用編號來找書.

圖書員想到,讀者還可能使用其它的憑證來查找圖書,例如作者的名字,所以他為作者創建了同樣的目錄.在創建了這些目錄后,圖書員可以使用這些憑證在1分鐘內找到圖書.圖書員的繁重的工作終于結束了,許多讀者也因為很快的查找到圖書而聚集在圖書館,圖書館變的非常熱鬧起來.

圖書員隨后開始過著他的快樂的生活,故事結束了.

到這里，現在我確信你已經明白了什么是索引,為什么它們如此重要以及它們的內部工作原理,,例如,我們有一個已創建聚焦索引的產品表Products,因為當創建了主鍵的時候,隨即就創建了聚焦索引。我門應當在Productname列創建一個非聚焦索引,一旦我們這樣作了,數據庫引擎就為非聚焦索引創建一個索引樹,象故事里的書名目錄，按照產品的名字在索引頁里排序。每個索引頁包含一定范圍的產品名字和與之對應的ProductID，所以當使用產品名字作為憑證搜索的時候，數據庫引擎首先查詢產品名字的非聚焦索引樹來發現這本書的主鍵productID，一旦發現，數據庫引擎就使用主鍵ProductID來搜索聚焦索引樹，從而并得到正確的結果。

索引樹的工作原理如下圖：

上圖被稱做為B+樹，中間的節點包含一定數量的值，指示數據庫引擎當從跟節點搜索一個索引值的時候如何遍歷.如果這是一個聚焦索引樹，頁節點是物理數據頁.如果是非聚焦索引樹，頁節點包含包含索引值和與之對應的聚焦索引值.

通常，在索引樹里發現需要的值并且轉到目標數據記錄，對于數據庫引擎來說花費的時間是很短的，所以，在數據庫應用索引極大的提高了數據的檢索操作.

請跟隨下列的步驟確保正確的索引包含在你的數據庫里。

確保數據庫的每個表有一個主健

這么做會確保每個表有一個聚焦索引，通過主健的值，表的數據頁通按物理順序排列在磁盤上。所以,任何使用主健的數據檢索操作,任何在主健字段的排序操作都能非常迅速的檢索數據。

在這些列上創建非聚焦索引

經常被作為搜索憑證的列

用來聯合其它表的列

用來作為外健的列

用來排序的列

高選擇性列

Xml類型

下面是一個創建索引的命令的例子

CREATEINDEX
NCLIX_OrderDetails_ProductIDON
dbo.OrderDetails(ProductID)

你也可以使用SQL Server控制臺在需要的列上創建索引

第二步：創建正確的復合索引

現在，你是否已經在數據庫創建了所有的適合的索引?假設，在一個Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)，你已經在外鍵(ProductID)創建了索引，如果ProductID是一個高選擇性列，任何在where語句里使用索引列(ProductID)的檢索數據的SELECT查詢都會運行的非常快嗎?

對，相對沒有在外鍵創建索引的情況(這需要全部數據頁的遍歷)來說，這是非常快的，但是，還有進一步提升的空間.

讓我們假設：Sales表包含10,000行數據，下面的SQL語句選擇400行。

SELECTSalesDate,SalesPersonIDFROMSalesWHEREProductID=112

首先讓我們弄明白在數據庫引擎怎么執行SQL語句的:

1.Sales表有在ProductID列一個非聚焦索引，所以，首先查詢非聚焦索引樹，發現包含ProductID=112的入口。

2.包含ProductID=112入口的索引頁同樣同樣也包含了聚焦索引的值(所有的主健的值，即SalesID)

3.對于每一個主健(共400個)，數據庫引擎進入聚焦索引樹來發現正確的行的位置

4.對于每一個主健，一旦發現正確的行的位置，數據庫引擎會從匹配的行得到SalesDate和SalesPersonID的列的值。

請注意，在上述的步驟中，對于每一個ProductID=112的主鍵入口(共400個)，數據庫引擎必須搜索聚焦索引樹400次，來檢索附加的列(SalesDate,SalesPersonID)。

讓我們猜想一下,如果非聚焦索引不但包含了聚焦索引的值(主健)，同時還包含查詢里標注的其他的2個列(SalesDate,SalesPersonID)的值，數據庫引擎就不用執行上述的第3步和第4步，只須進入ProductID的列的非聚焦索引樹，從索引頁上讀取3個列的值，這樣運行的速度不是更快嗎?

幸運的是，有一種辦法來實施這種特點，這就是復合索引。你可以在表的列上創建復合索引，標明哪些列是和聚焦索引一起的應該附加存儲的列。下面是一個在表Sales表的列ProductID創建復合索引的例子。

CREATEINDEXNCLIX_Sales_ProductID--Indexname
ONdbo.Sales(ProductID)--Columnonwhichindexistobecreated
INCLUDE(SalesDate,SalesPersonID)--Additionalcolumnvaluestoinclude

請注意，創建復合索引應當包含少數幾個列，并且這些列經常在select查詢里使用。在復合索引里包含太多的列不僅不會給你帶來太多好處。而且由于使用相當多的內存來存儲復合索引的列的值，其后果是內存溢出和性能降低。

當創建復合索引的時候，盡量使用DatabaseTuningAdvisor(數據庫優化顧問)的幫助。

我們知道，一旦一個SQL開始運行，SQLSERVER引擎優化器基于以下幾點動態的產生不同的檢索計劃。

數據量

統計

索引變化

TSQL的參數值

服務器的負載

這意味著：對于一個特殊的SQL語句，在產品服務器上的執行計劃可能和在測試服務器上的執行計劃不近相同，甚至表和索引結構一樣。這同樣也表明，一個在測試服務器上創建的索引可能會加速測試服務器上的性能，但是在產品服務器上的同樣的索引可能不會帶給你任何益處。為什么?因為在測試環境下的SQLSEVVER執行計劃可能使用創建的索引，因此給你很好的性能，但是，在產品服務器上的執行計劃可能出于下列的原因而根本不使用新創建的索引。例如：一個非聚焦索引列在產品服務器上不是高選擇性列，而在測試服務器上是高選擇性列.

所以，當創建索引的時候，我們需要弄明白這一點：索引是執行引擎用來提高速度的。但是我們該如何去做呢?

答案是我們必須在測試服務器上模擬產品服務器的負載，接著創建索引，以及測試他們。只有這樣，在測試服務器上能提高性能的索引，才能更有可能在產品服務器上提高性能。

這么做應該很困難，但幸運的是，我們有一些好用的工具去實現它，請跟隨下面的指導：

1：使用SQLprofiler捕獲產品服務器上的痕跡。使用Tuningtemplate(我知道，有人建議不要在產品服務器上使用SQLprofiler,但有些時候，你不得不在產品服務器上診斷性能問題的時候使用它)，如果你不熟悉這個工具，或者你想了解更多的關于SQLprofiler的知識，請閱讀http://msdn.microsoft.com/en-us/library/ms181091.aspx

2.利用上一步產生的跟蹤文件，用數據庫優化顧問在測試數據庫創建相似的負載,從優化顧問得到一些建議，特別是創建索引的建議，你很可能從優化顧問那里獲得比較實際的建議。因為優化顧問使用產品服務器產生的跟蹤文件來裝載測試服務器，所以能產生最可能好的索引建議。如果你不熟悉優化顧問工具，或者你想了解更多的關于使用優化顧問的的資料，請閱讀：http://msdn.microsoft.com/en-us/library/ms166575.aspx.

第三步：如果有碎片發生，重新整理它

到了這里，如果你已經在表里創建了所有正確的索引，但是，你可能還沒有獲得所希望的良好的性能。什么原因呢?有一種可能是出現了索引碎片。

1、什么是索引碎片

索引碎片是這樣一種情形：由于在表里大量的插入、修改、刪除操作而使索引頁分裂。如果索引有了高的碎片，有兩種情況，一種情況是掃描索引需要花費很多的時間，另一種情況是在查詢的時候索引根本不使用索引,都會導致性能降低。

有2種類型的碎片：

內部破碎：由于索引頁里的數據插入或修改操作而發生，以數據作為稀疏矩陣的形式的分布而結束，這將導致數據頁的增加，從而增加查詢時間。

外部破碎：由于索引/數據頁的數據插入或修改而發生，以頁碼分離和在文件系統里不連貫的新的索引頁的分配而結束，數據庫服務器不能利用預讀操作的優點，因為：下一個相關聯的數據頁不臨近，而且這些相關連的下面的頁碼可能在數據文件的任何地方。

2、如何知道索引破碎是否已經發生?

在數據庫執行下面的SQL語句(下面的語句在SQLserver2005及以后的版本運行正常，以你的目標數據庫的名字取代AdventureWorks’)

SELECTobject_name(dt.object_id)Tablename,si.name
IndexName,dt.avg_fragmentation_in_percentAS
ExternalFragmentation,dt.avg_page_space_used_in_percentAS
InternalFragmentation
FROM
(
SELECTobject_id,index_id,avg_fragmentation_in_percent,avg_page_space_used_in_percent
FROMsys.dm_db_index_physical_stats(db_id('AdventureWorks'),null,null,null,'DETAILED'
)
WHEREindex_id<>0)ASdtINNERJOINsys.indexessiONsi.object_id=dt.object_id
ANDsi.index_id=dt.index_idANDdt.avg_fragmentation_in_percent>10
ANDdt.avg_page_space_used_in_percent<75ORDERBYavg_fragmentation_in_percentDESC

上面的查詢顯示的AdventureWorks’數據庫的索引碎片信息如下：

分析上面的結果，你就能發現在哪里出現了索引碎片，應用下面的規則：

ExternalFragmentation的值>10，預示對應的索引出現外部碎片。InternalFragmentation的值<75，預示對應的索引出現內部碎片

3、怎樣重新整理索引碎片

有2種方式：

索引重組：執行下面的命令：

ALTERINDEXALLONTableNameRECOGNIZE

索引重建：

ALTERINDEXALLONTableNameREBUILDWITH(FILLFACTOR=90,ONLINE=ON)

通過使用具體索引的名字代替ALL,你能重組或重建單個的索引。你也可以使用數據庫控制臺來重建/重組索引

4、什么時候重組和重建索引?

當外部碎片的值在10-15，內部碎片的值在60-75，對于這樣的索引，你應該重組索引。否則，你應該重建索引。

關于索引重建的一個重要的事情是：一旦在一個特定的表上重建索引，表就會被鎖定(重組的時候不會發生)。所以，對于一個產品數據庫的一個大的表，因為在一個大表上的索引重建往往需要花費數個小時，我們不希望這種鎖定。幸運的是，在SQL2005有一個解決方法，你可以在重建一個表的索引的時候，把ONLINE選項的值設為ON，這樣會使重建索引和表上的數據事務同樣進行。

四、實現數據訪問結束語

在數據表里的所有適合創建索引的字段上創建索引，這是非常誘惑人的。但是如果你正在從事一個事務數據庫工作，在每個字段上創建索引并不是每次都是需要的。事實上，在一個OLTP系統上創建大量的索引可能會降低數據庫的性能。(因為當很多操作是更新操作的時候，更新數據意味著更新索引)

一個首要的規則建議如下：

如果你在從事一個事務性數據庫，平均不要在一個表上創建超過5個索引，另外，如果你在從事數據倉庫，平均最高可在一個表上創建10個索引。

【編輯推薦】

1. 淺談如何在SQL Server中生成腳本
2. SQL Server 2000中的數據同步問題
3. SQL Server 05數據庫被置為“可疑”的解決方法
4. 詳解SQL Server的版本區別及選擇
5. SQL Server即將提升實時數據功能

【責任編輯：彭凡 Tel：(010)68576606-8058