SQL 刪除操作性能優化：條件列索引的真實影響測試與分析

背景：一場關於 DELETE 索引效果的爭論

群內討論：DELETE FROM course WHERE classID = 500001，classID 無索引。為提升刪除效率，建立非聚集索引是否有效？

• 初始觀點： 無效。理由：非聚集索引查找後仍需回表（Key Lookup）定位聚集索引鍵再刪除，不如直接掃描聚集索引。
• 反思： 若表極大，無索引意味著全表掃描，代價極高。索引尋找+回表總成本是否低於掃描？

實驗設計

-- 表 A：classID 有非聚集索引
CREATE TABLE course (
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    sCourseName NCHAR(10),
    classID INT
);
CREATE INDEX IX_classID ON course(classID);

-- 表 B：無任何索引（除主鍵）
CREATE TABLE course2 (
    ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    sCourseName NCHAR(10),
    classID INT
);

各插入 1000 行，執行：

DELETE FROM course  WHERE classID = 500;
DELETE FROM course2 WHERE classID = 500;

核心結論

1. 非聚集索引顯著提升 DELETE 效率： 優化器選擇 Index Seek + Key Lookup，避免 Clustered Index Scan。
2. 決策依據是選擇率： 當符合條件行數佔比較低（通常 < 5%-10%），索引求值成本遠低於全掃描。
3. 聚集索引鍵回表開銷可控： 窄聚集鍵（INT）下，Key Lookup 成本極低。
4. 覆蓋索引可進一步消除回表： CREATE INDEX IX_classID_Cover ON course(classID) INCLUDE (ID) 使刪除僅需索引操作。

最佳實踐建議

• 高頻 WHERE 條件列必建索引： 無論 SELECT/UPDATE/DELETE，篩選列是索引首選候選。
• 避免過度索引： 寫入密集表權衡維護成本，考慮過濾索引 WHERE classID IS NOT NULL。
• 大批量刪除策略： 分批循環刪除（TOP (5000)）+ 索引，減少鎖升級與日誌膨脹。
• 檢視執行計劃： 關注 Index Seek vs Clustered Index Scan、Estimated Rows、Key Lookup 開銷。

延伸思考

若 classID 本身就是聚集索引鍵，刪除將直接定位數據頁，效率最高。但聚集索引選擇需綜合考慮範圍查詢、排序、碎片等因素，不可單為單一 DELETE 優化而定。