MySQL索引核心知識體系：從原理到實戰
索引本質與權衡
- 優點：大幅加速查詢（WHERE/JOIN/ORDER BY/GROUP BY）
- 代價：降低寫入性能（INSERT/UPDATE/DELETE需維護索引）、佔用磁盤空間
索引類型全景圖
| 類型 | 關鍵字 | 特點 | 適用場景 |
|---|
| 普通索引 | INDEX | 僅加速查詢，允許NULL、重複值 | 高頻查詢字段 |
| 唯一索引 | UNIQUE INDEX | 值唯一，允許NULL | 業務唯一鍵（手機號、郵箱） |
| 主鍵索引 | PRIMARY KEY | 唯一+非空，一表僅一個 | 表標識列（自增ID） |
| 全文索引 | FULLTEXT | 針對詞語分詞索引，MySQL 5.6+支持中文 | 文章搜索、日志分析 |
| 前綴索引 | INDEX(col(n)) | 索引列前n個字符，節省空間 | 長字符串（URL、標題） |
| 組合索引 | INDEX(a,b,c) | 遵循最左前綴原則 | 多條件聯合查詢 |
索引創建黃金法則
- 高選擇性優先：唯一值佔比>30%的列優先建索引
- WHERE/JOIN/ORDER BY高頻列：分析慢查詢日誌確定
- 拒絕低選擇性列：性別、狀態、類型等枚舉值少的列不建索引
- 避免過度索引：單表建議<5個索引，寫入密集表更少
- 組合索引遵循最左前綴：
INDEX(a,b,c)支持a/a,b/a,b,c，不支持b/c/b,c
實戰SQL示例
-- 建表時創建
CREATE TABLE test5 (
id INT,
username VARCHAR(20),
school VARCHAR(20),
intro TEXT,
PRIMARY KEY (id),
UNIQUE (username),
INDEX (school),
FULLTEXT (intro)
) ENGINE=MyISAM CHARSET=utf8;
-- 事後添加
ALTER TABLE test5 ADD INDEX idx_school (school);
ALTER TABLE test5 ADD UNIQUE uk_username (username);
ALTER TABLE test5 ADD FULLTEXT ft_intro (intro);
ALTER TABLE test5 ADD PRIMARY KEY (id); -- 無需指定索引名
-- 查看索引
SHOW INDEX FROM test5;
-- 刪除索引
ALTER TABLE test5 DROP INDEX idx_school;
ALTER TABLE test5 DROP PRIMARY KEY;
常見反模式與修正
| 反模式 | 問題 | 修正 |
|---|
| 為每個列單獨建索引 | 優化器難選擇、寫入放大 | 改為組合索引 |
| 主鍵用UUID | 頁面分裂嚴重、二級索引膨脹 | 用自增BIGINT或雪花算法ID |
| 組合索引列順序錯誤 | 無法命中最左前綴 | 按區分度高低排序：高→低 |
| 冗余索引 | INDEX(a) + INDEX(a,b) | 刪除INDEX(a) |
驗證索引生效
EXPLAIN SELECT * FROM test5 WHERE school = '清華';
-- 關鍵看：type=ref, key=idx_school, rows=1
掌握索引設計精髓，配合EXPLAIN持續優化，是MySQL性能調優的核心技能。
