我来详细讲解MySQL的索引类型，这是面试高频考点，也是SQL优化的核心基础。
一、按数据结构分类
1. B+Tree索引（InnoDB默认）
结构特点： [10 | 30 | 50] / | \ \ [3|7|9] [20|25] [40|45] [60|70|80] / | \ / \ / \ / | \ 叶子节点（双向链表）：存储完整数据行（聚簇索引）或主键（二级索引） 关键特性： - 非叶子节点只存键值，不存数据（树更矮，IO更少） - 叶子节点有序且双向链接（范围查询高效） - 聚簇索引的叶子节点就是数据页
适用场景：全值匹配、范围查询、排序、分组、前缀匹配（最左前缀）
2. Hash索引
-- Memory引擎支持显式Hash索引 CREATE TABLE t ( id INT PRIMARY KEY, name VARCHAR(20), INDEX USING HASH(name) -- 显式创建 ) ENGINE=MEMORY; -- InnoDB自适应Hash索引（AHI，自动维护，不可手动创建） SHOW VARIABLES LIKE 'innodb_adaptive_hash_index'; -- 默认ON
特点：
- 等值查询O(1)，无法范围查询
- 不支持排序、最左前缀、模糊查询
- 哈希冲突时用链表解决
3. Full-Text全文索引
-- 5.6+ InnoDB支持，之前只有MyISAM支持 CREATE TABLE articles ( id INT PRIMARY KEY, title VARCHAR(200), content TEXT, FULLTEXT INDEX idx_content(content) WITH PARSER ngram -- 中文需ngram解析器 ); -- 使用 SELECT * FROM articles WHERE MATCH(content) AGAINST('数据库优化' IN NATURAL LANGUAGE MODE);
注意：中文分词需配置ngram_token_size=2（默认2字节）
4. R-Tree空间索引
-- 5.7+ InnoDB支持，用于GIS地理数据 CREATE TABLE locations ( id INT PRIMARY KEY, name VARCHAR(50), loc POINT NOT NULL, SPATIAL INDEX idx_loc(loc) ); SELECT * FROM locations WHERE MBRContains(GeomFromText('Polygon(...)'), loc);
二、按物理存储分类（InnoDB核心）
1. 聚簇索引（Clustered Index）
-- 主键就是聚簇索引，数据行物理上按主键顺序存储 CREATE TABLE t ( id INT PRIMARY KEY, -- 聚簇索引 name VARCHAR(20), INDEX idx_name(name) -- 二级索引 );
特性：
- 叶子节点存储完整行数据
- 表数据本身就是索引，无需额外存储
- 只能有一个（数据物理排序唯一）
如果没有主键：选择第一个非空唯一索引 → 否则隐式创建6字节row_id
2. 二级索引（Secondary Index / 非聚簇索引）
结构： 非叶子节点：索引列值 叶子节点：索引列值 + 主键值（回表用） 查询流程： 二级索引查到主键 → 回表查聚簇索引获取完整数据
覆盖索引优化：如果二级索引包含所有查询列，无需回表
-- idx_name_age包含(name, age)两列 SELECT age FROM t WHERE name = '张三'; -- 覆盖索引，Extra: Using index
三、按功能/特性分类
1. 主键索引 vs 普通索引
CREATE TABLE t ( id INT PRIMARY KEY, -- 主键索引（聚簇） code VARCHAR(20) UNIQUE, -- 唯一索引（二级索引，但约束唯一） name VARCHAR(20), INDEX idx_name(name) -- 普通索引（二级索引） );
| 类型 | 约束 | 数量 | 存储 |
|---|---|---|---|
| 主键 | 非空 + 唯一 | 1个 | 聚簇，存数据 |
| 唯一 | 唯一（可空） | 多个 | 二级，存主键 |
| 普通 | 无 | 多个 | 二级，存主键 |
2. 单列索引 vs 联合索引（复合索引）
-- 单列索引 INDEX idx_name(name) -- 联合索引（最左前缀原则） INDEX idx_name_age(name, age) -- 先按name排序，name相同再按age排序 -- 有效查询 WHERE name = '张三'; -- ✓ 使用第1列 WHERE name = '张三' AND age = 20; -- ✓ 使用两列 WHERE name LIKE '张%'; -- ✓ 前缀匹配 -- 无效查询（违反最左前缀） WHERE age = 20; -- ✗ 跳过第1列 WHERE name LIKE '%三'; -- ✗ 前导模糊
索引下推（ICP，Index Condition Pushdown）： 5.6+优化，在存储引擎层过滤数据，减少回表
-- 索引(name, age)，查询： SELECT * FROM t WHERE name LIKE '张%' AND age = 20; -- 无ICP：引擎层返回所有name='张%'的记录，Server层过滤age -- 有ICP：引擎层直接过滤name和age，减少回表次数
3. 前缀索引（节省空间）
-- 对长字符串取前缀建索引 CREATE TABLE t ( email VARCHAR(255), INDEX idx_email(email(6)) -- 只索引前6个字符 ); -- 计算合适前缀长度（区分度接近1） SELECT COUNT(DISTINCT LEFT(email, 6)) / COUNT(DISTINCT email) as selectivity FROM t;
缺点：无法用于ORDER BY、GROUP BY、覆盖索引
4. 函数/表达式索引（5.7+）
-- 直接对表达式建索引 CREATE TABLE t ( id INT PRIMARY KEY, json_data JSON, INDEX idx_name((CAST(json_data->>'$.name' AS CHAR(20)))) ); -- 或虚拟列方式（推荐，更直观） CREATE TABLE t ( id INT PRIMARY KEY, json_data JSON, name VARCHAR(20) AS (json_data->>'$.name') VIRTUAL, INDEX idx_name(name) );
5. 降序索引（8.0+）
-- 8.0之前：DESC会被忽略，实际还是升序 -- 8.0+：真正支持降序索引 CREATE TABLE t ( a INT, b INT, INDEX idx_ab(a ASC, b DESC) -- a升序，b降序 ); -- 优化混合排序 ORDER BY a ASC, b DESC; -- 可直接使用索引，无需filesort
6. 不可见索引（8.0+）
CREATE INDEX idx_test ON t(name) INVISIBLE; -- 优化器不可见，用于测试 -- 软删除，验证后再真正DROP ALTER TABLE t ALTER INDEX idx_test VISIBLE;
7. 倒排索引（8.0.13+，InnoDB全文索引）
-- 自然语言处理优化 CREATE FULLTEXT INDEX idx_content ON articles(content) WITH PARSER ngram; -- ngram分词，支持中文
四、索引选择决策树
是否需要索引？ │ ├─ 数据量小（<1000行）→ 全表扫描更快，无需索引 │ └─ 数据量大 → 继续判断 │ ├─ 等值查询为主 → Hash索引（Memory）或B+Tree │ ├─ 范围查询/排序/分组 → B+Tree │ ├─ 文本搜索 → 全文索引（ngram解析器） │ ├─ 地理坐标 → R-Tree空间索引 │ └─ 多条件组合 → 联合索引（最左前缀匹配）
五、高频面试追问
Q：为什么InnoDB用B+Tree不用BTree或Hash？
| 结构 | 缺点 | B+Tree优势 |
|---|---|---|
| Hash | 无法范围查询，不支持排序 | B+Tree叶子节点有序，天然支持范围 |
| BTree | 非叶子节点存数据，树更高 | B+Tree非叶子节点只存键，更矮胖，IO少 |
| 红黑树 | 高度不可控，最坏O(n) | B+Tree高度固定（通常3-4层），稳定O(logN) |
Q：聚簇索引和非聚簇索引查询区别？
-- 聚簇索引（主键查询） SELECT * FROM t WHERE id = 1; -- 直接查聚簇索引，1次IO（如果不在Buffer Pool） -- 非聚簇索引（二级索引查询） SELECT * FROM t WHERE name = '张三'; -- 1. 查二级索引找到主键值 -- 2. 回表查聚簇索引获取完整数据 -- 2次IO（回表代价）
Q：什么是索引覆盖？有什么好处？
-- 索引idx_name_age(name, age) SELECT age FROM t WHERE name = '张三'; -- 二级索引叶子节点有(name, age, 主键)，无需回表查聚簇索引 好处： 1. 减少回表IO 2. 减少随机读（聚簇索引是随机IO） 3. 避免访问聚簇索引的锁竞争
Q：联合索引(A,B,C)的生效情况？
WHERE A=1 AND B=2 AND C=3 -- ✓ 全列使用 WHERE A=1 AND B=2 -- ✓ 使用A,B WHERE A=1 -- ✓ 使用A WHERE B=2 AND C=3 -- ✗ 违反最左前缀（无A） WHERE A=1 AND C=3 -- ✓ 使用A（C无法使用索引，但ICP可能优化） WHERE A=1 AND B>2 AND C=3 -- ✓ 使用A,B（B是范围，C无法使用） ORDER BY A,B -- ✓ 使用索引排序 ORDER BY B,A -- ✗ 排序顺序与索引不一致
Q：索引失效的常见场景？
-- 1. 函数操作 WHERE YEAR(create_time) = 2024 -- ✗ 函数破坏索引 WHERE create_time BETWEEN '2024-01-01' AND '2024-12-31' -- ✓ 范围查询 -- 2. 隐式类型转换 WHERE phone = 13800138000 -- ✗ phone是字符串，转数字 WHERE phone = '13800138000' -- ✓ -- 3. 前导模糊查询 WHERE name LIKE '%三' -- ✗ WHERE name LIKE '张%' -- ✓ -- 4. OR条件（部分情况） WHERE id = 1 OR age = 20 -- ✗ id有索引，age无索引，可能全表扫描 -- 优化：UNION ALL 或 分别查询后合并 -- 5. 不等于/NOT IN（数据量大时） WHERE status != 0 -- 可能全表扫描（取决于数据分布） -- 6. 索引列参与计算 WHERE id + 1 = 100 -- ✗ WHERE id = 99 -- ✓
掌握索引类型的特性和适用场景，是写出高性能SQL、进行索引优化的基础。建议结合EXPLAIN分析实际执行计划，验证索引使用效果。
