案例背景
資料表 `record` 62 萬列。測試環境：HP LHII、330MHz、128MB、Sybase 11.0.3（原理通用於 SQL Server）。比較不同索引策略對三類查詢的邏輯讀取與耗時。
查詢負載
- 範圍查詢 + 額外過濾：`WHERE date BETWEEN '19991201' AND '19991214' AND amount > 2000`
- 分組彙總：`SELECT date, SUM(amount) FROM record GROUP BY date`
- 範圍 + IN 列表：`WHERE date > '19990901' AND place IN ('BJ','SH')`
五種索引方案測試結果
| 方案 | 索引定義 | 查詢 1 | 查詢 2 | 查詢 3 | 分析 |
|---|
| 1 | `date` 非叢集 | 25 秒 | 55 秒 | 27 秒 | `date` 重複值高，非叢集索引無法避免書籤查找，退化為表掃描。 |
| 2 | `date` 叢集 | 14 秒 | 28 秒 | 14 秒 | 資料依 `date` 物理排序，範圍掃描只讀相關頁，大幅減少 I/O。 |
| 3 | `(place, date, amount)` 複合非叢集 | 26 秒 | 27 秒 | <1 秒 | 前導欄 `place` 未用於查詢 1/2，索引失效；查詢 3 覆蓋索引，極快。 |
| 4 | `(date, place, amount)` 複合非叢集 | <1 秒 | 11 秒 | <1 秒 | 前導欄 `date` 滿足所有查詢，查詢 1/3 形成覆蓋索引，整體最優。 |
核心結論：索引設計鐵律
- 叢集索引選擇：高重複值、頻繁範圍查詢 (`BETWEEN`, `>`, `<`)、`GROUP BY`/`ORDER BY` 欄位。避免單調遞增鍵（`IDENTITY`）作為叢集鍵（熱點頁爭用）。
- 複合索引前導欄：必須是**最常用**且**過濾力最強**的欄位，確保核心查詢能 Seek。
- 覆蓋索引：將 `SELECT` 清單與 `WHERE`/`JOIN` 欄位納入 `INCLUDE` 或鍵欄，消除 Key Lookup。
- 成本意識：每個索引增加 INSERT/UPDATE/DELETE 開銷（維護 B-tree）、佔用空間、增加統計維護負擔。OLTP 表建議 ≤ 5-6 個索引。
第二案例：聯結條件完整度決定執行計畫
表 `card` (7,896 列，`card_no` 非叢集索引) ⋈ 表 `account` (191,122 列，`account_no` 非叢集索引)。
-- 不完整條件：僅 JOIN card_no
SELECT SUM(a.amount) FROM account a JOIN card b ON a.card_no = b.card_no; -- 20 秒
-- 完整條件：補上 account_no
SELECT SUM(a.amount) FROM account a JOIN card b ON a.card_no = b.card_no AND a.account_no = b.account_no; -- <1 秒
成本模型解析
- 方案一：`account` 為外層 (191k 列) → 對每列去 `card` 索引求值 (3 頁) → I/O ≈ 22,541 + 191,122 × 3 = 595,907。
- 方案二：`card` 為外層 (7,896 列) → 對每列去 `account` 索引求值 (4 頁) → I/O ≈ 1,944 + 7,896 × 4 = 33,528。
**完整聯結條件讓優化器選到真正的最佳路徑**（小表驅動大表、索引 Seek）。缺少條件則誤判大表為外層，造成災難性 Nested Loops。
實務建議
- 所有 JOIN 條件**務必包含所有關聯鍵欄位**，即使某欄在邏輯上看似冗餘。
- 使用 `sp_helpindex` / `sys.index_columns` 定期審視索引定義與查詢模式是否對齊。
- 針對核心查詢，擷取實際執行計畫，檢查 `Estimated Rows` vs `Actual Rows`、是否出現 `Table Scan`、`Key Lookup`、`Sort`、`Hash Match`（大量資料時）。
- 統計資料自動更新閾值（20%+500）在大表上常不夠，排程 `UPDATE STATISTICS WITH FULLSCAN` 或 `PERSIST_SAMPLE_PERCENT`。
