SQL Server使用索引實現(xiàn)數(shù)據(jù)訪問優(yōu)化
一、簡介
自從你和你的團隊成功的開發(fā)和部署了一個INTERNET網(wǎng)站,已經(jīng)過去數(shù)月了,這個網(wǎng)站在很短的時間內(nèi)吸引了數(shù)千用戶前來注冊和使用,因此你有了一個非常滿意的客戶。包括你和你的團隊、管理層、客戶，每個人都非常高興。
生活并不總是一帆風順的。當站點的用戶開始日均高速增長的時候，問題隨即出現(xiàn)了，客戶發(fā)來郵件開始抱怨網(wǎng)站性能太慢，同時稱網(wǎng)站正在丟失客戶。
你開始調(diào)查這個系統(tǒng)，很快你發(fā)現(xiàn)當系統(tǒng)訪問或更新數(shù)據(jù)的時候，速度非常慢。打開數(shù)據(jù)庫一看，數(shù)據(jù)庫的記錄增加的很快，有些表的記錄達到了成千上萬行，測試團隊在產(chǎn)品數(shù)據(jù)庫上做了一個測試，結(jié)果發(fā)現(xiàn)在測試服務器上僅2/3秒就能完成的一個處理過程，現(xiàn)在需要5分鐘。”
這個古老的故事發(fā)生在全球范圍內(nèi)的數(shù)以千計的系統(tǒng)身上。包括我在內(nèi)，幾乎每個開發(fā)人員在他或她的開發(fā)過程中會碰到同樣的事情。我知道為什么這樣的情形會發(fā)生，同時我也知道如何去克服它。
二、閱讀范圍
請注意本一系列文章討論的主要的焦點是“事務性的SQLServer數(shù)據(jù)庫數(shù)據(jù)訪問性能優(yōu)化”，但大部分優(yōu)化技術(shù)同樣適用于其他的數(shù)據(jù)庫。
我將要討論的優(yōu)化技術(shù)僅僅適用于軟件開發(fā)人員。作為一個開發(fā)者，你需要跟隨我關(guān)注的問題，確認你已經(jīng)作了所有能做的事情，去優(yōu)化你已經(jīng)寫的或?qū)⒁獙懙臄?shù)據(jù)訪問代碼。數(shù)據(jù)庫管理人員(DBA)同樣在優(yōu)化和提高性能上扮演了很重要的角色，但是DBA領域的優(yōu)化將不屬于這篇文章討論的范圍。
三、開始優(yōu)化一個數(shù)據(jù)庫
當基于數(shù)據(jù)庫的應用系統(tǒng)放慢的時候，99%的可能是系統(tǒng)的數(shù)據(jù)訪問過程沒有優(yōu)化，或者沒有使用***的方式。所以你需要回顧和優(yōu)化你的數(shù)據(jù)訪問/操作過程，提高系統(tǒng)的全局性能。接下來我們通過一步一步的方式開始我們的優(yōu)化任務。
***步：在列上采用正確的索引
有些人可能爭論實施正確的索引是否是數(shù)據(jù)庫優(yōu)化過程的***步。但是我認為在數(shù)據(jù)庫應用正確的索引是***位的。原因有2點：
1.在一個產(chǎn)品系統(tǒng)里，它將使你在很快的時間內(nèi)提高盡可能大的性能。
2.創(chuàng)建數(shù)據(jù)庫索引不需要你做任何的系統(tǒng)修改，因此不需要任何重新編譯和部署
如果你發(fā)現(xiàn)有當前的數(shù)據(jù)庫沒有很好的處理索引，你建了索引，結(jié)果就是性能的快速提升。然而，如果索引已經(jīng)處理了，我們進入下面的步驟。
什么是索引
我相信你已經(jīng)明白了什么是索引，但是，我仍舊看到很多人對索引不太清楚。讓我們再一次弄明白什么是索引，請看下面的小故事。
很久以前，在一個古城市里有一個很大的圖書館，里面有數(shù)以千計的圖書，圖書凌亂的存放在書架上。因此，一旦有讀者向圖書員索要一本圖書，圖書員除了一本一本的檢查圖書,看是否匹配讀者索要的圖書，其它沒有更好的辦法。發(fā)現(xiàn)一本渴望的圖書往往需要花費圖書員數(shù)個小時。同時讀者也不得不等很長的時間。
[這看起來象一個沒有主鍵的表，當在表里進行搜索數(shù)據(jù)的時候，數(shù)據(jù)庫引擎需要遍歷全部的數(shù)據(jù)來查找相關(guān)的記錄，所以運行起來非常慢。]
當讀者和圖書每天都在大量增加的時候，圖書員的工作越來越繁重。有一天，有一個智者來到圖書館，看到圖書員的繁重的工作，建議他給每一本書編號，同時按順序碼放在書架上。“我可以從中得得什么好處?”圖書員問，那個智者回答到:“如果有讀者通過給你一個書號來索要圖書，你很快就能發(fā)現(xiàn)在哪個書架上存放了包含該書號的圖書，然后在這個書架上，你同樣能很快的找到需要的圖書”
[給書編號就象在數(shù)據(jù)表里創(chuàng)建一個主鍵,當你在一個表里創(chuàng)建了一個主健后,系統(tǒng)就創(chuàng)建了一個聚集索引樹,所有的包含記錄的數(shù)據(jù)頁按照主鍵的值在文件系統(tǒng)中進行排序.每一個數(shù)據(jù)頁內(nèi)部也同樣按照主鍵的值進行排序.所以,當你向數(shù)據(jù)庫請求任何一個數(shù)據(jù)行的時候,首先數(shù)據(jù)庫服務器使用聚焦索引找到合適的頁(象首先發(fā)現(xiàn)書架一樣),接著在頁里查找包含主鍵值的記錄(象在書架發(fā)現(xiàn)一本書)]
“這正是我所需要的”,興奮的圖書員開始給書編號,接著把它們排列在不同的書架上,他花費了一天的時間來排序.在那天快結(jié)束的時候,他做了測試,結(jié)果發(fā)現(xiàn)幾乎不用花費時間就能找到一本書.圖書員高興極了.
[這正是你創(chuàng)建了主鍵后所發(fā)生的事情.首先,創(chuàng)建了聚焦索引,接著數(shù)據(jù)頁在物理文件里按照主鍵的值被排序.有一點我想你應該很容易理解,因為數(shù)據(jù)僅僅只能使用一列的值作為憑證來排序,所以一個表只能創(chuàng)建一個聚焦索引.就象圖書只能使用一個標準即書號來排序一樣.]
等一等,問題還沒有被完全解決,在接下來的時間里,有個讀者沒有圖書的編號,只有圖書的名字,他想通過書名索要圖書,如何辦呢?可憐的圖書員只能按照從1到N來查遍所有已經(jīng)編號的圖書.如果圖書存放在67號書架上,他可能需要20分鐘,相比早間圖書沒有被排序的時候,他所花費的2-3個小時.這確實有一個進步.但是和花費30秒通過書號查找一本書比較起來,,20分鐘仍舊是一個不短的時間.還有沒有更好的辦法呢?他問那個智者。
[假設你有一個產(chǎn)品表,如果你只有一個ProductID主鍵而沒有其它的索引,上述的情況同樣會發(fā)生,所以,當使用產(chǎn)品名字來搜索的時候,數(shù)據(jù)引擎只能遍歷文件里所有物理排序的數(shù)據(jù)頁,沒有其它的辦法.]
那個智者告訴圖書員:因為你已經(jīng)按照書號對圖書做了排序,你不能使用其它的憑證重新排序,所以,較好的方法是創(chuàng)建一個包含書名和與之對應的編號的目錄或索引,在這個目錄上,按照圖書的字母順序排序,并使用阿拉伯字母進行分組,例如,當有人想查找DatabaseManagementSystem這本書的時候,你使用下列的規(guī)則就能發(fā)現(xiàn)這本書
1.在書名目錄里跳到D章,找到包含你的書名的圖書.
2.得到這本書的書號,然后用書號去查找這本書
“你真是一個天才”,圖書員喊到,他立即花費了一些時間創(chuàng)建了書名的目錄,通過一個快速的測試,他發(fā)現(xiàn)使用書名來查詢僅僅需要1分鐘,其中30秒查找書的編號,30秒用編號來找書.
圖書員想到,讀者還可能使用其它的憑證來查找圖書,例如作者的名字,所以他為作者創(chuàng)建了同樣的目錄.在創(chuàng)建了這些目錄后,圖書員可以使用這些憑證在1分鐘內(nèi)找到圖書.圖書員的繁重的工作終于結(jié)束了,許多讀者也因為很快的查找到圖書而聚集在圖書館,圖書館變的非常熱鬧起來.
圖書員隨后開始過著他的快樂的生活,故事結(jié)束了.
到這里，現(xiàn)在我確信你已經(jīng)明白了什么是索引,為什么它們?nèi)绱酥匾约八鼈兊膬?nèi)部工作原理,,例如,我們有一個已創(chuàng)建聚焦索引的產(chǎn)品表Products,因為當創(chuàng)建了主鍵的時候,隨即就創(chuàng)建了聚焦索引。我門應當在Productname列創(chuàng)建一個非聚焦索引,一旦我們這樣作了,數(shù)據(jù)庫引擎就為非聚焦索引創(chuàng)建一個索引樹,象故事里的書名目錄，按照產(chǎn)品的名字在索引頁里排序。每個索引頁包含一定范圍的產(chǎn)品名字和與之對應的ProductID，所以當使用產(chǎn)品名字作為憑證搜索的時候，數(shù)據(jù)庫引擎首先查詢產(chǎn)品名字的非聚焦索引樹來發(fā)現(xiàn)這本書的主鍵productID，一旦發(fā)現(xiàn)，數(shù)據(jù)庫引擎就使用主鍵ProductID來搜索聚焦索引樹，從而并得到正確的結(jié)果。
索引樹的工作原理如下圖：
上圖被稱做為B+樹，中間的節(jié)點包含一定數(shù)量的值，指示數(shù)據(jù)庫引擎當從跟節(jié)點搜索一個索引值的時候如何遍歷.如果這是一個聚焦索引樹，頁節(jié)點是物理數(shù)據(jù)頁.如果是非聚焦索引樹，頁節(jié)點包含包含索引值和與之對應的聚焦索引值.
通常，在索引樹里發(fā)現(xiàn)需要的值并且轉(zhuǎn)到目標數(shù)據(jù)記錄，對于數(shù)據(jù)庫引擎來說花費的時間是很短的，所以，在數(shù)據(jù)庫應用索引極大的提高了數(shù)據(jù)的檢索操作.
請跟隨下列的步驟確保正確的索引包含在你的數(shù)據(jù)庫里。
確保數(shù)據(jù)庫的每個表有一個主健
這么做會確保每個表有一個聚焦索引，通過主健的值，表的數(shù)據(jù)頁通按物理順序排列在磁盤上。所以,任何使用主健的數(shù)據(jù)檢索操作,任何在主健字段的排序操作都能非常迅速的檢索數(shù)據(jù)。
在這些列上創(chuàng)建非聚焦索引
經(jīng)常被作為搜索憑證的列
用來聯(lián)合其它表的列
用來作為外健的列
用來排序的列
高選擇性列
Xml類型
下面是一個創(chuàng)建索引的命令的例子
CREATEINDEX |
你也可以使用SQL Server控制臺在需要的列上創(chuàng)建索引
第二步：創(chuàng)建正確的復合索引
現(xiàn)在，你是否已經(jīng)在數(shù)據(jù)庫創(chuàng)建了所有的適合的索引?假設，在一個Sales表(SelesID,SalesDate,SalesPersonID,ProductID,Qty)，你已經(jīng)在外鍵(ProductID)創(chuàng)建了索引，如果ProductID是一個高選擇性列，任何在where語句里使用索引列(ProductID)的檢索數(shù)據(jù)的SELECT查詢都會運行的非常快嗎?
對，相對沒有在外鍵創(chuàng)建索引的情況(這需要全部數(shù)據(jù)頁的遍歷)來說，這是非?？斓?，但是，還有進一步提升的空間.
讓我們假設：Sales表包含10,000行數(shù)據(jù)，下面的SQL語句選擇400行。
SELECTSalesDate,SalesPersonIDFROMSalesWHEREProductID=112 |
首先讓我們弄明白在數(shù)據(jù)庫引擎怎么執(zhí)行SQL語句的:
1.Sales表有在ProductID列一個非聚焦索引，所以，首先查詢非聚焦索引樹，發(fā)現(xiàn)包含ProductID=112的入口。
2.包含ProductID=112入口的索引頁同樣同樣也包含了聚焦索引的值(所有的主健的值，即SalesID)
3.對于每一個主健(共400個)，數(shù)據(jù)庫引擎進入聚焦索引樹來發(fā)現(xiàn)正確的行的位置
4.對于每一個主健，一旦發(fā)現(xiàn)正確的行的位置，數(shù)據(jù)庫引擎會從匹配的行得到SalesDate和SalesPersonID的列的值。
請注意，在上述的步驟中，對于每一個ProductID=112的主鍵入口(共400個)，數(shù)據(jù)庫引擎必須搜索聚焦索引樹400次，來檢索附加的列(SalesDate,SalesPersonID)。
讓我們猜想一下,如果非聚焦索引不但包含了聚焦索引的值(主健)，同時還包含查詢里標注的其他的2個列(SalesDate,SalesPersonID)的值，數(shù)據(jù)庫引擎就不用執(zhí)行上述的第3步和第4步，只須進入ProductID的列的非聚焦索引樹，從索引頁上讀取3個列的值，這樣運行的速度不是更快嗎?
幸運的是，有一種辦法來實施這種特點，這就是復合索引。你可以在表的列上創(chuàng)建復合索引，標明哪些列是和聚焦索引一起的應該附加存儲的列。下面是一個在表Sales表的列ProductID創(chuàng)建復合索引的例子。
CREATEINDEXNCLIX_Sales_ProductID--Indexname |
請注意，創(chuàng)建復合索引應當包含少數(shù)幾個列，并且這些列經(jīng)常在select查詢里使用。在復合索引里包含太多的列不僅不會給你帶來太多好處。而且由于使用相當多的內(nèi)存來存儲復合索引的列的值，其后果是內(nèi)存溢出和性能降低。
當創(chuàng)建復合索引的時候，盡量使用DatabaseTuningAdvisor(數(shù)據(jù)庫優(yōu)化顧問)的幫助。
我們知道，一旦一個SQL開始運行，SQLSERVER引擎優(yōu)化器基于以下幾點動態(tài)的產(chǎn)生不同的檢索計劃。
數(shù)據(jù)量
統(tǒng)計
索引變化
TSQL的參數(shù)值
服務器的負載
這意味著：對于一個特殊的SQL語句，在產(chǎn)品服務器上的執(zhí)行計劃可能和在測試服務器上的執(zhí)行計劃不近相同，甚至表和索引結(jié)構(gòu)一樣。這同樣也表明，一個在測試服務器上創(chuàng)建的索引可能會加速測試服務器上的性能，但是在產(chǎn)品服務器上的同樣的索引可能不會帶給你任何益處。為什么?因為在測試環(huán)境下的SQLSEVVER執(zhí)行計劃可能使用創(chuàng)建的索引，因此給你很好的性能，但是，在產(chǎn)品服務器上的執(zhí)行計劃可能出于下列的原因而根本不使用新創(chuàng)建的索引。例如：一個非聚焦索引列在產(chǎn)品服務器上不是高選擇性列，而在測試服務器上是高選擇性列.
所以，當創(chuàng)建索引的時候，我們需要弄明白這一點：索引是執(zhí)行引擎用來提高速度的。但是我們該如何去做呢?
答案是我們必須在測試服務器上模擬產(chǎn)品服務器的負載，接著創(chuàng)建索引，以及測試他們。只有這樣，在測試服務器上能提高性能的索引，才能更有可能在產(chǎn)品服務器上提高性能。
這么做應該很困難，但幸運的是，我們有一些好用的工具去實現(xiàn)它，請跟隨下面的指導：
1：使用SQLprofiler捕獲產(chǎn)品服務器上的痕跡。使用Tuningtemplate(我知道，有人建議不要在產(chǎn)品服務器上使用SQLprofiler,但有些時候，你不得不在產(chǎn)品服務器上診斷性能問題的時候使用它)，如果你不熟悉這個工具，或者你想了解更多的關(guān)于SQLprofiler的知識，請閱讀http://msdn.microsoft.com/en-us/library/ms181091.aspx
2.利用上一步產(chǎn)生的跟蹤文件，用數(shù)據(jù)庫優(yōu)化顧問在測試數(shù)據(jù)庫創(chuàng)建相似的負載,從優(yōu)化顧問得到一些建議，特別是創(chuàng)建索引的建議，你很可能從優(yōu)化顧問那里獲得比較實際的建議。因為優(yōu)化顧問使用產(chǎn)品服務器產(chǎn)生的跟蹤文件來裝載測試服務器，所以能產(chǎn)生最可能好的索引建議。如果你不熟悉優(yōu)化顧問工具，或者你想了解更多的關(guān)于使用優(yōu)化顧問的的資料，請閱讀：http://msdn.microsoft.com/en-us/library/ms166575.aspx.
第三步：如果有碎片發(fā)生，重新整理它
到了這里，如果你已經(jīng)在表里創(chuàng)建了所有正確的索引，但是，你可能還沒有獲得所希望的良好的性能。什么原因呢?有一種可能是出現(xiàn)了索引碎片。
1、什么是索引碎片
索引碎片是這樣一種情形：由于在表里大量的插入、修改、刪除操作而使索引頁分裂。如果索引有了高的碎片，有兩種情況，一種情況是掃描索引需要花費很多的時間，另一種情況是在查詢的時候索引根本不使用索引,都會導致性能降低。
有2種類型的碎片：
內(nèi)部破碎：由于索引頁里的數(shù)據(jù)插入或修改操作而發(fā)生，以數(shù)據(jù)作為稀疏矩陣的形式的分布而結(jié)束，這將導致數(shù)據(jù)頁的增加，從而增加查詢時間。
外部破碎：由于索引/數(shù)據(jù)頁的數(shù)據(jù)插入或修改而發(fā)生，以頁碼分離和在文件系統(tǒng)里不連貫的新的索引頁的分配而結(jié)束，數(shù)據(jù)庫服務器不能利用預讀操作的優(yōu)點，因為：下一個相關(guān)聯(lián)的數(shù)據(jù)頁不臨近，而且這些相關(guān)連的下面的頁碼可能在數(shù)據(jù)文件的任何地方。
2、如何知道索引破碎是否已經(jīng)發(fā)生?
在數(shù)據(jù)庫執(zhí)行下面的SQL語句(下面的語句在SQLserver2005及以后的版本運行正常，以你的目標數(shù)據(jù)庫的名字取代AdventureWorks’)
SELECTobject_name(dt.object_id)Tablename,si.name |
上面的查詢顯示的AdventureWorks’數(shù)據(jù)庫的索引碎片信息如下：
分析上面的結(jié)果，你就能發(fā)現(xiàn)在哪里出現(xiàn)了索引碎片，應用下面的規(guī)則：
ExternalFragmentation的值>10，預示對應的索引出現(xiàn)外部碎片。InternalFragmentation的值<75，預示對應的索引出現(xiàn)內(nèi)部碎片
3、怎樣重新整理索引碎片
有2種方式：
索引重組：執(zhí)行下面的命令：
ALTERINDEXALLONTableNameRECOGNIZE |
索引重建：
ALTERINDEXALLONTableNameREBUILDWITH(FILLFACTOR=90,ONLINE=ON) |
通過使用具體索引的名字代替ALL,你能重組或重建單個的索引。你也可以使用數(shù)據(jù)庫控制臺來重建/重組索引
4、什么時候重組和重建索引?
當外部碎片的值在10-15，內(nèi)部碎片的值在60-75，對于這樣的索引，你應該重組索引。否則，你應該重建索引。
關(guān)于索引重建的一個重要的事情是：一旦在一個特定的表上重建索引，表就會被鎖定(重組的時候不會發(fā)生)。所以，對于一個產(chǎn)品數(shù)據(jù)庫的一個大的表，因為在一個大表上的索引重建往往需要花費數(shù)個小時，我們不希望這種鎖定。幸運的是，在SQL2005有一個解決方法，你可以在重建一個表的索引的時候，把ONLINE選項的值設為ON，這樣會使重建索引和表上的數(shù)據(jù)事務同樣進行。
四、實現(xiàn)數(shù)據(jù)訪問結(jié)束語
在數(shù)據(jù)表里的所有適合創(chuàng)建索引的字段上創(chuàng)建索引，這是非常誘惑人的。但是如果你正在從事一個事務數(shù)據(jù)庫工作，在每個字段上創(chuàng)建索引并不是每次都是需要的。事實上，在一個OLTP系統(tǒng)上創(chuàng)建大量的索引可能會降低數(shù)據(jù)庫的性能。(因為當很多操作是更新操作的時候，更新數(shù)據(jù)意味著更新索引)
一個首要的規(guī)則建議如下：
如果你在從事一個事務性數(shù)據(jù)庫，平均不要在一個表上創(chuàng)建超過5個索引，另外，如果你在從事數(shù)據(jù)倉庫，平均最高可在一個表上創(chuàng)建10個索引。
【編輯推薦】
- 淺談如何在SQL Server中生成腳本
- SQL Server 2000中的數(shù)據(jù)同步問題
- SQL Server 05數(shù)據(jù)庫被置為“可疑”的解決方法
- 詳解SQL Server的版本區(qū)別及選擇
- SQL Server即將提升實時數(shù)據(jù)功能
【責任編輯：彭凡 Tel：(010)68576606-8058
