索引的「利」：查詢加速器
- 極大降低 IO：B+ 樹結構將掃描行數從 O(N) 降為 O(log N)。
- 避免排序：索引天然有序，
ORDER BY、GROUP BY、DISTINCT 可直接利用索引順序，省去 filesort。
- 覆蓋索引：查詢列全包含在索引中，不回表，極致效能。
索引的「弊」：寫入負擔與空間成本
- 寫放大：每次 INSERT/UPDATE/DELETE 需同步維護所有相關索引 B+ 樹，調整頁分裂/合併。
- 空間佔用：每個索引佔用獨立存儲空間，隨表增長線性增長。寬表多索引易撐爆磁盤。
- 優化器誤判：統計信息過期/不準導致選錯索引，反而全表掃描更快。
決策框架：是否創建索引？
✅ 必建索引
- 頻繁出現在
WHERE、JOIN ON、ORDER BY、GROUP BY 的列。
- 高選擇性列（Cardinality 接近行數）：主鍵、身份證、手機號、郵箱、UUID。
- 外鍵關聯列：
JOIN 關聯欄位必建索引，否則全表掃描駭人聽聞。
❌ 拒絕建索引
- 低選擇性列：性別(2值)、狀態(<10值)、類型、是否刪除。
原因：優化器判斷掃描索引+回表成本 > 全表掃描，直接忽略索引。
- 極高頻更新列：如
last_login_time、view_count、balance。
原因：每次更新需維護索引，寫入延遲成倍增加。
- 永不作為查詢條件：如長文本
content、description、blob 列。
例外：全文搜索需 FULLTEXT 索引。
- 大字段前綴索引無意義：如
VARCHAR(500) 但前 10 字符全相同，前綴索引選擇性極低。
MyISAM B-Tree 索引結構可視化
[Root Page: 索引鍵指針]
/ | \
[Branch: 10-20] [Branch: 20-30] [Branch: 30-40]
/ \ / \ / \
[Leaf] [Leaf] [Leaf] [Leaf] [Leaf] [Leaf]
|id=12| |id=25| |id=35|
|ptr→行| |ptr→行| |ptr→行|
- 主鍵索引（聚簇索引）：Leaf 節點直接存儲完整行數據。
- 二級索引：Leaf 節點存儲主鍵值，查詢需「回表」查主鍵索引取完整行。
聯合索引最左前綴原則與失效場景
INDEX idx_a_b_c (a, b, c)
| 查詢條件 | 是否用到索引 | 用到列 | 原因 |
|---|
| WHERE a=1 AND b=2 AND c=3 | ✅ | a,b,c | 完美匹配 |
| WHERE a=1 AND b=2 | ✅ | a,b | 最左前綴 |
| WHERE a=1 | ✅ | a | 最左前綴 |
| WHERE b=2 AND c=3 | ❌ | 無 | 斷層，無最左列 |
| WHERE a=1 AND c=3 | ⚠️ | a | b 缺失，c 無法用 |
| WHERE a>1 AND b=2 | ⚠️ | a | a 範圍查詢，b 後列失效 |
| WHERE a=1 ORDER BY b, c | ✅ | a,b,c | 排序列符合索引序 |
| WHERE a=1 ORDER BY c | ❌ | a | 跳過 b，c 無序 |
實戰避坑指南
- 單表索引 ≤ 6 個：超過評估必要性，考慮合併為聯合索引。
- NOT NULL 約束：索引列盡量
NOT NULL DEFAULT '' 或 0，NULL 值統計信息複雜、優化困難。
- 避免函數/運算破壞索引：
❌ WHERE YEAR(create_time) = 2023 → ✅ WHERE create_time >= '2023-01-01' AND create_time < '2024-01-01'
❌ WHERE id + 1 = 100 → ✅ WHERE id = 99
- 強制/忽略索引：
FORCE INDEX(idx_name)、IGNORE INDEX(idx_name)、USE INDEX(idx_name) 僅作應急，根治靠優化統計/重寫 SQL。
- 定期 ANALYZE TABLE：更新 Cardinality，讓優化器決策更準確。
InnoDB 聚簇索引 vs 二級索引補充
- InnoDB 必須有主鍵，無顯式主鍵會選唯一非空索引，無則生成隱藏 6 字節 RowID。
- 二級索引葉子存主鍵值 → 主鍵越短越好（推薦自增 BIGINT/INT，不用 UUID/長字符串）。
- 覆蓋索引查詢
EXPLAIN 顯示 Using index，無 Using where、Using temporary、Using filesort。