索引是查詢性能的核心杠桿。本文系統整理 MySQL 索引六大類型、創建/刪除/修改/查看命令、前綴索引技巧、全文索引配置、復合索引最左前綴原理,並給出選型決策樹,助你精準建索引、拒絕盲目加索引。
| 類型 | 關鍵字 | 特點 | 適用場景 |
|---|---|---|---|
| 普通索引 | KEY / INDEX | 僅加速查詢,允許重複值 | WHERE/JOIN/ORDER BY 高頻列 |
| 唯一索引 | UNIQUE | 值唯一,插入時自動檢查 | 業務鍵:手機號、郵箱、身份證 |
| 主鍵索引 | PRIMARY KEY | 唯一+非空,聚簇索引(InnoDB) | 每張表必須有,建議無業務含義自增 ID |
| 外鍵索引 | FOREIGN KEY | 自動創建內部索引輔助約束 | 引用完整性強一致性場景 |
| 復合索引 | INDEX(a,b,c) | 多列組合,遵循最左前綴原則 | 多條件組合查詢 |
| 全文索引 | FULLTEXT | 分詞倒排索引,支持自然語言搜索 | 長文本字段模糊搜索(MyISAM/InnoDB 5.6+) |
-- 1. 獨立 CREATE INDEX
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX idx_name
[USING BTREE|HASH]
ON tbl_name (col_name[(length)], ...);
-- 2. ALTER TABLE 添加(同等效果)
ALTER TABLE tbl_name
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX idx_name (col_name[(length)], ...) [USING BTREE|HASH];
-- 3. 建表時直接定義
CREATE TABLE t (
id INT PRIMARY KEY,
name VARCHAR(50),
email VARCHAR(100),
UNIQUE KEY uk_email (email),
INDEX idx_name (name(10)) -- 前綴索引
);
USING BTREE:B+ 樹,範圍查詢、排序、最左前綴,InnoDB/MyISAM 默認USING HASH:哈希索引,等值查詢 O(1),Memory/Heap 引擎默認,不支持範圍/排序col_name(length):前綴索引,CHAR/VARCHAR/BLOB/TEXT 必須指定長度,VARCHAR(255) 建議 10-20 字符innodb_large_prefix=ON 可擴至 3072)INDEX idx_a_b_c (a, b, c)
等價於擁有:(a)、(a,b)、(a,b,c) 三個索引
可用:WHERE a=1 / WHERE a=1 AND b=2 / WHERE a=1 AND b=2 AND c=3
不可用:WHERE b=2 / WHERE c=3 / WHERE b=2 AND c=3(未包含最左列 a)
MySQL 無 ALTER INDEX,標準流程:
-- 1. 刪除舊索引
ALTER TABLE user DROP INDEX idx_username;
-- 2. 創建新索引(可同名)
CREATE INDEX idx_username ON user (username(8));
⚠️ 大表 DDL 會鎖表(5.6+ Online DDL 緩解),建議:
pt-online-schema-change 或 gh-ost 無鎖變更SHOW PROCESSLIST-- 方式一
SHOW INDEX FROM tbl_name [FROM db_name];
-- 方式二
SHOW INDEX FROM db_name.tbl_name;
-- 關鍵輸出列
Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment
Cardinality:不重複值估算數,越接近行數選擇性越高
-- 建表時
CREATE TABLE article (
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(200),
content TEXT,
FULLTEXT (title, content)
) ENGINE=MyISAM; -- 5.6 前僅 MyISAM 支持
-- 事後添加
ALTER TABLE article ADD FULLTEXT INDEX ft_title_content (title, content);
-- 自然語言模式(默認)
SELECT * FROM article
WHERE MATCH(title, content) AGAINST('search keywords');
-- 布爾模式(精確控制)
SELECT * FROM article
WHERE MATCH(title, content) AGAINST('+MySQL -Oracle' IN BOOLEAN MODE);
-- 查詢擴展模式
SELECT * FROM article
WHERE MATCH(title, content) AGAINST('database' WITH QUERY EXPANSION);
[mysqld]
ft_min_word_len = 3 # 最小索引詞長度(默認 4),修改後需 REPAIR TABLE 重建
ft_stopword_file = '' # 禁用停用詞表(默認內置英文停用詞)
MySQL 內建全文索引不支持中文分詞。生產環境請使用:
FULLTEXT INDEX ft_content (content) WITH PARSER ngram(按 bigram 分詞,精度一般)COUNT(DISTINCT col)/COUNT(*) > 0.3 → 低:考慮組合索引或不建UNIQUEINDEX(a,b) 按最左原則排序col(10)索引是把雙刃劍:讀快寫慢、佔空間、維護成本。定期 ANALYZE TABLE 更新統計信息,配合 EXPLAIN 驗證執行計劃,養成「先驗證後上線」習慣。