萬盛學電腦網

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

資料庫索引基礎知識全圖鑑:類型、特性、選擇策略與避坑指南

索引本質:有序的查找目錄

如同書末的「索引頁」讓你秒速定位頁碼,資料庫索引將特定列的值**排序存儲**,並保存指向原始行的指針。查詢時先查索引 → 獲取指針 → 回表取數據 (或直接覆蓋輸出)。

三大主流索引類型深度對比

類型 定義 物理順序 數量限制 適用場景
叢集索引
(Clustered Index)
資料行按索引鍵**物理順序**存放,葉層即資料頁 邏輯 = 物理順序 每表**僅 1 個** 範圍查詢、排序、頻繁按此列存取、作為 PK 最佳
非叢集索引
(Nonclustered Index)
獨立 B+ 樹,葉層存 **鍵值 + 行定位器** (RID 或 PK) 邏輯 ≠ 物理順序 SQL Server: 999
MySQL: 無硬限制
點查詢、覆蓋索引、輔助查詢路徑
堆表
(Heap)
無叢集索引,資料無序堆放,IAM 管理頁面鏈 無序 (插入位置由 PFS 決定) 無索引或僅非叢集 純追加寫入、暫存表、ETL 階段表

特殊功能索引類型

1. 唯一索引

  • 強制鍵值唯一 (允許單一 NULL,視資料庫而定)
  • 優化器知曉「最多 1 行」 → 選擇性極高 → 優先選擇
  • CREATE UNIQUE INDEX UX_Users_Email ON Users(email) WHERE email IS NOT NULL; (Filtered 解決多 NULL)

2. 主鍵索引

  • 唯一索引 + NOT NULL + 實體完整性核心
  • SQL Server 默認建立為叢集索引 (可指定 NONCLUSTERED)
  • InnoDB 強制 PK = 叢集索引 (無 PK 則隱式 6 字節 RowID)

3. 覆蓋索引 / Included Columns

CREATE INDEX IX_Orders_Cust_Status ON Orders (customer_id, status) 
  INCLUDE (order_date, total_amount, shipping_address);
-- 查詢:SELECT order_date, total_amount FROM Orders WHERE customer_id=? AND status=?
-- 執行計畫:Index Seek (Covered) → 無 Key Lookup,極速

4. 過濾/部分索引

-- 僅索引未刪除、啟用、特定狀態的行
CREATE INDEX IX_Users_Active_Email ON Users (email) 
  WHERE is_deleted = 0 AND status = 'ACTIVE';
-- 體積小、維護快、統計資料更精準

5. 函數/運算式索引

-- SQL Server: 計算欄 + 索引
ALTER TABLE Users ADD email_lower AS LOWER(email);
CREATE INDEX IX_Users_EmailLower ON Users (email_lower);

-- PostgreSQL / Oracle / MySQL 8.0+: 直接表達式索引
CREATE INDEX IX_Users_EmailLower ON Users (LOWER(email));

索引列選擇科學方法論

步驟 1:收集工作負載特徵

-- 1. 慢查詢日誌 / Query Store / pg_stat_statements
-- 2. 應用 ORM 生成的 SQL 模式
-- 3. 報告/BI 常用維度

步驟 2:計算選擇性

Selectivity = COUNT(DISTINCT column) / COUNT(*)
-- 經驗值:
-- < 1%   : 極佳,點查詢首選
-- 1-5%   : 良好,範圍查詢友好
-- 5-20%  : 邊界,視查詢佔比決定
-- > 20%  : 通常不建索引 (性別、狀態、類型碼)

步驟 3:檢查現有索引重疊

-- 重疊索引檢測:前綴列相同
-- IX_A (a,b) 和 IX_B (a) → IX_B 可刪除 (被 IX_A 覆蓋)
-- IX_C (a,b,c) 和 IX_D (a,b) → 視查詢模式保留其一

步驟 4:驗證執行計畫

  • 出現 Index Seek + 無 Key Lookup (或 Lookup 行數極少) → 成功
  • 出現 Index Scan / Table Scan / Key Lookup 行數巨大 → 失敗,調整索引或改寫 SQL

索引設計反模式 (反面教材)

反模式 危害 修正
「保險起見」給每列建單列索引 寫入放大、優化器困惑、空間浪費 合併為複合索引,按查詢模式設計
叢集索寬/不穩/非遞增 (UUID, 複合鍵) 頁面分裂狂魔、非叢集索引膨脹 (存 PK) 叢集鍵 = Identity/Sequence/日期+流水號
複合索引列序錯誤 (範圍列在前) 範圍列後的列無法利用索引順序 等值列前置,範圍列中間,排序列最後
忽略 INCLUDE 列,導致 Key Lookup 書籤查詢隨機 I/O,百萬行查詢變秒級 分析 SELECT 列表,高頻投影列加 INCLUDE
對大字段 (VARCHAR(MAX), BLOB) 建標準索引 無效/報錯,浪費維護窗口 Full-Text / JSON / XML / 向量 專用索引

跨資料庫索引術語對照表

概念 SQL Server MySQL (InnoDB) PostgreSQL Oracle
叢集索引 Clustered Index Primary Key (聚簇) — (Heap) Index Organized Table (IOT)
非叢集索引 Nonclustered Index Secondary Index Index (B-tree) B-tree Index
行定位器 RID / Clustered Key Primary Key Value CTID (TID) ROWID / PK
覆蓋索引 INCLUDE Columns 隱含 PK (不支援自定) INCLUDE (11+) —
填充因子 FILLFACTOR — (頁面留白由 PCTFREE 類似) FILLFACTOR PCTFREE
碎片整理 REORGANIZE / REBUILD OPTIMIZE TABLE / ALTER TABLE ... ENGINE=InnoDB REINDEX / pg_repack ALTER INDEX REBUILD

新手必記 10 條索引鐵律

  1. 沒有測量就沒有優化 — 先有慢查詢,再有索引
  2. 叢集索引選窄、穩、增 — Identity/Sequence 最佳
  3. 複合索引順序:等值 → 範圍 → 排序
  4. 覆蓋索引消除 Key Lookup — INCLUDE 是免費的加速器
  5. 拒絕隱式轉換、列上函數、前導 % — 索引殺手
  6. 低選擇性列 (性別/狀態) 不建標準索引 — 用 Filtered Index 或應用層過濾
  7. 定期清理未使用索引 — 寫入放大是隱形殺手
  8. 統計資料比索引結構更重要 — 過期統計 = 盲人騎瞎馬
  9. 大表分區 + 索引對齊 — 維護粒度降級到分區
  10. 文檔化每個索引的存在理由 — 包含建立目的、對應查詢、預估 ROI

索引是資料庫效能的槓桿,用得好撬動地球,用不好壓垮自己。建立「索引變更審批流程:業務驗證 → 執行計畫對比 → 壓測驗證 → 上線觀察 → 歸檔文檔」,讓每一個索引都經得起時間考驗。

copyright © 萬盛學電腦網 all rights reserved