InnoDB 聚簇索引 (主鍵) 葉子節點存 **完整行數據**；二級索引葉子節點存 **主鍵值**，查詢需「回表」查聚簇索引。MyISAM 索引葉子節點存 **數據文件指針**。
| 類型 | 關鍵字 | 約束 | 適用場景 |
|---|---|---|---|
| 主鍵 | PRIMARY KEY | 唯一、非空、單表唯一 | 業務主鍵/自增代理鍵 |
| 唯一 | UNIQUE | 唯一、可空 | 業務自然鍵 (手機、身份證、Email) |
| 普通 | INDEX / KEY | 無約束 | 高選擇性查詢列、JOIN 列、排序列 |
| 全文 | FULLTEXT | 5.6+ InnoDB 支援 | 長文本搜尋 (文章、日志、評論) |
| 空間 | SPATIAL | GIS 數據類型 | 地理位置查詢 |
| 前綴 | INDEX(col(N)) | 索引前 N 字符 | 長 VARCHAR/TEXT/BLOB，N 取選擇性 > 0.9 處 |
| 組合 | INDEX(a,b,c) | 多列有序 | WHERE a=1 AND b=2 / ORDER BY a,b |
| 覆蓋 | 無特殊語法 | 查詢列 ⊆ 索引列 | 避免回表，Extra=Using index |
-- 單列索引各自建立
INDEX idx_fn (firstname), INDEX idx_ln (lastname), INDEX idx_age (age)
-- 查詢：WHERE firstname='Mike' AND lastname='Sullivan' AND age=17
-- 優化器只能選擇 **一個** 最佳單列索引 (如 firstname)，掃描所有 Mike 再過濾
-- 組合索引
INDEX idx_fn_ln_age (firstname, lastname, age)
-- B+Tree 按 (firstname, lastname, age) 有序存儲
-- 直接定位到 firstname='Mike' → lastname='Sullivan' → age=17，**零掃描、零回表**
組合索引 INDEX(a,b,c) 等價於同時擁有：
INDEX(a)INDEX(a,b)INDEX(a,b,c)可用場景：WHERE a=1、WHERE a=1 AND b=2、WHERE a=1 AND b=2 AND c=3、ORDER BY a,b、GROUP BY a,b。
不可用場景：WHERE b=1、WHERE c=1、WHERE b=1 AND c=2、WHERE a=1 AND c=3 (跳過 b)。
-- 只在 WHERE/JOIN 出現 → 必建索引
SELECT age FROM people WHERE firstname='Mike' AND lastname='Sullivan';
-- firstname, lastname 建索引；age 無需索引
-- 出現在 JOIN ON 條件 → 必建索引
SELECT p.age, t.name
FROM people p LEFT JOIN town t ON p.townid = t.townid
WHERE p.firstname='Mike';
-- p.firstname, p.townid, t.townid 皆需索引
MySQL **僅**對以下操作符使用索引：=、>、>=、<、<=、BETWEEN、IN、LIKE 'prefix%'。
失效：LIKE '%suffix'、LIKE '%mid%'、!=、NOT IN、函數包裹列、隱式轉換。
-- 計算不同前綴長度的選擇性
SELECT
N,
COUNT(DISTINCT LEFT(email, N)) / COUNT(*) AS selectivity
FROM users
WHERE email IS NOT NULL
GROUP BY N
HAVING selectivity > 0.9
ORDER BY N LIMIT 1;
-- 結果 N=12 即可達 0.92，建立 INDEX(email(12)) 節省 70% 空間
索引設計是 **「業務查詢模式 → 索引結構 → 選擇性驗證 → 寫入成本核算」** 的閉環決策。掌握 B+Tree 有序性、最左前綴、覆蓋索引、前綴長度計算四大核心概念，配合 EXPLAIN 實測驗證，即可應對 95% 索引優化場景。
