資料庫索引深度解析：類型、原理、設計、維護與效能調優

前言

索引是資料庫效能的靈魂。本文系統講解索引原理 (B+ 樹)、分類 (主鍵/唯一/普通/全文/空間/覆蓋/聯合)、設計原則 (最左前綴/區分度/覆蓋/回表)、維護操作 (創建/刪除/重建/統計更新)、效能調優 (EXPLAIN 分析/索引下推/條件下推/直方圖)，並對比 MySQL/PostgreSQL/SQL Server/Oracle 差異。

一、索引底層原理：B+ 樹

• 結構：多路平衡搜索樹，非葉節點只存鍵值+指針，葉節點存完整行 (聚簇索引) 或主鍵值 (二級索引)，葉節點雙向鏈表支援範圍查詢。
• 為什麼不選 B 樹/哈希/跳表：B 樹非葉節點存數據導致扇出度低、層高增高；哈希不支援範圍/排序；跳表佔用內存大、持久化複雜 (Redis 用於內存)。
• InnoDB 兩層索引：
  ① 聚簇索引 (Clustered)：主鍵 B+ 樹，葉節點存全行數據，**表只能有一個**。
  ② 二級索引：葉節點存主鍵值，查詢需「回表」查聚簇索引取完整行。

二、索引分類與適用場景

三、索引設計黃金法則

1. 最左前綴：聯合索引 (a,b,c)，查詢必須從 a 開始連續匹配。
2. 區分度優先：高區分度列靠前 (如 user_id > status)，COUNT(DISTINCT col)/COUNT(*) > 0.1 為宜。
3. 覆蓋查詢：高頻 SELECT a,b FROM t WHERE c=1 → 索引 (c,a,b)，Using index。
4. 避免過度索引：單表 ≤ 5 個索引，單索引 ≤ 5 列，寫入 QPS 每增加一個索引下降 5-15%。
5. 外鍵慎用：InnoDB 外鍵強制加鎖、死鎖風險高、跨庫不支援；應用層維護引用完整性。
6. 字符串前綴索引：INDEX name_prefix (name(10))，節省空間但區分度下降，需計算最佳長度。

四、索引維護操作

-- 創建
CREATE INDEX idx_user_status ON users (status, created_at);
ALTER TABLE users ADD INDEX idx_user_status (status, created_at);

-- 刪除
DROP INDEX idx_user_status ON users;
ALTER TABLE users DROP INDEX idx_user_status;

-- 重建 (碎片整理、統計更新)
ANALYZE TABLE users;           -- 更新統計信息
OPTIMIZE TABLE users;          -- 重建表 (含索引)、釋放空間
ALTER TABLE users ENGINE=InnoDB; -- 在線重建 (5.6+)

-- 監控索引使用
SELECT * FROM sys.schema_unused_indexes;  -- 未用索引 (sys schema)
SELECT * FROM sys.schema_redundant_indexes; -- 冗余索引

五、效能調優進階特性 (MySQL 8.0+)

• 索引下推 (ICP)：二級索引遍歷時，在存儲引擎層過濾 WHERE 條件，減少回表次數。EXPLAIN 顯示 Using index condition。
• 直方圖統計：ANALYZE TABLE t UPDATE HISTOGRAM ON col WITH 100 BUCKETS; 讓優化器精準估算非均勻分佈列的選擇性。
• 隱藏索引：ALTER TABLE t ALTER INDEX idx INVISIBLE; 驗證刪除影響，無風險回滾。
• 函數索引：ALTER TABLE t ADD INDEX idx_upper_name ((UPPER(name))); 解決函數導致索引失效。
• 並行查詢：SET SESSION max_parallel_workers_per_gather = 4; 大表掃描自動並行。

六、跨資料庫索引差異速查

七、總結

索引是「空間換時間、有序換無序」的藝術。掌握 B+ 樹原理 → 分類選型 → 設計法則 → 維護操作 → 進階特性 → 跨庫差異 全鏈路知識，配合 EXPLAIN、sys schema、Performance Schema 三大診斷工具，即可將索引從「玄學」變為「工程」。記住：**最好的索引是業務不需要的索引 (不建表)、其次是精準覆蓋業務查詢模式的最小索引集合。**