MySQL查询优化器深度解析：为什么索引失效及执行计划选择逻辑

为什么“明明有索引”，却还是走了全表扫描？

很多 MySQL 性能问题，表面看是 SQL 写得不好，本质上，其实是没理解 MySQL 查询优化器 的决策逻辑。

这篇文章，我们就从 MySQL 查询优化器 的视角，一步步拆解它的决策逻辑。

一、先抛结论：优化器到底在做什么？

一句话总结：

MySQL 优化器是在“算账”。

它做的判断不是“有没有索引可用”，而是：

用哪个执行方案，整体成本最低。

这里的“成本”不是指实际执行时间，而是 MySQL 内部一套抽象的代价模型（Cost Model）。优化器就像个精打细算的管家，为每一条 SQL 寻找它认为最“划算”的执行路径。

二、MySQL 查询执行的大致流程

在真正开始执行你的 SQL 语句之前，MySQL 内部会经历这样一条“流水线”：

SQL 语句
  ↓
解析器（Parser）
  ↓
预处理（合法性、字段存在性检查）
  ↓
优化器（Optimizer） ← 我们今天的主角
  ↓
执行器（Executor）

优化器的“输入”信息包括：

• SQL 的语法树结构
• 相关表的结构定义
• 所有可用索引的信息
• 关键的统计信息（如表行数、索引区分度等）

优化器的“输出”结果： 👉 一个最终的 执行计划（Execution Plan）。

三、优化器最核心的三件事

面对一条 SQL，优化器主要围绕三类核心决策展开工作：

1. 表访问方式怎么选？（全表扫描还是走索引？）
2. 如果有多个可用索引，该用哪个？
3. 涉及多表 JOIN 时，谁来做驱动表？

我们下面就来逐一剖析。

四、表访问方式：索引扫描 vs 全表扫描

很多人存在一个经典误区：

有索引 = 一定会走索引 ❌

实际上，优化器会在下面几种访问方式中进行成本计算和选择：

• 全表扫描（ALL）
• 索引全扫描（index）
• 索引范围扫描（range）
• 唯一索引/主键查找（const / eq_ref）
• 索引覆盖扫描（Using index）

那么，为什么有时候有索引，优化器却选择了全表扫描呢？

核心原因只有一个，请务必记住：

走索引的“估算成本”高于全表扫描的成本。

这通常在哪些场景下发生呢？

1️⃣ 查询返回的数据比例过高

SELECT * FROM orders WHERE status = 1;

假设表中 status=1 的记录占了总行数的 80%，并且 status 索引并非覆盖索引（需要回表查询其他字段）。此时的成本构成是：

索引扫描成本 + 大量随机回表的 IO 成本

这个总成本，很可能比直接进行一次顺序 IO 的全表扫描还要高。

2️⃣ 表本身数据量很小
对于只有几十行或几百行的小表：

全表扫描的代价接近于 O(1)

此时，走索引反而需要额外进行一次 B+Tree 的查找，多此一举。

3️⃣ 索引列的区分度太低
例如在性别、是否删除、状态标志位这类字段上建立单独索引。这类索引几乎无法有效筛选数据，优化器会认为它的价值很低。不过，它们通常适合作为联合索引的后缀列，用于避免排序或实现索引覆盖。

五、索引是怎么被“选中”的？

假设你的表中存在多个索引：

idx_a (a)
idx_b (b)
idx_a_b (a, b)

当执行一个涉及字段 a 和 b 的查询时，优化器会：

1. 列出所有可能用到的索引（例如 idx_a 和 idx_a_b）。
2. 为每一个可用的索引生成一套候选执行方案。
3. 基于统计信息，估算每个方案的成本。
4. 选择估算成本最低的那个方案作为最终计划。

成本主要由哪些因素决定？

可以粗略地理解为：

总成本 ≈ IO 成本 + CPU 成本

具体影响因子包括：

• 需要扫描的行数（rows）（这是最重要的因素之一）
• 是否需要回表（回表意味着大量的随机IO）
• 数据访问是否是顺序 IO
• 数据页是否已在 Buffer Pool 中
• 执行过程中是否需要使用临时表或进行文件排序

六、驱动表是怎么选出来的？

这是 JOIN 查询优化 的核心。请忘记你的 SQL 书写顺序，记住优化器的准则：

MySQL 通常会把“预估结果集最小的表”作为驱动表。

而不是根据：

• SQL 语句中 FROM 子句的书写顺序
• 业务逻辑上的“主表”概念

举个例子：

SELECT *
FROM user u
JOIN order o ON u.id = o.user_id
WHERE o.create_time >= ‘2024-01-01’;

如果根据条件 o.create_time >= ‘2024-01-01’ 过滤后，order 表只剩 100行，而 user 表有 100万行。那么，更优的执行策略是：

以 order 作为驱动表（这100行）
→ 再用驱动结果中的 user_id 去关联 user 表

这样，被驱动表（user）只需要被精确查找100次，而不是用100万行去驱动关联。

七、为什么有时候 JOIN 顺序“看起来很反直觉”？

因为优化器在执行一个关键操作：

逻辑等价变换

对于 INNER JOIN 来说，A JOIN B 在逻辑上完全等价于 B JOIN A。因此，优化器被允许：

• 交换 JOIN 表的顺序
• 合并过滤条件
• 将 WHERE 条件“下推”到更早的执行步骤中

👉 所以，你在 EXPLAIN 输出中看到的表的连接顺序，很可能与你 SQL 语句中的书写顺序完全不同，这完全是优化器基于成本计算后重新排列的结果。

八、统计信息：优化器“判断失误”的根源

优化器判断是否“走索引”、选择哪个索引，高度依赖于它掌握的“情报”——统计信息。

常见的统计信息包括：

• 表的总行数
• 索引的基数（Cardinality），即索引列上不同值的数量估算
• 数据分布直方图（MySQL 8.0 支持）

如果这些统计信息过期或者不准确：

优化器就会“算错账”。

它可能高估了某个索引的效率，或者低估了全表扫描的代价。这也解释了为什么有时候执行一次 ANALYZE TABLE xxx; 更新统计信息后，原本缓慢的 SQL 会“神奇地”变快。

九、为什么加了索引，反而变慢？

这是一个非常经典的问题，主要原因可以归结为以下几点：

1. 索引区分度太低，优化器认为其过滤价值不大。
2. 回表成本太高，尤其是当查询需要返回大量行且是非覆盖索引时。
3. 覆盖索引没设计好，导致无法利用“Using index”的优化。
4. 联合索引的列顺序错误，无法有效支持查询条件。
5. 统计信息不准确，导致成本估算错误。
6. 优化器选择了“理论最优，但实际不优”的方案，这在复杂查询中偶有发生。

十、工程视角：我们该如何“配合”优化器？

我们的目标不是与优化器对抗，而是通过良好的设计去引导它做出正确的决策。

一些核心的实践建议：

• 索引设计：优先设计高区分度的联合索引，并注意列的顺序（最左前缀原则）。
• 查询设计：尽量让查询能够使用覆盖索引（查询的字段都在索引中），避免回表。
• JOIN 优化：注意 JOIN 条件上的过滤，确保驱动表能够被有效筛选。
• 避免索引失效：避免在索引列上使用函数、进行隐式类型转换。
• 维护统计信息：定期对核心表执行 ANALYZE TABLE，特别是在数据发生重大变化后。
• 读懂执行计划：使用 EXPLAIN 时，关键要看 rows（扫描行数）、type（访问类型）、Extra（额外信息）的组合，而不是仅仅看有没有用到 index。

十一、总结

MySQL 查询优化器并非“不聪明”或“有 bug”，它的行为是高度理性和可预测的。它只是在自身的规则框架内，基于有限的信息（统计信息），坚定不移地选择它认为 “成本最低” 的方案。

因此，理解其决策逻辑，就是我们进行高性能数据库设计和 SQL 优化的基石。当你下次再疑惑“为什么不用我的索引？”时，不妨从优化器的成本视角去思考，答案往往就在其中。对于更多数据库架构和性能优化的深度讨论，欢迎访问云栈社区的数据库板块进行交流。