开篇引入
"为什么加了索引查询就快了？" "索引是怎么工作的？" "为什么有时候索引不生效？"
要回答这些问题，必须从数据结构层面理解索引。《高性能MySQL》第7章开篇就讲索引基础，这篇文章帮你把B+树索引的原理彻底搞懂。
索引的本质
类比：书的索引
想象一本技术书籍：
- 没有索引：找一个知识点，要翻遍全书
- 有索引：先查索引找到页码，直接翻到那一页
MySQL的索引就是书的索引，数据是书的正文。
书的索引 → 数据库索引
页码 → 数据行的物理位置
章节排序 → 索引的排序规则
索引数据结构的选择
为什么MySQL选择B+树作为索引结构？
| 数据结构 | 查找效率 | 插入效率 | 范围查询 | 适用场景 |
|---|---|---|---|---|
| 哈希表 | O(1) | O(1) | 不支持 | 等值查询 |
| 二叉树 | O(log n) | O(log n) | 有序 | 数据量小 |
| 平衡树 | O(log n) | O(log n) | 有序 | 通用 |
| B树 | O(log n) | O(log n) | 有序 | 磁盘存储 |
| B+树 | O(log n) | O(log n) | 优秀 | 数据库索引 |
为什么不用二叉树？
- 数据量大时，树会很高
- 每层一次磁盘IO，成本太高
- B+树多叉特性：层数少，IO次数少
B树 vs B+树
B树的特性
-- B树（B-Tree）特点：
-- 1. 每个节点都存储数据
-- 2. 节点满了会分裂
-- 3. 所有叶子节点在同一层
B树结构示例：
[50|100]
/ | \
[20|30] [60|70] [120|150]
/ | \ / | \ / | \
... ... ... ... ... ...
B+树的特性
-- B+树（B+Tree）特点：
-- 1. 只有叶子节点存储数据
-- 2. 叶子节点用链表连接
-- 3. 非叶子节点只存储索引（键）
B+树结构示例：
[50|100]
/ | \
[20|30] [60|70] [120|150]
/ | \ / | \ / | \
... ... ... ... ... ...
↓ 所有数据在叶子
叶子节点（双向链表）：
[20]↔[30]↔[50]↔[60]↔[70]↔[100]↔[120]↔[150]
为什么MySQL选择B+树
B树的问题：
- 非叶子节点也存数据，占用空间
- 同一层能容纳的索引少，树层数高
- 范围查询要遍历多个节点
B+树的优势：
- 非叶子节点只存索引，树更"矮胖"
- 叶子节点链表连接，范围查询极快
- 所有数据在叶子，查询更稳定
InnoDB的B+树结构
聚簇索引
InnoDB的表数据就是按主键构建的B+树，这叫聚簇索引：
聚簇索引B+树示例（主键ID = 1, 3, 5, 7, 9...）：
[5|9]
/ | \
[1|3] [7] [11|15]
/ | \ /|\ / | \
... ... ... ... ...
叶子节点包含完整行数据：
[ID=1] → [name=Alice, age=25, ...完整数据]
[ID=3] → [name=Bob, age=30, ...完整数据]
...
次级索引（非聚簇索引）
除主键索引外，其他索引都是次级索引：
次级索引B+树示例（INDEX idx_name(name)）：
[Mary|Sam]
/ | \
[Alice|Jack] [John] [Tom|Zoe]
/ | \ /|\ / | \
... ... ... ... ...
叶子节点只存储：name值 + 主键ID
[Mary] → 主键ID=5
[Sam] → 主键ID=3
回表查询：用主键ID去聚簇索引查找完整数据
索引的查找过程
等值查询
-- 查询：SELECT * FROM users WHERE id = 15;
-- 索引：主键id
-- 查找过程：
1. 从B+树根节点开始
2. 根节点：[10|20] → 15在10和20之间，走中间指针
3. 中间节点：[12|14|18] → 15在14和18之间
4. 叶子节点找到15
5. 返回完整行数据
范围查询
-- 查询：SELECT * FROM users WHERE id BETWEEN 10 AND 20;
-- 索引：主键id
-- 查找过程：
1. 先定位到10（等值查找）
2. 从叶子节点10开始
3. 顺着链表向后遍历：10 → 12 → 15 → 18 → 20
4. 返回所有匹配的行数据
最左前缀匹配
-- 索引：(name, age, city)
-- 查询：WHERE name = 'Alice'
-- 查找过程：
1. 从name列开始匹配
2. 找到所有name='Alice'的记录
3. 命中索引
-- 无法使用索引的情况：
WHERE age = 25 -- 跳过最左列，无法使用
WHERE name = 'Alice' AND city = 'Beijing' -- 可以，但只用到name列
索引的限制
1. 最左前缀原则
-- 索引：INDEX idx_name_age (name, age)
-- 能使用索引：
WHERE name = 'Alice' -- ✓ 匹配name列
WHERE name = 'Alice' AND age = 25 -- ✓ 匹配name和age列
-- 无法使用索引：
WHERE age = 25 -- ✗ 跳过name列
WHERE city = 'Beijing' -- ✗ 跳过前两列
2. 范围查询后的列无法使用
-- 索引：INDEX idx (a, b, c)
-- 能使用索引：
WHERE a > 5 AND b = 1 -- ✓ a用范围，b用等值
WHERE a = 1 AND b > 5 -- ✓ a和b都用上了
-- 部分无法使用：
WHERE a = 1 AND b > 5 AND c = 'x'
-- → a和b用索引，c无法使用
3. 索引列参与运算
-- 无法使用索引：
WHERE YEAR(created_at) = 2024
WHERE id + 1 = 100
WHERE name LIKE '%Alice%' -- 前导通配符
-- 能使用索引：
WHERE created_at >= '2024-01-01' AND created_at < '2025-01-01'
WHERE id = 99
WHERE name LIKE 'Alice%' -- 后缀通配符
4. 数据类型转换
-- 假设 phone 是 VARCHAR 类型
-- 无法使用索引：
WHERE phone = 13800138000 -- MySQL会把字符串转为数字
-- 能使用索引：
WHERE phone = '13800138000' -- 字符串比较字符串
索引的优缺点
优点
-- 1. 减少扫描数据量
SELECT * FROM users WHERE name = 'Alice';
-- 无索引：全表扫描100万行
-- 有索引：只扫描索引树，几十行
-- 2. 避免排序和临时表
SELECT * FROM users ORDER BY created_at DESC;
-- 无索引：filesort，可能需要临时表
-- 有索引：直接从索引读取，已排序
-- 3. 随机IO变顺序IO
-- 索引按顺序存储相关数据
-- 磁盘预读更有效
缺点
-- 1. 占用磁盘空间
-- 索引本身是额外的数据结构
-- 数据量越大，索引越大
-- 2. 增加写操作成本
INSERT / UPDATE / DELETE 时需要维护索引
-- 维护B+树的平衡
-- 更新索引结构
-- 3. 选择性低的索引效果差
-- 例如：性别只有男/女
-- 索引选择性 = 2/1000000 ≈ 0
三星索引评价系统
《高性能MySQL》引用了Lahdenmaki和Leach提出的三星索引理论：
一星：相关记录聚在一起
索引将相关记录放在同一个地方
例如：查用户的所有订单，订单按user_id排序
二星：索引顺序与查找顺序一致
索引顺序 = ORDER BY顺序
例如：ORDER BY created_at
索引也应按created_at排序
三星：覆盖索引
查询所需的所有列都在索引中
不需要回表
-- 完美的覆盖索引查询
CREATE INDEX idx_covering ON orders(user_id, status, created_at);
SELECT user_id, status, created_at
FROM orders
WHERE user_id = 1 AND status = 'paid'
ORDER BY created_at DESC;
自适应哈希索引
InnoDB还有一个自动优化机制：
-- 自适应哈希索引（Adaptive Hash Index）
-- InnoDB监控索引使用情况
-- 如果某些索引被频繁访问，在B+树上构建哈希索引
-- 实现O(1)的查找速度
-- 可以通过配置控制
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';
-- 关闭（不推荐，除非确定影响性能）
SET GLOBAL innodb_adaptive_hash_index = OFF;
InnoDB行格式
-- COMPACT行格式示意图
|变长字段长度列表 | NULL标志位 | 隐藏列 | 事务ID | 回滚指针 | 列1 | 列2 | 列3 |
-- 隐藏列（聚簇索引特有）：
-- 1. DB_ROW_ID: 如果没有主键，用这个
-- 2. DB_TRX_ID: 事务ID
-- 3. DB_ROLL_PTR: 回滚指针
小结
- B+树是MySQL索引的核心：多叉平衡树，适合磁盘存储
- 聚簇索引存储完整数据：主键索引的叶子节点包含整行
- 次级索引存储主键：需要回表查找完整数据
- 最左前缀原则：索引从最左列开始匹配
- 范围查询阻断后续列：WHERE a > 5 AND b = 1，b能用到索引
- 索引列不能参与运算：不能WHERE YEAR(d) = 2024
- 覆盖索引最快：查询列都在索引中，无需回表
- 自适应哈希索引：热点索引自动优化
理解B+树原理是索引优化的基础。知道索引怎么工作，才能理解为什么某些查询走索引、某些不走。
延伸阅读
- 《高性能MySQL》第7章 创建高性能的索引
- 《Relational Database Index Design and the Optimizers》——Tapio Lahdenmaki
- MySQL 8.0 Reference Manual: B-Tree Indexes
