[MS SQL 筆記] SQL Index 的語法和範例

6 min readFeb 16, 2023

建立 SQL index 的語法:

CREATE [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ] INDEX index_name 
ON table_name ( column1 [ ASC | DESC ] [ , column2 [ ASC | DESC ] ] [ , ... ] ) 
[ INCLUDE ( column_name1, column_name2, ... ) ]
[ WHERE <filter_predicate> ]

其中，index_name是指定索引的名稱，table_name是需要創建索引的資料表名稱，column1, column2, ...表示需要創建索引的欄位。

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

CLUSTERED和NONCLUSTERED是指定索引類型的關鍵字，CLUSTERED表示創建聚集索引，而NONCLUSTERED表示創建非聚集索引。在一個資料表中，只能有一個聚集索引，因為聚集索引會決定資料表中數據的物理排序方式，而非聚集索引可以有多個。

在創建索引時，我們需要指定一個或多個欄位來作為索引的鍵。我們可以使用ASC或DESC來指定每個鍵的排序順序。如果不指定排序順序，則默認是ASC（升序）。

此外，我們還可以使用INCLUDE來包含非索引列的資訊以提高查詢效能。使用WHERE來指定索引僅包含滿足條件的行。

實務上的做法:

▨ Clustered Index的部分

在設計 Table 時，習慣上會設定一個欄位(Id)類型為 GUID 作為 Primary Key，而 SQL Server 的機制是會自動將 Primary Key 設定成 Clustered Index。此時要考慮的是該不該用 GUID 當 Primary Key。GUID Primary Key資料庫避雷守則-黑暗執行緒 (darkthread.net)

通常的做法是另外設定一個欄位(SeqNo 或 SqlId)類型為 INT，以IDENTITY(1,1)設定自動跳號，並將其設為 Clustered Index。Id 為 GUID 是 Table 的 Primary Key，則設為 NonClustered Index。此做法可以有效的避開叢集索引造成的索引碎片化(Index Fragmentation)。[MS SQL 筆記] 索引碎片化 (Index Fragmentation) — Daniel Chang — Medium

-- Clustered Index
CREATE CLUSTERED INDEX CX_AspNetUserDetail_SqlId
ON [PointCalculationEngine_YATA].[dbo].[AspNetUserDetail] ([SqlId]);
GO

-- NonClustered Index
CREATE NONCLUSTERED INDEX IX_AspNetUserDetail_Id
ON [PointCalculationEngine_YATA].[dbo].[AspNetUserDetail] ([Id]);
GO

[MS SQL 筆記] 介面精靈操作整理 — Daniel Chang — Medium

決定叢集索引欄位的考量

欄位數盡可能越少越好
欄位大小盡可能越少越好
常被用來 ORDER BY 或是 GROUP BY 的欄位
若索引鍵值常用於 ORDER BY 與 GROUP BY中時，因為實體資料已經排序好了，系統不會再進行排序的動作，因此會增加執行的速度。
常被用來 BETWEEN 查詢的欄位
因為實體資料是按照鍵值依序得存入硬碟中，若依 Between >= <= 等符號查詢時，當系統找到第一筆資料後，依序逐筆往下讀取，查詢的結果是連續性的範圍，則執行的速度也會提升。
被定義成 IDENTITY 欄位
連續性的範圍查詢結果
會用於 JOIN 指令中欄位建議加入，一般都是 foreign Key 欄位

不適合當叢集索引的欄位

更新頻率過高的欄位
因為 Clustered Index 每次更新時都會對實體資料進行排序，如果資料量較大的資料表，系統會在排序上會多花不少時間處理，所以並不建議以此類的欄位當索引鍵。
過多或過大的欄位所組成鍵值
因為過多或過大都會造成系統在進行排序時的負擔。
獨特性過高的欄位
因為實體資料是經過排序存入到硬碟中，若欄位中每筆記錄都沒甚麼順序性，則無法有效利用到此排序索引，例如，一個門市的銷售檔內，只以「稅別」當索引鍵，當查詢時若用到的是日期、商品或是公司來進行查詢時，都不會使用到 Clustered-Index 排序的功能，除此外也可能因一千萬筆的銷售資料中，只有一筆是免稅，其他都是含稅，導致無法有效的利用到排序。

Citation: VITO の學習筆記: 建立索引(1)-叢集與非叢集索引 (vito-note.blogspot.com)

▨ NonClustered Index的部分

大致上的概念是，當程式碼有使用 Where 關鍵字時(不論是 Select 或是 Update 語句)，就需要確認是否需要建立 NonClustered Index 以提高查詢效能，並把 Select 的欄位放到NonClustered Index 的 INCLUDE 子句中。

public class Customer
{
  public int Id { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public string Email { get; set; }
  // 其他欄位省略
}
// Sample_1 Entity Framework Core
var customerContactList = _dbContext.Customer
.Where(c => c.LastName == "Smith")
.Select(c => new { c.LastName, c.FirstName, c.Email })
.ToList();
// Sample_2 ADO.NET
SELECT FirstName, LastName, Email 
FROM Customers
WHERE LastName = 'Smith'

CREATE NONCLUSTERED INDEX idx_customer ON Customer (LastName) 
INCLUDE (FirstName, Address, City, State, Zip);

參考:

VITO の學習筆記: 建立索引(1)-叢集與非叢集索引 (vito-note.blogspot.com)

GUID Primary Key資料庫避雷守則-黑暗執行緒 (darkthread.net)

[MS SQL 筆記] 索引碎片化 (Index Fragmentation) — Daniel Chang — Medium

[MS SQL 筆記] SQL Index 的語法和範例

建立 SQL index 的語法:

實務上的做法:

▨ Clustered Index的部分

▨ NonClustered Index的部分

參考:

Written by Daniel Chang