索引是儲存引擎用於快速尋找記錄的資料結構 (B+ Tree 為主)。好比書的**目錄**,避免全表掃描。InnoDB 索引即資料 (Clustered Index),MyISAM 索引指向資料物理位置 (Non-clustered)。
| 類型 | 關鍵字 | 特性 | 適用場景 | 建立語法 |
|---|---|---|---|---|
| 主鍵 | PRIMARY KEY | 唯一、非空、叢集索引 (InnoDB) | 每張表必有,業務主鍵或自增 ID | `PRIMARY KEY (id)` |
| 唯一索引 | UNIQUE | 值唯一、允許 NULL (單列單一 NULL) | 業務唯一鍵 (身分證、Email、手機) | `UNIQUE INDEX uk_email (email)` |
| 普通索引 | INDEX / KEY | 允許重複、允許 NULL | WHERE/JOIN/ORDER BY/GROUP BY 高頻欄位 | `INDEX idx_name (name)` |
| 全文索引 | FULLTEXT | 支援自然語言/布林模式搜尋 (5.6+ InnoDB 支援) | 文章、日誌、評論內容搜尋 | `FULLTEXT INDEX ft_content (content) WITH PARSER ngram` |
| 空間索引 | SPATIAL | GIS 幾何類型 (POINT, POLYGON...) | 地理位置查詢 | `SPATIAL INDEX sp_loc (location)` |
| 組合索引 | INDEX (a,b,c) | 多欄位組合,**遵循最左前綴原則** | 多條件聯合查詢、覆蓋索引 | `INDEX idx_a_b_c (a,b,c)` |
組合索引 `(a, b, c)` 等價於建立三個虛擬索引:`(a)`、`(a,b)`、`(a,b,c)`。
優化技巧: 調整欄位順序將**高選擇性 (區分度高) 欄位放左邊**,或建立多個組合索引覆蓋不同查詢模式。
| 操作類型 | 叢集索引 (PK) | 非叢集索引 (Secondary) |
|---|---|---|
| 範圍查詢 (BETWEEN, >, <) | ✅ 極快 (葉節點即資料) | ⚠️ 需回表 (雙重 B+ Tree 遍歷) |
| 單點查詢 (PK = ?) | ✅ 最快 | ✅ 快 (回表 1 次) |
| 頻繁 UPDATE 索引欄位 | ❌ 移動資料頁、頁分裂嚴重 | ✅ 僅修改索引樹 |
| 覆蓋索引查詢 | N/A | ✅ 避免回表,極速 |
建議: InnoDB **必須有明確主鍵** (最好自增 BIGINT),業務查詢走覆蓋索引或二級索引回表。
-- 1. 函數/運算在索引欄上
WHERE YEAR(create_time) = 2024 -- ❌
WHERE create_time >= '2024-01-01' AND create_time < '2025-01-01' -- ✅
-- 2. 隱式類型轉換
WHERE varchar_col = 123 -- ❌ (字串轉數字)
WHERE varchar_col = '123' -- ✅
-- 3. 前導萬用字元
WHERE name LIKE '%張%' -- ❌
WHERE name LIKE '張%' -- ✅
-- 4. OR 條件未全覆蓋索引
WHERE a=1 OR b=2 -- ❌ (通常全掃)
-- 改寫: UNION ALL 或建立 (a,b) 組合索引視情況
-- 5. 不等於 / NOT IN
WHERE status != 1 -- ❌ (通常全掃)
-- 改寫: WHERE status IN (0,2,3...) 若值集固定
-- 查詢: SELECT id, name FROM users WHERE status=1 AND city='Taipei';
-- 優化: 建立覆蓋索引
CREATE INDEX idx_status_city_cover ON users (status, city) INCLUDE (id, name); -- MySQL 8.0+ 支援 INCLUDE
-- 或 5.7: CREATE INDEX idx_status_city_cover ON users (status, city, id, name); -- 欄位順序: WHERE -> SELECT