前言
索引是關聯式資料庫效能調優的核心手段。正確的索引能讓查詢速度提升數十倍；錯誤的索引卻會拖垮寫入效能、浪費儲存空間。本文系統分析索引的優缺點、適用欄位判斷、建立方法、特徵（唯一/複合）、類型（叢集/非叢集）架構原理，並提供實戰決策清單。
一、索引五大優點
- 唯一性保證：唯一索引/主鍵確保每列資料唯一，維護實體完整性。
- 大幅加速查詢：將全表掃描
O(N) 降為 B+ 樹搜尋 O(log N)，是建立索引最主要原因。
- 加速 JOIN 連接：外鍵欄位加索引可讓 Nested Loop Join 變為 Index Lookup，效能指數級提升。
- 優化 GROUP BY / ORDER BY：索引本身有序，可直接輸出分組/排序結果，避免 filesort。
- 查詢優化器選項：更多索引 = 優化器有更多執行計畫可選，增加命中最佳計畫機率。
二、索引三大代價（為何不對每個欄位都建索引）
- 維護成本：INSERT/UPDATE/DELETE 時需同步更新所有相關索引，索引越多寫入越慢。隨資料量增長維護時間非線性增長。
- 儲存空間：每個索引佔用額外磁碟空間。叢集索引更大（含資料頁）。大表索引可能超過資料本身大小。
- 寫入效能下降：修改操作需「找到位置 → 修改資料頁 → 修改索引頁 → 寫入 Redo/Undo Log」，索引每多一個多一輪開銷。
三、該建索引的欄位特徵（正向清單）
| 場景 | 理由 | 建議索引類型 |
|---|
| WHERE 條件頻繁出現 | 篩選行數大幅減少 | 單欄或複合索引首欄 |
| 主鍵欄位 | 唯一性 + 叢集索引組織表 | PRIMARY KEY (叢集) |
| JOIN ON 條件欄位 (FK) | 加速關聯查詢 | 單欄索引 |
| 範圍查詢 | B+ 樹有序特性支援 range scan | 叢集或非叢集均可 |
| ORDER BY / GROUP BY 欄位 | 避免臨時表 + filesort | 單欄或複合索引 |
| 覆蓋索引潛力欄位 | 查詢只需索引欄位，無需回表 | 複合索引包含 SELECT 列 |
四、不宜建索引的欄位特徵（反向清單）
| 特徵 | 理由 | 例外 |
|---|
| 查詢極少使用/從不出現在 WHERE/JOIN/ORDER BY | 純增維護成本無收益 | — |
| 基數極低 | 如性別、狀態碼(0/1/2)，選擇率 > 30% 時優化器傾向全表掃描 | 複合索引首欄為高基數欄位時可考慮 |
| 大型物件欄位 | TEXT, BLOB, JSON (MySQL 5.7+ 支援前綴索引) | 前綴索引 INDEX idx_content (content(100)) |
| 頻繁大批量 UPDATE/DELETE 的表 | 維護成本超過查詢收益 | 離線建立、分區表、延遲索引維護 |
五、建立索引的兩種路徑
- 直接建立：
CREATE INDEX / 管理工具精靈
- 彈性最高：可指定 Fill Factor、排序、統計資訊更新、索引類型(叢集/非叢集)、唯一性、複合欄位順序。
- 間接建立：
PRIMARY KEY / UNIQUE CONSTRAINT
- 語義驅動：PK 自動建立唯一叢集索引，UNIQUE 建立唯一非叢集索引。優先級高於手工 CREATE INDEX，會覆蓋同欄位的普通索引。
六、索引特徵深度解析
唯一性索引
- 保證索引鍵值不重複，
NULL 在多數資料庫視為「值」允許單一 NULL（SQL Server 允許單一 NULL，PostgreSQL 視為不相等允許多 NULL，MySQL 視為不相等允許多 NULL）。
- 業務上「業務鍵」宜加
UNIQUE 而非單靠應用層檢查。
- 建立時若表已有重複資料會失敗，需先清理。
複合索引
- 最多 16 欄位（SQL Server），總長度 ≤ 900 bytes（MySQL 3072 bytes / InnoDB 頁面 1/2）。
- 欄位順序至關重要：
INDEX (a,b) ≠ INDEX (b,a)。原則：最左前綴原則——WHERE/JOIN/ORDER BY 必須包含第一欄才會使用。
- 高選擇性（基數大）欄位放前面，如
(user_id, created_at) 優於 (created_at, user_id)。
- 可實現覆蓋索引：
SELECT a,b FROM t WHERE a=1 若有 INDEX (a,b) 直接從索引葉節點回傳，無需回表。
七、索引類型：叢集 vs 非叢集
叢集索引
- 資料頁即葉節點：表資料按索引鍵物理排序存放。
- 每表僅一個（因物理順序只能有一種）。
- 預設主鍵即叢集索引；無 PK 時 InnoDB 選第一個非空唯一索引，否則生成隱藏 6-byte row_id。
- 優點：範圍掃描、排序輸出極快；主鍵查詢無需回表。
- 缺點：插入非遞增主鍵導致頁分裂/碎片；二級索引葉節點存主鍵值而非行指標，回表需二次查找。
- 建立需 1.2 倍表空間 暫存，大表務必預留空間。
非叢集索引
- 結構同樣為 B+ 樹，但葉節點存「叢集鍵值」而非資料。
- 每表最多 249 個（SQL Server）或無硬限制 (MySQL)。
- 兩種架構：
- Heap 表 (無叢集索引)：葉節點存 RID (File:Page:Slot) → 透過 IAM 頁定位資料。
- 叢集表上：葉節點存 PK 值 → 透過叢集索引回表定位資料。
- 優點：不改變資料物理順序，適合多種查詢路徑。
八、實戰決策清單（Checklist）
- 收集慢查詢：
pt-query-digest、EXPLAIN ANALYZE、sys.schema_unused_indexes。
- 識別高頻 WHERE/JOIN/ORDER BY 欄位組合。
- 評估基數：
SELECT COUNT(DISTINCT col)/COUNT(*) FROM t > 0.1 通常值得索引。
- 設計複合索引順序：等值條件欄位 → 範圍條件欄位 → 排序/分組欄位 → 覆蓋查詢欄位。
- 檢查現有索引是否可合併（刪除冗余索引）。
- 線上建立：MySQL 5.6+
ALGORITHM=INPLACE, LOCK=NONE，SQL Server ONLINE=ON，避免鎖表。
- 建立後更新統計：
ANALYZE TABLE / UPDATE STATISTICS。
- 監控寫入延遲與磁碟增長，必要時調整 Fill Factor / innodb_fill_factor。
- 定期清理未使用索引：
sys.schema_unused_indexes (MySQL 8.0) 或 dm_db_index_usage_stats (SQL Server)。
九、常見誤區
- ❌ 「加了索引查詢就一定快」
- 優化器可能因統計過期、選擇率高、隱式轉型、函數包裹欄位(
WHERE DATE(created_at)=...) 而忽略索引。
- ❌ 「複合索引順序隨意」
INDEX (a,b) 無法支援 WHERE b=1，也無法支援 ORDER BY b。- ❌ 「主鍵一定要用自增 ID」
- UUID/雪花算法主鍵會導致叢集索引嚴重碎片，InnoDB 建議有序主鍵或改用二級索引作業務查詢。
- ❌ 「索引越多越好」
- 寫入密集型表 (日誌、監控) 通常只需主鍵 + 1-2 個關鍵查詢索引。
總結
索引是「以空間換時間、以寫入換讀取」的經典權衡。掌握 B+ 樹原理、最左前綴、叢集/非叢集架構、覆蓋索引、基數選擇性 核心概念，配合 慢查詢分析 → 設計複合索引 → 線上建立 → 統計更新 → 持續監控 閉環流程，即能在保寫入效能前提下最大化查詢吞吐。
