六大索引類型
| 類型 | 關鍵字 | 適用場景 |
|---|
| 普通索引 | INDEX / KEY | 無約束，純加速查詢 |
| 唯一索引 | UNIQUE INDEX | 值唯一，允許 NULL（多個 NULL） |
| 主鍵索引 | PRIMARY KEY | 唯一+非空，聚簇索引（InnoDB） |
| 全文索引 | FULLTEXT INDEX | CHAR/VARCHAR/TEXT 大文本搜尋（MyISAM/5.6+ InnoDB） |
| 單列索引 | — | 單欄位 |
| 多列/組合索引 | — | 多欄位，遵循最左前綴原則 |
| 空間索引 | SPATIAL INDEX | 幾何數據（MyISAM/InnoDB 5.7+） |
何時創建索引
WHERE 條件欄位GROUP BY / ORDER BY 欄位- 關聯查詢的
JOIN ON 欄位（主鍵/外鍵）
- 唯一性約束欄位
三種創建方式
-- 1. 建表時
CREATE TABLE t (
id INT,
name VARCHAR(20),
INDEX idx_name (name)
);
-- 2. 獨立語句
CREATE INDEX idx_name ON t (name);
CREATE UNIQUE INDEX uk_email ON t (email);
-- 3. ALTER TABLE
ALTER TABLE t ADD INDEX idx_name (name);
ALTER TABLE t ADD UNIQUE INDEX uk_email (email);
多列索引注意事項
CREATE INDEX idx_a_b_c ON t (a, b, c);
-- 僅當 WHERE 條件包含 a（最左列）時生效
-- WHERE a=1 AND b=2 ✅
-- WHERE b=2 ❌
-- WHERE a=1 AND c=3 ✅ (用到 a，c 可能用到索引下推)
查看與刪除
SHOW INDEX FROM t\G;
-- 關鍵欄位：Key_name、Column_name、Cardinality（基數）、Index_type
DROP INDEX idx_name ON t;
驗證索引生效
EXPLAIN SELECT * FROM t WHERE name='test'\G;
-- type=ref/eq_ref、key=idx_name、rows=1 表示命中
存儲引擎差異
- InnoDB / MyISAM：支援 BTREE（預設）。
- MEMORY：預設 HASH，可指定 BTREE。
- InnoDB 最大索引長度 3072 字節（innodb_large_prefix=ON 時），單列前綴長度建議 ≤ 767 字節（utf8mb3）或 191 字符（utf8mb4）。
