一条 SQL 变慢，最常见的结论是"缺索引"。但补上索引后执行计划仍不按预期走，或者只差一个字段的两个查询差了十几倍，问题就不在索引存不存在，而在优化器对两个方案的成本估算谁更低。优化器不是规则引擎，它每次都在算一笔账：读多少页、回表多少次、比较多少行。读懂这笔账，才谈得上改索引。
1. 优化器在算什么：一份成本报价单
优化器给每个候选方案算一个总成本，取最低者。成本由 IO 与 CPU 两部分组成，单价存在两张系统表里：
| 成本项 | 含义 | 默认值（按 MySQL 8.0.x 核对） |
|---|---|---|
io_block_read_cost |
从磁盘读一个页 | 1.0 |
memory_block_read_cost |
页已在 buffer pool 中 | 0.25 |
row_evaluate_cost |
判断一行是否满足条件 | 0.1 |
key_compare_cost |
一次索引键比较 | 0.05 |
disk_temptable_create_cost |
建磁盘临时表 | 20 |
SELECT * FROM mysql.engine_cost\G
SELECT * FROM mysql.server_cost\G
关键在两条隐含假设：全表扫描按顺序读计价，页进来大概率预读命中，成本约等于页数 × 1.0；二级索引回表按随机读计价，每命中一行就要再拿一次主键回聚簇索引。所以同样读一万行，走二级索引要多付"一万次随机定位"的账。这就是选择性差时优化器宁愿全表扫的真实原因——不是判断错了，是回表太贵。
它还估"要读多少行"：全表扫取表行数；range 取区间行数；ref 取"表行数 ÷ 索引基数"。三个数全靠统计信息，一歪整笔账就歪。
2. 统计信息：优化器看到的数字从哪来
SELECT table_name, table_rows, data_length, index_length, auto_increment
FROM information_schema.TABLES
WHERE table_schema = 'app';
SELECT table_name, index_name, seq_in_index, column_name, cardinality
FROM information_schema.STATISTICS
WHERE table_schema = 'app' AND table_name = 'orders';
| 字段 | 含义 | 注意点 |
|---|---|---|
TABLE_ROWS |
行数估算 | InnoDB 是采样值，不是精确值 |
CARDINALITY |
索引列不同值个数估算 | 复合索引每列一行，末列常不准 |
innodb_stats_persistent |
统计信息是否持久化 | 默认 ON，重启不丢 |
innodb_stats_persistent_sample_pages |
持久化统计的采样页数 | 默认 20 页，大表必偏 |
innodb_stats_auto_recalc |
变更超阈值自动重算 | 小表、分散变更时可能长期不触发 |
采 20 个页去估一张几千万行的表，误差是结构性的，不是偶发。索引选择性越不均匀（比如 status 列 99% 是同一个值），采样越容易把基数估成"接近行数"，等值条件就被当范围条件处理。8.0 的直方图补这个缺口：
ANALYZE TABLE orders UPDATE HISTOGRAM ON status, city WITH 64 BUCKETS;
SELECT * FROM information_schema.COLUMN_STATISTICS WHERE TABLE_NAME = 'orders';
直方图存的是值的分布（等宽或等高桶），让优化器知道 status='failed' 只占 0.3%，不是均匀假设下的 25%。它只改善过滤行数估算，不改索引本身；对没有索引的列同样生效，这是它与 CARDINALITY 的本质差别。
3. 估算偏差的三种后果
| 症状 | 估算错在哪 | 表现 | 处理方向 |
|---|---|---|---|
| 该走索引却全表扫 | 区间行数估得过大 | type=ALL，rows 接近表行数 |
补直方图、ANALYZE TABLE、收窄条件 |
| 选错驱动表 | 两表过滤后行数比例估反 | join 顺序与直觉相反 | 看 filtered，必要时 STRAIGHT_JOIN |
| 索引选错 | 两个候选索引基数都不准 | 用了选择性差的索引，回表爆炸 | FORCE INDEX 验证后改索引设计 |
| 排序走了 filesort | 优化器认为索引排序更贵 | Using filesort 且 rows 很大 |
让排序列进索引前缀，或缩小结果集 |
偏差源头常是同一个：优化器假设数据均匀分布。某列一旦高度倾斜（状态、类型、地区、软删除标记），均匀假设就崩。软删除表里 deleted=0 占 99.9%，为它建单列索引，优化器算出"要扫 99.9% 的行"，于是放弃索引——这个判断是对的，问题在业务上不该拿它过滤。
4. EXPLAIN 怎么读
EXPLAIN SELECT id, amount FROM orders
WHERE user_id = 10086 AND status = 'paid'
ORDER BY created_at DESC LIMIT 20;
type 的优劣有严格序：
type |
含义 | 相对代价 |
|---|---|---|
system |
表仅一行 | 最优 |
const |
主键或唯一索引等值命中一行 | 极低 |
eq_ref |
join 时被驱动表按唯一索引命中 | 很低 |
ref |
非唯一索引等值命中 | 低 |
range |
索引区间扫描 | 中 |
index |
扫整个索引树 | 高 |
ALL |
全表扫描 | 最高 |
key_len 是实际用了索引的字节数，不是字符数：
| 列类型 | 索引中占的字节 |
|---|---|
INT |
4 |
BIGINT |
8 |
VARCHAR(50) utf8mb4 |
50×4+2 = 202 |
DATETIME |
5 |
| 可空列 | 再加 1 字节 NULL 标志 |
复合索引 idx(user_id, status, created_at)：user_id 4 字节，status VARCHAR(16) utf8mb4 为 66 字节，created_at 5 字节。key_len=70 说明只用到前两列，key_len=75 才是三列全用上。没到理论最大值，就说明索引被截断了——通常是被范围条件或类型转换断掉。
rows 与 filtered 必须相乘：rows 是估算要读的行数，filtered 是读完还剩百分之几。rows=1000, filtered=10 意味着约 100 行进入下一步。只看 rows 小就放心，是常见误判。
Extra 关键字 |
含义 | 好坏 |
|---|---|---|
Using index |
覆盖索引，不回表 | 好 |
Using index condition |
索引条件下推 ICP | 好 |
Using where |
回表后再过滤 | 中性，看 filtered |
Using temporary |
用临时表 | 差，常见于 GROUP BY/DISTINCT |
Using filesort |
额外排序 | 差，数据量大时明显 |
Using join buffer |
被驱动表无索引 | 差，基本等于缺索引 |
5. EXPLAIN ANALYZE、FORMAT=JSON 与 optimizer_trace
| 工具 | 回答的问题 | 开销 | 用在什么时候 |
|---|---|---|---|
EXPLAIN |
优化器打算怎么做 | 无 | 常规排查 |
EXPLAIN ANALYZE |
实际每步多少行、多少耗时 | 真跑一遍（8.0.18+） | 估算与实际差距大时 |
EXPLAIN FORMAT=JSON |
成本明细、attached_condition |
无 | 想看具体成本数字 |
optimizer_trace |
每个候选方案与淘汰理由 | 仅当前会话 | 想知道"为什么没选它" |
EXPLAIN ANALYZE SELECT ...;
EXPLAIN FORMAT=JSON SELECT ...\G
optimizer_trace 是三段结构：
SET optimizer_trace = 'enabled=on';
SELECT * FROM orders WHERE user_id = 10086 AND status = 'paid';
SELECT trace FROM information_schema.OPTIMIZER_TRACE\G
SET optimizer_trace = 'enabled=off';
| 段 | 内容 |
|---|---|
join_preparation |
条件化简、常量传播、外连接转内连接 |
join_optimization |
每表可选访问路径、considered_access_paths 与 cost |
rows_estimation |
候选路径行数估算、table_scan、potential_range_indexes |
要看的是 considered_access_paths 里每个候选的 cost 与 chosen 标记——它直接告诉你优化器给"走索引"和"全表扫"各报了多少价。trace 只对当前会话生效，输出很大，务必 \G 单条跑。
6. 索引提示与优化器开关
| 写法 | 语义 | 边界 |
|---|---|---|
USE INDEX (idx_a) |
缩小候选范围，仍允许全表扫 | 优化器可以不采纳 |
FORCE INDEX (idx_a) |
只在给定索引中选择，禁止全表扫 | 掩盖真实成本估算问题 |
IGNORE INDEX (idx_a) |
排除某索引 | 适合做对比实验 |
FORCE INDEX 的正当用途只有两个：临时验证假设、统计信息长期失真且短期无法修正。写进代码长期使用，等于把一次性的估算错误固化成永久技术债——数据分布一变，它就从救火变成放火。
SET SESSION optimizer_switch = 'mrr=on,mrr_cost_based=off';
SELECT @@optimizer_switch\G
| 开关 | 作用 | 什么时候动 |
|---|---|---|
index_merge |
允许多索引合并（intersection/union） |
出现大量 Using intersect 且回表更贵时可关 |
index_condition_pushdown |
ICP，过滤下推到引擎层 | 保持开，除非有已知异常 |
mrr / mrr_cost_based |
多范围读，随机 IO 合并为顺序 IO | 机械盘、大范围扫描值得试；SSD 收益有限 |
prefer_ordering_index |
8.0.20+ 优先用索引满足 ORDER BY |
为排序选了烂索引时可临时关 |
skip_scan |
8.0.13+ 不满足最左前缀也能用索引 | 默认开，见 Using index for skip scan |
动开关的原则：先会话级验证，再评估是否全局。SET GLOBAL 影响所有新连接，重启即失效，要持久必须写进 my.cnf。
7. 排错：五种"索引明明有却不走"
| 现象 | 原因 | 处理 |
|---|---|---|
type=ALL 但索引存在 |
区间行数估得过大，回表贵过全表扫 | ANALYZE TABLE；补直方图；收窄条件 |
rows=1 却扫了百万行 |
rows 是估算，const/eq_ref 也常为 1 |
EXPLAIN ANALYZE 看真实行数 |
| 昨天走索引今天不走 | 数据量跨过成本拐点 | 对比 TABLE_ROWS；谨慎固定计划 |
key_len 比预期小 |
复合索引被范围条件截断 | 等值列放前，范围列放最后 |
| 加了索引反而更慢 | 优化器选新索引但回表更多 | IGNORE INDEX 对比；考虑覆盖索引 |
8. 边界：优化器管不到的部分
| 场景 | 优化器能否解决 | 该做什么 |
|---|---|---|
| 索引缺失 | 不能，只能全表扫 | 按 WHERE + ORDER BY 设计索引 |
| 数据极度倾斜 | 部分能（直方图） | 直方图 + 改写查询 + 分区 |
深分页 LIMIT 100000,20 |
不能 | 游标分页/延迟关联 |
| 隐式类型转换 | 不能，索引直接失效 | 统一类型，字符串列别用数字比较 |
| 函数包裹索引列 | 不能 | 改写成范围条件，或用 8.0 函数索引 |
| 大事务锁等待 | 不能 | 拆事务，见锁与死锁那篇 |
一句话收束：EXPLAIN 读"估算"，EXPLAIN ANALYZE 读"事实"，optimizer_trace 读"理由"。三者对上，才敢动手改索引。
9. 自测
- 全表扫描和二级索引回表，在成本模型里最大的差别是哪一项？
innodb_stats_persistent_sample_pages默认是多少？为什么大表容易估歪？- 直方图和
CARDINALITY改善的是同一件事吗？ - 复合索引三列，
key_len停在第二列的理论值上，说明什么？ rows=1的const访问，能证明只读了一行吗？- 什么情况下
FORCE INDEX是合理的，什么情况下它是技术债？