[MS SQL 筆記] SQL Index

9 min readFeb 15, 2023

簡介

SQL index 是一種獨立的資料庫物件，可以與資料表一起創建、修改和刪除，目的是用來加快查詢速度。它是一個由一個或多個欄位構成，它可以幫助數據庫系統快速定位到查詢所需要的數據，而不是將整個資料表逐行掃描。通常情況下，索引可以提高查詢的速度，但同時也可能增加數據庫系統的負擔，需要合理的使用。

什麼是SQL索引？

索引通常包含一個或多個欄位(類似於複合鍵，但仍有所不同)，並根據其值來對資料進行排序。當我們在資料表中執行查詢時，資料庫系統可以使用這些索引來快速找到需要的資料，而不必將所有資料逐一掃描。

舉例來說，假設我們要在一本園藝書中找如何種植青椒的訊息。若這本書沒有索引的話，那我們是必須要從頭開始讀，直到我們找到有關種直青椒的地方為止。若這本書有索引的話，我們就可以先去索引找出種植青椒的資訊是在哪一頁，然後直接到那一頁去閱讀。很明顯地，運用索引是一種有效且省時的方式。
Citation: SQL CREATE INDEX — 1Keydata SQL 語法教學

如果一張 Table 包含一百萬筆紀錄(records)，當我們使用 SELECT 查詢語句要撈出其中幾千筆符合條件的 records 時，如果有建立適當的 Index，系統只需要用這個 Index 去找出需要的資料是在 Table 的什麼地方，然後直接撈取這些資料，而不是全表掃描(Table Scan)，從而提高查詢速度。

SQL index 通常使用 B-tree 或 Hash table 等資料結構實現，並且它們可以建立在單一欄位或多個欄位上。當我們在設計資料庫時，需要考慮到哪些欄位需要索引，以及如何最大化索引對查詢效率的提升，同時還需要注意索引對資料庫性能的影響。

什麼時候要建立索引?

當一個資料表(Table)沒有叢集索引(Clustered Index)時，我們稱之為Heap(堆叠)。堆叠資料表的資料不是按照資料新增的順序做排序，所以在查詢語法時SQL server需要掃描資料表裏的每一筆資料，這個動作就是資料表掃描(Table Scan)。
當一個資料表有索引時，在資料存在Data page時就是依照索引的鍵值來排序並儲存。所以 SQL Server 將基於該索引鍵值和結構來定位 (透過指標) 資料位置，簡單來說只搜尋必要的資料頁，而這些資料頁已經包含使用者最終所需要的資料結果集，這樣的操作就稱為索引搜尋 (Index Seek)。
Citation: SQL Server Index 介紹(基本) | 小小菜鳥的成長日記 — 點部落 (dotblogs.com.tw)

建立SQL索引的基本語法

SQL index 的語法如下：

CREATE [UNIQUE] [CLUSTERED | NONCLUSTERED] INDEX index_name
ON table_name (column1, column2, ...);
-- index_name的命名通常是在名稱前加一個字首
-- 例如 (叢集索引)"CX_"或 (非叢集索引)"IX_"，來標註為索引。
-- 在index_name之內包括table_name及column_name也是一個好的方式。

其中，index_name為索引的名稱(自行決定)，table_name為需要創建索引的資料表名稱，column1, column2, ...表示需要創建索引的欄位。

可以使用UNIQUE關鍵字來指定索引是否為唯一索引，這意味著索引中的值必須是唯一的。

CLUSTERED和NONCLUSTERED是指定索引類型的關鍵字，CLUSTERED表示聚集索引，而NONCLUSTERED表示非聚集索引。

完整的指令和範例，請參考 :[MS SQL 筆記] SQL Index 的語法和範例 — Daniel Chang — Medium

什麼是叢集索引(Clustered Index)？

當資料表建置叢集索引時，資料會依照叢集索引鍵值順序來存放，所以一張資料表或檢視表(view)只能有一個叢集索引。Clustered index 把實際資料存在索引的Leaf-Level。當資料表設立Primary key時，SQL server會自動建立叢集索引。
Citation: SQL Server Index 介紹(基本) | 小小菜鳥的成長日記 — 點部落 (dotblogs.com.tw)

在 SQL Server 中，Clustered 和 NonClustered 是兩種不同的索引類型，它們之間的區別在於索引的組織方式。

[MS SQL 筆記] Clustered和NonClustered Index的差異 — Daniel Chang — Medium

Clustered Index 是基於 Table 的 Primary key 建立的一種物理排序索引，也可以使用非主鍵欄位建立 Clustered Index(e.g. 流水號)。每個表只能擁有一個 Clustered Index，Clustered Index 決定了數據在磁盤上的存儲方式，數據按照 Clustered Index的鍵值進行排序，以便快速查詢和檢索。

在 Clustered Index 中，數據行的順序和索引的順序相同，即數據行按照索引的順序存儲在磁盤(硬碟)上。因此，當使用 Clustered Index 進行查詢時，可以快速地找到需要的數據行。但是，由於數據行按照索引的順序存儲，當需要插入或更新資料時，需要重新排序整個表，因此，Clustered Index 對插入和更新操作的效能影響比較大。

其中一種設計方式，請參考: GUID Primary Key資料庫避雷守則-黑暗執行緒 (darkthread.net)，可以解決插入或更新資料時，造成的效能影響。

什麼是非叢集索引(NonClustered Index)

非叢集索引在分葉層級并不會存放是資料，而是存放row locator(指向紀錄的pointer)，這個row locator會指到實際資料存放的位置，這代表使用非叢集索引時，Query Engine需要多一步來找到實際資料。非叢集索引并不會真的把資料排序，所以一個資料表可以有多個非叢集索引。
Citation: SQL Server Index 介紹(基本) | 小小菜鳥的成長日記 — 點部落 (dotblogs.com.tw)

NonClustered Index 是另一種常見的索引類型，它與 Clustered Index 不同，不影響數據在磁盤上的存儲方式，它是一種獨立的數據結構。每個表可以擁有多個 NonClustered Index，它們可以基於主鍵或非主鍵欄位建立。

在 NonClustered Index 中，數據行的順序與索引的順序不同，索引中保存了指向數據行的指針(pointer)，這些 pointer 可以快速地查找需要的數據行。由於 NonClustered Index 不影響數據的存儲方式，因此對插入和更新操作的效能影響比較小。

建立 NonClustered Index 時，使用 INCLUDE 來避免 Query Engine 二次查詢[MS SQL 筆記] 使用 SQL 索引中的 INCLUDE 關鍵字以提高查詢效能 | by Daniel Chang | Feb, 2023 | Medium

如何使用SQL索引？

使用SQL索引的關鍵是選擇正確的欄位和類型(Clustered或NonClustered)，這樣可以確保索引可以快速找到需要查詢的數據。

在創建SQL索引之前，需要分析數據表中的欄位以及預計執行的查詢。選擇哪些欄位需要建立索引，通常需要考慮以下因素：

頻繁使用的欄位，例如經常被查詢的欄位。
大數據量的欄位，通常需要建立索引。
欄位中的數據分佈，如果數據分佈均勻，則索引效果更好。

建立索引需要注意的事項:

使用索引可以加速查詢的速度，但是在使用索引時需要注意以下幾點：

過多的索引會影響數據庫的性能。因此，需要根據具體的應用場景，合理地選擇需要創建索引的欄位，以避免過度使用索引。
索引應該建立在資料重複性不高的欄位(視需求而定，可以使用複合鍵來改善必須再重複性高的欄位建索引的問題)。[MS SQL 筆記] SQL Index 的語法和範例 — Daniel Chang — Medium
當索引欄位資料被更新時，索引也會被自動更新，這會帶來額外的開銷，並影響效能，並隨著時間的推移索引會碎片化，因此需要定期維護。
需要依據索引破碎率(index fragmentation)定期重組(reorganise)或重建(rebuild)，以確保索引維護良好。[MS SQL 筆記] 索引碎片化 (Index Fragmentation) — Daniel Chang — Medium
索引並不是萬能的。在一些特殊情況下，索引的效能可能比未使用索引時更差。因此，需要根據具體情況，慎重考慮是否使用索引。
當 Table 很龐大時，索引其實很占空間(Disk space)。