資料庫查詢性能優化技巧:索引概念、叢集、非叢集
前言
🔗當資料表中儲存了大量的資料，就算TSQL語法正確，在讀取寫入時會異常緩滿，這時侯就跟索引的設置有很大的關聯，甚至我覺得在一開始設計schema時，就該設置好對的索引，養成良好的習慣。
索引
🔗索引是一種數據結構，用於加速資料庫中的查詢操作。它通常是在資料表的一個或多個欄位上建立的，以便快速定位和檢索特定資料行或符合特定條件的資料。簡單來講就是用空間換時間，如果有設定好的索引，在查詢資料時，就能透過B-樹得結構快速查詢到所要的資料，而不是藉由掃描整個資料表來取得。
索引種類主要有 叢集索引（Clustered Index）、非叢集索引（Non-Clustered Index）、唯一索引（Unique Index）、主鍵索引（Primary Key Index）、聚集索引（Clustered Index）與 全文索引（Full-Text Index）。
此篇主要討論:
叢集索引（Clustered Index）：叢集索引決定了資料在資料庫中的物理排序方式。每個資料庫表只能有一個叢集索引，它決定了表的物理存儲順序。
非叢集索引（Non-Clustered Index）：非叢集索引是建立在叢集索引之外的索引。它包含索引鍵和指向資料頁面的指標，用於快速定位資料，一個資料庫表可以有多個非叢集索引。
幾個問題釐清
🔗- 資料表的索引愈多愈好？
雖然可能提升查詢效率，但佔用的磁碟空間也愈大，在資料表的異動也可能因此變慢。
- PRIMARY KEY 是何種索引？
建立 PRIMARY KEY 時若無叢集索引，則會自動建立叢集索引，因此應該進一步評估是否合適。
- FOREIGN KEY 是何種索引？
建立 FOREIGN KEY 時並不會建立任何索引，因此若是經常需要 JOIN，則應該建立索引。
- Clustered Index 是 UNIQUE Index？
Clustered Index 不一定是 UNIQUE Index。
索引選擇原則
🔗索引選擇應該是選擇最常被查詢且重覆性較低(唯一性高)的欄位。
建立叢集索引
🔗SQLCREATE CLUSTERED INDEX IX_ClusteredIndexName
ON YourTableName (ColumnName);
叢集索引會影響資料表實際儲存資料時的排序規則，適用於叢集索引的欄位包含:
- Where 條件中會搜尋大量重複值的欄位, ex: where city='台北'
- 經常使用 ORDER BY 的欄位
- 範圍查詢的欄位，ex: where [時間] between '20230101' and '20231231'
- 常用於連結子句中使用的欄位
在 SQL Server 中，建立主索引（Primary Key Index）時預設就是叢集索引。當你為資料表定義主索引時，如果沒有顯式指定索引的類型，SQL Server 將自動將主索引建立為叢集索引。
若使用 GUID 當 Primary Key 會遇到因GUID具有不連續的隨機性，即使循序寫入資料，常常後寫的資料GUID排序較前，依叢集索引特性，實體儲存位置應擺在前段，造成每次寫入資料都需挪動調整既有資料造成索引破碎，拖累寫入與查詢效能，詳細資訊可以參考 GUID Primary Key資料庫避雷守則。
建立非叢集索引
🔗SQLCREATE INDEX index_name
ON table_name (column_name);
一個以上的欄位建立複合索引
SQLCREATE INDEX idx_column1_column2
ON your_table (column1, column2);
非叢集索引不會影響資料表實際儲存資料時的排序規則。
顯示估計執行計畫(Ctrl + L)
🔗不管有沒有建立索引，在撰寫複雜TSQL時也能查看執行計畫，確認此查詢哪個部分耗掉大部分的查詢效能。
其實太複雜的查詢，我也不是看的很懂，還在研究中，但基本上就能明確清楚知道這次查詢是使用索引搜尋還是索引掃描。
叢集索引搜尋（Clustered Index Seek）和叢集索引掃描（Clustered Index Scan）
🔗- 叢集索引搜尋（Clustered Index Seek）：
使用叢集索引的搜尋是根據索引鍵值進行查找。搜尋操作是基於索引的 B-樹結構，通過快速定位到特定索引鍵值的節點，從而直接存取所需的資料頁面。叢集索引搜尋通常是一個高效的存取方式，尤其是當查詢的條件可以有效地使用索引鍵值進行過濾時。
基本上有看到叢集索引搜尋就代表你建立的叢集索引有被此次查詢所使用，若此次查詢不符合叢集索引的鍵值排序順序，那麼資料庫引擎可能無法有效地使用叢集索引，而需要掃描整個資料表或使用其他索引（如非叢集索引）來執行查詢。
- 叢集索引掃描（Clustered Index Scan）：
使用叢集索引的掃描是通過遍歷整個叢集索引來查找符合查詢條件的資料。 掃描操作需要遍歷整個索引結構，因此對於較大的資料表或包含大量資料的索引，可能需要較長的執行時間。 叢集索引掃描通常在以下情況下發生：查詢沒有有效的篩選條件、需要檢索整個資料表或索引覆蓋了查詢所需的所有欄位。
總結來說，叢集索引搜尋是根據索引鍵值快速定位到特定資料，而叢集索引掃描是遍歷整個索引來查找符合條件的資料。搜尋操作通常更有效率，而掃描操作則需要較長的執行時間。若因為掃描操作耗掉大部分查詢時間則可能需要使用其他索引或重新設計索引策略來提高查詢效能。
結語
🔗索引設計應該是每個後端工程師必定會碰到的問題，雖然部分公司會有專業的資料庫管理師，但還是得好好研究，我也還在努力中，不然每次都在納悶自己code寫的那麼完美，怎麼還是那麼慢卻不知道其實是資料庫的問題🤣。
Alvin
軟體工程師，喜歡金融知識、健康觀念、心理哲學、自助旅遊與系統設計。
相關文章
留言區 (0)
尚無留言