SQL Server 叢集索引與非叢集索引實戰演示：物理儲存順序、頁分裂與查詢計畫差異

實驗環境建立

CREATE DATABASE IndexDemo
GO
USE IndexDemo
GO

CREATE TABLE ABC (
    A INT NOT NULL,
    B CHAR(10),
    C VARCHAR(10)
)
GO

-- 插入初始資料：1, 5, 7, 9
INSERT INTO ABC SELECT 1,'B','C' UNION ALL
SELECT 5,'B','C' UNION ALL
SELECT 7,'B','C' UNION ALL
SELECT 9,'B','C'
GO

場景一：建立叢集索引 (Clustered Index)

CREATE CLUSTERED INDEX CLU_ABC ON ABC(A)
GO

-- 查看索引
EXEC sp_helpindex 'ABC'
GO

-- 插入中間值 2
INSERT INTO ABC VALUES (2, 'B', 'C')
GO

-- 物理順序驗證
SELECT * FROM ABC WITH (INDEX = CLU_ABC) WHERE A > 1 AND A < 5
-- 結果順序：1, 2 (新插入), 5 → 物理儲存已重排

-- 範圍查詢計畫
SET STATISTICS IO ON
SELECT * FROM ABC WHERE A BETWEEN 2 AND 7
-- Clustered Index Seek + Range Scan，極高效

叢集索引特性

• 資料頁即葉層：叢集索引的葉層就是資料本身，物理順序 = 索引鍵順序
• 唯一性：單表僅能有一個叢集索引
• 插入中間值觸發頁分裂：頁面滿時分裂 50/50，產生碎片、寫入額外 IO
• 範圍查詢最優：掃描連續頁面，最少 IO

場景二：移除叢集索引

DROP INDEX ABC.CLU_ABC
GO

SELECT * FROM ABC
-- 物理順序維持插入順序：1, 5, 7, 9, 2 (Heap 結構，新列附加在末尾或可用空間)

場景三：建立非叢集索引 (Nonclustered Index)

CREATE NONCLUSTERED INDEX NONCLU_ABC ON ABC(A)
GO

EXEC sp_helpindex 'ABC'
GO

-- 插入中間值 4
INSERT INTO ABC VALUES (4, 'B', 'C')
GO

-- 查詢計畫
SELECT * FROM ABC WITH (INDEX = NONCLU_ABC)
-- Nonclustered Index Seek → Key Lookup (RID Lookup，因為是 Heap)
-- 若有叢集索引 → Key Lookup (Clustered Key Lookup)

非叢集索引特性

• 獨立 B-tree 結構：葉層含索引鍵值 + 行定位器
• 行定位器：
  　Heap → RID (File:Page:Slot)
  　叢集索引 → 叢集鍵值
• 不影響資料物理順序：Heap 仍按插入/可用空間儲存
• 覆蓋索引消除 Lookup：INCLUDE 欄位滿足查詢 → 純索引掃描

關鍵差異對照

選擇指南

1. 叢集索引首選：窄 (4-8 位元組)、唯一、遞增、靜態、常用於範圍查詢/排序/群組 → Identity INT/BIGINT 最佳
2. 非叢集索引：高選擇性 WHERE/JOIN 欄位、覆蓋查詢欄位、外鍵欄位
3. 避免：寬鍵 (VARCHAR(100))、非唯一高重複 (性別)、隨機鍵 (GUID) 做叢集索引

進階：GUID 叢集索引災難與 NEWSEQUENTIALID()

-- 錯誤示範
CREATE TABLE BadDemo (ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID(), ...)
-- 每次插入隨機頁面 → 大量頁分裂、碎片率 99%、效能崩潰

-- 正確做法
CREATE TABLE GoodDemo (ID UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWSEQUENTIALID(), ...)
-- 遞增 GUID，類似 Identity 行為

-- 或用非叢集索引
CREATE TABLE BetterDemo (ID UNIQUEIDENTIFIER DEFAULT NEWID(), 
    SeqID BIGINT IDENTITY PRIMARY KEY CLUSTERED, ...)
-- 叢集用 Identity，GUID 只建非叢集索引

碎片監控與維護

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

• 5-30% → ALTER INDEX ... REORGANIZE (線上、輕量)
• > 30% → ALTER INDEX ... REBUILD (Enterprise 可 ONLINE=ON)