MySQL 索引优化原理

引言

在数据库开发中，绝大多数查询缓慢问题都与索引设计不当有关。深入理解 MySQL InnoDB 引擎的索引底层原理，能帮助我们写出高效的查询 SQL，并在面临海量数据时依然能游刃有余地调优。

InnoDB 引擎的数据存储结构是基于 B+ 树实现的。相比于二叉树、红黑树或传统的 B 树，B+ 树有以下决定性的优势：

B+ 树的非叶子节点仅存储键值和指针，并不存储真实的数据行。这意味着单个节点可以容纳极多的分支。一个高度为 3 的 B+ 树，其叶子节点就可以存储数千万行的数据，查找任意记录只需 3 次磁盘 I/O。

在 B+ 树中，所有的数据都保存在叶子节点中，且叶子节点之间通过双向链表相连。当执行区间查询（如 WHERE id BETWEEN 10 AND 50）时，只需定位到第一个节点，然后顺着双向链表向后遍历即可，避免了在树的各个层级间做复杂的中序遍历。

聚簇索引（Clustered Index）：以主键构建的 B+ 树。其叶子节点直接存放了完整的数据行信息。
辅助索引（Secondary Index）：以非主键字段构建的索引。其叶子节点存储的是索引键值以及对应的主键值。
回表查询（Look Up）：当通过辅助索引查找记录时，MySQL 首先在辅助索引树中找到主键，然后再拿着主键去聚簇索引树中查找到完整的一行数据，这个过程称为回表。回表会导致额外的磁盘 I/O 损耗。

如果一个辅助索引中已经包含了查询所需的所有字段，MySQL 就可以直接返回结果，不再需要去主键树做回表查询：

-- 如果建立了联合索引 (name, age)
SELECT name, age FROM users WHERE name = 'Alice';

在 EXPLAIN 的 Extra 列中会显示 Using index。

在多列联合索引（如 INDEX (a, b, c)）中，MySQL 是从左到右匹配的。如果你的查询没有使用最左边的列 a（例如直接 WHERE b = 2），联合索引将无法被使用。

B+ 树索引底层设计

InnoDB 引擎的数据存储结构是基于 B+ 树实现的。相比于二叉树、红黑树或传统的 B 树，B+ 树有以下决定性的优势：

极矮胖的结构降低 I/O 次数

顺序扫描性能更佳

聚簇索引与辅助索引

聚簇索引（Clustered Index）：以主键构建的 B+ 树。其叶子节点直接存放了完整的数据行信息。

辅助索引（Secondary Index）：以非主键字段构建的索引。其叶子节点存储的是索引键值以及对应的主键值。

回表查询（Look Up）：当通过辅助索引查找记录时，MySQL 首先在辅助索引树中找到主键，然后再拿着主键去聚簇索引树中查找到完整的一行数据，这个过程称为回表。回表会导致额外的磁盘 I/O 损耗。

黄金优化策略

索引覆盖 (Covering Index)

如果一个辅助索引中已经包含了查询所需的所有字段，MySQL 就可以直接返回结果，不再需要去主键树做回表查询：

-- 如果建立了联合索引 (name, age) SELECT name, age FROM users WHERE name = 'Alice';

在 EXPLAIN 的 Extra 列中会显示 Using index。

最左匹配原则 (Leftmost Prefix)

避免索引失效的场景

对索引字段进行函数计算：例如 WHERE DATE(create_time) = '2026-05-26'。这会导致索引无法直接比对。

隐式类型转换：例如 WHERE phone = 13800000000（而 phone 字段为 varchar 类型）。

模糊查询以 % 开头：WHERE name LIKE '%Alice'，这会导致索引树无法按顺序匹配。