一、索引是什麼？
索引是**獨立的物理結構**，包含「索引鍵值」與「指向資料行的邏輯指標」。類比：**書的目錄** → 快速定位頁碼 → 翻閱內容。
二、索引儲存結構：B+Tree
- **非葉節點**僅存鍵值 + 子頁指標，不存資料
- **葉節點**存完整鍵值 + 行指標（非聚簇）或完整行資料（聚簇）
- 所有葉節點透過雙向鏈表串聯，支援範圍掃描、全序遍歷
- 高度通常 2-4 層，百萬行資料僅需 3-4 次 IO 定位
三、MySQL 兩大索引類型
| 特性 | 聚簇索引 | 非聚簇索引 |
|---|
| 資料與索引關係 | 資料即索引，葉節點存完整行 | 索引與資料分離，葉節點存主鍵值 |
| 每表數量 | **僅一個**（主鍵或首個非空唯一鍵） | 可多個 |
| 查詢流程 | 直接定位葉節點拿資料 | 先走二級索引 → 拿主鍵值 → 回表查聚簇索引 |
| 插入效能 | 按主鍵順序插入最快（頁分裂少） | 需維護額外 B+Tree |
| 適用場景 | 主鍵查詢、範圍掃描、排序 | 非主鍵查詢、覆蓋索引、聯表 JOIN |
四、索引分類與建立語法
| 類型 | 語法 | 特點 |
|---|
| 主鍵 | PRIMARY KEY (id) | 聚簇、非空、唯一 |
| 唯一 | UNIQUE KEY uk_email (email) | 非聚簇、允許一個 NULL |
| 普通 | KEY idx_name (name) | 非聚簇、允許重複、NULL |
| 複合/組合 | KEY idx_a_b (a,b) | **最左前綴原則**生效 |
| 全文 | FULLTEXT KEY ft_content (content) | MATCH...AGAINST 專用 |
| 空間 | SPATIAL KEY sp_geo (geo) | GIS 幾何類型專用 |
五、索引優勢與代價
| 優勢 | 代價 |
|---|
| 查詢速度提升 10-1000 倍 | 寫入（INSERT/UPDATE/DELETE）額外維護 B+Tree |
| 支援 ORDER BY/GROUP BY 避免檔案排序 | 佔用額外磁碟空間（約資料量 10-30%） |
| 加速 JOIN（關聯欄位有索引） | 統計資訊過期導致優化器誤判 |
| 實現唯一性約束 | 過多索引擴大優化器搜尋空間 |
六、實戰核心原則
- 最左前綴：
KEY (a,b,c) 支援 a、a,b、a,b,c，**不支援** b、c、b,c。
- 覆蓋索引：查詢欄位全部包含於索引中，**避免回表**，
EXPLAIN 顯示 Using index。
- 索引下推 (ICP, 5.6+)：二級索引遍歷時在存儲引擎層過濾
WHERE 條件，減少回表次數。
- 避免索引失效：
- 函數/運算作用於索引欄：
WHERE YEAR(dt) = 2023 → WHERE dt >= '2023-01-01' AND dt < '2024-01-01'
- 隱式轉型：
VARCHAR 欄位 WHERE col = 123 → WHERE col = '123'
LIKE '%keyword' 導掃OR 兩側缺一無索引
- 單表索引數量建議 ≤ 5，超過需嚴格評估寫入效能影響。
七、索引維護與監控
-- 檢查索引使用情況
SELECT * FROM sys.schema_unused_indexes; -- 5.7+ sys schema
-- 碎片整理
OPTIMIZE TABLE t; -- 或 ALTER TABLE t ENGINE=InnoDB;
-- 統計資訊更新
ANALYZE TABLE t;
結語
索引是資料庫效能的**放大鏡**，也是寫入路徑的**負擔**。掌握 B+Tree 結構、聚簇 vs 非聚簇、最左前綴、覆蓋索引、ICP 下推五大核心概念，配合「查詢驅動建索引、寫入驅動控索引」的動態平衡策略，才能在 OLTP 高併發與 OLAP 大查詢間遊刃有餘。
