SQLSERVER索引最佳化二

 

一、簡介

自從你和你的團隊成功的開發和部署了一個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個索引。