mysql索引

在 MySQL（尤其是 InnoDB 存储引擎）中，索引是提高数据检索效率的核心机制。本文将系统对比常用的索引数据结构，并深入剖析联合索引的底层原理与实战最佳实践。

一、 索引数据结构对比

为了在海量数据中实现高效查找，数据库对底层数据结构的选择经历了多次演进。下面对比常见的几种数据结构：

1. Hash 索引 vs B+ 树

• Hash 索引：利用哈希表存储，通过计算键值的 Hash 码映射到对应的内存/磁盘地址。
  • 优点：等值查询（=、IN）非常快，时间复杂度接近 $O(1)$。
  • 缺点：不支持范围查询（如 >, <, BETWEEN）、不支持排序（数据无序）、无法利用部分索引键进行前缀匹配。
• B+ 树索引：多路平衡查找树。
  • 优点：天然适合范围查询和排序，查询效率稳定（复杂度为 $O(\log N)$）。

2. B 树 vs B+ 树

MySQL 为什么选择 B+ 树 而不是 B 树？核心差异如下：

特性	B 树 (B-Tree)	B+ 树 (B+Tree)
数据存储位置	所有节点（根节点、内部节点、叶子节点）都存储 key 和 data	仅叶子节点存储 key 和 data，非叶子节点仅存储 key（索引列值 + 页指针）
单页节点容量	节点既存索引又存数据，单页（16KB）能容纳的 key 较少，树的高度相对更高	内部节点只存索引，单页能容纳极多 key，树的高度更矮（通常 3~4 层即可存千万级数据）
磁盘 I/O 次数	较高（树越高，磁盘 I/O 越频繁）	更少（树的高度更矮，极大减少磁盘 I/O 次数）
范围查询能力	需要对树进行中序遍历	叶子节点之间通过双向链表相连，范围查询只需在叶子节点链表上顺序扫描即可
查询稳定性	查找最好情况 $O(1)$，最坏 $O(\log N)$，性能不稳定	任何数据的查找都需要从根节点走到叶子节点，路径长度一致，查找性能稳定

3. B+ 树 vs 跳表 (SkipList)

• 跳表：Redis 中 Sorted Set 使用的数据结构，基于链表+多层索引，增删改查复杂度均为 $O(\log N)$。
• 对比选择：跳表纯在内存中操作时性能极佳且实现简单；但 MySQL 数据主要存储在磁盘中。B+ 树的“高扇出（Fan-out）”特性使得树高度极低，大幅减少了磁盘 I/O 次数，因此更适合关系型数据库存储引擎。

二、 联合索引（复合索引）深度剖析

联合索引是指基于表中的多个字段共同创建的索引，例如 INDEX idx_a_b_c (a, b, c)。

1. 底层存储原理

在 B+ 树中，联合索引的叶子节点按照创建索引时定义的字段顺序排布：

1. 先按照第一个字段 a 进行排序。
2. 当 a 的值相同时，再按照第二个字段 b 进行排序。
3. 当 b 的值也相同时，才按照第三个字段 c 进行排序。

核心结论：联合索引中，只有第一个字段 a 是全局有序的；而后续字段（如 b、c）仅在前序字段相等的前提下才是局部有序的。

2. 最左前缀匹配原则 (Leftmost Prefix Principle)

基于上面的存储原理，查询条件使用联合索引时必须遵循最左前缀匹配原则：

假设创建联合索引 (a, b, c)：

• 完全生效：
  • WHERE a = 1 （利用 a 索引）
  • WHERE a = 1 AND b = 2 （利用 a, b 索引）
  • WHERE a = 1 AND b = 2 AND c = 3 （利用 a, b, c 索引）
• 顺序无关性：
  • WHERE b = 2 AND a = 1 （MySQL 优化器会自动调整顺序为 a = 1 AND b = 2，仍可完全命中索引）
• 部分生效 / 失效：
  • WHERE b = 2 AND c = 3 （完全不生效，跳过了最左列 a）
  • WHERE a = 1 AND c = 3 （仅 a 生效，c 无法通过索引快速定位）

3. 范围查询与索引截断

当联合索引中出现范围查询（如 >, <, BETWEEN, LIKE 'prefix%'）时，范围列可以使用索引，但范围列之后的所有列都将无法继续使用索引进行精确定位。

对于索引 (a, b, c)，若执行 WHERE a = 1 AND b > 2 AND c = 3：

• a = 1：精确定位，命中索引。
• b > 2：范围定位，命中索引。
• c = 3：无法利用索引定位。因为当 b > 2 时，c 的存储在整体上是无序的，无法通过 B+ 树二分查找定位，只能对满足 a=1 AND b>2 的记录逐一检查 c 的值。

4. 索引下推 (Index Condition Pushdown, ICP)

在 MySQL 5.6 引入 ICP 之前：

• 如果执行 WHERE a = 1 AND c = 3（索引 a, b, c），存储引擎通过索引找到所有满足 a = 1 的主键，然后回表读取完整行数据，再由 Server 层过滤 c = 3。

在 MySQL 5.6 及之后（开启 ICP）：

• 存储引擎在遍历联合索引时，虽然 c 无法用于快速 B+ 树定位，但由于索引树节点中已包含 c 字段的值，存储引擎会直接在索引层检查 c = 3，不满足条件者直接过滤，大幅减少回表次数。

5. 覆盖索引 (Covered Index)

如果一个查询的语句中，SELECT 所需的字段以及 WHERE / ORDER BY 条件全部包含在联合索引的字段中，存储引擎只需扫描索引树即可拿到所需数据，无需读取聚簇索引（主键树）。

• 优势：避免了“回表”查询带来的随机磁盘 I/O，极大地提升了查询性能。
• 查看方式：使用 EXPLAIN 分析 SQL 语句，当 Extra 列显示 Using index 时，即代表触发了覆盖索引。

三、 联合索引建表与优化最佳实践

1. 高频与高选择性字段置前：把查询频率最高、区分度（选择性）最好的列放在联合索引的最左侧。
2. 合理利用覆盖索引：尽量只查询（SELECT）必要的字段，避免 SELECT *，从而促成覆盖索引。
3. 注意范围条件的位置：尽量将需要范围查询的字段放在联合索引的末尾。
4. 控制索引数量：联合索引可以替代多个单列索引（例如 (a, b) 已经包含了 a 单列索引的功能），避免重复建索引增加写操作（INSERT/UPDATE）的开销。