MySQL 实战宝典（五）：MySQL 索引——从 B+ 树原理到实战优化

TracyCoder123

墨衍会员 · AI 创作全网分发码龄8年

关注

原创 2026-01-15 12:18:36 1.5k 阅读 AI 写同款· GEO 优化 ›

Mysql高级篇（中）—— 索引优化 MySQL索引失效的场景、案例分析及如何优化索引阅读详情

文章目录

一、什么是索引？
二、核心原理：为什么选择 B+ 树？
三、 MySQL 索引实战
四、索引设计最佳实践
参考资料

一、什么是索引？

索引（Index） 是帮助 MySQL 高效获取数据的排好序的数据结构。如果把数据库表比作一本书，索引就是书的目录，它能让你在海量数据中快速定位到所需内容，而无需翻阅整本书（全表扫描）。

由于数据量通常很大，索引无法全部加载到内存，因此索引往往以索引文件的形式存储在磁盘上。

1. 聚簇索引 vs 非聚簇索引

InnoDB 存储引擎根据索引的物理存储方式，将索引分为两类：

索引类型	别名	描述	叶子节点存储内容	是否需要回表
聚簇索引	主键索引	每张表有且仅有一个。表数据本身就是按 B+ 树组织存储的。	完整的行记录（所有列）	否
非聚簇索引	二级索引 / 辅助索引	可以有多个。	索引列的值 + 主键值	是（除非是覆盖索引）

🔍 图解：两种索引的区别

聚簇索引（Clustered Index）：
找到叶子节点，就直接拿到了所有数据。
非聚簇索引（Secondary Index）：
找到叶子节点拿到主键 ID，再去聚簇索引中查数据的过程，称为回表。

2. 联合索引

针对多个列（如 col1, col2）构建的索引。排序规则为：先按 col1 排序；在 col1 相同的情况下，再按 col2 排序。

二、核心原理：为什么选择 B+ 树？

数据库的读取是以页（Page） 为单位的（通常 16KB）。索引设计的目标是：在尽可能少的磁盘 I/O 次数内找到目标数据。

我们来看看不同数据结构的表现：

1. 为什么不用二叉查找树 / 红黑树？

结构问题：树太高（瘦高型）。
I/O 瓶颈：二叉树每个节点只能存一个数据。对于千万级数据，树的高度会非常高，导致每一次查询都要进行大量的磁盘 I/O 操作，性能极差。

二叉树

2. 为什么不用 B 树（B-Tree）？

B 树虽然是多叉树（矮胖型），但它有一个特点：非叶子节点也存储数据（行记录）。

缺点 1：路数变少。由于数据页大小是固定的（16KB），如果节点存了很大的行数据，那么能存的索引指针就变少了，树就会变高，I/O 增多。
缺点 2：范围查询困难。叶子节点之间没有指针连接，范围查询需要反复回溯遍历。

3. 为什么 B+ 树是神？

B+ 树是对 B 树的升级版，完美契合数据库查询场景：

更矮更胖：非叶子节点只存索引（key），不存数据。一个节点能塞下更多的索引值，使得整棵树的层级非常低（通常 3 层就能存千万级数据）。
查询稳定：所有数据都在叶子节点，查询路径长度一致。
天生支持范围查询：叶子节点之间通过双向链表连接，范围查询只需定位起点，然后顺着链表遍历即可。

B+树

三、 MySQL 索引实战

1. 索引创建演示

CREATE TABLE student (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY, -- 聚簇索引
    `name` VARCHAR(10) NOT NULL,
    `gender` CHAR(1) NOT NULL,
    `address` VARCHAR(100) NOT NULL,
    KEY `idx_name_gender` (`name`, `gender`) -- 联合索引（非聚簇）
);

💡 最佳实践：为什么建议使用自增主键？

存储效率：自增 ID 占用空间小，B+ 树非叶子节点能存更多指针，树更矮。
写入性能：自增 ID 保证了写入是顺序追加，避免了随机写和页分裂带来的性能损耗。

2. 最左前缀原则（核心规则）

对于联合索引 (col1, col2, col3)，B+ 树是按照从左到右的顺序建立的。索引匹配必须严格遵循“从左到右，不能跳过”的原则。

(1) 字段顺序匹配

✅ WHERE col1='a' AND col2='b' -> 命中 (col1, col2)
✅ WHERE col2='b' AND col1='a' -> 命中 (MySQL 优化器会自动调整顺序)
❌ WHERE col2='b' -> 失效 (跳过了 col1，没法查)
⚠️ WHERE col1='a' AND col3='c' -> 部分命中 (只用到了 col1，col3 无法使用索引)

(2) 字符串模糊匹配

✅ LIKE 'abc%' -> 命中 (前缀确定，是有序的)
❌ LIKE '%abc' -> 失效 (前缀未知，无序)

(3) 范围查询的中断

如果中间某个字段使用了范围查询（>, <, BETWEEN, LIKE），则其右边的列无法使用索引。

WHERE col1='a' AND col2 > 10 AND col3='c'
- col1：命中
- col2：命中
- col3：失效 (因为 col2 是范围，导致后面的 col3 在 B+ 树中不再有序)

3. 索引失效的常见场景

场景	原因	示例
计算/函数	索引是对原始值建立的，计算后无序	`WHERE YEAR(create_time) = 2023`
类型转换	隐式转换会导致全表扫描	字符串字段不加引号：`WHERE name = 123`
最左前缀断裂	见上文	`WHERE age = 18` (索引是 name_age)
OR 条件	OR 两边只要有一个没索引，就会全表扫描	`WHERE id = 1 OR age = 18` (age 无索引)
不等于	`!=` 或 `<>` 无法精确命中，通常走全表	`WHERE name != 'Tom'`

四、索引设计最佳实践

在实际开发中，建立索引需要权衡“查询性能”与“维护成本”。

覆盖索引 (Covering Index) 🔥
- 尽量让 SQL 查询的字段都包含在联合索引中。
- 效果：直接在二级索引中就能查到结果，避免回表，性能极高。
- 例：SELECT name, gender FROM student WHERE name='Tom'，正好命中 idx_name_gender，无需回表。
避免 SELECT *
- SELECT * 几乎必然导致回表，且传输大量无用数据，浪费 I/O 和带宽。
高区分度原则
- 只为区分度高（唯一值多）的列建索引。
- 反例：不要为 gender（只有男/女）单独建索引，因为过滤后数据量依然很大，MySQL 可能直接走全表扫描。
字符串前缀索引
- 如果字符串很长（如文章标题），可以只对前 N 个字符建索引：CREATE INDEX idx_title ON table(title(10))。
控制数量
- 索引不是越多越好。每个索引都需要占用磁盘空间，且每次 INSERT/UPDATE/DELETE 都要维护索引树，过多的索引会降低写入性能。