• 發行項

一般索引設計指導方針

經驗豐富的資料庫管理員可以設計出一組數量適中的索引，但即使是普通複雜的資料庫與工作量，這都是一件非常複雜、費時，且容易出錯的工作。瞭解資料庫、查詢和資料行的特性可以協助您設計最佳化的索引。

資料庫考量

當您設計索引時，請考慮下列資料庫指導方針：

  • 資料表中的索引數量過多會影響到 INSERT、UPDATE、DELETE 和 MERGE 陳述式的效能，因為只要資料表中的資料一變更，所有的索引也都必須隨著調整。

    • 請避免對時常更新的資料表過度索引，保持索引窄小，愈少資料行愈好。

    • 對不常更新、但有大量資料的資料表使用多個索引可增進查詢效能。大量索引可以協助不修改資料之查詢的效能，例如 SELECT 陳述式，因為查詢最佳化工具有較多的索引可供選擇，以判斷最快的存取方法。

  • 為小型資料表建立索引並不是最佳的方式，因為查詢最佳化工具透過查閱索引來搜尋資料，會比執行簡單的資料表掃描更費時。因此，小型資料表上的索引不僅很少使用，而且還必須在資料表中的資料變更時進行維護。

  • 當檢視包含彙總、資料表聯結或彙總與聯結的組合時，在檢視上建立索引可以提供重要的效能增進。查詢中不必明確參考檢視，查詢最佳化工具會使用它。如需詳細資訊，請參閱＜設計索引檢視＞。

  • 使用 Database Engine Tuning Advisor 可分析資料庫，並提供索引建議。如需詳細資訊，請參閱＜了解 Database Engine Tuning Advisor＞。

查詢考量

當您設計索引時，請考慮下列查詢指導方針：

  • 在經常使用於述詞中，以及在查詢中使用聯結條件的所有資料行上建立非叢集索引。

    重要注意事項重要事項

    避免加入不必要的資料行。加入太多索引資料行可能會對磁碟空間和索引維護效能產生不利的影響。

  • 涵蓋索引可以增進查詢效能，因為查詢就存在於索引本身裡面，所有需要的資料都符合查詢的需求。也就是說，擷取要求的資料時只需要索引頁，非資料表或叢集索引的資料頁；因此，可以減少整體的磁碟 I/O。例如，某個資料表在 a 和 b 和 c 資料行上已建立複合的索引，則對資料行 a 和 b 查詢可以單獨從索引擷取指定的資料。

  • 撰寫的查詢應盡可能在一個陳述式中插入或修改最多資料列，而不是使用多個查詢來更新同樣的資料列。只使用一個陳述式，才能利用到最佳化的索引維護方式。

  • 評估查詢類型，以及查詢中如何使用資料行。例如，在完全相符查詢類型中使用的資料行，就很適合當作非叢集或叢集索引。如需詳細資訊，請參閱＜查詢類型和索引＞。

資料行考量

當您設計索引時，請考慮下列資料行指導方針：

  • 讓叢集索引保持短小的索引鍵。此外，對唯一或非 Null 資料行建立叢集索引，會有幫助。如需詳細資訊，請參閱＜叢集索引設計指導方針＞。

  • ntext、text、image、varchar(max)、nvarchar(max) 和 varbinary(max) 資料類型的資料行不能指定為索引鍵資料行。但是，varchar(max)、nvarchar(max)、varbinary(max) 和 xml 資料類型則可參與非叢集索引，作為非索引鍵之索引資料行。如需詳細資訊，請參閱＜具有內含資料行的索引＞。

  • xml 資料類型只可以是 XML 索引的索引鍵資料行。如需詳細資訊，請參閱＜XML 資料類型資料行中的索引＞。

  • 檢驗資料行唯一性。在相同組合的資料行上，唯一索引可提供額外的資訊給查詢最佳化工具，讓索引變得更有用。如需詳細資訊，請參閱＜唯一索引設計指導方針＞。

  • 檢驗資料行中的資料分散情形。執行時間長的查詢往往是因為對唯一值少的資料行製作索引所造成，或對這樣的資料行執行聯結所造成。這是資料與查詢本身的問題，若不找出問題，通常是無法解決的。例如，如果城市裡每個人的名字都是 Smith 或 Jones，那麼依姓氏排序的實體電話簿就無法使尋找某人的速度加快。如需有關資料分散的詳細資訊，請參閱＜使用統計資料來改善查詢效能＞。

  • 請考慮在具有定義良好之子集的資料行 (如疏鬆資料行)、大部分的值都是 NULL 的資料行、具有值類別的資料行以及具有相異值範圍的資料行上使用篩選的索引。設計良好的已篩選索引可以提升查詢效能、降低索引維護成本，並減少儲存成本。如需詳細資訊，請參閱＜篩選索引設計指導方針＞。

  • 如果索引將包含多個資料行，可考慮資料行的順序。用於 WHERE 子句等於 (=)、大於 (>)、小於 (<) 或 BETWEEN 搜尋條件中的資料行，或是參與聯結的資料行，應該要放在前面。其他資料行應該按照它們的區分程度排序，亦即，從最能區分的排到最不能區分的。

    例如，如果索引定義為 LastName、FirstName，當搜尋條件是 WHERE LastName = 'Smith' 或 WHERE LastName = Smith AND FirstName LIKE 'J%' 時索引就會很有用。但是，查詢最佳化工具不會為只搜尋 FirstName (WHERE FirstName = 'Jane') 的查詢使用索引。

  • 考慮為計算的資料行建立索引。如需詳細資訊，請參閱＜在計算資料行上建立索引＞。

索引特性

當您決定好適合某一查詢的索引之後，便可選取最符合您需要的索引類型。索引的特性包括下列項目：

  • 叢集或非叢集

  • 唯一或非唯一

  • 單一資料行或多重資料行

  • 索引中的資料行遞增或遞減順序

  • 完整資料表與非叢集索引的篩選

您也可以自訂索引的初始儲存特性，設定如 FILLFACTOR 的選項來最佳化其效能或維護。如需詳細資訊，請參閱＜設定索引選項＞。您也可以使用檔案群組或資料分割配置的方式決定索引儲存位置，以最佳化效能。如需詳細資訊，請參閱＜在檔案群組中放置索引＞。