MySQL 查询优化器是数据库性能的核心组件之一，它负责将 SQL 语句转换为高效的执行计划，并决定使用哪些索引。理解其工作原理，不仅能帮助你在面试中脱颖而出，更能指导日常的 SQL 优化实践。
一、查询优化器的作用与架构
当一条 SQL 到达 MySQL Server 层时，会经历解析器（Parser）→ 预处理器（Preprocessor）→ 查询优化器（Optimizer）→ 执行器（Executor）几个阶段。优化器的核心任务是在语义等价的前提下，寻找代价最小的执行路径。
优化器主要完成两类工作：
- 逻辑优化：如子查询展开、外连接转内连接、常量折叠、谓词下推、等价类推导等。
- 物理优化：选择访问路径（全表扫描 vs 索引扫描）、选择连接算法（Nested Loop Join、Hash Join、BNL）、决定连接顺序。
二、基于代价的优化模型（CBO）
MySQL 采用基于代价的优化器（Cost-Based Optimizer）。它并不保证找到理论最优解，而是在有限时间内找到一个“足够好”的计划。代价模型主要估算两类开销：
- I/O 代价：读取数据页的成本。默认
innodb_page_size = 16KB，优化器通过统计信息估算需要读取多少页。 - CPU 代价：比较、排序、聚合等操作的成本。
总代价公式可简化为：
Total Cost = IO_cost + CPU_cost
优化器会枚举多个候选执行计划，分别计算代价，最终选择代价最低者。
三、统计信息：优化器的“眼睛”
没有准确的统计信息，CBO 就是“盲人摸象”。MySQL 的统计信息主要来自：
- 表统计：
SHOW TABLE STATUS中的Rows（表行数估算）、Data_length等。 - 索引统计：
SHOW INDEX FROM table中的Cardinality（索引列唯一值数量），它决定了索引的选择性。 - 直方图（MySQL 8.0+）：
information_schema.COLUMN_STATISTICS，用于描述列的数据分布，对非均匀分布列尤为重要。
统计信息由后台线程或 ANALYZE TABLE 更新。innodb_stats_auto_recalc 控制是否自动更新，innodb_stats_persistent 决定是否持久化存储。
面试常问：为什么索引明明存在，优化器却不用？
常见原因：Cardinality 过低（如性别列）、统计信息过期、查询返回行数占比过高（如超过 20%~30%）、使用了函数或隐式类型转换导致索引失效。
四、访问路径的选择
对于单表查询，优化器主要在以下访问方法中选择：
- 全表扫描（ALL）：当需要读取大部分数据行时，顺序 I/O 比随机 I/O 更划算。
- 索引扫描（index）：遍历整棵二级索引树，通常比全表扫描快，因为索引更小。
- 范围扫描（range）：用于
>、<、BETWEEN、IN等范围条件。 - ref / eq_ref：等值匹配，
eq_ref用于主键或唯一索引连接。 - const：通过主键或唯一索引与常量比较，最多返回一行。
- index merge：对多个索引分别扫描后合并（交集、并集），实际使用较少，因为效率常不如复合索引。
优化器如何决策？核心是选择性（Selectivity）：
选择性 = 不重复值数量 / 总行数
选择性越接近 1，索引过滤效果越好。例如 user_id 选择性高，适合建索引；status 只有几个值，选择性低，全表扫描可能更优。
五、连接顺序与连接算法
多表连接时，优化器需要决定：
- 驱动表（outer table）与被驱动表（inner table）的顺序。
- 使用哪种连接算法。
MySQL 常用算法：
- Nested Loop Join（NLJ）：驱动表每行去被驱动表匹配，适合被驱动表连接列有索引。
- Block Nested Loop（BNL）：无索引时使用 join buffer 批量匹配，MySQL 8.0.18 前常用。
- Hash Join：MySQL 8.0.18+ 引入，适合等值连接且无可用索引的场景。
优化器倾向于选择结果集小的表作为驱动表，以减少外层循环次数。STRAIGHT_JOIN 可强制指定连接顺序，但应谨慎使用。
六、如何查看与分析执行计划
使用 EXPLAIN 或 EXPLAIN FORMAT=JSON 查看执行计划。关键字段：
| 字段 | 含义 |
|---|---|
| type | 访问类型，性能：system > const > eq_ref > ref > range > index > ALL |
| key | 实际使用的索引 |
| rows | 预估扫描行数 |
| filtered | 过滤后剩余行数百分比 |
| Extra | 额外信息，如 Using index、Using filesort、Using temporary |
EXPLAIN ANALYZE（8.0.18+）可输出实际执行的耗时与行数，对比预估可发现统计信息偏差。
七、优化器提示（Optimizer Hints）
当优化器选错计划时，可用 Hint 干预：
SELECT /*+ INDEX(t idx_name) */ * FROM t WHERE ...;
SELECT /*+ NO_INDEX(t idx_name) */ * FROM t WHERE ...;
SELECT /*+ JOIN_ORDER(t1, t2) */ ...;
但 Hint 是“最后手段”，优先应通过更新统计信息、调整索引或重写 SQL 解决。
八、面试高频问题总结
-
优化器为什么有时不走索引？
统计信息不准、选择性低、回表代价高、隐式转换、函数操作、OR 条件等。 -
Cardinality 是什么？如何更新？
索引列唯一值估算，通过ANALYZE TABLE或自动更新。 -
EXPLAIN 中 type 为 ALL 一定差吗？
不一定。若表很小或需返回大部分数据，全表扫描反而更优。 -
如何强制使用某个索引？
FORCE INDEX或USE INDEX，但需评估副作用。 -
MySQL 8.0 优化器有哪些改进？
直方图、Hash Join、降序索引、EXPLAIN ANALYZE、优化器提示增强等。
九、实践建议
- 定期
ANALYZE TABLE，尤其在大批量数据变更后。 - 为高选择性列建立索引，优先考虑复合索引的最左前缀。
- 避免在索引列上使用函数、类型转换、前导通配符
LIKE '%x'。 - 用
EXPLAIN验证每个慢查询的执行计划。 - 理解业务数据分布，必要时用直方图辅助优化器。
掌握查询优化器的工作原理，是从“会写 SQL”到“写好 SQL”的关键一步。在面试中，能结合统计信息、代价模型和执行计划综合分析，往往能体现出扎实的数据库功底。
未经允许不得转载：任鹏个人博客 » MySQL 查询优化器如何选择执行计划与索引
