周一我们深入探讨了 InnoDB 的整体架构，周二则详细拆解了 EXPLAIN 执行计划。今天，这两方面的知识将派上大用场——我们聚焦于 MySQL 索引优化。
你可能听过不少经验口诀：“创建索引要优先选择区分度高的列”“复合索引应把最常用的字段放在前面”“尽量避免使用 SELECT *”……但你知道这些规则背后的技术原理吗？这些口诀的底层逻辑，全部源于 InnoDB 的索引结构以及优化器基于 Cardinality（基数）的评估机制。
打个比方，你去图书馆找一本书。如果你知道书的编号，直接按编号去书架上取书，这就是聚簇索引——数据本身就是按照主键顺序排列存放的。如果你只知道书的分类“计算机”，那你得先去查阅分类索引卡，卡片上写着“计算机类 → 书架3排2层”，然后你再去那个位置取书，这就是二级索引加上回表操作。如果卡片上直接把书名和作者都标注齐全，你连书架都不用跑，这就是覆盖索引。
下面我们一步步剖析其中的关键要点。
一、InnoDB 的索引结构：B+ Tree
InnoDB 采用 B+ Tree 作为索引的底层数据结构。你可以把它想象成一棵倒挂的树，所有数据都存储在底部的叶子节点上，上层的内部节点只充当“路标”作用。叶子节点之间通过双向链表连接，因此范围扫描（Range Scan）效率极高。B+ Tree 的高度通常只有 2 到 4 层，意味着一次索引查找仅需 2 到 4 次磁盘 I/O 操作。
聚簇索引（Clustered Index）：InnoDB 表的数据本身就是以主键为排序依据的 B+ Tree，叶子节点存放完整的行数据。如果你没有显式定义主键，InnoDB 会自动为你添加一个隐藏的 ROWID 作为聚簇索引。
二级索引（Secondary Index）：它的叶子节点只存储索引列的值以及对应的主键值。当通过二级索引查找数据时，会先获取主键，再回到聚簇索引中检索完整的行——这个过程就是回表。
二、回表与覆盖索引
回表是造成性能损耗的重要因素。来看一个典型的例子：
-- 假设有一个二级索引 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，查询可以直接从索引中返回结果，无需回表。这就是覆盖索引（Covering Index）。使用 EXPLAIN 查看执行计划时，Extra 列会显示 Using index。
因此，对于高频查询，尽量将查询所需的全部列都加入索引中，从而避免回表带来的额外开销。
三、最左前缀原则
复合索引就像一本按照多列排序的通讯录：先按姓排序，姓相同再按名排序，名相同再按电话排序。如果你想找所有姓“张”的人，可以直接翻到张姓段落——此时用到了第一列。如果你想找所有叫“小耶”的人（无论姓什么），那就无法直接通过索引快速定位，因为名不是第一排序依据。
这就是最左前缀原则（Leftmost Prefix Principle）：查询条件必须从复合索引的第一列开始，并且不能跳过中间的列。
假设复合索引为 (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（基数）表示索引中不重复值的数量。你可以理解为“分类的细粒度”：身份证号几乎人人不同，基数就很高；性别只有男/女两种，基数就很低。
优化器在选择索引时，会优先考虑基数高的索引，因为它能快速缩小数据范围。如果某个索引的基数很低（比如 status 字段仅有 3 种取值），优化器可能会评估：使用这个索引需要多次回表，还不如直接全表扫描快。这就是为什么有时明明存在索引，优化器却弃用的原因之一。
查看 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）、索引下推等高级特性。
