MySQL 索引底层原理：B+ 树深度剖析

索引是 MySQL 性能优化的核心，理解 B+ 树原理是掌握索引优化的前提。

一、为什么需要索引？

没有索引时，查询一条记录需要全表扫描，时间复杂度 O(n)。当表数据量达到百万级，查询会非常慢。

索引的本质：排好序的快速查找数据结构。

二、数据结构的演进

1. 二叉搜索树（BST）

左子树 < 根 < 右子树，查找效率 O(log n)。

问题：当数据本身有序时，BST 会退化为链表，查找效率 O(n)。

2. 平衡二叉树（AVL）

通过旋转保持平衡，左右子树高度差不超过 1。

问题：每个节点只存一个元素，树高仍然较高。百万数据需要 20 层，磁盘 IO 次数多。

3. B 树（多路搜索树）

每个节点可以存多个元素，且有多个子节点。

        [13 | 26 | 38]
       /    |    |    \
   [1,3,7] [15,18] [28,32] [40,45,48]

优势：树高降低，IO 次数减少。

缺点：每个节点都存数据，单个节点能放的元素有限；范围查询需要中序遍历。

4. B+ 树（MySQL 的选择）

在 B 树基础上的改进：

  • 非叶子节点只存索引，不存数据 → 单个节点能存更多索引 → 树更矮
  • 所有数据都在叶子节点 → 查询性能稳定
  • 叶子节点之间用双向链表连接 → 范围查询高效
        [13 | 26 | 38]              ← 非叶子节点
       /    |    |    \
   [1,3,7] [13,15,18] [26,28,32] [38,40,45]   ← 叶子节点
      ⇄       ⇄         ⇄          ⇄

三、MySQL 索引存储大小估算

InnoDB 默认页大小 16KB。

假设主键 bigint 占 8 字节，指针 6 字节：

  • 非叶子节点：16KB / (8+6) ≈ 1170 个指针
  • 叶子节点：假设一行数据 1KB → 16 行

三层 B+ 树可存储：

$$1170 \times 1170 \times 16 \approx 2190万 \text{ 行数据}$$

也就是说，千万级表查询只需要 3 次磁盘 IO！

四、索引的分类

按数据结构分

  • B+ 树索引：默认
  • Hash 索引：Memory 引擎，等值查询快
  • 全文索引：FULLTEXT，文本搜索

按物理存储分

  • 聚簇索引：数据和索引在一起（主键索引）
  • 非聚簇索引：数据和索引分开（二级索引）

按逻辑功能分

  • 主键索引：唯一非空
  • 唯一索引：唯一
  • 普通索引：无约束
  • 联合索引：多列组合

五、回表与索引覆盖

回表

-- 假设 name 上有普通索引
SELECT * FROM user WHERE name = '小宇宙';

执行过程：

  1. 在 name 索引树上找到「小宇宙」对应的主键 ID
  2. 再到主键索引树上根据 ID 查找完整数据

两次索引查找，称为回表。

索引覆盖

-- 只查询索引列，无需回表
SELECT id, name FROM user WHERE name = '小宇宙';

索引中包含了所有查询字段，无需回表，性能大幅提升。

六、最左前缀原则

联合索引 (a, b, c) 的排序规则是：先按 a 排序，a 相同按 b，b 相同按 c。

-- ✅ 命中索引
WHERE a = 1
WHERE a = 1 AND b = 2
WHERE a = 1 AND b = 2 AND c = 3

-- ❌ 无法命中索引
WHERE b = 2
WHERE c = 3
WHERE b = 2 AND c = 3

七、索引失效场景

-- 1. 计算操作
WHERE age + 1 = 18

-- 2. 函数操作
WHERE DATE(create_time) = '2026-06-24'

-- 3. 类型转换
WHERE phone = 13800000000  -- phone 是 varchar

-- 4. LIKE 左模糊
WHERE name LIKE '%小宇宙'

-- 5. OR 连接非索引列
WHERE a = 1 OR b = 2  -- b 无索引

-- 6. 不符合最左前缀

八、EXPLAIN 实战

EXPLAIN SELECT * FROM user WHERE name = '小宇宙';

重点关注：

| 字段 | 含义 |

| ---- | ---- |

| type | 访问类型，最好 ref/const，最差 ALL |

| key | 实际使用的索引 |

| rows | 估算扫描行数 |

| Extra | Using index（覆盖索引）/ Using filesort（额外排序） |

九、小结

  • B+ 树是 MySQL 索引的首选，矮胖 + 链表适合磁盘存储
  • 三层 B+ 树能存储千万级数据
  • 联合索引遵循最左前缀原则
  • 尽量做到索引覆盖，避免回表

小宇宙总结：理解索引的本质，是写 SQL 调优的根。慢查询 90% 都是索引没建好。