索引优化深潜（上）：InnoDB 索引结构、Cardinality 与索引策略

大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！

周一我们讲了InnoDB的整体架构，周二讲了EXPLAIN执行计划。今天这两块知识要派上大用场了——​索引优化​。

你可能听过很多口诀：“建索引要选区分度高的列”“复合索引要把最常用的放前面”“不要用SELECT *”……但你知道为什么吗？这些口诀背后的底层原理，全都在InnoDB的索引结构和优化器的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。

所以，对于高频查询，尽量把查询需要的列都塞进索引里，避免回表。

三、最左前缀原则

复合索引就像一本按多列排序的通讯录：先按姓排，姓相同再按名排，名相同再按电话排。如果你想找所有姓“张”的人，可以直接翻到张那一段——用到了第一列。如果你想找所有叫“小耶”的人，不管姓什么，那就没法直接翻，因为名不是第一排序依据。

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

假设复合索引 (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、索引下推等高级特性。

小耶在手，SQL 不愁

还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~