Microsoft SQL Server 2000 超級管理手冊(十七)-CSDN博客

本文链接：https://blog.csdn.net/gzwl/article/details/123812

17. 建立與使用索引

什麼是索引？

索引概念

建立索引

重新建置索引

使用索引

設計有效的索引

本章總結

對資料庫設計者而言，索引是最有用的工具之一。索引（index）是一個讓您能增進查詢效能的輔助結構，它能減少檢索查詢資料所必須的I/O動作數量。換言之，索引讓Microsoft SQL Server 2000以較少的I/O操作來找出資料，減少搜尋資料的量。當您使用資料庫資料表的索引來檢索資料列時，SQL Server能很快地判斷出資料儲存的位置並且立即擷取該資料。因此，資料庫資料表的索引與書本所用的索引相當類似－兩者均提供快速擷取的功能，特別是我們面對大量資訊的時候。

在本章中，您將學到索引的基本觀念，包括如何建立索引以及SQL Server中有哪幾種不同類型的索引可用。您也會學到何時應使用索引、何時不使用索引－因為索引並非無往不利的工具，在某些情況下，使用索引反而可能有害於系統的效能。

什麼是索引？
　

如前所述，索引是SQL Server用來存取資料的輔助資料結構。依其類型的不同，索引可能是與資料一起儲存，也可能與資料分開儲存。撇開其類型不談，索引的運作方式基本上是相同的，這也是本節中您將學到的部分。

在一個沒有索引的系統中，所有資料檢索的工作都要靠掃描資料表來完成。在掃描資料表的操作中，所有的資料都需要被讀取，並且與查詢的資料相互比對。一般說來，我們會盡量避免資料表掃描－因為這種操作會造成驚人的I/O數量，尤其是掃描一份龐大的資料表時，可能要花掉不少的時間並吃掉可觀的系統資源。如果使用索引的話，您便能具體的降低I/O操作的數量，提昇資料存取的速度並盡快將系統資源釋放給其他的作業來使用。

資料庫索引是以B型樹狀結構來組織。索引中的每一分頁被稱作 索引分頁 或 索引節點 。索引結構由頂層的根節點開始。 根節點 （root node）標示出索引的起點；當需要尋找資料時，它是最先被存取的資料。根節點也包含了一些索引資料列；這些索引資料列含有一個鍵值及一個稱為中介節點的指標指向另一個索引分頁，如圖17-1所示。這種組態是必須的，因為在一個平均大小的資料表中，索引可能是由成千上萬的索引分頁來構成。藉著由根節點起始並瀏覽中介節點，SQL Server能夠「放大」您要的資料。

圖17-1 根節點與中介節點

當我們使用書本中的索引時，使用的方法可能與下面所述極為類似：假設索引是由一頁包含「a」、「b」、「c」等字母項目所在部分之頁碼的目錄頁所開始。接著翻到「a」這一頁，又會發現進入「aa-ab」、「ac-ad」、「ae-af」等範圍所在部分的頁碼。進入「aa」，裡面又列有「aaa-aab」、「aac-aad」、「aad-aaf」等範圍所在部分的頁碼。藉著這種排列，您可以很快的找到您要的資料，而不用翻遍整本書。索引以根節點作為第一頁，與上述方式相當雷同。

一如根節點，每個中介節點包含有索引分頁，分頁中包含著索引資料列。每個索引資料列指向其他的中介節點或分葉節點，如圖17-2。分葉節點由索引的最底層組成。與根節點不同的是，每個中介節點還包含了一份連接到同一層中介節點的連結清單（Linked List）。換句話說，此節點知道相鄰節點以及較低層級的節點。

顧名思義，所謂「B型樹」，意味著從根節點展開中介節點後其型態猶如樹枝般分佈。每一組樹狀結構中同一層的中介節點稱為 索引層級 ，如圖17-3所示。到達分葉節點（樹狀結構中最底層的節點）所需的I/O操作數量要依索引層級的數目而定。如果資料庫資料表含有的資料很少，根節點可以直接指向分葉節點，索引將不需要包含任何中介節點（當然這種情況不太可能發生）。

圖17-2 顯示中介節點與分葉節點的樹

圖17-3 索引層級

在一個非叢集索引中，分葉節點包含有鍵值，以及一個指向資料表中所需資料列的資料列識別碼（ROW ID），或者會包含一個叢集索引鍵值－如果資料表中還有一個叢集索引的話。而在叢集索引中，資料本身即位在分葉節點中（叢集與非叢集索引將在本章稍後〈索引類型〉一節中有所說明）。分葉節點中資料列的數量要看索引建立時引用的項目大小。而在叢集索引中，則要視資料的大小而定。

說明

資料列識別碼（ROW ID）是SQL Server由檔案編號、分頁碼及資料列號碼自動建立的指標。藉著資料列識別碼，您只需一個額外的I/O操作便可擷取資料。因為您知道要擷取的是哪個分頁，而且SQL Server知道分頁所在的位置，因此只要單一的I/O操作即可將該分頁讀入記憶體之中。這種檢索過程的簡化也是為何索引能更有效率並增進效能的主要原因。

要特別留心的是，由於索引是以排序方式建立的，任何資料的變更都可能造成額外的負擔。舉例來說，如果插入動作造成一個新的資料列必須被插入到已經滿的分葉節點，SQL Server必須挪出空間給新的資料列。此舉必須將近乎半數的資料列移至分葉節點中的另一個分頁中。此種資料搬移稱為 分頁分隔 （Page Split）。分頁分隔會造成索引樹一連串的分隔動作。透過謹慎的設定 填滿因數 （fill factor）可以避免分頁分隔，我們會在本章稍後〈使用填滿因數避免分頁分隔〉一節中介紹填滿因數。

索引概念
　

現在您已經對索引結構有了基本的瞭解，接著讓我們看一些與索引有關的概念。在本節中，您將學到索引鍵、索引唯一性以及索引的類型。

索引鍵
　

索引鍵 （index key）指出用於產生索引的一個資料行或數個資料行。索引鍵是一個讓您可以快速找到資料列的值，此資料列包含有您需要的資料。索引鍵就像是書本中的索引項目，可指向文中特定的主題。要使用索引取得資料列，您必須在SQL陳述式的WHERE子句中包含索引鍵值，還得要看看索引是一個簡單索引還是複合索引。

簡單索引
　

簡單索引 （simple index）即為使用單一資料行的索引，如圖17-4。如果您要取得索引的效率，則該資料行必須在SQL陳述式的WHERE子句中引用。

圖17-4 簡單索引

根據資料儲存的資料型別、以及資料行中唯一性項目的數量，還有使用何種類型SQL陳述式等等因素，有時使用簡單索引便可以很有效率，但有些情況則可能使用複合索引會比較合適。舉例來說，如果您索引的是一個由上千個名字與地址構成的通訊錄， state資料行就不應該是一個簡單索引的候選鍵，因為同一州裡可能有很多相同的項目。然而，若在索引中增加street與city資料行，使其成為複合索引，您可以讓每個項目近乎唯一。這種方法會很有用－若您的查詢是依地址來找資料列的話。

複合索引
　

複合索引 （composite index）是定義超過一個資料行的索引，如圖17-5。複合索引可以利用一個多或個索引鍵來存取。在SQL Server 2000中，索引最多可包含16個資料行，並且鍵值總資料長度不可超過900個位元組。

圖17-5 複合索引

當您使用複合索引進行查詢時，並不需要在SQL陳述式的WHERE子句中放入所有的索引鍵，不過使用一個以上的索引鍵會是比較聰明的做法。舉例來說，假設索引是由資料表中a、b、c三個資料行建立的，使用包含有（a AND b AND c）或（a AND b）或a的SQL陳述式均可存取該索引。當然，使用最嚴格的WHERE子句，例如包含a AND b AND c，效率會比較高些。它能從資料庫中檢索出較少的資料列，因為它檢驗資料列時更為明確。如果您使用a AND b或只有a，會開始進行索引掃描的操作。

當超過一個索引項目可以滿足搜尋條件時，就會發生索引掃描。在索引掃描中，索引的節點會被掃描以取得多個資料集。此外，索引只部分涵蓋了選取的值。舉例來說，如果索引是由a、b、c三個資料行建立而查詢僅指定了a，所有滿足a的值的資料列都會被傳回，包括所有b和c的值。

由於使用在索引中的基礎資料行是按數字排列，SQL Server查詢最佳化器可以判斷出大略包含您所需資料的索引分頁範圍。一旦知道了開始頁和結束頁，含有資料值的所有分頁都將被檢索，而且資料將被掃描以選擇您所需的資料。

真實世界 CustomerLocation資料表

假設我們現有一份資料表，其中包含了我們公司顧客地址的相關資訊。索引是由state、country以及city資料行所建立，並以 state 、 country 、 city 的順序儲存在B型樹狀結構中。如果一個查詢在WHERE子句中指定 state 的值為 Texas ，將會使用索引的功能。由於查詢中 country 及 city 的值並未指定，索引將會傳回索引資料集中所有state資料行值為 Texas 的資料列。索引掃描會先用來檢索出索引分頁的範圍，隨後依state資料行的值擷取資料分頁。索引分頁將被循序的讀取，方式與資料表掃描存取資料分頁一樣。

說明

如果您要使用索引的特色，只有在SQL查詢的WHERE子句中至少用到一個索引鍵時，索引才會起作用。以剛剛的例子來說，如果查詢的WHERE子句裡只有名字或電話這類資料行的話，就不會用到索引的功能。

在大部分的情況下，索引掃描十分有效率。不過，若資料表中將有20% 以上的資料列會被存取，使用讀取資料表中所有資料列的資料表掃描會更有效率。索引利用的查詢效率，要視您如何使用索引（本章稍後〈使用索引〉一節會介紹）以及我們接著要討論的問題：索引的唯一性。

索引的唯一性
　

您可以將SQL Server的索引定義為唯一索引或非唯一索引。在 唯一性索引 （unique index）中，每個索引鍵的值必須是唯一的。 非唯一性索引 （nonunique index）則允許索引鍵的值可以重複。非唯一索引的效力或效率要看索引的選擇性而定。

唯一索引
　

唯一索引的每個索引鍵只包含一列資料－換句話說，索引鍵值在資料表中不會出兩次。唯一索引非常有效率，因為它們保證要擷取查詢所需的資料只需一個額外的I/O操作。SQL Server會強制一個或數個資料行的唯一性以建立索引鍵。SQL Server將不會允許在資料庫中插入重複的鍵值，如果您試圖這麼做，則會傳回錯誤訊息。當您在資料表中建立一個PRIMARY KEY條件約束或一個UNIQUE條件約束時，SQL Server便會建立唯一性索引。PRIMARY KEY條件約束與UNIQUE條件約束已經在第16章中討論過。

當資料本身即是唯一的，索引自然可以建立其唯一性。但若資料行中所包含的資料並不是唯一的，您仍然可以利用複合索引來建立唯一索引。舉例來說，last name資料行可能並不是唯一的，但是若與first name和middle name資料行組合起來，您便能在資料表上建立唯一索引。

說明

如果您在資料表中插入一資料列的結果可能會使唯一索引出現重複的索引鍵值，這個插入動作將會失敗。

非唯一索引
　

非唯一性索引的運作方式與唯一性索引並沒有什麼不一樣（除了可以在分葉節點中包含重複的值以外）。只要符合SELECT陳述式中指定的基準，所有重複的值都會被擷取。

非唯一性索引並不如唯一性索引般那麼有效率，因為它需要額外的程序（額外的I/O操作）來檢索出查詢所需的資料。不過有些應用程式需要用到重複的鍵值，它有可能無法建立唯一索引。在這類情況下，非唯一索引至少比沒有索引要來得好些。

索引類型
　

B型樹狀結構索引有兩種類型： 叢集索引 （clustered indexes）與 非叢集索引 （nonclustered indexes）。叢集索引在其分葉節點中儲存實際資料的資料列。非叢集索引則是一個指出資料表中資料位置的輔助結構。在本節中，我們將看一下兩種索引類型之間不同的地方。我們也會在本節中介紹 全文檢索索引 （full-text index），它實際上更像是一個目錄而非索引。

叢集索引
　

一如前述，叢集索引是一種將資料表的實際資料列資料按順序儲存在其分葉節點的B型樹狀結構索引，如圖17-6。這種系統提供了一些優點與缺點。

圖17-6 叢集索引

由於叢集索引的資料是儲存在分葉節點上，亦即一旦到達分葉節點便可取用資料，這種方式意味著只要少數I/O操作便可得出結果。任何操作數量的減少都可使單一操作產生較高的效能，並對系統整體的效能有所提昇。

叢集索引的另一個優點是，資料將會按索引排序時的順序來擷取。舉例來說，如果現在有一個叢集索引是由state、country、city資料行來建立，而查詢選擇所有的state ? Texas的值，輸出的結果將會以country和city資料行在索引定義時的排序方式來排序。如果應用程式與資料庫在設計時考量過這類問題，這種功能就可以讓您避免不必要的排序操作。例如，若您已經確定資料需要的排序方式並用於索引之中，使用叢集索引便可讓您在擷取資料之後不需再執行排序的工作。

使用叢集索引的一個缺點是，存取資料表時常需穿梭整個索引，如此將會造成系統額外的負擔。SQL Server存取資料時會從根節點開始搜尋索引直到它到達包含資料的分葉節點。如果由於資料數量的緣故建立了許多分葉節點，支援這些分葉節點所需的索引層級數量也相當多，SQL Server從根節點到分葉節點的搜尋過程就會需要更多的I/O操作。

因為實際資料是儲存在叢集索引中，在一個資料表上您只能建立一個叢集索引。另一方面，您可以在叢集資料表的頂層建立不只一個非叢集索引。（叢集資料表僅僅是擁有一個叢集索引的資料表。）您應該使用最常用以存取的索引鍵來建立叢集索引，如此可讓您在最佳時機透過叢集索引存取資料，並提供最好的效能。

非叢集索引
　

不同於叢集索引，非叢集索引並不會把實際資料包含在其分葉節點之中。分葉節點包含著兩種資料列的位置資訊其中的一種。第一，如果資料表上沒有叢集索引，此資料表的非叢集索引的分葉節點儲存的是資料列識別碼，如圖17-7。每個資料列識別碼指向資料表中實際資料存在的資料列。構成資料列識別碼的值包括資料檔案識別碼、分頁碼，以及分頁中的資料列編號。這個值精確的指出資料儲存的位置以便快速的存取實際資料。

圖17-7 在沒有叢集索引的資料表上的非叢集索引

如果資料表上有一個叢集索引，非叢集索引的分葉節點將會包含此資料的叢集索引鍵值，如圖17-8。當到達非叢集索引的分葉節點時，可找到叢集索引鍵值，並且接著被用在叢集索引中繼續搜尋，直到在叢集索引的分葉節點中找到資料為止。

圖17-8 一個擁有叢集索引的資料表上的非叢集索引

之前已經說過，每個資料表只能擁有一個叢集索引。您可以在每個資料表上建立249個非叢集索引，但那絕不是聰明的做法（本章稍後〈索引指導原則〉一節中會解釋這一點）。在資料表不同的資料行上擁有幾個非叢集索引是很常見的，WHERE子句要用到哪個索引則由查詢最佳器來決定。

全文檢索索引
　

如前所述，SQL Server全文檢索索引事實上比較像是一個目錄而不是索引，並且也不是B型樹狀結構。全文檢索索引允許您以關鍵字的群組來搜尋資料。全文檢索索引是Microsoft Search Services的一部分，並廣泛地應用在Web網站搜尋引擎以及其他以文字為基礎的操作中。

不同於B型樹狀結構索引，全文檢索索引儲存在資料庫的外部，但是由資料庫來維護。由於它是儲存在外部，索引可以自行維護其結構。執行全文檢索索引有下列限制：

全文檢索索引中必須包含一個資料行，並且該資料行在資料表中的每一列都要是唯一的。
　
全文檢索索引也必須包含資料表中一個或多個字元資料型別的資料行。
　
每一個資料表只可以允許有一個全文檢索索引。

全文檢索索引並不像B型樹狀結構索引會自動更新。在B型樹狀結構索引中，資料表的插入、更新或刪除操作將會更新索引。但是在全文檢索索引中，資料表上的這些操作將不會自動的更新索引，您必須以排程或手動的方式來更新。
　

全文檢索索引有許多在B型樹狀結構索引中找不到的豐富功能。由於此索引是設計為一個文字搜尋引擎，它比一般的文字搜尋支援更多的功能。您可以使用全文檢索索引搜尋單字、片語、某一個單字或單字的集合，或搜尋類似的單字。在本章稍後的〈使用全文檢索索引精靈〉一節中您將學會如何建立全文索引。

建立索引
　

建立索引並不困難。建立叢集與非叢集索引的方法差別不大，都可利用Enterprise Manager所提供的精靈或是利用SQL命令CREATE INDEX來建立。在本節中，您將學到如何利用這兩種方法來建立索引，也會學到使用填滿因數的相關問題，以及如何使用預存程序來建立全文檢索索引。

說明

雖然精靈較易於使用，但是若您的作業需要重複多次或是必須建立多個類似的資料庫，您會發現指令檔是很方便的工具。指令檔允許您將建立程序文件化並且可以一再的執行。

使用建立索引精靈
　

顯然地，若您打算在資料表上建立索引，資料表必須是已存在於資料庫中。您可以利用 建立索引精靈 在資料表上建立叢集或非叢集索引，請遵循下列步驟：

- 開啟Enterprise Manager，按一下工具列的 執行精靈 按鈕，顯示 選擇精靈 對話方塊。在本例中，我們將使用 Northwind 資料庫。
- 展開 資料庫 ，選擇 建立索引精靈 ，按一下確定。
- 出現 歡迎使用建立索引精靈 畫面，如圖17-9。注意一下您所選擇的伺服器與資料庫的名稱會出現在標題列。
  　
  
  圖17-9 「歡迎使用建立索引精靈」畫面
- 按一下 下一步 顯示 選取資料庫與資料表 畫面，如圖17-10。在這裡您可以指定您要建立索引的資料庫資料表。預設資料庫是您在開啟精靈時已選取的資料庫，資料庫中的資料表也會有一個清單供您選取。
- 按一下 下一步 繼續到 目前的索引資訊 畫面，如圖17-11。本例使用Customers資料表，因為它包含著為數不少的資料列。如您所見，在Customers資料表上已建立了幾個索引，包括一個叢集索引及四個非叢集索引。記住，一個資料表上只能建立一個叢集索引。
  所有Customers資料表上已經建立的索引均為簡單索引（只列出一個資料行），並且每個索引均建立在不同的資料行上。當 查詢最佳化器 （Query Optimizer）分析一個查詢並要選擇查詢執行方案時，它會依照目前可用的索引及WHERE子句來決定要使用哪個索引。
  
  　
  
  圖17-10 選取資料庫與資料表畫面
  
  　
  
  圖17-11 目前的索引資訊畫面
- 按一下 下一步 顯示 選取資料行 畫面，如圖17-12。這個畫面讓您選取索引要包含的資料行。此處不用擔心資料行的順序－稍後您仍然可以變更它們。
  　
  
  圖17-12 「選取資料行」畫面
- 要指定打算包含在索引中的資料行，可選取該資料行名稱右邊的核取方塊。在本例中，我們將建立一個在CompanyName、ContactName和Region資料行上的複合索引。
按一下 下一步 顯示 指定索引選項 畫面，如圖17-13。這個畫面讓您可以設定一些重要的選項，以決定要建立何種索引。您可以選取 將此作成叢集索引 核取方塊，讓新索引成為叢集索引。在本例中，用來建立叢集索引的核取方塊呈現無法選取的狀態，因為Customers資料表上已經建立了一個叢集索引。
您也可以選取 將此作成唯一索引 選項，讓此索引成為唯一性索引而不是非唯一性索引。您還可以指定填滿因數為 最佳化 或 使用固定比率 。由於索引中的資料列是依其順序儲存的，SQL Server有時可能需要搬移資料才能維持其順序。填滿因數選項可讓您指定新建立的索引在填滿至何種程度時，就應預留空間讓將來的插入動作可以使用。預設的填滿因數（若您選擇 最佳化 便是預設值）是 0 ，亦即指定填滿分葉節點但在索引節點的較高層保留一些空間。關於填滿因數的詳細情形，請參閱本章稍後
 〈使用填滿因數避免分頁分隔〉一節。

　

圖17-13 指定索引選項畫面
選取您索引選項並按 下一步 顯示 完成建立索引精靈 畫面，如圖17-14。在這個畫面裡，您可以重新安排組成索引的資料行順序。選取您要移動的資料行，按一下上移或下移直到資料行到達您想要的位置。在這個畫面裡您也可以指定索引的名稱。
　

圖17-14 「完成建立索引精靈」畫面

複合索引的順序相當重要。SQL陳述式要充分利用索引的優點，陳述式的WHERE子句中必須是索引的重要部分。圖17-15顯示同樣的畫面，不過索引已經重新命名為 CustomerAreaIndex ，資料行的順序也重新安排為Region、CompanyName及ContactName。

　

圖17-15 重新安排索引中資料行的順序

由於索引資料行以此順序排列，使用到此索引的SQL陳述式就必須在其WHERE子句中包含Region資料行，因為Region是主要資料行。當然，陳述式也可在WHERE子句中含有Region以及CompanyName，或者甚至Region、CompanyName以及CompanyName都使用。如果您在WHERE子句中使用全部三個值，將可達到最好的效能，因為執行最少的I/O操作。WHERE子句中資料行名稱出現的順序並不會產生任何影響。
一旦您滿意了資料行的順序，按一下完成後索引就會開始建立。索引建立的過程可能是幾秒鐘也可能是幾小時，時間的長短依資料量多寡、系統效能、磁碟效能以及系統記憶體大小而定。要在資料表上建立索引，SQL Server必須讀取資料表上所有的資料，因此耗時長短的變化相當大。

注意

如果您建立的是唯一性索引而索引鍵卻發現有重複的值，索引建立程序將會失敗。

利用 建立索引精靈 來建立索引相當簡單，但是建立過程中會有一些缺點。尤其當您使用 建立索引精靈 時，它並不會留下任何與您工作相關的資訊，因此每次您想要建立另一個索引時，就必須依本節介紹的程序從頭到尾再次進行一遍。相對地，若建立索引時使用的是指令碼檔案，您就可以重複地使用。若您打算重建資料庫，您必須為資料庫中的每個索引再次應用 建立索引精靈 。不過，在您建立索引之後，可以利用Enterprise Manager為它們產生SQL指令碼。

使用Transact-SQL
　

若您使用Transact-SQL（T-SQL）來建立索引，可以將命令儲存成指令碼並可一再重複地使用，您也可以修改索引建立的指令碼來建立其他的索引。此外，這種方法也讓您在建立索引時更為有彈性，因為有更多的引數可以利用。要使用這種技術來建立索引，只需將T-SQL命令儲存入一個檔案之中，並使用下列的語法將檔案讀入OSQL：

Osql -Uusername -Ppassword < create_index.sql

此命令假設您建立的檔案名稱為create_index.sql。您也可以使用Query Analyzer來執行指令碼。（關於此一程序的相關資訊請參閱第13章。）

要使用T-SQL來建立索引，必須利用CREATE INDEX命令。CREATE INDEX的語法如下：

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED]
INDEX index_name   ON table_name
(
column_name [, column_name, column_name,   ... ]
)
[ WITH options ]
[ ON filegroup_name ]

中括號內的值是選擇性的。您可以將索引建立為唯一性或非唯一性索引、叢集或非叢集索引、使用一個或多個資料行，並且可使用表17-1所列出的選擇性引數。您還可以選擇性地指定索引儲存的檔案群組。

表17-1 用於CREATE INDEX命令中的選擇性引數

引數	說明
PAD_INDEX	與FILL_FACTOR引數搭配使用，指定分葉節點以外的較高層節點應預留的空間，而不僅是預留在分葉節點中。
FILL_FACTOR ? number	以百分比的方式（從0到100）指定每個分葉節點填滿的程度。
IGNORE_DUP_KEY	當試圖對一個唯一性索引插入一個重複的值時，指定忽略其插入動作並發出警告。若未指定IGNORE_DUP_KEY，整個插入將會被復原。
DROP_EXISTING	指定已經存在的同名索引應被卸除，並且重建該索引。當您在一個已經有非叢集索引的資料表上重新建立叢集索引時，這個引數會很有用，因為不必再執行卸除與重建非叢集索引的其他步驟。
STATISTICS_NORECOMPUTE	指定統計資料不會被重新計算。不建議使用此選項，因為執行方案若是基於過時的資料，它們可能不是最佳化的狀態。只有在您計劃手動更新統計資料時才使用這個引數。

相關資訊

要了解這些選擇性引數的更多內容，可利用《線上叢書》索引 CREATE INDEX ，並在 找到的主題 對話方塊中選擇 CREATE INDEX （Transact-SQL參閱）。

建立索引時，比起 建立索引精靈 來說使用T-SQL會是較好的選擇。雖然剛開始T-SQL比較難用，但就長期而言，您會發現使用T-SQL來建立多個索引比用 建立索引精靈 要來得簡單的多。

使用填滿因數避免分頁分隔
　

當擁有索引的資料表有更新或插入動作時，索引分頁也必須更新。索引分頁是靠指標將彼此鏈結在一起。每一索引分頁有兩個指標，一個指向下一頁而另一個指向前一頁。當索引分頁填滿時，索引的更新將會造成指標鏈的變更，因為在兩個分頁之間必須插入新的分頁，此一過程被稱為 索引分頁分隔 （index page split），如此新的資訊才能被放入索引鏈中正確的地點。SQL Server會將已經存在的分頁（新資料必須置入的分頁）裡近乎半數的資料列搬移到新的索引分頁之中。原先指向彼此的兩個分頁如今必須指向新的分頁，新的分頁也必須指向原先的兩頁（向前及向後）。現在新的分頁在鏈中的指向次序已經正確，但是資料庫中索引分頁自然而規律的次序並不會維持太久。由於索引資料列會不斷地加進索引當中（假設更新或插入時而發生的話），然而索引分頁的大小有限，越來越多的索引分頁會被填滿。發生這種情況時，新的分頁必須找到額外的空間。要建立更多空間，SQL Server就必須一再執行索引分頁分隔，結果就是造成系統負荷不斷增加，因為這些工作需要更多CPU使用量及額外的I/O操作。這也會造成一個片斷的索引。索引資料被散佈在整個資料庫之中，效能因而緩慢無比。

圖17-16 索引分頁分隔

要減少分頁分隔與片斷化的問題，便是調整索引節點的填滿因數。當您建立索引的時候， 填滿因數 （fill factor）指定節點填滿的百分比，讓您可以為額外的索引資料列預留空間。您可以利用T-SQL陳述式CREATE INDEX的FILL_FACTOR選項指定一個索引的填滿因數，就如之前所說的那樣。如果CREATE INDEX命令中並未指定填滿因數，系統將會使用預設值。預設值可利用 sp_configure 引數 fill factor 來設定。當您安裝SQL Server時預設值設定為 0 。

說明

只有在建立索引時改變填滿因數引數的設定才會對索引有所影響，當索引已建立後才改變它則不會產生任何效果。

填滿因數的值（範圍從0到100）指定了索引分頁被填滿的百分比率。值為 0 時較為特殊。當填滿因數指定為0，分葉節點會被完全地填滿，但中介節點與根節點會預留一些空間。這個值是SQL Server安裝預設值，並且通常不會出什麼問題。

若建立索引時指定填滿因數值為100，所有的索引節點都會被完全地填滿。對一個完全不會有新資料插入也不會有任何更新動作的資料表而言，這個值是它的索引最佳的選擇。索引的分葉節點及較高層的節點都會完全地塞滿，任何插入都會造成分頁分隔。這種設定對唯讀資料表來說最為理想，即使有資料會被刪除也不會有什麼問題，因為資料刪除不會造成分頁分隔。

較低的填滿因數值將會預留許多空間給插入動作，但也許要更多額外的空間來建立索引。除非您對資料庫會執行固定的插入動作，通常不建議使用較低的填滿因數設定值。如果發生太多的分頁分隔，試著降低填滿因數的設定值並重建索引，看看使否能減少分頁分隔的次數。

使用效能監視器的 Page Splits/Sec 計數器可以找出您的系統目前每秒發生多少次分頁分隔。這個計數器可以在 SQL Server : Access Methods 物件中找到。

如果分頁分隔已經造成，並且您的索引也已變得嚴重地片斷化，解決辦法是重建您的索引。只有在您使用填滿因數讓索引分頁預留空間時，才會發生片斷化的情況。這些空間遲早也會被填滿。要了解更多這一方面的問題，請參閱本章稍後〈重新建置索引〉一節。

使用全文檢索索引精靈
　

要使用全文檢索索引精靈來建立全文檢索索引，遵循下列步驟（下一節會介紹如何利用全文檢索索引）：

- 在Enterprise Manager中選取您要建立全文檢索索引的資料表。本例使用Northwind資料庫的Customers資料表。
- 按一下工具列中的 執行精靈 按鈕。要不然的話，從功能表裡按工具，在下拉式選單中選擇精靈。出現 選擇精靈 對話方塊。
在 選擇精靈 對話方塊中展開 資料庫 。選擇 全文檢索索引精靈 ，按一下確定。出現 歡迎使用SQL Server全文檢索索引精靈 畫面，如圖17-17。
　

圖17-17 「歡迎使用SQL Server全文檢索索引精靈」畫面
按一下 下一步 進入 選擇資料庫 畫面。我們選擇 Northwind 作為範例。（說明若在按一下精靈按鈕之前已經選取資料庫，則不會出現這個畫面。）
按一下 下一步 顯示 選擇資料表 畫面，如圖17-18。我們選擇 Customers 資料表，然後按一下 下一步 。
出現 選擇索引 對話方塊。這個精靈需要您選擇一個現存的唯一性索引，用來與全文檢索操作搭配使用。Customers資料表目前只有一個唯一索引PK_Customers可用。
　

圖17-18 「選擇資料表」畫面
按一下 下一步 顯示 選擇資料表資料行 畫面。此處您可選擇適用於全文檢索查詢的資料行。圖17-19顯示的是已經選取了數個資料行的情形。
　

圖17-19 選擇資料表資料行畫面，已選取數個資料行的情形
按一下 下一步 顯示 選擇全文檢索目錄 畫面，如圖17-20。這個畫面讓您選擇使用一個現存的索引目錄（如果已經有的話）或是建立一個新目錄。若您打算建立一個新目錄，請確定目錄放在I/O系統可支援的位置並在名稱文字方塊內輸入一個足以描述此目錄的名稱。
　

圖17-20 選擇全文檢索目錄畫面
按一下 下一步 顯示 選擇或建立擴展排程（選擇性） 畫面，如圖17-21。全文檢索索引與B型樹索引不同，它並不會在資料插入時不停地更新。排程功能讓您指定索引每隔多久就必須進行更新的動作。此處您可選擇一個已經存在的排程表（如果有的話），或是建立一個新的排程來擴展目錄，新排程可以資料表為基礎，或依每個全文檢索目錄（一個目錄可包含多個啟用全文檢索索引的資料表）而定，再不然就是選擇不使用排程。若您打算建立一個排程，您可選擇 完全擴展 或是 遞增擴展 。完全擴展意指系統會為資料表內所有的資料列（或目錄中所有的資料表）建立索引項目（如果它們已有索引項目，則會重建）。完全擴展通常只發生在目錄建立的時候。遞增擴展則是僅為資料表中被修改的資料列更新它們的索引項目。要執行遞增擴展，資料表中必須有timestamp資料行。如果沒有，則會執行完全擴展。
　

圖17-21 選擇或建立擴展排程（選擇性）畫面

從這裡，您可以按 下一步 繼續精靈或選擇建立一個排程。如果您按一下 下一步 而沒有建立任何擴展排程，全文檢索索引只會在精靈結束時被建立一次（而不是每隔一段時間便重新建立）。

說明

由於全文檢索索引在資料庫變更發生時不會不停地更新，您必須週期性地更新它。排程功能允許您讓全文檢索索引的更新動作可依時程自動執行。一旦建立了排程，索引便會依照排程的設定來進行更新。
按一下 下一步 顯示 完成SQL Server全文檢索索引精靈 畫面，如圖17-22。按一下完成，全文檢索索引精靈將會為您建立全文檢索索引目錄。如果您制定了更新排程，排程也會開始進行。一旦建立了目錄，您便可開始使用全文檢索索引的功能。
　

圖17-22 「完成SQL Server全文檢索索引精靈」畫面

使用預存程序建立全文檢索索引
　

您也可以使用預存程序來建立全文檢索索引。此處只概略的介紹一下如何使用預存程序來建立全文檢索索引，完整的語法請參考SQL Server 2000線上叢書。

- 呼叫sp_fulltext_database預存程序，利用enable引數啟用SQL Server中的全文檢索索引。
- 呼叫sp_fulltext_catalog，利用create引數建立目錄。
- 呼叫sp_fulltext_table來建立目錄與資料表/索引的對應連接。此預存程序必須以create引數來呼叫，且必須提供全文檢索索引要使用的資料表名稱以及唯一索引的名稱。
- 呼叫sp_fulltext_column來新增用於全文檢索索引的一個資料行。此預存程序必須以add選項以及打算加入目錄的資料行名稱來執行，且索引中的每個資料行都必須執行一次。
- 再次呼叫sp_fulltext_table。此次預存程序呼叫需利用activate引數來啟動含有該資料表的目錄。
- 再次呼叫sp_fulltext_catalog，這一次需利用start_full引數來啟動目錄的完全擴展，全文檢索索引會擷取與目錄相關的每個資料表的每個資料列。

使用預存程序來建立全文檢索索引，比起使用T-SQL命令來建立B型樹狀結構索引要來得複雜。然而，若您必須建立為數不少的全文檢索索引目錄，最好是設計一個指令碼來執行這個任務，雖然有點麻煩，但卻相當值得。

使用全文檢索索引
　

一旦全文檢索索引建立好了之後，要利用它的功能是很容易的事。您可指定利用全文檢索索引的T-SQL關鍵字： CONTAINS 以及 FREETEXT 。若您在不使用全文檢索索引的情況下，使用SQL搜尋字串，下列陳述式即為您會使用的典型方式。如您所見，查詢的WHERE子句將會用到LIKE關鍵字：

SELECT * FROM Customers WHERE ContactName
    LIKE '%PETE%'

這個陳述式可能會無法得到您想要的結果。要使用更為方便的查詢方式，並且利用全文檢索索引，您可使用CONTAINS述詞。CONTAINS述詞必須包含資料行名稱及想要檢索的特定文字：

SELECT * FROM Customers WHERE
    CONTAINS(ContactName, '"PETE"')

CONTAINS 述詞會利用全文檢索索引找出包含特定文字的文字字串，例如「PETER」或「PETEY」。

您也可以利用 FREETEXT 關鍵字來搜尋全文檢索索引。如同 CONTAINS ， FREETEXT 是在 WHERE 子句中使用。 FREETEXT 可用來找出單字（或相關單字），這些單字與您在 FREETEXT 中指定的單字大略相符，但是找出來的單字並不一定要與您在 FREETEXT 中指定的單字一模一樣。在SQL陳述式中使用FREETEXT的方式如下：

SELECT CategoryName FROM Categories WHERE
    FREETEXT(Description, 'Sweets candy bread')

這個查詢會找出一些含有像是「sweetened」、「candied」或是「breads」這類字的名稱。

重新建置索引
　

SQL Server會保留每個索引的統計資料，這些統計資料描述了它的唯一性或選擇性，以及索引鍵值的離散性。SQL Server查詢最佳化器隨後會利用這些統計資料來判斷使用哪個索引，最能滿足特定查詢的需求。在預設狀況下，索引統計資料會定期地更新。不過，由於分頁分隔實際上會分散資料庫中的索引分頁，索引在經過一段時間後可能會變得極為片斷，結果就是惡化了系統的效能。索引也可能變得不平衡，亦即索引樹中的某一部份比起其他部分來說有更多填滿的索引分頁。您可以重新建置索引來回復其平衡與連續性。另外，重新建置索引時其統計資料也會重建。但請特別注意，並不需要先卸除索引再重建索引。我們會在稍後解釋這個問題。

說明

在預設狀況下，索引統計資料會自動更新，不過您可利用sp_autostats預存程序將這個功能關閉。

對一個已經變得片斷化的索引而言，另一個問題是索引會有多於適量需求的索引層級。更多的索引層級意味著每個索引查詢需要更多的I/O操作。您可以重新建置索引來減少索引層級的數目，並且因而降低所有索引查詢所需的I/O操作量。

重新建置索引的一個方法是先手動移除索引，然後再重新建立該索引。對一個較小的資料表而言，這個方式勉強可以接受。但若處理的是中型或大型的資料表，則不應使用這個方法。最好是選擇本節稍後將要介紹的方式來重建索引，該方法並不需要先卸除再重新建立索引。有幾個理由支持這個選擇。當我們在叢集資料表上建立非叢集索引時，非叢集索引是以叢集索引為基礎而建立。若我們卸除了叢集索引，非叢集索引就必須重建一次，因為原先作為根據的叢集索引已不存在於資料表上。假設我們接著又在資料表上建立新的叢集索引，非叢集索引又必須再重建第二次！換言之，如果您先卸除再重新建立叢集索引，您必須重建非叢集索引兩次：一次發生在叢集索引卸除時，一次發生在叢集索引重建時。若您使用另一個方法來重新建置叢集索引，非叢集索引將只需要重建一次。

要重建索引而不需先卸除再重新建立，可使用 CREATE INDEX...DROP_EXISTING 或使用 DBCC DBREINDEX 。這兩種選擇都可以一個步驟來重新建置索引，並告訴SQL Server要重新組織現有的索引。使用這些方法可以讓您在重新建置叢集索引時避免非叢集索引的刪除與重建。這些只要一個步驟的方法也會利用目前索引中資料排列的順序，這些資料並不需要再重新排序。

CREATE INDEX...DROP_EXISTING 是用來在資料表上一次只重建一個索引。 DBCC DBREINDEX 則是與資料庫名稱和資料表名稱一起使用，可以重建該資料表上所有的索引，而不必為每個索引執行個別的命令。這兩個命令的語法與選項請參閱線上叢書。

更新索引統計資料
　

如果您沒有時間或資源去重建索引，您也可以單獨地只更新統計資料。這個技術得到的結果也許不如重新建置索引那般有效，因為索引可能已經片斷化，產生的問題遠比統計資料過期要來得複雜。這裡也假設您已將SQL Server中統計資料自動更新的功能關閉（否則的話，您的統計資料無論如何都會被週期性的更新）。您可使用 UPDATE STATISTICS 命令手動更新索引統計資料，語法如下：

UPDATE STATISTICS table_name
[ index_name | (statistics_name   [, statistics_name, ...] ]
[ WITH
[ FULLSCAN | SAMPLE number {PERCENT | ROWS} ]
[ ALL | COLUMNS | INDEX ]
[ NORECOMPUTE]
]

中括號內的值是選擇性的，唯一必須的引數是 table_name 。選擇性的引數在表17-2中說明。

如果您的系統必須處理大量的插入、更新，以及刪除的作業，您應偶爾重新建置索引以避免效能如前述狀況般降低。若您無法重新建置索引，至少也應定期的更新統計資料。

表17-2 UPDATE STATISTICS命令的選擇性引數

引數	說明
index_name	指定要重新計算統計資料的索引。在預設狀況下，資料表中所有的索引其統計資料都會重新計算。如果指定了index_name，就只有該索引的統計資料會重新計算。
statistics_name	允許您指定那些統計資料要重新計算。如果不指定這個值，所有的統計資料都會重新計算。
FULLSCAN	指定讀取資料表中所有的資料列以收集統計資料。使用這個引數是收集統計資料最好的方式，但它也會消耗最多的系統資源與時間。
SAMPLE number PERCENT \| ROWS	指定統計資料要以多少資料列為基礎，您可以提供資料列數或百分比率。在預設狀況下，SQL Server會自行決定要取樣的資料列數。這個選項無法與FULLSCAN選項共存。
ALL \| COLUMN \| INDEX	指定要收集所有的統計資料、或是資料行統計資料、或只收集索引統計資料。
NORECOMPUTE	指定統計資料在未來不會自動地重新計算。要重新開啟統計資料自動重新計算的功能，可再次執行這個命令並且不要使用NORECOMPUTE選項，或是執行sp_autostats 預存程序。

使用索引
　

現在您已經了解如何建立索引，讓我們接著來看一下如何使用索引。即使索引已經存在也不意味著SQL Server一定會使用它。一個索引是否會被SQL Server使用要看索引與SQL陳述式而定。此外，如果存在著多個索引，SQL Server可能就必須去選擇要使用哪個索引。在本節中，您會看到SQL Server如何選擇索引，您還會學到如何利用 索引提示 來指定使用哪個索引。您也會學到如何利用Query Analyzer來檢視一個查詢執行計劃。

使用提示
　

當SQL Server查詢最佳化器產生一個查詢執行計劃，它會選擇一個能提供最佳效能的索引－通常這個索引會用到最少的I/O操作並檢索最少量的資料列。

雖然查詢最佳化器通常會為您的查詢選擇最有效率的查詢執行計劃與存取路徑，但是若您對資料的了解比查詢最佳化器知道得更多，您也許可以做出更好的決定。舉例來說，假設您想在某一個資料表的名稱資料行上檢索名稱為「Smith」的個人相關資料。索引統計資料是依照該資料行來建立的，並且假設其顯示出在該資料行中每個名字平均出現三次。這個資訊意味著該資料行具有相當良好的選擇性；然而，您卻知道「Smith」事實上出現的次數比平均值要高很多。若您知道如何指示SQL作業會更有效率，您可使用提示。提示（hint）是您給予查詢最佳化器的簡單建議，指定其不需進行自動地選擇。

有數種型態的提示可用－包括聯結提示、查詢提示以及資料表提示－但此處我們將焦點放在資料表提示上。資料表提示讓您指定如何存取該資料表（其他類型的提示會在第35章中介紹）。資料表提示可用來指定下列資訊：

資料表掃瞄 在某些狀況下，您可能會發現一個資料表掃瞄要比一個索引查詢或索引掃瞄更有效率。當索引掃瞄需檢索資料表中超過20% 的資料列時，資料表掃瞄會更有效率－例如，當資料表中有30% 是「Smith」。
　
要使用哪個索引 您可指定一個特定索引作為唯一要考慮的索引。您可能不知道SQL Server查詢最佳化器在沒有提示的狀況下將會選擇哪個索引，但您直覺得了解提示使用該索引會有最佳的效能。
　
要從哪一組索引中作選擇 您可以對查詢最佳化器建議數個索引，這些索引查詢最佳化器都會使用（忽略重複的部分）。當您知道某個索引集合可以運作良好時，這個選項會很有用。

鎖定方法 當查詢最佳化器從特定資料表存取資料時，您可以告訴它要使用哪種類型的鎖定。若您直覺到錯誤的鎖定類型可能會被選取用在此資料表上，您可指定查詢最佳化器應使用資料列鎖定、分頁鎖定或資料表鎖定。
　

讓我們看一下如何利用提示來指定應使用的索引－也就是索引提示。下列範例顯示出在T-SQL陳述式中使用索引提示的情形（該查詢使用Region索引）：

SELECT *
FROM Customers WITH (INDEX(Region))
WHERE Region = 'OR' AND City = 'Portland'

請注意索引提示是擺在WITH關鍵字之後。如果您想要指定多個索引給SQL Server使用，在T-SQL陳述式中把它們列出來，方式如下：

SELECT *
FROM Customers WITH (INDEX(Region, City, CompanyName))
WHERE Region = 'OR' AND City = 'Portland'

索引提示的引數可以是一個索引名稱（就如剛剛看到的那樣）或是一個索引識別碼（Index ID）。在提示中利用索引識別碼時有些特殊的情況，如下表所示。

提示	結果
INDEX(0)使用在一個叢集資料表上（此資料表上存在一個叢集索引）	強制執行一個叢集索引掃瞄
INDEX(1)使用在一個叢集資料表上	強制執行一個叢集索引掃瞄或搜尋
INDEX(0)使用在一個非叢集資料表上（此資料表上沒有叢集索引）	強制執行一個資料表掃瞄
INDEX(1)使用在一個非叢集資料表上	會像發生錯誤一樣被中斷