MySQL 优化器原理

优化器决定一条 SQL 怎么执行。很多人以为“我建了索引，MySQL 就一定用”，这就是没有理解优化器。

一句话理解：

优化器会根据统计信息估算不同执行方案的成本，然后选择它认为成本最低的方案。

优化器做什么

flowchart TD
    A["收到 SQL"] --> B["解析语法"]
    B --> C["检查表和字段"]
    C --> D["收集候选访问路径"]
    D --> E["根据统计信息估算成本"]
    E --> F["选择执行计划"]
    F --> G["执行器按计划执行"]

优化器会考虑：

1. 是否全表扫描。
2. 是否走某个索引。
3. 多表 Join 的连接顺序。
4. 是否需要排序。
5. 是否使用临时表。
6. 扫描多少行、回表多少次。

成本是什么

成本不是单纯的时间，而是数据库内部估算的代价。它通常和这些因素有关：

因素	影响
扫描页数	读的页越多，成本越高
扫描行数	需要判断的记录越多，成本越高
回表次数	二级索引查到主键后再查聚簇索引
排序	filesort 可能消耗 CPU、内存和磁盘
临时表	复杂 group by、distinct 可能产生临时表

统计信息

优化器不可能真的执行所有方案再比较，它依赖统计信息估算。

flowchart TD
    A["表数据"] --> B["统计信息"]
    B --> C["基数 Cardinality"]
    B --> D["数据分布"]
    B --> E["索引选择性"]
    C --> F["优化器估算 rows"]
    D --> F
    E --> F

如果统计信息不准，就可能选错索引。

更新统计信息：

analyze table orders;

为什么有索引但不用

常见原因：

原因	解释
返回数据太多	走索引加大量回表不如全表扫描
条件选择性差	status = 1 如果占 90%，索引过滤价值低
索引列被函数处理	无法利用 B+Tree 原始顺序
隐式类型转换	字段类型和条件类型不一致
统计信息不准	优化器估算错
索引顺序不匹配	联合索引没有符合最左前缀或排序要求

示例：

select *
from orders
where status = 1;

如果大部分订单都是 status = 1，优化器可能认为全表扫描更便宜。

优化器如何看联合索引

索引：

create index idx_user_status_created
on orders(user_id, status, created_at);

查询 A：

select *
from orders
where user_id = 1001
  and status = 1
order by created_at desc
limit 20;

这类查询适合这个索引。

查询 B：

select *
from orders
where status = 1
order by created_at desc
limit 20;

跳过了最左列 user_id，通常无法高效使用整个索引顺序。

Index Condition Pushdown

ICP 是 MySQL 的一种优化：在存储引擎层尽量用索引条件先过滤，减少回表。

示例索引：

create index idx_user_status on orders(user_id, status);

查询：

select *
from orders
where user_id = 1001
  and status = 1;

如果条件能在索引里判断，就先过滤掉不满足的索引记录，再回表读取完整行。

flowchart TD
    A["扫描二级索引"] --> B["在索引层判断部分条件"]
    B --> C{"满足条件吗"}
    C -- "否" --> D["丢弃，不回表"]
    C -- "是" --> E["回表读取完整行"]

覆盖索引为什么快

覆盖索引是查询字段都在索引里。

select user_id, status, created_at
from orders
where user_id = 1001
order by created_at desc
limit 20;

如果索引包含这些字段，就不需要回表。

覆盖索引快的原因：

1. 少一次聚簇索引查找。
2. 少读取完整行。
3. 索引页通常比数据页更小，缓存效率更高。

但覆盖索引不是万能的。如果扫描范围巨大，即使不回表也会慢。继续看：大表覆盖索引仍然慢。

商业排查流程

flowchart TD
    A["SQL 慢"] --> B["EXPLAIN"]
    B --> C["看 key 和 rows"]
    C --> D{"是否用了预期索引"}
    D -- "否" --> E["检查条件、类型、函数、最左前缀"]
    D -- "是" --> F{"扫描是否仍然很大"}
    F -- "是" --> G["缩小范围、改分页、归档、拆表"]
    F -- "否" --> H["看锁等待、排序、临时表、网络"]

面试标准回答

MySQL 优化器会根据统计信息估算不同执行计划的成本，并选择它认为成本最低的方案，所以有索引不一定会用索引。成本主要来自扫描页数、扫描行数、回表、排序和临时表。统计信息会影响 rows、filtered 和索引选择，如果统计信息不准可能选错计划。索引选择还受条件选择性、联合索引顺序、隐式类型转换、函数处理和返回数据量影响。排查时要看 EXPLAIN 的 type、key、rows、filtered、Extra，并结合真实数据量和慢日志判断。

关联学习：EXPLAIN、索引、优化相关。