萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> 資料庫綜合

資料庫索引實戰使用指南:建立時機、複合索引順序、避坑模式與優化清單

索引建立決策流程圖

發現慢查詢 (Duration > 閾值 / 執行計畫掃描)
    ↓
分析 WHERE / JOIN / ORDER BY / GROUP BY 條件列
    ↓
評估選擇性 = DISTINCT 值 / 總行數
    ↓
選擇性 < 1-5%  → 適合建索引
選擇性 > 20%   → 全表掃描更優,別建索引
    ↓
設計複合索引順序:等值 > 範圍 > 排序/分組
    ↓
驗證:執行計畫出現 Index Seek + 無 Key Lookup (覆蓋)
    ↓
監控:dm_db_index_usage_stats 確認實際被使用

核心建立原則:何時建、建什麼、怎麼建

1. 必須建索引的 6 大場景

  1. 主鍵 (PK):唯一標識,叢集索引首選 (窄、穩、增)
  2. 外鍵 (FK):JOIN 連接鍵,防止刪除父表時全表掃描子表
  3. WHERE 高選擇性列:WHERE user_id = ?、WHERE order_no = ?
  4. JOIN ON 條件列:ON o.customer_id = c.id 雙邊均需索引
  5. ORDER BY / GROUP BY 列:避免 Sort 運算子 (Top N Sort 除外)
  6. 覆蓋索引需求列: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

總結:索引使用黃金法則

  1. 測量先行:無數據不優化,用執行計畫、DMV、慢查詢日誌說話
  2. 精準建立:複合索引順序 = 等值 → 範圍 → 排序,善用 INCLUDE 覆蓋
  3. 防止失效:拒絕隱式轉換、列上函數、前導萬用字元、OR 跨列
  4. 定期清理:監控 user_updates >> user_seeks+scans+lookups 的索引果斷 DROP
  5. 統計為王:統計資料過期 = 優化器盲目 = 好索引也被忽略

索引是雙刃劍:讀快寫慢、佔空間、增維護。唯有基於工作負載實測、持續監控使用情況、配合統計資料維護,才能讓索引真正成為效能加速器而非負擔。

copyright © 萬盛學電腦網 all rights reserved