InnoDB索引深度解析（上篇）：结构、Cardinality与策略

2026-06-11阅读 0热度 0

其他

周一剖析了 InnoDB 的整体存储架构，周二拆解了 EXPLAIN 的执行语义。今天轮到这两个知识点落地实操——索引优化。

你肯定背过不少口诀：“优先选择高选择性的列建索引”“复合索引把最频繁的等值条件放左边”“避免 SELECT *”……但背后的逻辑是什么？这些规则的本质，全藏在 InnoDB 的 B+ Tree 结构以及优化器对 Cardinality 的估算机制里。

换个场景理解：去图书馆找一本书。知道索书号，直接按编号到对应书架取书——这是聚簇索引，数据按主键顺序物理存放。只知道分类“计算机”，需要先查分类索引卡，卡片指明“计算机类→书架3排2层”，再到对应位置拿书——这就是二级索引加回表。如果卡片上直接印好了书名和作者，连书架都不用跑——这就是覆盖索引。

下面逐步深入。

一、InnoDB 的索引结构：B+ Tree

InnoDB 底层索引统一采用 B+ Tree。你可以把它看作一棵倒挂的树：所有数据行挂在叶子节点，非叶子节点只存键值和指针，起到路标作用。叶子节点之间用双向链表串联，因此范围扫描只需顺序遍历链表。树高通常为 2~4 层，意味着一次索引查找仅需 2~4 次磁盘 I/O。

聚簇索引：InnoDB 表的数据行本身按主键构建 B+ Tree，叶子节点存放完整行数据。如果没有显式定义主键，InnoDB 会自动生成一个隐藏的 ROWID 作为聚簇索引。

二级索引：叶子节点只存储索引列的值和对应的主键值。通过二级索引定位到主键后，必须再到聚簇索引中获取完整行——这个过程就是回表。

二、回表与覆盖索引

回表是性能损耗的核心瓶颈。看一个具体场景：

-- 二级索引 idx_name (name)
SELECT name, age FROM user WHERE name = '张三';

这条 SQL 的执行路径：先在 idx_name 索引中找到 name='张三' 的记录，拿到主键 id；再用 id 到聚簇索引中定位整行，取出 age。两次索引遍历，两次 I/O。

如果把 age 也纳入索引：

CREATE INDEX idx_name_age ON user(name, age);

此时二级索引的叶子节点已经包含 name 和 age，查询直接返回，无需回表。这就是覆盖索引。通过 EXPLAIN 查看，Extra 列会输出 Using index。

所以，针对高频查询，尽量把 SELECT 涉及的列全部塞进索引中，彻底避免回表开销。

三、最左前缀原则

复合索引相当于一本按多列排序的电话簿：先按姓氏排序，姓氏相同再按名字排序，名字相同再按电话号码排序。要查找所有姓“张”的人，可以直接翻到“张”那一页——用到了第一列。但要想找所有叫“小明”的人，无论姓什么，就没法直接翻，因为名字不是第一排序依据。

这就是最左前缀原则：查询条件必须从复合索引的第一列开始连续匹配，不能跳过中间列。

假设复合索引 (a, b, c)：

WHERE a = 1 ✅ 用到 a
WHERE a = 1 AND b = 2 ✅ 用到 a 和 b
WHERE a = 1 AND c = 3 ✅ 只用到 a，b 被跳过，c 无法利用索引
WHERE b = 2 ❌ 完全无法使用索引

实战建议：将查询中频繁出现的等值条件放在复合索引的最左侧；范围查询（>、<、BETWEEN）放在右侧，因为一旦遇到范围条件，其右侧所有列都无法再走索引。

四、Cardinality——优化器如何选择索引

Cardinality（基数）指索引中不重复值的数量。可以理解为“分类的精细度”：身份证号的基数极高，性别列的基数极低（通常只有 2）。

优化器选择索引时，优先参考基数高的列，因为能更快地缩小扫描范围。如果某索引的基数很低（比如 status 只有 3 种状态），优化器可能估算：用该索引需要大量回表，不如直接全表扫描来得快。这就是有些字段明明建有索引，但 EXPLAIN 显示使用全表扫描的根本原因之一。

查看 Cardinality：

SHOW INDEX FROM table_name;

结果中的 Cardinality 列为估算值。

案例：

SELECT * FROM orders WHERE status = 'PAID';

如果 status 只有 3 种取值，Cardinality=3，选择性约 33%。当表记录只有几千行时，全表扫描可能比索引+回表更快。

索引失效的常见场景：

低 Cardinality（优化器主动放弃）
隐式类型转换（mobile 字段为字符串，却用数字比较）
函数包裹索引列（WHERE UPPER(name) = 'ABC'）
LIKE 以 % 开头（WHERE name LIKE '%abc'）

如果 Cardinality 估算不准确怎么办？统计信息过旧会导致优化器选错索引。执行 ANALYZE TABLE 可强制重新收集统计信息。

五、如何设计复合索引的顺序？

一条核心准则：等值在前，范围在后，高基数优先。

举例：

SELECT * FROM orders 
WHERE customer_id = 123 
  AND create_time BETWEEN '2026-01-01' AND '2026-06-01' 
  AND status = 'PAID';

推荐索引顺序：(customer_id, status, create_time)

理由如下：

customer_id 是等值查询，且基数高，放在最左能最快缩小范围。
status 也是等值查询，虽然基数低，但能进一步过滤。
create_time 是范围查询，放在最后，因为范围之后的列无法再使用索引。

六、真实案例：复合索引顺序调优

原始 SQL：

SELECT * FROM orders 
WHERE shop_id = 10086 
  AND status = 'PAID' 
  AND create_time > '2026-05-01';

原始索引：(create_time, shop_id, status)

执行计划显示：type=range，仅用到 create_time，扫描 5 万行，filtered=10%。意味着 90% 的行在回表后被过滤掉，性能严重浪费。

优化后索引：(shop_id, status, create_time)

新执行计划：type=ref，用到了 shop_id 和 status，扫描 200 行，filtered=100%。查询耗时从 2 秒降至 0.05 秒。

七、总结

索引优化不是玄学，而是基于 B+ Tree 结构和 Cardinality 的科学决策。吃透聚簇索引与二级索引的本质、最左前缀原则的生效条件、回表代价的量化分析、以及 Cardinality 如何影响优化器路径，你就能设计出精准高效的索引，同时解释“为什么这个索引有效”或“为什么优化器没选它”。下期将继续探讨索引合并、ICP、索引下推等进阶特性。