SQL Server索引進階：第十五級，索引的最佳實務

標籤：

在本文中我們將推薦14條貫穿本系列的規則，這些規則協助你為資料庫建立最好的索引結構。

格式來自於《Framework Design Guidelines》。每條推薦用四個詞來總結：Do做，Consider考慮，Void避免，Do Not不要做。

• 做。總是要遵守的規則。
• 考慮。通常來說應該遵守，但是如果你完全理解了規則背後的原因，並且有你不遵守的原因。
• 避免。和考慮相反，通常建議不這麼做，但是如果你完全理解了為什麼不應該這麼做，你有這麼做的原因，你可以這麼做。
• 不要做。比避免語氣要強，表面有些事永遠不要做。

指導原則

Do know your Application/Users

索引的主要目的是提高查詢和操作資料的效能，除非你知道這些操作是什麼，否則你沒有希望改進他們。

最好是在應用的開始就考慮，在設計和開發中加入。如果你繼承了一個已經存在的資料庫和應用，從兩方面理解你繼承的是什麼：內部和外部。

外部包括從使用者角度，和他們聊天，觀察他們使用應用，閱讀使用者文檔和手冊，查看當前的表格和報表。

內部包括檢查應用本身，應用的定義和應用的執行。使用工具，Activity Monitor，Profiler，sys.dm_db_index usage_stats動態視圖，以及sys.dm_db_missing_index_XXX系列的動態視圖觀察常用查詢，慢查詢，常用索引，未使用的索引，應該存在但卻沒有建立的索引。

檢查常用查詢和慢查詢的源頭，例如，報表格服務的模板。TSQL作業的步驟，SSIS應用中的TSQL任務，預存程序，都應該被最佳化。

在瞭解了這些資訊之後，對於那些索引是好的，那些索引是不好的，你就可以做出更好的決定。

Do Not Over Index

索引太多和太少都是不好的。對錶來說沒有“最佳索引個數”這種說法。每張表的情況都不同。但是如果你要在主鍵，候選索引鍵，合適的外鍵，潛在的查詢列上建立索引，請在建立之前做一些分析。

Do Understand that:Same Database + Different Situation = Different Indexing

不管是白天處理，還是非高峰期處理；不管是聯機處理，還是對資料庫拷貝的報表處理；不同的情況，建立的索引是不同的。

Do Have a Primary Key on Every Table

儘管主鍵不是SQL Server必須的，但是沒有主鍵的表在事務的時候是非常危險的，因為不能保證行是唯一的。如果允許重複行，就會發生，你不知道是同一個實體重複插入了兩次，還是沒有足夠的資訊來區分這兩個實體。

儘管SQL Server沒有要求，主鍵是關係理論的基礎，所有關係系統的基本構成。沒有主鍵的約束，或者是唯一索引，可能會導致意外的結果，或者不好的效能。

另外，很多用戶端開發工具和組件都需要你的表有主鍵。主鍵約束的名稱就是索引的名稱。

Consider Having a Clustered Index on Every Table

在第三級，叢集索引介紹了叢集索引的好處。讓表成為叢集索引表而不是堆表。主要的好處是一個簡單的事實，使用者在查看錶資料的時候肯定會以一個預設的順序，所以就以哪個順序來維護表。

如果你按照本文的規則，每張表都有主鍵，每張表至少有一個索引，甚至更多。因此，一個叢集索引不會增加索引的數量，但是相比堆表，會給你的錶帶來一個很好的結構。

在決定叢集索引列的時候，要記住第六級，標籤中的指導原則：一個叢集索引應該唯一，短小，不變的。

Consider Using a Foreign Key in the Search Key of the Clustered Index

考慮將外鍵作為叢集索引鍵中最左面的列，這樣可以將子item的資訊聚集在父的周圍，這是一個典型的需求。你的信用卡的消費資訊和信用卡關聯，我的消費資訊和我的卡關聯。這個關係要比消費記錄和商家，或者消費記錄和處理消費記錄的金融機構，要比這些關係強。卡號是包含在消費表的叢集索引鍵中的外鍵，而不是商家編號和銀行編號。將卡號放在叢集索引的最左邊，同一個持卡人的消費記錄就會聚集在相同的頁中。

Consider Having Included Columns in your Indexes

考慮在你的非叢集索引中添加包含列。

因為一般的非叢集索引都是從某種角度查看錶，或者是建立的外鍵的基礎上，但是除了非叢集索引的鍵列，還會需要一些其他列，但是這些列不作為查詢條件，只是需要顯示或者統計它們，這時候，這些列就可以添加為包含列，就不用再去訪問資料行了，直接在非叢集索引中就可以完成請求。

Avoid Nonclustered, Unfilterd Indexes on Columns that have few Distinct Values

有句老話：“不要在性別列上建立索引”。表中的一頁將會有一半的值為男，一半的值為女，不管是請求男還是女，掃描表都是最好的決定。因此，這樣的一個索引永遠不會被查詢最佳化工具使用。

Consider Create a Filtered Index for Columns that Have a Dominate Value

如果表中有一列，大部分行的值都相同，或者都是NULL，那麼就在這列上建立一個過濾索引。那些查詢小部分值的時候，就會使用索引；查詢大部分值的時候就掃描表。

Consider Specifying Fill Factor Values that Anticipate Future Size Requirements

 

Consider Specifying Fill Factor Values that Reflect the Table‘s Steady-state Page Fragmentation Value

 

Do Create a Table‘s Clustered Index Before Creating its Nonclustered Indexes

這條規則的一個推論就是：刪除叢集索引之前，先刪除非叢集索引。否則會導致非叢集索引出現不必要的重建。表從堆表，轉變成叢集索引表，總是會導致非叢集索引的重建，因為非叢集索引的書籤的內容會從行號變成叢集索引的鍵。

Do Plan Your Index Defragmenting and Rebuilding Based Upon Usage

如果一個索引經常被掃描，索引的外部片段是很重要的，對於全掃描或者掃描部分葉子層會產生重要的影響。如果是這種情況，在外部片段達到10%的時候，考慮重新組織索引，當達到30%的時候，考慮重建索引。

但是，如果，索引只是通過一個鍵來查詢，外部片段對效能的影響很小，甚至沒有影響。從根頁到葉子層的一頁所需要的IO，將會忽略外部片段，將會是相同的。這時候，重新組織和重建索引對於效能沒有提升。

Do Update Index Statistics On a Regular Basis

關鍵字是“規律的”，因為只有知道你的應用在做什麼，你才能決定什麼時候統計資訊需要更新。在第十四級中有這部分的介紹。

結論

這些指導原則來自於很多在SQL Server上工作過多年的開發人員，根據這些指導原則，你可以在你的資料庫上建立最好的索引。

SQL Server索引進階：第十五級，索引的最佳實務