SQL Server 聚集索引對非聚集索引影響深度解析：B+樹指針重導向機制

前言

「非聚集索引葉節點存指向堆或聚集索引的指針」——這句教科書定義背後隱藏著什麼實際行為？本文透過實戰重現：建立非聚集索引後再建聚集索引，觀察查詢計劃從「表掃描」轉為「非聚集索引查找」的奧秘。

實驗環境建立

USE SQLStudy
GO
CREATE TABLE [dbo].[Test] (
    [ID] [int] NOT NULL,
    [First] [nchar](10) NULL,
    [Second] [nchar](10) NULL
)
GO

INSERT INTO [dbo].[Test] ([ID],[First],[Second])
SELECT 1,'First1','Second1' UNION ALL
SELECT 2,'First2','Second2' UNION ALL
SELECT 3,'First3','Second3' UNION ALL
SELECT 4,'First4','Second4' UNION ALL
SELECT 5,'First5','Second5'
GO

-- 先建非聚集索引
CREATE NONCLUSTERED INDEX [IX_MyTable_NonClustered] ON [dbo].[Test] ([First] ASC, [Second] ASC)
GO

階段一：無聚集索引（Heap 表）

SELECT ID FROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'
SELECT Second FROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'
GO

結果：

• 查詢 1（取 ID）：表掃描 —— 非聚集索引不含 ID，需回表，優化器判定掃描更划算。
• 查詢 2（取 Second）：非聚集索引查找 —— 覆蓋索引，直接從索引葉節點取值。

階段二：建立聚集索引於 ID

CREATE CLUSTERED INDEX [IX_MyTable_Clustered] ON [dbo].[Test] ([ID] ASC)
GO

-- 重跑相同查詢
SELECT ID FROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'
SELECT Second FROM [dbo].[Test] WHERE [First] = 'First1' AND [Second] = 'Second1'
GO

驚人變化： 兩個查詢均走非聚集索引查找！查詢 1 明明要取聚集索引鍵 ID，卻不走聚集索引查找？

核心機制揭秘：非聚集索引指針重導向

1. Heap 階段： 非聚集索引葉節點存 RID（Row Identifier：檔案號:頁號:槽號） 直接指向資料行。
2. 建聚集索引後： 資料頁重組為 B+樹，原 RID 失效。SQL Server 自動重建所有非聚集索引，葉節點指針改為 聚集索引鍵值。
3. 查詢 ID 時： 優化器發現非聚集索引葉節點已直接包含聚集鍵 ID → 覆蓋索引 → 直接從非聚集索引取值，無需再走聚集索引樹。

這正是定義中「指向堆或聚集索引的指針」的動態體現：聚集索引存在則指向聚集鍵，不存在則指向 RID。

效能啟示

• 聚集索引鍵應窄： 因所有非聚集索引葉節點都會複製儲存聚集鍵值，寬鍵值會成倍放大非聚集索引體積。
• 避免更新聚集鍵： 更新聚集鍵會導致所有非聚集索引同步更新指針，開銷極大。
• 覆蓋索引威力： 適當 INCLUDE 欄位可讓非聚集索引直接滿足查詢，避免鍵查找。

總結

聚集索引建立會觸發非聚集索引重建與指針重導向（RID → 聚集鍵）。此機制使得「查詢聚集鍵」反而可能走非聚集索引，理解此點對索引設計、效能診斷至關重要。