SQL Server索引設計三大陷阱與優化實戰

測試環境

HP LH II / 330MHz / 128MB / Sybase 11.0.3（原理通用於SQL Server）。record表62萬行。

陷阱一：不合理的索引設計

分析

• 非叢集索引上重複值多 → 範圍查詢退化為表掃描。
• 叢集索引物理有序 → 範圍定位只掃目標頁。
• 複合索引前導欄位決定一切：方案3前導place，前兩查詢未引用 → 索引失效；方案4前導date，所有查詢受益並形成索引覆蓋（查詢欄位全在索引中） → 極致效能。

索引設計原則

1. 大量重複值 + 範圍查詢/ORDER BY/GROUP BY → 考慮叢集索引。
2. 多欄位頻繁聯合存取 + 各含重複值 → 考慮複合索引。
3. 複合索引前導欄位 = 使用最頻繁的欄位；盡量讓關鍵查詢形成索引覆蓋。

陷阱二：不充分的連接條件

card（7896行，card_no非叢集）JOIN account（19萬行，account_no非叢集）。

-- 僅連接card_no：20秒（外層account掃描22541頁 + 19萬×3頁書籤查找）
SELECT SUM(a.amount) FROM account a, card b WHERE a.card_no = b.card_no

-- 增加account_no：<1秒（外層card掃描1944頁 + 7896×4頁書籤查找）
SELECT SUM(a.amount) FROM account a, card b 
WHERE a.card_no = b.card_no AND a.account_no = b.account_no

結論：優化器根據連接條件枚舉方案，充分條件才能觸發真正最優方案。I/O成本估算公式：外層頁數 + 外層行數 × 內層單次查找頁數，乘積最小者勝出。

陷阱三：不可優化的WHERE子句（SARGable原則）

鐵律：WHERE子句對欄位的任何運算（函數、運算式、隱含轉換）都會導致索引失效、全表掃描。將運算移至等號右側，保持欄位「裸露」。

IN / OR 陷阱

-- IN邏輯等價OR，優化器採「OR策略」：建臨時表+唯一索引去重 → 索引失效
SELECT COUNT(*) FROM stuff WHERE id_no IN ('0','1')  -- 23秒（62萬行達220秒）

-- 拆分為兩查詢再相加：各用索引 → 3秒
SELECT COUNT(*) FROM stuff WHERE id_no='0'
SELECT COUNT(*) FROM stuff WHERE id_no='1'

或寫存儲過程拆分執行，避免工作表開銷。

總結

SQL優化本質：在結果正確前提下，寫出優化器可識別的語句，充分利用索引，減少I/O，避免表掃描。應用層優化只是冰山一角，深層涉及資源配置、網路、OS調度。