SQL 索引實戰：從全表掃描到索引尋找的查詢優化之路

場景：百萬學生表的班級查詢

CREATE TABLE student (
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    sUserName NCHAR(10),
    sAddress VARCHAR(200),
    classID INT,
    create_date DATETIME DEFAULT GETDATE()
);
-- 無任何非聚集索引

需求：SELECT sUserName, sAddress FROM student WHERE classID = 9

執行計劃演進

階段 1：無索引 → Clustered Index Scan

• 掃描 100 萬行聚集索引葉級頁面。
• I/O 成本高、CPU 成本高、無法利用並行度（因無序）。

階段 2：建立非聚集索引 IX_classID → Index Seek + Key Lookup

CREATE INDEX IX_classID ON student(classID);

• 索引樹快速定位 classID=9 的鍵值範圍。
• 通過葉級頁的聚集鍵 ID 回表取 sUserName, sAddress。
• 若匹配行數少（如 100 行），總成本遠低於全掃描。

階段 3：覆蓋索引 IX_classID_Cover → 純 Index Seek

CREATE INDEX IX_classID_Cover ON student(classID) INCLUDE (sUserName, sAddress);

• 查詢所需列全部在索引中，零回表。
• 最優 I/O：僅讀取索引頁，頁密度高、可預讀。

關鍵洞察

索引設計黃金法則

1. WHERE/JOIN/ORDER BY 列優先入鍵： 等值查詢列放前，範圍列放後。
2. INCLUDE 覆蓋高頻投影列： 避免 Key Lookup，但控制索引寬度（< 900 字節）。
3. 監控 sys.dm_db_index_usage_stats： 定期清理未用/重疊索引。
4. 統計信息自動更新： 確保優化器擁有準確基數估計。

結語

索引不是銀彈，但「無索引必全掃」是鐵律。從執行計劃看懂 Scan→Seek→Cover 的演進，是每個 SQL 開發者的必修課。