索引建立決策流程圖
發現慢查詢 (Duration > 閾值 / 執行計畫掃描)
↓
分析 WHERE / JOIN / ORDER BY / GROUP BY 條件列
↓
評估選擇性 = DISTINCT 值 / 總行數
↓
選擇性 < 1-5% → 適合建索引
選擇性 > 20% → 全表掃描更優,別建索引
↓
設計複合索引順序:等值 > 範圍 > 排序/分組
↓
驗證:執行計畫出現 Index Seek + 無 Key Lookup (覆蓋)
↓
監控:dm_db_index_usage_stats 確認實際被使用
核心建立原則:何時建、建什麼、怎麼建
1. 必須建索引的 6 大場景
- 主鍵 (PK):唯一標識,叢集索引首選 (窄、穩、增)
- 外鍵 (FK):JOIN 連接鍵,防止刪除父表時全表掃描子表
- WHERE 高選擇性列:
WHERE user_id = ?、WHERE order_no = ?
- JOIN ON 條件列:
ON o.customer_id = c.id 雙邊均需索引
- ORDER BY / GROUP BY 列:避免 Sort 運算子 (Top N Sort 除外)
- 覆蓋索引需求列:
INCLUDE (col1, col2) 消除 Key Lookup
2. 複合索引列順序鐵律:等值 → 範圍 → 排序
-- 查詢:WHERE status = 1 AND create_time > '2024-01-01' ORDER BY amount DESC
-- ✅ 最佳索引順序
CREATE INDEX IX_Orders_Status_Time_Amount ON Orders (status, create_time, amount DESC)
INCLUDE (customer_id, product_id); -- 覆蓋查詢
-- 原因:
-- 1. status 等值過濾 → 縮小搜尋範圍到單一區間
-- 2. create_time 範圍過濾 → 在區間內有序掃描
-- 3. amount 已按需排序 → 省略 Sort 運算子
-- 4. INCLUDE 列不參與排序,僅存葉層 → 節省非葉層空間
3. 索引列類型轉換陷阱:隱式轉換導致索引失效
-- ❌ 列是 VARCHAR,傳入數字 → 隱式轉換發生在列上 → 掃描
WHERE phone = 13800000000 -- 實際執行: WHERE CONVERT(BIGINT, phone) = 13800000000
-- ✅ 修正:參數類型與列定義一致
WHERE phone = '13800000000'
-- ❌ 對索引列做函數運算 → 索引失效
WHERE YEAR(create_time) = 2024
WHERE UPPER(name) = 'JOHN'
WHERE ISNULL(status, 0) = 1
-- ✅ 修正:改寫為範圍/函數索引 (SQL Server 支援 Computed Column + Index)
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01'
-- 或
ALTER TABLE Users ADD name_upper AS UPPER(name);
CREATE INDEX IX_Users_NameUpper ON Users (name_upper);
4. WHERE 子句殺手:導致索引失效的常見模式
| 模式 |
失效原因 |
重寫方案 |
col != val / col <> val |
索引只知「什麼存在」,不知「什麼不存在」 |
col < val OR col > val (仍可能掃描),或應用層過濾 |
col IS NULL / IS NOT NULL |
NULL 不存入 B+ 樹 (除非 filtered index) |
Filtered Index: CREATE INDEX ... WHERE col IS NOT NULL |
col LIKE '%keyword%' |
前導萬用字元無法利用索引順序 |
Full-Text Search / Elasticsearch / 字首索引 LIKE 'keyword%' |
col + 100 > 200 / col || 'str' = 'abc' |
運算/函數作用在列上 → 掃描 |
移項: col > 100 / 應用層拼接 |
col = COALESCE(@param, col) |
參數為 NULL 時自我比較 → 掃描 |
動態 SQL / OPTION (RECOMPILE) / WHERE (@param IS NULL OR col = @param) |
| OR 條件跨列 |
WHERE a=1 OR b=2 難以單一索引覆蓋 |
UNION ALL 分開查詢各命中索引 |
5. 覆蓋索引與 Included Columns:消除 Key Lookup 的銀彈
-- 查詢:SELECT id, name, email FROM Users WHERE status = 1 AND city = 'Taipei';
-- ❌ 僅建 (status, city) → Key Lookup 回表取 name, email
CREATE INDEX IX_Users_Status_City ON Users (status, city);
-- ✅ 覆蓋索引:INCLUDE 列只存葉層,不佔非葉層空間,不影響排序
CREATE INDEX IX_Users_Status_City_Cover ON Users (status, city)
INCLUDE (name, email);
-- 執行計畫對比:
-- 無 INCLUDE: Index Seek → Key Lookup (Clustered) → Nested Loops (估算行數 x 成本)
-- 有 INCLUDE: Index Seek (Covered) → 直接輸出,0 Key Lookup
索引監控與生命週期管理
1. 監控索引使用情況 (SQL Server / PostgreSQL / MySQL 通用邏輯)
-- SQL Server: 重啟後統計歸零,需持久化收集
SELECT
OBJECT_NAME(s.object_id) AS TableName,
i.name AS IndexName,
i.type_desc,
s.user_seeks, s.user_scans, s.user_lookups, s.user_updates,
s.last_user_seek, s.last_user_scan, s.last_user_update,
CASE WHEN s.user_seeks+s.user_scans+s.user_lookups = 0 AND s.user_updates > 0
THEN 'UNUSED - CANDIDATE TO DROP' ELSE 'ACTIVE' END AS Status
FROM sys.dm_db_index_usage_stats s
JOIN sys.indexes i ON s.object_id = i.object_id AND s.index_id = i.index_id
WHERE s.database_id = DB_ID()
AND i.is_primary_key = 0 AND i.is_unique_constraint = 0
ORDER BY s.user_updates DESC;
2. 索引維護:重組 vs 重建
| 操作 |
碎片率 |
鎖模式 |
線上可用 |
耗時 |
| REORGANIZE |
5% - 30% |
短時共享鎖 |
✅ 完全線上 |
較長 (單執行緒) |
| REBUILD |
> 30% |
排他鎖 (ONLINE=ON 為短時 S/Sch-M) |
✅ ONLINE=ON (Enterprise/Developer) |
較短 (可並行) |
-- 自動化維護腳本邏輯
IF avg_fragmentation_in_percent BETWEEN 5 AND 30
ALTER INDEX ... REORGANIZE;
ELSE IF avg_fragmentation_in_percent > 30
ALTER INDEX ... REBUILD WITH (ONLINE = ON, FILLFACTOR = 80, MAXDOP = 4);
3. 統計資料維護:比索引重建更關鍵
AUTO_UPDATE_STATISTICS ON (默認) - 閾值:500 + 20% 變更
- 大表 (億級) 閾值太高 →
TRACE FLAG 2371 (動態閾值) 或 手動 UPDATE STATISTICS ... WITH FULLSCAN
- 關鍵表建立
Statistics Only 維護計畫,每日低峰執行
不同資料庫的索引特性速查
| 特性 |
SQL Server |
MySQL (InnoDB) |
PostgreSQL |
Oracle |
| 叢集索引 |
✅ 任意列 (PK 默認) |
✅ 僅 PK / 首個 NOT NULL UNIQUE |
❌ (Heap + 索引指向 CTID) |
✅ IOT (Index Organized Table) |
| 非叢集索引葉層 |
叢集鍵 / RID |
主鍵值 |
CTID (Tuple ID) |
ROWID / 主鍵 |
| Included Columns |
✅ INCLUDE |
❌ (隱式包含 PK) |
✅ INCLUDE (PG 11+) |
❌ |
| Filtered/Partial Index |
✅ WHERE predicate |
❌ (8.0+ 不支援) |
✅ WHERE predicate |
✅ WHERE predicate |
| 函數/表達式索引 |
✅ Computed Column + Index |
✅ Virtual Column + Index (8.0+) |
✅ Expression Index |
✅ Function-Based Index |
| 降序索引 |
✅ DESC |
✅ DESC (8.0+) |
✅ DESC |
✅ DESC |
總結:索引使用黃金法則
- 測量先行:無數據不優化,用執行計畫、DMV、慢查詢日誌說話
- 精準建立:複合索引順序 = 等值 → 範圍 → 排序,善用 INCLUDE 覆蓋
- 防止失效:拒絕隱式轉換、列上函數、前導萬用字元、OR 跨列
- 定期清理:監控
user_updates >> user_seeks+scans+lookups 的索引果斷 DROP
- 統計為王:統計資料過期 = 優化器盲目 = 好索引也被忽略
索引是雙刃劍:讀快寫慢、佔空間、增維護。唯有基於工作負載實測、持續監控使用情況、配合統計資料維護,才能讓索引真正成為效能加速器而非負擔。