前言
說起來很慚愧，學了三年的資料庫，到昨天才第一次知道資料庫索引的重要性，最近因為系統在顯示時非常的慢，只是從70萬筆資料中Select出1筆，就花了10幾秒，客戶不斷的抱怨，試了很久，發現瓶頸是在資料庫，可在當時我卻不知道要怎麼處理，反而是昨天客戶問我說你有沒有建索引，我說有(因為建立PrimaryKey就會同時建立索引)，他說他之前也有遇過效能的問題，建立索引後就改善了不少，當下我就將Where用到的資料行加上索引，同樣條件的Select快了十至百倍，原本要10幾秒才回應的資料，現在不到1秒就顯示完成了，今天我就收集些索引的資訊，也作了簡單的嘗試，現在分享給大家別犯跟我一樣的錯誤。
有索引沒索引的差異
測試平台:Sql Server 2005 Express
測試工具:SQL Server Profiler
測試資料產生器: Microsoft Visual Studio Team System 2008 Database Edition
測試資料筆數:100萬筆
測試資料表:Customs(Id,Name,.....)
測試語法:SELECT * FROM Customs WHERE Name LIKE '%黃%' (2491筆結果)
圖一 用Microsoft Visual Studio Team System 2008 Database Edition產生的資料
沒有索引的測試結果
測試結果: 49秒21
有索引的測試結果
測試結果: 4秒52
差了近十倍的時間。
如何建立索引鍵
建立索引可以用SQL，也可以用工具建立
使用SQL
CREATE NONCLUSTERED INDEX IX_Customs ON dbo.Customs
(
NAME
)
使用工具如 SQL Server Management Studio
在使用SQL Server Management Studio編輯資料表時，Menu會出現資料表設計工具，選擇索引/索引鍵，會出現索引/索引鍵建立工具來編輯。
圖二 資料表設計工具
圖三 索引/索引鍵工具
下一回將介紹 如何使用 Database Engine Tuning Advisor 可分析資料庫，並提供索引建議。
什麼是索引(從MSDN擷取出來)
就像書籍中的索引一樣，資料庫中的索引可讓您快速地找到資料表或索引檢視中的特定資訊。索引中包含從資料表或檢視中一或多個資料行建立的索引鍵，並包含對應至指定資料儲存位置的指標。您可以建立設計精良的索引來支援查詢，以大幅改善資料庫查詢及應用程式的效能。索引可以減少必須讀取才能傳回查詢結果集的資料量。索引也可以對資料表的資料列強制唯一性，以確保資料表資料的資料完整性。
索引基本概念
索引是一種與資料表或檢視有關的磁碟內存結構，它會加快從該資料表或檢視中擷取資料列的速度。索引中包含從資料表或檢視中一或多個資料行建出的索引鍵。這些索引鍵儲存在結構中 (B 型樹狀目錄)，讓 SQL Server 可以快速有效地找到與索引鍵值相關的一或多個資料列。
資料表或檢視可包含下列類型的索引：
- 叢集
- 叢集索引將資料表或檢視中的資料列依其索引鍵值排序與儲存。這些就是索引定義中包含的資料行。因為資料列本身只能以一種順序排序，所以每個資料表只能有一個叢集索引。
- 只有當資料表包含叢集索引時，資料表中的資料列才會以排序順序儲存。當資料表有叢集索引時，資料表又稱為叢集資料表。如果資料表沒有任何叢集索引，它的資料列就儲存在未排序的結構中，這個結構稱為堆積。
- 非叢集
- 非叢集索引有一個與資料列完全分開的結構。非叢集索引包含非叢集索引鍵值，而每個索引鍵值項目都有一個指標，指向包含索引鍵值的資料列。
- 從非叢集索引中的索引列指向資料列的指標被稱為資料列定位器。資料列定位器的結構須視資料頁儲存在堆積或叢集資料表而定。若是堆積，資料列定位器是指向資料列的指標。若是叢集資料表，資料列定位器就是叢集索引鍵。
- 在 SQL Server 2005 中，您可以將無索引鍵資料行加入非叢集索引的分葉層級中，以規避現有索引鍵的限制 (900 位元組和 16 個索引鍵資料行)，並執行完全涵蓋的索引查詢。如需詳細資訊。
叢集與非叢集索引都可以是唯一的。這表示任何兩個資料列不得以相同的值做為索引鍵。否則，索引就不是唯一的，那麼多個資料列就可以共用同一個索引鍵值。如需詳細資訊。
每當修改資料表的資料時，就會自動維護資料表或檢視的索引。
索引與條件約束
在資料表的資料行上定義 PRIMARY KEY 與 UNIQUE 條件約束時，會自動建立索引。例如，當您建立資料表和識別特定資料行做為主索引鍵時，SQL Server 2005 Database Engine 會自動為該資料行建立 PRIMARY KEY 條件約束與索引。
下表列出 SQL Server 2005 中可用的索引類型。
| 索引類型 | 描述 |
|---|---|
|
叢集 |
索引叢集根據叢集索引鍵的順序來排序和儲存資料表或檢視的資料列。叢集索引將實作成 B 型樹狀索引結構，以根據它們的叢集索引鍵值快速地擷取資料列。 |
|
非叢集 |
非叢集索引可在具有叢集索引的資料表或檢視中、或是堆積中定義。非叢集索引中的每個索引資料列都含有非叢集鍵值與資料列定位器。此定位器指向含有鍵值之叢集索引或堆積中的資料列。索引中的資料列會依據索引鍵值的順序儲存，但除非叢集索引建立在資料表中，否則資料列不一定會依循任何特定的順序排列。 |
|
唯一 |
唯一索引可確保索引鍵不含重複的值，因此資料表或檢視中的每個資料列就某方面而言都是唯一的。 叢集與非叢集索引都可以是唯一的。 |
|
具有內含資料行的索引 |
除了索引鍵資料行以外，擴充為含有無索引鍵資料行的非叢集索引。 |
|
索引檢視 |
將檢視中的索引具體化 (執行) 時，檢視與結果集會永久儲存在唯一的叢集索引中，儲存方式與具有叢集索引的資料表相同。叢集索引建立後，即可加入檢視中的非叢集索引。 |
|
全文檢索 |
一種特殊類型的 Token 式功能索引，由 Microsoft Full-Text Engine for SQL Server (MSFTESQL) 服務所建立與維護。它可以有效地在字元字串資料中進行複雜字的搜尋。 |
|
XML |
在 xml 資料類型資料行中，一種細分且持續的 XML 二進位大型物件 (BLOB) 表示法。 |
索引設計基本概念
設計不良的索引與不足的索引是資料庫應用程式瓶頸的主要原因。設計有效的索引是達到良好資料庫和應用程式效能最重要的一點。為資料庫選擇正確的索引及工作負載時，往往很難在查詢速度與更新成本之間取得平衡。範圍較小的索引，或是索引的索引鍵中包含較少的資料行，所需的磁碟空間與維護負擔相對較小。相反的，如果索引範圍較大，能涵蓋的查詢就更多。在找到最有效率的索引之前，可能需要先試過數種不同的設計。索引可以新增、修改和卸除，不會影響資料庫結構描述或應用程式的設計。所以，不要吝於嘗試各種不同的索引。
使用索引不一定就會有良好的效能，良好的效能和有效率地使用索引也不能劃上等號。如果使用索引對產生最佳效能一定有幫助，查詢最佳化工具的作業就很單純。但事實上，選擇不正確的索引可能得不到最佳效能。
下列工作是針對設計索引所建議的策略：
- 瞭解資料庫本身的特性。例如，這是經常修改資料的線上交易處理 (OLTP) 資料庫嗎？或是包含主要唯讀資料的決策支援系統 (DSS) 或資料倉儲 (OLAP) 資料庫？
- 瞭解最常使用的查詢特性。例如，知道最常使用的查詢會聯結兩個以上的資料表，將有助於判斷要使用的最佳類型索引。
- 瞭解用於查詢的資料行特性。例如，對於具有整數資料類型的資料行且也是唯一或非 Null 的資料行來說，索引是最理想的方式。
- 建立或維護索引時，決定可能會提升效能的索引選項。例如，ONLINE 索引選項對於在現有的大型資料表上建立叢集索引就有幫助。ONLINE 選項會在建立或重建索引的同時，允許繼續進行基礎資料上的並行活動。
- 決定最理想的索引儲存位置。非叢集索引可以作為基礎資料表儲存在相同的檔案群組中，或儲存在不同的檔案群組中。藉由增加磁碟 I/O 效能，索引的儲存位置可提升查詢效能。例如，將非叢集索引儲存在不同磁碟機上 (與資料表檔案群組不同的磁碟機) 的檔案群組中，可以同時讀取多部磁碟機，所以可提升效能。
此外，叢集和非叢集索引可跨多個檔案群組使用資料分割配置。透過讓您快速及有效地存取或管理資料子集，分割可在維護整體集合的完整性時，讓大型資料表或索引更容易管理。當您考慮使用分割時，請決定是否應該校準索引，也就是說，使用分割資料表相同的方法進行分割，或獨立進行分割。
資料庫考量
- 資料表中的索引數量過多，會影響到 INSERT、UPDATE 與 DELETE 陳述式的執行效能，因為只要資料表中的資料一變更，所有的索引也都必須隨著調整。
- 請避免對時常更新的資料表過度索引，保持索引窄小，愈少資料行愈好。
- 對不常更新、但有大量資料的資料表使用多個索引可增進查詢效能。大量索引可以協助不修改資料之查詢的效能，例如 SELECT 陳述式，因為查詢最佳化工具有較多的索引可供選擇，以判斷最快的存取方法。
- 為小型資料表建立索引並不是最佳的方式，因為查詢最佳化工具透過查閱索引來搜尋資料，會比執行簡單的資料表掃描更費時。因此，在小型資料表上建立索引不僅很少使用，而且還必須在資料表變更時隨著資料維護。
- 當檢視包含彙總、資料表聯結或彙總與聯結的組合時，在檢視上建立索引可以提供重要的效能增進。查詢中不必明確參考檢視，查詢最佳化工具會使用它。
查詢考量
- 在經常使用於述詞中，以及在查詢中使用聯結條件的所有資料行上建立非叢集索引。
- 重要： 避免加入不必要的資料行。加入太多索引資料行可能會對磁碟空間和索引維護效能產生不利的影響。
- 涵蓋索引可以增進查詢效能，因為查詢就存在於索引本身裡面，所有需要的資料都符合查詢的需求。也就是說，擷取要求的資料時只需要索引頁，非資料表或叢集索引的資料頁；因此，可以減少整體的磁碟 I/O。例如，某個資料表在 a 和 b 和 c 資料行上已建立複合的索引，則對資料行 a 和 b 查詢可以單獨從索引擷取指定的資料。
- 撰寫的查詢應盡可能在一個陳述式中插入或修改最多資料列，而不是使用多個查詢來更新同樣的資料列。只使用一個陳述式，才能利用到最佳化的索引維護方式。
- 評估查詢類型，以及查詢中如何使用資料行。例如，在完全相符查詢類型中使用的資料行，就很適合當作非叢集或叢集索引。
