資料庫索引，資料庫索引原理

資料庫索引，資料庫索引原理

一、索引是什麼

索引是與表或視圖關聯的磁碟上結構，可以加快從表或視圖中檢索行的速度。索引包含由表或視圖中的一列或多列產生的鍵。這些鍵儲存在一個結構（B 樹）中，使 SQL Server 可以快速有效地尋找與鍵值關聯的行。

>  1.1表或視圖可以包含以下類型的索引：

• 聚集 o 叢集索引根據資料行的鍵值在表或視圖中排序和儲存這些資料行。索引定義中包含叢集索引列。每個表只能有一個叢集索引，因為資料行本身只能按一個順序排序。 o 只有當表包含叢集索引時，表中的資料行才按排序次序儲存。如果表具有叢集索引，則該表稱為聚集表。如果表沒有叢集索引，則其資料行儲存在一個稱為堆的無序結構中。
• 非聚集 o 非叢集索引具有獨立於資料行的結構。非叢集索引包含非叢集索引鍵值，並且每個鍵值項都有指向包含該鍵值的資料行的指標。 o 從非叢集索引中的索引行指向資料行的指標稱為行定位器。行定位器的結構取決於資料頁是儲存在堆中還是聚集表中。對於堆，行定位器是指向行的指標。對於聚集表，行定位器是叢集索引鍵。 o 您可以向非叢集索引的葉級添加非鍵列以跳過現有的索引鍵限制（900 位元組和 16 鍵列），並執行完整範圍內的索引查詢。

叢集索引和非叢集索引都可以是唯一的。這意味著任何兩行都不能有相同的索引鍵值。另外，索引也可以不是唯一的，即多行可以共用同一鍵值。

每當修改了表資料後，都會自動維護表或視圖的索引。

>  1.2索引和約束

對錶列定義了 PRIMARY KEY 約束和 UNIQUE 約束時，會自動建立索引。例如，如果建立了表並將一個特定列標識為主鍵，則 資料庫引擎自動對該列建立 PRIMARY KEY 約束和索引。有關詳細資料，請參閱建立索引（資料庫引擎）。

二、索引有什麼用

• 與書中的索引一樣，資料庫中的索引使您可以快速找到表或索引檢視表中的特定資訊。索引包含從表或視圖中一個或多個列產生的鍵，以及映射到指定資料的儲存位置的指標。通過建立設計良好的索引以支援查詢，可以顯著提高資料庫查詢和應用程式的效能。索引可以減少為返回查詢結果集而必須讀取的資料量。索引還可以強製表中的行具有唯一性，從而確保表資料的資料完整性。

• 設計良好的索引可以減少磁碟 I/O 操作，並且消耗的系統資源也較少，從而可以提高查詢效能。對於包含 SELECT、UPDATE、DELETE 或 MERGE 語句的各種查詢，索引會很有用。例如，在 AdventureWorks 資料庫中執行的查詢 SELECT Title, HireDate FROM HumanResources.Employee WHERE EmployeeID = 250。執行此查詢時，查詢最佳化工具評估可用於檢索資料的每個方法，然後選擇最有效方法。可能採用的方法包括掃描表和掃描一個或多個索引（如果有）。

• 掃描表時，查詢最佳化工具讀取表中的所有行，並提取滿足查詢條件的行。掃描表會有許多磁碟 I/O 操作，並佔用大量資源。但是，如果查詢的結果集是占表中較高百分比的行，掃描表會是最為有效方法。

• 查詢最佳化工具使用索引時，搜尋索引鍵列，尋找到查詢所需行的儲存位置，然後從該位置提取匹配行。通常，搜尋索引比搜尋表要快很多，因為索引與表不同，一般每行包含的列非常少，且行遵循排序次序。

• 查詢最佳化工具在執行查詢時通常會選擇最有效方法。但如果沒有索引，則查詢最佳化工具必須掃描表。您的任務是設計並建立最適合您的環境的索引，以便查詢最佳化工具可以從多個有效索引中選擇。SQL Server 提供的Database Engine Tuning Advisor可以協助分析資料庫環境並選擇適當的索引。

三、索引的優勢

第一，通過建立唯一性索引，可以保證資料庫表中每一行資料的唯一性。 第二，可以大大加快 資料的檢索速度，這也是建立索引的最主要的原因。 第三，可以加速表和表之間的串連，特別是在實現資料的參考完整性方面特別有意義。 第四，在使用分組和排序 子句進行資料檢索時，同樣可以顯著減少查詢中分組和排序的時間。 第五，通過使用索引，可以在查詢的過程中，使用最佳化隱藏器，提高系統的效能。

四、索引既然這麼有優勢，為什麼不每一列都建立

第一，建立索引和維護索引要耗費時間，這種時間隨著資料 量的增加而增加。 第二，索引需要佔物理空間，除了資料表占資料空間之外，每一個索引還要佔一定的物理空間，如果要建立聚簇索引，那麼需要的空間就會更大。 第三，當對錶中的資料進行增加、刪除和修改的時候，索引也要動態維護，這樣就降低了資料的維護速度。

五、 建立索引的一些策略

• 在經常需要搜尋的列上，可以加快搜尋的速度；
• 在作為主鍵的列上，強制該列的唯一性和組織表中資料的排列結構；
• 在經常用在串連的列上，這 些列主要是一些外鍵，可以加快串連的速度；
• 在經常需要根據範圍進行搜尋的列上建立索引，因為索引已經排序，其指定的範圍是連續的；
• 在經常需要排序的列上創 建索引，因為索引已經排序，這樣查詢可以利用索引的排序，加快排序查詢時間；
• 在經常使用在WHERE子句中的列上面建立索引，加快條件的判斷速度。

同樣，對於有些列不應該建立索引。

• 第一，對於那些在查詢中很少使用或者參考的列不應該建立索引。這是因 為，既然這些列很少使用到，因此有索引或者無索引，並不能提高查詢速度。相反，由於增加了索引，反而降低了系統的維護速度和增大了空間需求。
• 第二，對於那 些只有很少資料值的列也不應該增加索引。這是因為，由於這些列的取值很少，例如人事表的性別列，在查詢的結果中，結果集的資料行佔了表中資料行的很大比 例，即需要在表中搜尋的資料行的比例很大。增加索引，並不能明顯加快檢索速度。
• 第三，對於那些定義為text, image和bit資料類型的列不應該增加索引。這是因為，這些列的資料量要麼相當大，要麼取值很少。
• 第四，當修改效能遠遠大於檢索效能時，不應該建立索 引。這是因為，修改效能和檢索效能是互相矛盾的。當增加索引時，會提高檢索效能，但是會降低修改效能。當減少索引時，會提高修改效能，降低檢索效能。因 此，當修改效能遠遠大於檢索效能時，不應該建立索引。

六、建立索引的方法

建立索引有多種方法，這些方法包括直接建立索引的方法和間接建立索引的方法。

• 直接建立索引，例如使用CREATE INDEX語句或者使用建立索引嚮導

• 間接建立索引，例如在表中定義主鍵約束或者唯一性鍵約束時，同時也建立了索引。

  雖然，這兩種方法都可以建立索引，但 是，它們建立索引的具體內容是有區別的。

  使用CREATE INDEX語句或者使用建立索引嚮導來建立索引，這是最基本的索引建立方式，並且這種方法最具有柔性，可以定製建立出符合自己需要的索引。在使用這種方式 建立索引時，可以使用許多選項，例如指定資料頁的充滿度、進行排序、整理統計資訊等，這樣可以最佳化索引。使用這種方法，可以指定索引的類型、唯一性和複合 性，也就是說，既可以建立聚簇索引，也可以建立非聚簇索引，既可以在一個列上建立索引，也可以在兩個或者兩個以上的列上建立索引。通過定義主鍵約束或者唯一性鍵約束，也可以間接建立索引。主鍵約束是一種保持資料完整性的邏輯，它限制表中的記錄有相同的主鍵記錄。在建立主鍵約束時，系 統自動建立了一個唯一性的聚簇索引。雖然，在邏輯上，主鍵約束是一種重要的結構，但是，在物理結構上，與主鍵約束相對應的結構是唯一性的聚簇索引。換句話 說，在物理實現上，不存在主鍵約束，而只存在唯一性的聚簇索引。同樣，在建立唯一性鍵約束時，也同時建立了索引，這種索引則是唯一性的非聚簇索引。因此， 當使用約束建立索引時，索引的類型和特徵基本上都已經確定了，由使用者定製的餘地比較小。

當在表上定義主鍵或者唯一性鍵約束時，如果表中已經有了使用CREATE INDEX語句建立的標準索引時，那麼主鍵約束或者唯一性鍵約束建立的索引覆蓋以前建立的標準索引。也就是說，主鍵約束或者唯一性鍵約束建立的索引的優先 級高於使用CREATE INDEX語句建立的索引。

七、索引的特徵

    索引有兩個特徵，即唯一性索引和複合索引。 

• 唯一性索引保證在索引列中的全部資料是唯一的，不會包含冗餘資料。如果表中已經有一個主鍵約束或者唯一性鍵約束，那麼當建立表或者修改表時，SQL Server自動建立一個唯一性索引。然而，如果必須保證唯一性，那麼應該建立主鍵約束或者唯一性鍵約束，而不是建立一個唯一性索引。當建立唯一性索引 時，應該認真考慮這些規則：當在表中建立主鍵約束或者唯一性鍵約束時，SQL Server自動建立一個唯一性索引；如果表中已經包含有資料，那麼當建立索引時，SQL Server檢查表中已有資料的冗餘性；每當使用插入語句插入資料或者使用修改語句修改資料時，SQL Server檢查資料的冗餘性：如果有冗餘值，那麼SQL Server取消該語句的執行，並且返回一個錯誤訊息；確保表中的每一行資料都有一個唯一值，這樣可以確保每一個實體都可以唯一確認；只能在可以保證實體 完整性的列上建立唯一性索引，例如，不能在人事表中的姓名列上建立唯一性索引，因為人們可以有相同的姓名。

• 複合索引就是一個索引建立在兩個列或者多個列上。在搜尋時，當兩個或者多個列作為一個關鍵值時，最好在這些列上建立複合索引。當建立複合索引時，應該考慮 這些規則：最多可以把16個列合并成一個單獨的複合索引，構成複合索引的列的總長度不能超過900位元組，也就是說複合列的長度不能太長；在複合索引中，所 有的列必須來自同一個表中，不能跨表建立複合列；在複合索引中，列的排列順序是非常重要的，因此要認真排列列的順序，原則上，應該首先定義最唯一的列，例 如在（COL1，COL2）上的索引與在（COL2，COL1）上的索引是不相同的，因為兩個索引的列的順序不同；為了使查詢最佳化工具使用複合索引，查詢語 句中的WHERE子句必須參考複合索引中第一個列；當表中有多個關鍵列時，複合索引是非常有用的；使用複合索引可以提高查詢效能，減少在一個表中所建立的 索引數量。