SQL Server 索引優化實戰：從全表掃描到索引搜尋的查詢計畫演變分析

本文以一個真實的全文檢索查詢為例，展示從「無索引 → 全表掃描」到「建立全文索引/複合索引 → 索引搜尋」的完整優化過程，解讀 SET STATISTICS IO ON 輸出的邏輯讀取變化，掌握索引優化的核心判斷依據。

原始查詢與執行計畫 (無索引)

SELECT TOP 50
    ROW_NUMBER() OVER (ORDER BY ResumeCreateTime DESC) AS RowID,
    TopDegree, DegreeRankID, UserResume.UserResumeID, UserResume.UserID,
    ResumeName, BirthDate, WorkStartedDate, SalaryNeeded,
    BufferTimeSpanID, ResumeCreateTime
FROM dbo.UserResume
INNER JOIN dbo.WorkExperience ON WorkExperience.UserResumeID = UserResume.UserResumeID
WHERE CONTAINS(WorkExperience.WorkSummary, '經理') OR CONTAINS(UserResume.ResumeName, '簡歷');

STATISTICS IO 輸出 (首次執行，無索引)

Table 'Worktable'. Scan count 0, logical reads 0
Table 'UserResume'. Scan count 1, logical reads 18,524
Table 'WorkExperience'. Scan count 1, logical reads 8,679
(1 row affected)

分析：
— UserResume 18,524 邏輯讀 = 全表掃描 (Clustered Index Scan / Table Scan)。
— WorkExperience 8,679 邏輯讀 = 全表掃描。
— CONTAINS 謂詞無全文索引支援，退化為 LIKE 掃描 (或報錯需全文索引)。
— 總邏輯讀 ~27K，隨資料增長線性惡化。

優化策略與索引設計

1. 全文檢索索引 (核心優化)

CONTAINS / FREETEXT **必須**有全文索引才能高效執行。

-- 建立全文目錄 (若不存在)
CREATE FULLTEXT CATALOG FTCatalog AS DEFAULT;

-- UserResume.ResumeName 全文索引
CREATE FULLTEXT INDEX ON dbo.UserResume (ResumeName LANGUAGE 1028)  -- 1028 = 繁體中文
KEY INDEX PK_UserResume_UserResumeID  -- 必須有唯一索引作為全文鍵
ON FTCatalog WITH CHANGE_TRACKING AUTO;

-- WorkExperience.WorkSummary 全文索引
CREATE FULLTEXT INDEX ON dbo.WorkExperience (WorkSummary LANGUAGE 1028)
KEY INDEX PK_WorkExperience_WorkExperienceID
ON FTCatalog WITH CHANGE_TRACKING AUTO;

2. 連接/排序/篩選欄位複合索引

-- UserResume: 連接鍵 + 排序欄位 + 篩選欄位覆蓋
CREATE NONCLUSTERED INDEX IX_UserResume_Join_Sort_Cover
ON dbo.UserResume (UserResumeID) INCLUDE (ResumeCreateTime, TopDegree, DegreeRankID, UserID, ResumeName, BirthDate, WorkStartedDate, SalaryNeeded, BufferTimeSpanID);

-- WorkExperience: 連接鍵 + 全文鍵已涵蓋，若有額外篩選可加入
CREATE NONCLUSTERED INDEX IX_WorkExperience_Join_Cover
ON dbo.WorkExperience (UserResumeID) INCLUDE (WorkSummary);

優化後執行計畫預期變化

驗證優化效果指令碼

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
GO
-- 執行優化後查詢
SELECT TOP 50 ... (同上)
GO
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

索引維護與監控

• 全文索引填充：CHANGE_TRACKING AUTO 非同步更新，大量匯入時可 ALTER FULLTEXT INDEX ... SET CHANGE_TRACKING MANUAL → 批次完成後 START FULL POPULATION / START INCREMENTAL POPULATION。
• 碎片監控：sys.dm_db_index_physical_stats 定期檢查 avg_fragmentation_in_percent > 30% 重建，5-30% 重組。
• 未使用索引清理：sys.dm_db_index_usage_stats 監控 user_seeks + user_scans + user_lookups = 0 且 user_updates > 0 的索引，評估刪除。

核心原則總結

1. 全文檢索謂詞 (CONTAINS/FREETEXT) 必配全文索引，否則退化掃描或報錯。
2. 邏輯讀是衡量 I/O 效率的黃金指標，優化目標：將 Scan 轉為 Seek，將 Key Lookup 消除 (Covering Index)。
3. 索引非越多越好：每個索引增加寫入開銷 (INSERT/UPDATE/DELETE 需同步維護)、佔用空間、統計資訊維護成本。以 user_updates / (user_seeks+scans+lookups) 比率評估 ROI。
4. 統計資訊是優化器的眼睛：AUTO_UPDATE_STATISTICS ON、大表啟用 AUTO_UPDATE_STATISTICS_ASYNC ON、維護窗口手動 UPDATE STATISTICS ... WITH FULLSCAN。

索引優化是「測量 → 假設 → 驗證 → 迭代」的科學過程，STATISTICS IO/TIME 是最誠實的量儀。