大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
周一我们讲了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✅ 能用到aWHERE a = 1 AND b = 2✅ 能用到a和bWHERE 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 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
