索引本質：有序的查找結構
索引是儲存引擎用於快速查找的資料結構 (B+ 樹為主流)。理解「聚集/非聚集」關鍵在於葉子節點存什麼。
聚集索引 vs 非聚集索引：字典 vs 目錄
| 維度 | 聚集索引 | 非聚集索引 |
|---|
| 葉子節點內容 | 完整數據行 | 主鍵值 (指向聚集索引) |
| 每張表數量 | 只能有一個 (數據只能按一種順序物理存放) | 可有多個 |
| 預設建立 | 主鍵 (PK) 自動建立 | 手動建立 (Secondary Index) |
| 查找流程 | 直接命中數據 | 先查非聚集 → 拿 PK 回表查聚集 |
| 類比 | 新華字典正文 (按拼音排序，查到即得字) | 部首檢字表 (查到頁碼再翻正文) |
何時使用聚集/非聚集索引：決策表
| 場景 | 聚集索引 | 非聚集索引 |
|---|
| 列經常 GROUP BY / ORDER BY | ✅ 適合 | ❌ |
| 範圍查詢 (BETWEEN, >, <) | ✅ 適合 | ❌ |
| 極少不同值 (如性別、狀態) | ❌ 不適合 | ❌ 低選擇性，全表掃描更快 |
| 大量不同值 (如 ID、時間戳) | ✅ 適合 | ✅ 適合 |
| 頻繁 UPDATE 的列 | ❌ 會導致頁分裂/行遷移 | ⚠️ 同樣維護開銷大 |
| 外鍵列 | ✅ 適合 | ✅ 適合 |
| 主鍵列 | ✅ 預設即是 | N/A |
實戰案例：1000 萬行辦公自動化系統
-- 表結構
CREATE TABLE tgongwen (
gid INT AUTO_INCREMENT, -- 自增 ID
fariqi DATE, -- 發文日期
neibuyonghu VARCHAR(50), -- 內部用戶
title VARCHAR(200),
PRIMARY KEY (gid) -- 預設聚集索引在 gid
);
查詢：近 3 個月文件 (25 萬行)
| 索引策略 | SQL | 耗時 | 分析 |
|---|
| 僅 PK 聚集 |
SELECT ... FROM tgongwen |
128 秒 |
全表掃描 |
| PK 聚集 + fariqi 非聚集 |
SELECT ... WHERE fariqi > DATE_SUB(NOW(), INTERVAL 90 DAY) |
54 秒 |
非聚集索引掃描 + 大量回表 |
| 聚集索引移至 fariqi |
同上 |
2 秒 |
聚集索引範圍掃描，**零回表**，數據連續 |
結論：將聚集索引建在高頻範圍查詢列 (日期)，而非自增 ID，效能提升 60 倍。
複合索引最佳實踐：最左前綴原則
-- 複合聚集索引 (fariqi, neibuyonghu)
-- 查詢 1：WHERE fariqi > '2024-01-01' ✅ 用到索引
-- 查詢 2：WHERE fariqi > '2024-01-01' AND neibuyonghu='辦公室' ✅ 用到索引
-- 查詢 3：WHERE neibuyonghu = '辦公室' ❌ 不用索引 (非最左列)
- 複合索引第一列必須是最高頻查詢列
- 查詢條件包含第一列時，索引生效；僅查後列則失效
- 覆蓋索引：
SELECT fariqi, neibuyonghu FROM ... 僅讀索引，不回表，最快
索引使用五大誠區
- 「主鍵即聚集索引」盲目信仰：自增 ID 很少用於業務查詢，浪費寶貴的聚集索引資源。
- 「建了索引就快」：非聚集索引若選擇性低、需大量回表，反而比全表掃描慢。
- 「所有查詢列都加進複合索引」：違反最左原則，導致索引失效、寫入放大。
- 忽略類型轉換：
WHERE varchar_col = 123 觸發隱式轉換，索引失效。
- 過度索引：每個索引佔空間、拖慢寫入。定期用
sys.schema_unused_indexes 清理無用索引。
InnoDB 索架構補充
- 主鍵即聚集索引 (若無 PK，選第一個 NOT NULL UNIQUE 鍵；否則生成 6 字節隱藏 RowID)
- 二級索引葉子存主鍵值，故主鍵應盡量短 (推薦 BIGINT/UNSIGNED INT，避免 UUID/長字串)
- 自適應哈希索引 (AHI) 自動為熱點頁建立哈希，加速點查詢
