MySQL 索引及其底层数据结构详解

业务适配：数据库中 70% 以上的查询包含范围条件（如时间范围、价格范围），B+ 树的链表设计完美适配这一核心场景。

数据访问一致性：B+ 树所有查询最终落到叶子节点

• B 树：等值查询可能在非叶子节点找到数据并返回，不同查询的终止节点层级不一致，缓存命中率低；
• B+ 树：无论等值查询还是范围查询，最终都要遍历到叶子节点，数据访问路径统一，缓存策略更易优化，且叶子节点存储完整数据（InnoDB）/数据指针（MyISAM），查询结果更完整。

更新效率：B+ 树非叶子节点无数据，更新成本更低

• B 树：更新数据时，若数据存储在非叶子节点，需修改该节点，且可能触发节点分裂/合并，影响范围广；
• B+ 树：数据仅在叶子节点，更新时仅需修改叶子节点，非叶子节点的索引值仅用于导航，无需频繁调整，维护成本更低。

B+ 树：为磁盘 I/O 优化的多路平衡树

基于上述优势，B+ 树成为 MySQL 索引的最优选择，其核心结构如下（以 InnoDB 的 B+ 树为例）：

根节点 （索引值 + 子节点指针）

中间节点 1 （索引值 + 子节点指针）

中间节点 2 （索引值 + 子节点指针）

叶子节点 1 （完整索引值 + 数据指针/数据）

叶子节点 2 （完整索引值 + 数据指针/数据）

叶子节点 3 （完整索引值 + 数据指针/数据）

叶子节点链表

B+ 树的核心特性

1. 数据只存于叶子节点：

• 非叶子节点仅存储'索引值 + 子节点指针'，可容纳更多索引项，大幅降低树高（百万数据仅需 3-4 层）；
• 叶子节点存储完整索引值 + 数据指针（MyISAM）或直接存储数据（InnoDB 聚簇索引）。

2. 叶子节点形成双向链表：

• 所有叶子节点按索引值有序串联，支持高效的范围查询（如 id BETWEEN 100 AND 200），只需找到起始节点，沿链表遍历即可；
• 对比 B 树：范围查询无需回溯父节点，效率提升数倍。

3. 多路平衡树结构：

• 每个节点可包含多个索引项（而非二叉树的 2 个），树高极低（3 层 B+ 树可存储千万级数据）；
• 磁盘 I/O 的最小单位是'页（Page，默认 16KB）'，B+ 树的节点大小适配页大小，一次 I/O 可加载整个节点，减少 I/O 次数。

InnoDB 和 MyISAM 的 B+ 树差异

哈希索引（Memory 引擎）

Memory 引擎支持哈希索引，核心特性：

• 等值查询（WHERE id = 100）效率极致（O(1)）；
• 不支持范围查询、排序、模糊查询；
• 仅适用于 Memory 引擎的临时表，非 MySQL 主流索引结构。

MySQL 常见索引类型：适用场景与使用规则

基于 B+ 树的底层结构，MySQL 衍生出多种索引类型，不同类型适配不同的查询场景：

主键索引（PRIMARY KEY）

• 核心特性：唯一、非空，InnoDB 中是聚簇索引（数据与索引合一）；
• 底层结构：B+ 树，叶子节点存储整行数据；
• 适用场景：表的唯一标识（如订单 ID、用户 ID）；
• 使用规则：
• 优先用自增整型主键（INT/BIGINT），避免 UUID（无序，插入导致 B+ 树频繁分裂）；
• 一张表仅能有一个主键索引。

唯一索引（UNIQUE）

• 核心特性：索引值唯一，允许 NULL（多个 NULL 不冲突）；
• 底层结构：B+ 树；
• 适用场景：需唯一约束的字段（如手机号、邮箱、订单编号）；
• 使用规则：避免对频繁更新的字段建立唯一索引（维护成本高）。

普通索引（INDEX）

• 核心特性：无唯一约束，最常用的索引类型；
• 底层结构：B+ 树；
• 适用场景：高频查询的非唯一字段（如商品分类、用户昵称）；
• 使用规则：按需创建，避免过度索引（写入性能下降）。

联合索引（复合索引）

• 核心特性：基于多个字段的组合索引，遵循'最左前缀匹配原则'；
• 底层结构：B+ 树（按第一个字段排序，第一个字段相同则按第二个，以此类推）；
• 适用场景：多字段联合查询（如 WHERE category_id = 1 AND price < 100）；
• 使用规则：
• 字段顺序：将区分度高的字段放在前面（如 (category_id, price) 优于 (price, category_id)）；
• 最左前缀匹配：查询条件需包含联合索引的最左字段（如索引 (a,b,c)，仅查 b=1 会导致索引失效）。

全文索引（FULLTEXT）

• 核心特性：针对文本内容的关键词检索，MyISAM/InnoDB（5.6+）支持；
• 底层结构：倒排索引（非 B+ 树）；
• 适用场景：文章内容、商品描述的模糊检索（如 MATCH(content) AGAINST('MySQL 索引')）；
• 使用规则：仅支持 CHAR/VARCHAR/TEXT 类型，不适合短文本字段。

空间索引（SPATIAL）

• 核心特性：针对地理空间数据（如经纬度）；
• 底层结构：R 树（非 B+ 树）；
• 适用场景：地图应用、位置检索（如附近的商家）；
• 使用规则：仅支持 MyISAM/InnoDB（5.7+），字段需为 GEOMETRY 类型。

索引实战避坑与优化技巧

索引失效的常见场景

索引优化核心原则

（1）优先创建'区分度高'的索引

区分度 = 唯一值数量 / 总数据量（越接近 1 越好）：

• 好例子：手机号（区分度≈1）、订单 ID（区分度≈1）；
• 坏例子：性别（区分度≈0.02）、状态（区分度≈0.1）。

（2）避免过度索引

• 每张表的索引数控制在 5 个以内；
• 避免对频繁更新的字段建索引（如库存、销量）；
• 冗余索引要删除（如已有 (a,b)，无需再建 (a)）。

（3）覆盖索引（Using Index）

查询的所有字段均在索引中，无需回表查询数据：

-- 索引`(id, name)`，查询仅需索引即可完成（覆盖索引）
SELECT id, name FROM user WHERE id = 100;
-- 对比：查询 age 需回表（索引仅含 id,name）
SELECT id, name, age FROM user WHERE id = 100;

（4）避免索引列使用函数/运算

-- 失效：索引列参与运算
SELECT * FROM order WHERE id + 1 = 100;
-- 有效：调整为常量运算
SELECT * FROM order WHERE id = 99;

索引有效性验证

通过 EXPLAIN 命令分析 SQL 执行计划，判断索引是否生效：

-- 分析查询语句的执行计划
EXPLAIN SELECT * FROM user WHERE phone = '13800138000';

关键字段解读：

• type：最优为 const（常量），其次 range（范围）、ref（索引匹配），最差 ALL（全表扫描）；
• key：显示实际使用的索引（NULL 表示未使用索引）；
• Extra：Using index 表示覆盖索引，Using where; Using index 为最优。

总结

底层核心：MySQL 主流索引基于 B+ 树实现，选择 B+ 树而非 B 树的核心原因是：B+ 树非叶子节点仅存索引项（树高更低、I/O 更少）、叶子节点链表适配范围查询、数据访问路径统一； 索引类型：主键索引是 InnoDB 的核心（聚簇索引），联合索引需遵循'最左前缀匹配'，全文索引/空间索引为特殊场景设计； 优化关键：避免索引失效场景（函数、%开头、类型不匹配），优先创建高区分度索引，用 EXPLAIN 验证索引有效性，避免过度索引。