大家好，我是小耶，写功课只是为了我踩过的坑，你们别再踩了！
前几天，有个开发同学火急火燎地来找我：“小耶，见鬼了！我给 status 字段加了个索引，查询不但没变快，反而从 0.5 秒变成了 5 秒！把索引一删，又秒回了 0.5 秒。”
“加索引反而慢，删索引反而快？”
这听起来完全违背常理。在很多人的直觉里，B+树索引不就是用来加速查询的吗？为什么加了反而成了拖油瓶？
今天，我们就来扒一扒 MySQL 内核里一个极具欺骗性的机制：回表代价。顺便看看优化器到底是怎么在背后“算计”我们的。
先搞清楚一个背景：
InnoDB 有两大核心索引：聚簇索引（主键索引） 和 二级索引（辅助索引）。
聚簇索引：叶子节点存的是整行数据。
二级索引：叶子节点存的是索引列的值 + 主键ID。
当你写 SELECT * FROM orders WHERE status = 'DONE' 时，如果走了二级索引，MySQL 需要先在 status 索引树上找到所有 DONE 的主键ID，然后再拿着这些ID去主键索引树上查完整的行数据。
这个过程，就叫回表。
一、为什么优化器会“放弃”你的索引？
假设我们有一张 1000 万行的订单表，status 的状态分布如下：
DONE：900 万行（占 90%）PENDING：90 万行CANCELLED：10 万行
现在你要查 status = 'DONE'。
方案A：走二级索引 + 回表
在
status索引树上，扫描出 900 万个主键ID。拿到这 900 万个ID后，回到主键（聚簇）索引树里，进行 900 万次随机I/O查找。
方案B：全表扫描
直接顺着主键的聚簇索引，从头到尾扫一遍，把每一行读出来，判断 status 是不是 DONE。这是 1000 万次顺序I/O。
在机械硬盘或普通的SSD上，随机I/O的速度是远远低于顺序I/O的。
优化器（CBO）估算了一下代价：900 万次回表产生的随机 I/O 成本，甚至超过了 1000 万次顺序全表扫描。于是，优化器果断决定：“这索引不用了，直接全表扫！”
这就是为什么你明明建了索引，EXPLAIN 里的 type 却显示 ALL，key 显示 NULL。
如何验证？
用 EXPLAIN 查看执行计划，你会发现 possible_keys 里有这个索引，但 key 是 NULL。这就实锤了优化器主动放弃了它。
二、如果强制用索引，为什么会慢 10 倍？
有时候，如果你用 FORCE INDEX 强制 MySQL 走索引，或者 MySQL 8.0 的优化器由于统计信息过期硬选了索引，性能就会惨烈翻车。
为什么？因为你在业务代码里写了 SELECT *，导致 MySQL 必须进行 900 万次回表。这就有了开头那一幕：0.5 秒的全表扫描，变成了 5 秒的随机乱跳。
三、终极解法：消灭回表（覆盖索引）
既然瓶颈在“回表”，解法就很明确了：不要回表。
如果我们在索引树上，就能拿到业务需要的所有列，就完全不需要去聚簇索引里找完整行了。这叫覆盖索引（Covering Index）。
比如，业务其实只需要查订单号和金额：
SELECT id, amount FROM orders WHERE status = 'DONE';
我们可以建一个联合索引：
ALTER TABLE orders ADD INDEX idx_status_amount (status, amount);
这时候，索引的叶子节点上，存的是 status、amount 和 id。
MySQL 在 idx_status_amount 里扫完 900 万个数据，直接返回即可。整个过程只有顺序读取索引页，没有任何随机 I/O。
看 EXPLAIN 的结果：Extra 列里会出现一个极度舒适的字眼：Using index。
实测数据对比：
全表扫描：0.52 秒
强制走普通索引（大量回表）：4.85 秒
走覆盖索引（无回表）：0.02 秒
四、避坑指南：怎么写对覆盖索引？
尽量不要写
SELECT *。这是导致回表的最直接元凶。只查你需要的列。联合索引的设计。通常把 WHERE 条件里的列放在前面，把 SELECT 里的列放在后面。
不要盲目建索引。如果一个字段的基数（不同值的数量）极低，比如性别（男/女）、布尔状态（0/1），单独建索引毫无意义。如果业务经常查低频值（比如 PENDING），可以只建这个值的部分索引（MySQL 8.0 暂不支持，但可以走其它优化），或者把高频字段放在联合索引的最左侧。
五、小结
优化器不是傻子，它比人更懂 I/O 代价。加索引反而慢，通常是因为大比例数据查询带来的“回表随机 I/O”压垮了性能。看执行计划时，别只盯着有没有走索引，更要看 Extra 里有没有 Using index。能用覆盖索引解决的，绝不让它回表！
小耶在手，SQL 不愁
还有什么想了解的，欢迎留言！小耶一定知无不言言无不尽……我们下次见~
