MySQL索引原理图文详解
一、索引的基本概念与作用
1.1 什么是索引？
索引是帮助MySQL高效获取数据的数据结构，类似于书籍的目录，可以快速定位到数据的位置。
1.2 为什么需要索引？
提高查询速度：避免全表扫描
保证数据唯一性：唯一索引
加速表连接：JOIN操作优化
排序优化：ORDER BY、GROUP BY优化
1.3 索引的代价
空间占用：索引需要额外的存储空间
维护成本：DML操作需要更新索引
优化器负担：需要选择合适的索引
二、索引的底层数据结构
2.1 B-Tree（平衡多路查找树）
text
[P1 | 20 | P2 | 50 | P3] / | \ / | \ [10|15] [25|30|35] [55|60|70]
特点：
所有叶子节点在同一层
每个节点有多个子节点
保证查找效率 O(log n)
2.2 B+Tree（MySQL主要索引结构）
text
┌─────────────────┐ │ 非叶子节点 │ │ [P1|20|P2|50|P3]│ └────────┬────────┘ ┌─────┼─────┐ │ │ │ ┌──────▼─┐ ┌─▼──────┐ ┌──────▼─┐ │叶子节点1│ │叶子节点2│ │叶子节点3│ │10→15→20│ │25→...→50│ │55→...→70│ └────┬───┘ └────┬───┘ └────┬───┘ │ │ │ ▼ ▼ ▼ 数据页 数据页 数据页
与B-Tree的区别：
非叶子节点只存键值，不存数据
叶子节点包含所有键值和数据指针
叶子节点形成双向链表，支持范围查询
2.3 Hash索引
text
键值 → Hash函数 → Hash值 → 桶 → 链表存储数据指针
特点：
O(1)查找效率
只支持等值查询，不支持范围查询
Memory引擎支持
2.4 全文索引（FULLTEXT）
text
文档 → 分词 → 倒排索引 "我爱中国" → ["我", "爱", "中国"]
三、MySQL存储引擎与索引实现
3.1 InnoDB索引实现
聚簇索引（Clustered Index）
text
┌─────────────────────────────────────┐ │ B+Tree索引结构 │ │ │ │ 非叶节点: [键值+子节点指针] │ │ ↓ │ │ 叶子节点: [键值+整行数据] ← 数据就是这里! │ └─────────────────────────────────────┘
特点：
数据按主键顺序存储
叶子节点存储完整行数据
一个表只能有一个聚簇索引
主键就是聚簇索引
二级索引（Secondary Index）
text
┌─────────────────────────────────────┐ │ B+Tree索引结构 │ │ │ │ 非叶节点: [索引列值+子节点指针] │ │ ↓ │ │ 叶子节点: [索引列值+主键值] → 需要回表! │ └─────────────────────────────────────┘ ↓ ┌─────────────────────────────────────┐ │ 到聚簇索引查找完整数据 │ └─────────────────────────────────────┘
回表查询：通过二级索引找到主键，再通过主键到聚簇索引找完整数据
3.2 MyISAM索引实现
text
┌─────────────────────────────────────┐ │ B+Tree索引结构 │ │ │ │ 非叶节点: [键值+子节点指针] │ │ ↓ │ │ 叶子节点: [键值+数据行地址] → 指向数据文件 │ └─────────────────────────────────────┘ ↓ ┌─────────────────────────────────────┐ │ .MYD数据文件[行数据] │ │ .MYI索引文件 │ └─────────────────────────────────────┘
特点：
索引和数据分开存储
都是非聚簇索引
叶子节点存储数据文件地址
四、索引类型详解
4.1 按功能分类
普通索引（INDEX）
sql
CREATE INDEX idx_name ON users(name);
最基本的索引
无任何限制
唯一索引（UNIQUE）
sql
CREATE UNIQUE INDEX uni_email ON users(email);
保证列值唯一
可以有NULL值
主键索引（PRIMARY KEY）
sql
ALTER TABLE users ADD PRIMARY KEY (id);
特殊的唯一索引
不允许NULL值
一个表只能有一个
全文索引（FULLTEXT）
sql
CREATE FULLTEXT INDEX ft_content ON articles(content);
用于全文搜索
仅限CHAR、VARCHAR、TEXT类型
组合索引（复合索引）
sql
CREATE INDEX idx_name_age ON users(name, age);
多列组成的索引
遵守最左前缀原则
4.2 按物理存储分类
聚簇索引 vs 非聚簇索引
| 特性 | 聚簇索引 | 非聚簇索引 |
|---|---|---|
| 数据存储 | 索引叶子节点存储数据 | 索引叶子节点存储指针 |
| 数量 | 每表1个 | 每表多个 |
| 更新代价 | 高（数据重排） | 低 |
| 范围查询 | 高效 | 相对较慢 |
五、索引的使用原则
5.1 最左前缀原则
对于组合索引(A, B, C)：
text
有效查询： ✓ WHERE A = 1 ✓ WHERE A = 1 AND B = 2 ✓ WHERE A = 1 AND B = 2 AND C = 3 ✓ WHERE A = 1 AND C = 3 # 只用到A 无效查询： ✗ WHERE B = 2 ✗ WHERE C = 3 ✗ WHERE B = 2 AND C = 3
5.2 索引覆盖
sql
-- 假设有索引 (name, age) SELECT name, age FROM users WHERE name = '张三'; -- 索引覆盖：数据直接从索引获取，无需回表
5.3 索引下推（ICP）
MySQL 5.6+引入的优化：
text
传统方式： 1. 使用索引定位到数据位置 2. 回表取出整行数据 3. 在Server层过滤数据 索引下推： 1. 使用索引定位 2. 在存储引擎层过滤 3. 只对符合条件的回表
5.4 索引失效场景
sql
-- 1. 对索引列进行运算 SELECT * FROM users WHERE YEAR(create_time) = 2023; -- 失效 SELECT * FROM users WHERE create_time >= '2023-01-01'; -- 有效 -- 2. 使用 NOT、!=、<>、NOT IN SELECT * FROM users WHERE name != '张三'; -- 可能失效 -- 3. 使用 OR 连接（除非所有列都有索引） SELECT * FROM users WHERE name = '张三' OR age = 20; -- 可能失效 -- 4. LIKE 以通配符开头 SELECT * FROM users WHERE name LIKE '%张%'; -- 失效 SELECT * FROM users WHERE name LIKE '张%'; -- 有效 -- 5. 隐式类型转换 SELECT * FROM users WHERE phone = 13800138000; -- phone是varchar，失效 -- 6. 使用函数 SELECT * FROM users WHERE LOWER(name) = 'zhangsan'; -- 失效
六、EXPLAIN执行计划详解
6.1 EXPLAIN关键字段
sql
EXPLAIN SELECT * FROM users WHERE name = '张三';
| 字段 | 说明 | 重要值 |
|---|---|---|
| type | 访问类型 | system > const > eq_ref > ref > range > index > ALL |
| key | 使用的索引 | 实际使用的索引名 |
| rows | 预估扫描行数 | 越小越好 |
| Extra | 额外信息 | Using index, Using where, Using filesort等 |
6.2 type字段详解
text
查询效率从高到低： system：系统表，仅一行 const：通过主键或唯一索引查询 eq_ref：关联查询，主键或唯一索引 ref：非唯一索引查询 range：范围查询 index：全索引扫描 ALL：全表扫描（需要优化）
七、索引优化实战
7.1 选择合适的索引列
sql
-- 高选择性列适合建索引 SELECT COUNT(DISTINCT gender) / COUNT(*) FROM users; -- 选择性低 SELECT COUNT(DISTINCT email) / COUNT(*) FROM users; -- 选择性高 -- 为WHERE、JOIN、ORDER BY、GROUP BY的列建索引
7.2 避免过度索引
sql
-- 冗余索引示例 INDEX (A, B) -- 已经包含 INDEX (A) -- 冗余！ -- 重复索引示例 UNIQUE (A) -- 唯一索引 INDEX (A) -- 重复！
7.3 使用索引排序
sql
-- 使用索引排序 SELECT * FROM users ORDER BY name; -- 如果name有索引，可以使用 -- 无法使用索引排序的情况 SELECT * FROM users ORDER BY name, age DESC; -- 混合排序方向 SELECT * FROM users WHERE name = '张三' ORDER BY age; -- 如果索引是(name, age)，可用
八、高级索引技术
8.1 自适应哈希索引（AHI）
text
InnoDB自动为热点数据创建哈希索引 监控 → 检测热点数据 → 自动创建哈希索引
8.2 索引合并
sql
-- MySQL可能合并多个索引 SELECT * FROM users WHERE name = '张三' OR age = 20; -- 可能使用 name索引 + age索引，然后合并结果
8.3 前缀索引
sql
-- 为文本字段前N个字符创建索引 CREATE INDEX idx_email_prefix ON users(email(10)); -- 计算合适的前缀长度 SELECT COUNT(DISTINCT LEFT(email, 5)) / COUNT(*) as len5, COUNT(DISTINCT LEFT(email, 10)) / COUNT(*) as len10, COUNT(DISTINCT LEFT(email, 20)) / COUNT(*) as len20 FROM users;
九、索引设计最佳实践
9.1 设计原则
不是越多越好：每个索引都需要维护成本
经常查询的列：WHERE、JOIN、ORDER BY、GROUP BY
高选择性的列：区分度高的列
使用组合索引：减少索引数量
小字段优先：减少索引大小
避免更新频繁的列：增加维护成本
9.2 具体建议
sql
-- 1. 主键自增 CREATE TABLE users ( id INT AUTO_INCREMENT PRIMARY KEY, ... ); -- 2. 常用查询组合索引 CREATE INDEX idx_status_created ON orders(status, created_at); -- 3. 覆盖索引设计 -- 查询：SELECT id, name, age FROM users WHERE name LIKE '张%'; CREATE INDEX idx_name_age ON users(name, age); -- id是主键，自动包含 -- 4. 长字段使用前缀索引 CREATE INDEX idx_content ON articles(content(100));
9.3 监控与维护
sql
-- 查看索引使用情况 SELECT * FROM sys.schema_unused_indexes; -- 查看重复索引 SELECT * FROM sys.schema_redundant_indexes; -- 索引统计信息 SHOW INDEX FROM users; ANALYZE TABLE users; -- 更新统计信息
十、索引与锁机制
10.1 行锁与索引
sql
-- 没有索引：表锁 UPDATE users SET age = 20 WHERE name = '张三'; -- name无索引 → 锁全表 -- 有索引：行锁 UPDATE users SET age = 20 WHERE id = 100; -- id有主键索引 → 只锁该行
10.2 间隙锁（Gap Lock）
text
索引范围：10, 20, 30, 40, 50 UPDATE users SET ... WHERE age > 20 AND age < 40; 间隙锁锁定：(20, 30), (30, 40) 这两个区间 防止其他事务在这个范围内插入数据
总结
核心要点回顾：
B+Tree是MySQL索引的核心数据结构
InnoDB使用聚簇索引，数据和索引在一起
最左前缀原则是组合索引的黄金法则
索引覆盖可以避免回表，大幅提升性能
索引不是越多越好，需要权衡利弊
优化路线图：
text
1. 分析慢查询（slow log） 2. EXPLAIN查看执行计划 3. 检查索引使用情况 4. 设计合适的索引 5. 避免索引失效场景 6. 定期监控和维护
最后提醒：
索引是双刃剑，合理使用才能发挥最大价值
理论结合实践，根据实际业务调整
测试环境充分验证后再上线
监控生产环境索引使用情况