[MS SQL 筆記] SQL Index
簡介
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)。
參考:
SQL CREATE INDEX — 1Keydata SQL 語法教學
SQL Server Index 介紹(基本) | 小小菜鳥的成長日記 — 點部落 (dotblogs.com.tw)
GUID Primary Key資料庫避雷守則-黑暗執行緒 (darkthread.net)
VITO の 學習筆記: 建立索引(1)-叢集與非叢集索引 (vito-note.blogspot.com)
資料庫索引深入淺出(二) — iT 邦幫忙::一起幫忙解決難題,拯救 IT 人的一天 (ithome.com.tw)
MS SQL Server 索引重建或重組標準與T-SQL語法 | 學習分享,研究分享 — 點部落 (dotblogs.com.tw)
使用遺漏索引建議調整非叢集索引 — SQL Server | Microsoft Learn
[食譜好菜] Clustered Index 與 Non-Clustered Index 不同之處 | 軟體主廚的程式料理廚房 — 點部落 (dotblogs.com.tw)