MySQL 索引原理：B+ 树、聚簇索引、辅助索引、联合索引与覆盖索引

索引是 MySQL 查询优化里最常见、也最容易误用的工具。它能显著提升查询速度，但也会占用空间、拖慢写入、增加维护成本。

真正理解索引，要从两个问题开始：为什么 MySQL 选择 B+ 树？为什么有时候建了索引却没用上？

什么是索引

索引是帮助 MySQL 高效获取数据的数据结构。可以把它类比成书的目录：没有目录时只能从头翻到尾，有目录时可以快速定位章节。

索引的优点：

• 提高查询效率。
• 加速排序和分组。
• 加速关联查询。
• 通过唯一索引保证唯一性。

索引的代价：

• 占用磁盘空间。
• 降低插入、更新、删除效率。
• 索引过多会增加优化器选择成本。

索引为什么通常不用 Hash

Hash 查询等值条件很快，但它不适合数据库通用索引。

主要问题：

• 不支持范围查询。
• 不支持排序。
• 不支持最左前缀匹配。
• 哈希冲突需要额外处理。

数据库常见查询不仅有 id = 1，还有范围、排序、分组、模糊前缀等场景。

为什么不用二叉树和红黑树

二叉树、红黑树在内存中很好用，但数据库索引存储在磁盘上。磁盘 IO 是瓶颈，树越高，访问磁盘次数越多。

如果数据量很大，二叉树每个节点分叉太少，树高会比较高。每下一层都可能意味着一次磁盘 IO。

数据库索引更希望：

• 树高度低。
• 每个节点存更多索引项。
• 支持范围查询。
• 叶子节点方便顺序遍历。

B 树和 B+ 树

B 树是多叉树，一个节点可以存多个 key 和数据，树高比二叉树低。

B+ 树在 B 树基础上进一步优化：

• 非叶子节点只存 key，不存完整数据。
• 叶子节点存所有数据或索引项。
• 叶子节点之间有指针连接。
• 更适合范围查询和顺序扫描。

MySQL InnoDB 索引通常使用 B+ 树。

B+ 树为什么适合 MySQL

B+ 树适合数据库的原因：

特点	好处
多叉结构	降低树高，减少磁盘 IO
非叶子节点不存数据	单页能放更多 key
叶子节点链表	范围查询效率高
数据有序	支持排序和范围扫描

索引设计的本质，是用额外空间换更少的磁盘 IO。

MyISAM 和 InnoDB 索引差异

MyISAM 和 InnoDB 都可以使用 B+ 树，但索引和数据的组织方式不同。

对比项	MyISAM	InnoDB
数据和索引	分开存储	主键索引叶子节点存整行数据
主键索引	非聚簇索引	聚簇索引
辅助索引叶子节点	存数据文件地址	存主键值
回表方式	根据地址取数据	根据主键再查聚簇索引

InnoDB 的表数据本身就是按主键聚簇索引组织的。

聚簇索引

聚簇索引不是一种单独索引类型，而是一种数据组织方式。

InnoDB 中：

• 如果有主键，主键索引就是聚簇索引。
• 如果没有主键，会选择第一个非空唯一索引。
• 如果还没有，会生成隐藏 ROWID。

聚簇索引的叶子节点存放整行数据。因此用主键查询效率很高。

辅助索引和回表

InnoDB 的辅助索引叶子节点存储的是主键值。

如果通过辅助索引查询，但要返回的列不在辅助索引中，就需要再根据主键回到聚簇索引查询整行数据，这就是回表。

例如：

select * from user where age = 18;

如果 age 有索引，先在 age 索引上找到主键，再根据主键回表取完整记录。

联合索引和最左前缀

联合索引是多个字段组成的索引，例如：

create index idx_a_b_c on t(a, b, c);

使用联合索引要遵循最左前缀原则。

可以利用索引的条件：

• where a = ?
• where a = ? and b = ?
• where a = ? and b = ? and c = ?

不容易利用完整索引的条件：

• where b = ?
• where c = ?
• where b = ? and c = ?

联合索引的顺序非常重要。

范围之后全失效

联合索引中，如果遇到范围查询，范围列右边的列通常不能继续用于索引定位。

例如索引 (a, b, c)：

where a = 1 and b > 10 and c = 5

a 可以用，b 可以用于范围扫描，c 通常不能继续用于缩小索引扫描范围。

这就是口诀里的“范围之后全失效”。

覆盖索引

如果查询需要的列都在同一个索引里，就不需要回表，这就是覆盖索引。

例如索引 (name, age)：

select name, age from user where name = '刘备';

查询列和条件列都在索引中，可以直接从索引返回结果。

覆盖索引常用于优化高频查询，因为它能减少回表 IO。

ICP：索引条件下推

ICP 是 Index Condition Pushdown，索引条件下推。

没有 ICP 时，存储引擎可能先根据索引找到记录并回表，再由 Server 层判断剩余条件。

有 ICP 后，部分条件可以下推到存储引擎层，在索引遍历时先过滤，减少回表次数。

它的目标是：能在索引层判断的条件，就尽量不要回表后再判断。

索引创建原则

适合创建索引的字段：

• 频繁出现在 where 条件中。
• 频繁用于 order by、group by。
• 多表 join 的关联字段。
• 查询列可以组成覆盖索引。

不适合创建索引的字段：

• 表记录很少。
• 区分度很低。
• 频繁更新。
• 字段很长。
• 无序值，如 UUID，容易导致页分裂。

InnoDB 主键建议使用自增长整型，减少页分裂和索引膨胀。

常见索引失效场景

常见口诀：

全值匹配我最爱，最左前缀要遵守。
索引列上不计算，范围之后全失效。
覆盖索引尽量用，不等空值还有 or。
like 百分写最左，字符串别忘加引号。

典型失效场景：

• 在索引列上使用函数或计算。
• 字符串不加引号导致隐式转换。
• like '%abc' 前缀通配。
• or 两边有未建索引字段。
• !=、<>、is null、is not null 在部分场景下效果差。

小结

MySQL 索引可以这样理解：

1. 索引用空间换查询速度。
2. B+ 树适合磁盘 IO 和范围查询。
3. InnoDB 主键索引就是数据本身的组织方式。
4. 辅助索引查询可能需要回表。
5. 联合索引要遵循最左前缀。
6. 覆盖索引可以减少回表。
7. 索引不是越多越好，要结合查询和写入成本设计。