二、详解 MySQL 索引结构

为什么 MySQL 选择 B+ 树？聚簇索引和二级索引到底有什么区别？回表、覆盖索引、索引下推又是怎么回事？本文将从底层数据结构出发，彻底讲透 MySQL 索引的每一个关键概念。

一、前言

数据库查询是后端开发中最频繁的操作之一。当表中只有几十条数据时，全表扫描毫无压力；但当数据量达到千万级，一条没走索引的 SQL 就可能导致整个系统瘫痪。

索引的本质，就是排好序的快速查找数据结构。它的核心目标是——减少磁盘 I/O，避免全表扫描。

MySQL 的 InnoDB 引擎使用B+ 树作为索引的底层数据结构。理解索引结构，是理解一切查询优化、慢查询排查、索引设计的基础。

二、从二叉树到 B+ 树：一场问题驱动的进化

要理解 MySQL 为什么选择 B+ 树，需要先看其他数据结构为什么不行。

2.1 二叉搜索树（BST）

最直观的索引结构：左子树所有节点值小于父节点，右子树所有节点值大于父节点。

致命缺陷：

• 极端场景下退化为链表：如果插入的数据是有序的（如自增 ID），树会退化成单链表，查询时间从 O(log N) 变成 O(N)
• 树高过高：即使平衡状态下，100 万条数据的树高约 20 层，意味着每次查询需要 20 次磁盘 I/O

2.2 平衡二叉树（AVL）/ 红黑树

AVL 通过旋转保证左右子树高度差不超过 1；红黑树通过弱平衡规则限制最长路径。它们解决了退化问题。

核心缺陷：

• 树高依然过高：100 万条数据，树高接近 20，每次查询约 20 次磁盘 I/O，无法满足高并发场景
• 每个节点只存一个键值：完全浪费了磁盘预读特性（每次 I/O 读取一个 16KB 的数据页）

2.3 B 树（平衡多路查找树）

B 树让每个节点存储多个键值，树高大幅降低。

仍然存在的问题：

• 非叶子节点也存储数据：导致每个节点能存储的索引键数量减少，树高依然高于 B+ 树
• 范围查询效率低：需要多次中序遍历，跨节点 I/O 次数多
• 查询性能不稳定：部分查询在非叶子节点就能返回，部分需要到叶子节点，优化器难以稳定预估成本

2.4 B+ 树——数据库的终极选择

B+ 树在 B 树基础上做了三个关键改进，完美适配数据库的磁盘存储和查询场景：

图1：B+ 树索引结构示意图

三、B+ 树的核心特性

3.1 非叶子节点只存键，不存数据

所有完整数据都存储在叶子节点，非叶子节点只存索引键和指针。这意味着同样大小的页（16KB），非叶子节点能存储更多的键，树高极低。

算一笔账：

假设主键是BIGINT（8 字节），指针占 6 字节，一个页不存数据只存键和指针：

(16 × 1024) / (8 + 6) ≈ 1170 个键

也就是说，一个非叶子节点最多有1170 个子节点。假设叶子节点一行数据的大小是 1KB，一个叶子节点可以存储16条数据，那么：

结论：一棵高度仅 3 层的 B+ 树，就能支撑约 2000 万条数据。每次查询只需 3 次磁盘 I/O。

3.2 叶子节点用双向链表串联

所有叶子节点按索引键升序排列，通过双向链表连接。这使得：

• 范围查询极其高效：找到起始位置后，沿链表顺序扫描即可
• 排序操作不需要额外开销：数据本身已经有序
• 分页查询直接跳过：沿链表移动即可

3.3 所有查询都落到叶子节点

无论查询的键值是在非叶子节点就能匹配，还是必须到叶子节点，最终都会走到叶子节点。这意味着：

• 查询性能稳定：每次查询的 I/O 次数固定
• 优化器可以精准预估成本：利于生成稳定的执行计划

四、InnoDB 的数据页结构

B+ 树的每个节点，在 InnoDB 中对应一个或多个数据页（Page）。

4.1 页的基本概念

4.2 页内结构

一个数据页的核心组成部分：

┌─────────────────────────────────────────┐ │ File Header（16 字节） │ ← 页的元信息 ├─────────────────────────────────────────┤ │ Page Header（56 字节） │ ← 页的状态信息 ├─────────────────────────────────────────┤ │ Infimum Record │ ← 虚拟最小记录 ├─────────────────────────────────────────┤ │ User Records（行记录区） │ ← 实际数据行 │ ┌────────┐ ┌────────┐ ┌────────┐ │ │ │ Row 1 │ │ Row 2 │ │ Row 3 │ ... │ │ └────────┘ └────────┘ └────────┘ │ ├─────────────────────────────────────────┤ │ Supremum Record │ ← 虚拟最大记录 ├─────────────────────────────────────────┤ │ Free Space（空闲空间） │ ← 新数据插入位置 ├─────────────────────────────────────────┤ │ Page Directory（页目录） │ ← 槽（Slot）数组， │ [Slot1] [Slot2] [Slot3] ... │ 快速定位行记录 └─────────────────────────────────────────┘

4.3 行记录格式

InnoDB 的每一行数据，除了用户定义的列之外，还包含一些隐藏列：

五、聚簇索引（Clustered Index）

5.1 什么是聚簇索引

聚簇索引是 InnoDB 最核心的概念：索引和数据存储在一起，叶子节点直接包含完整的行数据。

换句话说，表数据的物理存储顺序就是聚簇索引的顺序。

5.2 聚簇索引的选择规则

InnoDB 按以下优先级确定聚簇索引：

1. 如果表定义了PRIMARY KEY→ 主键就是聚簇索引
2. 如果没有主键 → 选择第一个UNIQUE NOT NULL的索引
3. 如果以上都没有 → InnoDB 自动生成一个6 字节的隐藏ROW_ID作为聚簇索引

5.3 聚簇索引的 B+ 树结构

[根节点] / | \ [非叶子] [非叶子] [非叶子] / | \ / | \ / | \ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ │L1 │→│L2 │→│L3 │→│L4 │→│L5 │ ← 叶子节点 │id=1 │ │id=5 │ │id=9 │ │id=13│ │id=17│ 存完整行数据 │全行 │ │全行 │ │全行 │ │全行 │ │全行 │ └─────┘ └─────┘ └─────┘ └─────┘ └─────┘ ←──── 双向链表连接 ────→

特点：

• 叶子节点存储完整的行数据（所有列的值）
• 叶子节点之间通过双向链表连接
• 数据按主键顺序物理有序存储

5.4 聚簇索引的优劣势

六、二级索引（Secondary Index）

6.1 什么是二级索引

二级索引（也叫辅助索引、非聚簇索引）是基于非主键字段构建的 B+ 树。

核心区别：二级索引的叶子节点不存储完整行数据，只存储索引列的值 + 对应的主键值。

6.2 二级索引的 B+ 树结构

以CREATE INDEX idx_name ON users(name)为例：

[根节点] / | \ [非叶子] [非叶子] [非叶子] / | \ / | \ / | \ ┌──────┐ ┌──────┐ ┌──────┐ ┌──────┐ │name= │→│name= │→│name= │→│name= │ ← 叶子节点 │Alice │ │Bob │ │Carol │ │David │ 存索引列+主键值 │id=5 │ │id=3 │ │id=8 │ │id=12 │ └──────┘ └──────┘ └──────┘ └──────┘ ←──── 双向链表 ────→

6.3 为什么存主键值，不存物理地址？

这是一个非常重要的设计决策：

聚簇索引会发生页分裂，数据行的物理地址会变化。如果二级索引存储物理地址，每次页分裂都需要更新所有二级索引，维护成本极高。而存储主键值，页分裂不影响二级索引，只需通过主键到聚簇索引中查找即可。

6.4 聚簇索引 vs 二级索引

七、回表查询

7.1 什么是回表

当通过二级索引查找数据时，如果查询所需的列不在二级索引中，就需要：

1. 第一步：在二级索引的 B+ 树中查找，获取主键值
2. 第二步：用主键值到聚簇索引中查找完整行数据

第二步就是回表——从二级索引"回到"聚簇索引再查一次。

7.2 完整示例

CREATETABLEusers(idINTPRIMARYKEY,nameVARCHAR(20),ageINT,cityVARCHAR(20),INDEXidx_name(name))ENGINE=InnoDB;-- 这条 SQL 需要回表SELECT*FROMusersWHEREname='Alice';

执行过程：

① 在 idx_name 的 B+ 树中查找 name='Alice' → 找到叶子节点：name='Alice', id=5 ② 用 id=5 到聚簇索引的 B+ 树中查找 → 找到完整行：id=5, name='Alice', age=30, city='Beijing' ③ 返回结果

7.3 回表的性能影响

• 每次回表 = 1 次额外的 B+ 树查找（2~3 次磁盘 I/O）
• 如果查询返回 1000 行，最坏情况需要1000 次回表
• 当优化器预估回表行数过多时，会认为回表的随机 I/O 成本 > 全表扫描的顺序 I/O 成本，直接放弃索引走全表扫描

八、覆盖索引——消除回表

8.1 原理

如果查询所需的所有列都包含在索引中，就不需要回表了——这就是覆盖索引（Covering Index）。

-- 建立联合索引CREATEINDEXidx_name_ageONusers(name,age);-- 查询列都在索引中 → 覆盖索引，无需回表SELECTid,name,ageFROMusersWHEREname='Alice';

idx_name_age的叶子节点存储(name, age, id)，而查询只需要id, name, age——所有数据都在索引里，一次查找搞定。

8.2 如何判断

用EXPLAIN查看执行计划，如果Extra列显示Using index，表示使用了覆盖索引。

EXPLAINSELECTid,name,ageFROMusersWHEREname='Alice';-- Extra: Using index ✅

8.3 为什么常说"避免 SELECT *"

SELECT *会查询所有列，而二级索引通常不包含所有列，因此几乎必然触发回表。更严重的是，当优化器预估回表成本过高时，会直接放弃索引走全表扫描——索引白建了。

九、联合索引与最左前缀原则

9.1 联合索引的结构

联合索引（复合索引）是基于多个列建立的索引。以INDEX idx_a_b_c (a, b, c)为例：

B+ 树的排序规则：先按a排序 →a相同时按b排序 →a, b都相同时按c排序。

叶子节点排列顺序： (a=1, b=1, c=1) → (a=1, b=1, c=3) → (a=1, b=2, c=1) → (a=2, b=1, c=1) → ...

9.2 最左前缀原则

查询条件必须从联合索引的最左列开始，且中间不能跳过列：

CREATEINDEXidx_a_b_cONusers(a,b,c);-- ✅ 使用 aWHEREa=1-- ✅ 使用 a, bWHEREa=1ANDb=2-- ✅ 使用 a, b, cWHEREa=1ANDb=2ANDc=3-- ❌ 不使用索引（跳过了 a）WHEREb=2-- ❌ 不使用索引（跳过了 a）WHEREb=2ANDc=3

9.3 范围查询的影响

范围查询（>、<、BETWEEN、LIKE）后面的列无法使用索引：

-- ✅ a 用等值，b 用范围，c 无法使用索引WHEREa=1ANDb>2ANDc=3-- 实际用到索引的列：a, b（c 失效）-- ✅ a 用范围，b 和 c 都无法使用索引WHEREa>1ANDb=2ANDc=3-- 实际用到索引的列：a（b、c 失效）

原因：联合索引先按a排序，a相同时按b排序。当b是范围查询时，b > 2的结果中b值不唯一，c就失去了排序意义，因此c的索引无法使用。

9.4 = 和 IN 的区别

在 MySQL 8.0 中，IN被视为等值查询，不会中断最左前缀：

-- ✅ a 用 IN，b 仍可使用索引WHEREaIN(1,2,3)ANDb=2

9.5 索引设计建议

• 等值查询的列放前面，范围查询的列放后面
• 区分度高的列放前面（如user_id优于gender）
• 尽量用联合索引代替多个单列索引

十、索引下推（Index Condition Pushdown, ICP）

10.1 背景

在没有索引下推之前，对于联合索引(a, b)，查询WHERE a = 1 AND b = 2的过程：

① 在索引中找到 a=1 的所有记录（通过最左前缀） ② 逐条回表，取出完整行 ③ 在 Server 层判断 b 是否等于 2

步骤 ②③ 中大量回表取出的数据最终被过滤掉，造成不必要的 I/O。

10.2 索引下推的优化

MySQL 5.6 引入了索引下推（ICP）：在遍历索引的过程中，直接在存储引擎层过滤掉不满足条件的记录，减少回表次数。

① 在索引中找到 a=1 的所有记录 ② 在索引层直接判断 b 是否等于 2（不满足的直接跳过） ③ 只对满足 a=1 AND b=2 的记录回表

10.3 如何判断

EXPLAIN的Extra列显示Using index condition，表示使用了索引下推。

十一、Hash 索引

11.1 基本结构

Hash 索引基于哈希表实现：对索引键计算 Hash 值，Hash 值指向数据行的存储位置。

11.2 Memory 引擎的 Hash 索引

MySQL 的 Memory 存储引擎默认使用 Hash 索引。适合场景：

• 内存中的临时表
• 只需要等值查询（WHERE id = ?）
• 数据量不大

11.3 InnoDB 的自适应哈希索引（AHI）

InnoDB 在 B+ 树的基础上，内部维护了一个自适应哈希索引：

• 当 InnoDB 观察到某些索引值被频繁等值查询时，自动为这些值建立 Hash 索引
• 将 B+ 树的 O(log N) 查询优化为 O(1)
• 完全自动，无需手动配置
• 当数据访问模式变化时，自动释放不常用的 Hash 条目

十二、MySQL 索引分类总结

12.1 按数据结构分类

12.2 按应用方式分类

12.3 按物理存储分类

十三、索引失效的常见场景

图2：MySQL 索引失效常见场景总结

建了索引却不走索引，是最常见的性能陷阱。以下是高频失效场景：

13.1 对索引列使用函数或表达式

-- ❌ 索引失效WHEREDATE_FORMAT(create_time,'%Y-%m-%d')='2026-01-01'WHEREid+1=1000-- ✅ 改写为等值范围查询WHEREcreate_time>='2026-01-01'ANDcreate_time<'2026-01-02'WHEREid=999

原因：B+ 树中存的是原始值，函数运算后无法匹配索引的有序性。

13.2 隐式类型转换

-- phone 是 VARCHAR 类型-- ❌ 传入数字，触发隐式转换，索引失效WHEREphone=13800138000-- ✅ 传入字符串WHEREphone='13800138000'

原因：MySQL 将字符串列转为数字比较，等价于对索引列使用了函数。

13.3 不满足最左前缀原则

-- 联合索引 (a, b, c)-- ❌ 跳过最左列 aWHEREb=2ANDc=3

13.4 LIKE 以通配符开头

-- ❌ 索引失效WHEREnameLIKE'%张'WHEREnameLIKE'%张%'-- ✅ 索引生效WHEREnameLIKE'张%'

原因：B+ 树按前缀排序，通配符在前无法确定扫描范围。

13.5 OR 条件中有无索引列

-- ❌ 如果 b 没有索引，整个 OR 条件导致全表扫描WHEREa=1ORb=2

原因：OR 要求两边的条件都能走索引，否则优化器选择全表扫描。

13.6 索引列参与运算

-- ❌ 索引失效WHEREage*2=40-- ✅ 改写WHEREage=20

13.7 NOT IN / NOT EXISTS

某些场景下，NOT IN和NOT EXISTS会导致优化器放弃索引，选择全表扫描。可以用LEFT JOIN ... WHERE ... IS NULL改写。

13.8 优化器判断全表扫描更快

当表数据量很小，或者使用索引需要回表的行数占比过大时，优化器会认为全表扫描的顺序 I/O 比索引的随机 I/O 更快，主动放弃索引。

十四、用 EXPLAIN 验证索引使用情况

判断索引是否生效，不要靠猜——用EXPLAIN：

EXPLAINSELECT*FROMusersWHEREname='Alice';

重点关注：

十五、总结

核心知识脉络

B+ 树（底层数据结构） ├── 聚簇索引（主键索引，叶子存完整数据行） │ └── 每表 1 个，数据按主键物理有序 ├── 二级索引（辅助索引，叶子存索引列+主键值） │ └── 每表可建多个 │ └── 可能需要回表 │ ├── 覆盖索引 → 消除回表 ✅ │ └── 索引下推 → 减少回表 ✅ ├── 联合索引（多列组合） │ └── 最左前缀原则 ├── Hash 索引（等值查询 O(1)） │ └── InnoDB 自适应哈希索引 └── 索引失效场景 └── 函数、隐式转换、不满足最左前缀、LIKE %、OR 等

一句话记忆

参考资源

• MySQL 官方文档 — InnoDB Index Types