資料庫索引：綜合詳細指南

簡介

資料庫索引對於最佳化資料庫效能至關重要。它們透過提供表中行的快速訪問路徑來幫助加快資料檢索速度。瞭解索引的工作原理、型別及其最佳實踐可以顯著提高資料庫查詢的效率。

什麼是索引？

索引是一種資料結構，可以提高資料庫表上資料檢索操作的速度。它就像書中的索引一樣，讓您無需掃描整個文字即可快速找到資訊。

想象一下一本 1000 頁的大書，假設您正在嘗試查詢包含與某個詞相關的資訊的頁面。如果沒有索引頁，您將必須瀏覽每一頁，這可能要花費幾個小時甚至幾天的時間。但是有了索引頁，您就知道該去哪裡找！一旦找到正確的索引，您就可以有效地跳轉到該頁面。

索引是按字母順序排列的，並且為特定資訊提供了頁碼，這樣就節省了我們翻閱每一頁的時間。

資料庫索引的工作方式類似。它們引導資料庫找到資料的準確位置，從而實現更快、更高效的資料檢索。

在本文中，我們將探討：

• 什麼是資料庫索引？

• 它們如何工作？

• 使用它們的好處。

• 不同型別的索引。

• 他們使用哪種資料結構？

• 如何巧妙地使用它們？

什麼是資料庫索引？

資料庫索引是一種超高效的查詢表，可以讓資料庫更快地找到資料。

它儲存索引列值以及指向表中相應行的指標。

如果沒有索引，資料庫可能必須掃描海量表中的每一行才能找到所需內容——這是一個非常緩慢的過程。

但是，有了索引，資料庫就可以使用索引的指標精確地找到所需資料的準確位置。

如何建立索引？

以下是在 MySQL 資料庫中建立索引的示例。

假設我們有一個名為employees以下結構的表：

現在，讓我們在該last_name列上建立一個索引，以提高根據姓氏經常搜尋或排序的查詢的效能。

在本例中，我們使用語句在表上CREATE INDEX建立一個名為的索引。索引是在列上建立的。idx_last_name``employees``last_name

建立索引後，涉及條件或對該last_name列進行排序的查詢將被最佳化。例如：

該查詢將使用idx_last_name索引快速定位為“Smith”的行last_name，避免全表掃描。

如果您的查詢經常同時涉及多個列上的條件，您還可以在多個列上建立索引（複合索引）。例如：

first_name這會在和列上建立一個複合索引last_name，這對於基於兩列進行搜尋或排序的查詢很有用。

資料庫索引如何工作？

以下是資料庫索引如何工作的逐步說明：

1. 索引建立：資料庫管理員在特定列或一組列上建立索引。

2. 索引構建：資料庫管理系統透過掃描表並儲存索引列的值以及指向相應資料的指標來構建索引。

3. 查詢執行：執行查詢時，資料庫引擎會檢查所請求的列是否存在索引。

4. 索引搜尋：如果存在索引，資料庫將在索引中搜尋所請求的資料，並使用指標快速定位資料。

5. 資料檢索：資料庫使用索引中的指標檢索請求的資料。

資料庫索引的好處？

資料庫索引有幾個好處，包括：

• 更快的查詢效能：索引可以透過減少需要掃描的資料量來顯著提高查詢效能，特別是對於大型資料集。

• 減少 CPU 使用率：透過減少需要掃描的行數，索引可以降低 CPU 使用率並最佳化資源利用率。

• 快速資料檢索：索引可以對涉及索引列上的相等性或範圍條件的查詢進行快速資料檢索。

• 高效排序：索引還可用於根據索引列對資料進行高效排序，從而無需昂貴的排序操作。

• 更好的資料組織：索引可以幫助維護資料組織和結構，使資料庫更易於管理和維護。

資料庫索引的型別

基於結構和關鍵屬性的索引：

• 主索引：在表上定義主鍵約束時自動建立。確保唯一性並幫助使用主鍵進行超快速查詢。

• 聚集索引：確定資料在表中的物理儲存順序。當我們在某個範圍內搜尋時，聚集索引最有用。每個表只能有一個聚集索引。

• 非聚集索引或二級索引：這種索引不按索引順序儲存資料。相反，它提供指向資料實際儲存位置的虛擬指標或引用列表。

基於資料覆蓋範圍的索引：

• 密集索引：表中每個搜尋鍵值都有一個條目。適用於資料具有少量不同搜尋鍵值或需要快速訪問單個記錄的情況。

• 稀疏索引：僅包含部分搜尋鍵值的條目。適用於資料具有大量不同搜尋鍵值的情況。

專業指數型別：

• 點陣圖索引：非常適合基數較低的列（不同值較少）。在資料倉儲中很常見。

• 雜湊索引：使用雜湊函式將值對映到特定位置的索引。非常適合精確匹配查詢。

• 過濾索引：根據特定過濾條件對行子集進行索引。有助於提高常見過濾列的查詢速度。

• 覆蓋索引：索引本身包含查詢所需的所有列，無需訪問底層表資料。

• 基於函式的索引：根據對錶的一個或多個列應用函式或表示式的結果建立的索引。

• 全文索引：專為全文搜尋而設計的索引，可以有效地搜尋文字資料。

• 空間索引：用於索引地理資料型別。

索引使用什麼資料結構？

最常用的索引資料結構是 B 樹、雜湊表和點陣圖。

B 樹（平衡樹）

大多數資料庫引擎都使用 B 樹或 B 樹的變體，例如 B+ 樹。

B 樹具有層次結構，包括根節點、內部節點（索引節點）和葉節點。

B 樹中的每個節點都包含一個已排序的鍵陣列和指向子節點的指標。

它們如此適合的原因如下：

• 自平衡：B 樹確保樹的“高度”即使在插入或刪除資料時也能保持平衡。這確保了logarithmic time complexity插入、刪除和搜尋。

• 有序：B 樹保持資料排序，使得範圍查詢（“查詢日期 X 和 Y 之間的所有訂單”）和不等式比較非常快。

• 磁碟友好：B 樹的設計旨在與基於磁碟的儲存配合使用。B 樹的單個節點通常對應一個磁碟塊，從而最大限度地減少磁碟訪問操作。

許多資料庫使用經過稍微修改的 B 樹變體，稱為 B+ 樹。

在 B+ 樹中，所有資料值都僅儲存在葉節點中，這可以進一步提高範圍查詢等某些用例的效能。

雜湊表

雜湊表用於雜湊索引，它基於雜湊函式。

雜湊表由一組桶組成，每個桶包含資料中行的地址。

雜湊索引使用雜湊函式將鍵對映到雜湊表中對應的儲存桶，從而實現恆定時間的查詢操作。

雜湊索引提供快速的相等查詢，因為雜湊函式根據鍵確定資料的確切位置。

但是，雜湊索引不能有效地支援範圍查詢或排序。

點陣圖

點陣圖中的每個位對應一行，位的值表示該鍵值是否存在於該行中。

點陣圖索引使用點陣圖（二進位制陣列）來表示表的每一行中特定鍵值的存在或不存在。

點陣圖索引非常適合基數較低（少量不同值）的列以及執行涉及多個條件的複雜查詢。

點陣圖運算（例如 AND、OR 和 NOT）可以透過按位運算高效執行，從而使點陣圖索引適合涉及多列的分析查詢。

如何巧妙使用資料庫索引？

為了充分利用資料庫索引，請考慮以下最佳做法：

• 識別查詢模式：分析針對資料庫執行的最常見和最關鍵的查詢，以確定要索引哪些列以及使用哪種型別的索引。

• 索引常用列：考慮索引在 WHERE、JOIN 和 ORDER BY 子句中經常使用的列。

• 索引選擇性列：索引對資料值分佈良好（高基數）的列最有效。索引gender列的益處可能不如具有唯一值的列customer_id。

• 使用適當的索引型別：為您的資料和查詢選擇正確的索引型別。

• 考慮複合索引：對於涉及多列的查詢，請考慮建立包含所有相關列的複合索引。這減少了對多個單列索引的需求並提高了查詢效能。

• 監控索引效能：定期監控索引效能，刪除未使用的索引並隨著資料庫工作負載的變化調整索引策略。

• 避免過度索引：避免建立過多的索引，因為這會導致儲存需求增加和寫入效能降低。

  • 索引佔用額外的磁碟空間，因為它們是需要與表一起儲存的附加資料結構。

  • 每次在有索引的表中插入、更新或刪除資料時，索引也需要更新。這會稍微減慢寫入操作的速度。

總而言之，索引是最佳化資料庫查詢效能的有力工具。

但請記住選擇正確的列和索引型別，監控效能，並避免過度索引以充分利用它們。

歡迎關注，在此感謝大家了 ~