SQL Server 叢集索引優化核心原則：主鍵≠叢集、避免插入熱點、窄鍵優先

前言

叢集索引決定資料表的物理儲存順序，每個資料表僅能擁有一個。許多開發人員誤以為「主鍵 = 叢集索引」，導致設計失誤。本文釐清三大核心原則：主鍵與叢集索引可分離、叢集索引鍵值單調遞增避免頁分裂、鍵值寬度越窄越好，並提供實務建議與反模式對照。

一、主鍵 ≠ 叢集索引 (可分離)

預設行為：建立主鍵時，若資料表無叢集索引，SQL Server 自動在主鍵上建立唯一叢集索引。

分離場景：

-- 先在非主鍵欄位建立叢集索引
CREATE CLUSTERED INDEX IX_Orders_OrderDate ON Orders (OrderDate);

-- 再指定其他欄位為主鍵
ALTER TABLE Orders ADD CONSTRAINT PK_Orders PRIMARY KEY NONCLUSTERED (OrderID);

結果：OrderDate 為叢集索引，OrderID 為唯一非叢集索引。

決策依據：主鍵用於實體識別與外鍵參考 (適合窄、不變、隨機)；叢集索引用於範圍查詢、排序、物理排序 (適合單調遞增、常用於 WHERE/ORDER BY/JOIN)。兩者職責不同，應依查詢模式獨立選擇。

二、叢集索引鍵值必須單調遞增 (避免插入熱點與頁分裂)

為什麼？

• 叢集索引葉層即資料頁，新資料依鍵值順序插入。
• 若鍵值隨機 (如 UUID、身分證字號)，新列會插入現有頁中間 → 頁分裂 → 碎片率飆升、IO 擴大、鎖競爭、非叢集索引同步更新開銷大。
• 若鍵值單調遞增 (如 IDENTITY、DATETIME、SEQUENCE)，新列永遠附加在尾頁 → 零頁分裂、最佳填充因子、最小碎片、非叢集索引維護成本最低。

最佳候選欄位

填充因子 設定

• 單調遞增鍵值：FILLFACTOR = 100 (或 0，同義)，葉頁填滿，空間利用率最高、掃描最少頁數。
• 若極少數更新導致列長度變大 (可變長度欄位)，可設 90-95 預留空間；但嚴格單調遞增 + 固定長度欄位，100 最優。

三、叢集索引鍵值寬度直接影響所有非叢集索引大小

機制：非叢集索引葉層儲存「索引鍵值 + 叢集索引鍵值 (作為 Row Locator)」。叢集鍵越寬，每個非叢集索引列越寬 → 索引頁容納列數減少 → 樹高增加、IO 增加、記憶體壓力增大。

推薦資料類型 (由窄到寬)

1. SMALLINT (2 bytes)
2. INT (4 bytes)
3. BIGINT (8 bytes)
4. DATETIME (8 bytes) / DATETIME2(0-7) (6-8 bytes)
5. UNIQUEIDENTIFIER (16 bytes)

避免：CHAR/VARCHAR/NVARCHAR、DECIMAL(19,4)、多欄位組合鍵 (除非極窄)。

四、綜合決策矩陣

五、驗證與維護腳本

-- 查看資料表索引屬性
SELECT
    t.name AS TableName,
    i.name AS IndexName,
    i.type_desc AS IndexType,
    i.is_primary_key,
    i.is_unique,
    i.fill_factor,
    STUFF((SELECT ', ' + c.name
           FROM sys.index_columns ic
           JOIN sys.columns c ON ic.object_id = c.object_id AND ic.column_id = c.column_id
           WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
           ORDER BY ic.key_ordinal
           FOR XML PATH('')), 1, 2, '') AS KeyColumns
FROM sys.indexes i
JOIN sys.tables t ON i.object_id = t.object_id
WHERE t.name = 'YourTable'
ORDER BY i.type_desc, i.name;

-- 檢查碎片率
SELECT
    OBJECT_NAME(ips.object_id) AS TableName,
    i.name AS IndexName,
    ips.index_type_desc,
    ips.avg_fragmentation_in_percent,
    ips.page_count
FROM sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'LIMITED') ips
JOIN sys.indexes i ON ips.object_id = i.object_id AND ips.index_id = i.index_id
WHERE ips.avg_fragmentation_in_percent > 10
ORDER BY ips.avg_fragmentation_in_percent DESC;

六、總結

1. 主鍵與叢集索引可分離：依查詢模式獨立設計，切勿盲目綁定。
2. 叢集索引鍵值必須單調遞增：IDENTITY、DATETIME、NEWSEQUENTIALID 為首選，杜絕隨機 GUID/字串。
3. 叢集索引鍵值越窄越好：INT/BIGINT/DATETIME 為黃金標準，直接決定所有非叢集索引大小與效能。
4. 填充因子 100：單調遞增場景下最大化頁密度、最小化 IO。

遵循三大原則，即可為 SQL Server 資料表奠定穩健的物理儲存基礎，大幅降低碎片、鎖爭用、IO 壓力，提升查詢與寫入效能。