当前位置：首页 > news >正文

MySQL 的查询优化器如何选择执行计划？

news 2026/3/27 9:29:39

MySQL 的查询优化器（Query Optimizer）是 MySQL 服务器的核心组件之一，它的核心任务是在接收到一条 SQL 语句后，从众多可能的执行方案中，选择成本最低（Cost-Based）的执行计划。

优化器选择执行计划的过程是一个复杂的决策流程，主要包含以下几个关键步骤和策略：

1. 核心原则：基于成本的优化 (Cost-Based Optimization, CBO)

MySQL 优化器主要采用CBO策略。它不会盲目地尝试所有可能的计划，而是基于统计信息估算每个候选计划的“成本”（Cost），选择成本最小的那个。

成本单位：在 MySQL 中，成本通常以“随机读取一次磁盘页”的代价为基准单位（默认约为 1.0）。
成本构成：
- I/O 成本：读取数据页、索引页的磁盘开销。
- CPU 成本：比较、排序、过滤、计算等 CPU 操作开销。
- 网络/传输成本：返回结果集的大小（在某些版本和配置中考虑）。

2. 执行计划选择的具体流程

第一步：预处理与语法分析 (Parsing & Preprocessing)

检查 SQL 语法是否正确。
进行语义分析（表是否存在、字段是否存在、权限检查）。
重写查询：优化器会先对 SQL 进行逻辑重写，例如：
- 消除冗余的JOIN或子查询。
- 将OR转换为UNION（在某些情况下）。
- 常量折叠（如WHERE 1=1被移除）。
- 谓词下推（Predicate Pushdown）：将过滤条件尽可能下推到最底层的表访问中。

第二步：生成候选执行计划 (Plan Generation)

优化器会生成多种可能的执行路径，主要包括：

表访问路径选择：
- 全表扫描 (Full Table Scan)：遍历整张表。
- 索引扫描 (Index Scan)：利用索引查找数据。
- 索引范围扫描 (Range Scan)：利用索引查找特定范围。
- 唯一索引查找 (Unique Lookup)：利用主键或唯一索引直接定位一行。
- 索引覆盖扫描 (Index Only Scan / Using index)：所需数据全在索引中，无需回表。
连接顺序 (Join Order)：
- 对于多表连接（JOIN），优化器需要决定先查哪张表，再查哪张表。
- 对于NNN张表，理论上排列组合有N!N!N!种，优化器会使用启发式算法（如动态规划）剪枝，只评估最有潜力的顺序。
连接算法选择 (Join Algorithm)：
- Nested Loop Join (NLJ)：最基础，适合小表驱动大表。
- Block Nested Loop Join (BNL)：利用 Buffer Pool 减少 I/O。
- Index Nested Loop Join (INLJ)：驱动表每行去被驱动表走索引。
- Hash Join：MySQL 8.0+ 引入，适合大表等值连接，性能通常优于 NLJ。
- Merge Join：较少用，通常用于已排序数据的连接。

第三步：成本估算 (Cost Estimation)

这是最关键的一步。优化器利用统计信息来计算每个候选计划的成本：

统计信息来源：
- INFORMATION_SCHEMA或mysql库中的统计信息表。
- 通过ANALYZE TABLE命令更新。
- 自动更新机制（当数据变化超过一定比例时）。
关键统计指标：
- 基数 (Cardinality)：列中不同值的数量（用于估算WHERE过滤后的行数）。
- 数据分布：直方图（Histogram，MySQL 8.0+ 支持），用于处理数据倾斜问题。
- 索引选择性：索引区分度越高，成本越低。
- 表行数：估算全表扫描的成本。

计算公式示例：
Cost=(读取页数×随机 I/O 成本)+(处理行数×CPU 成本) \text{Cost} = (\text{读取页数} \times \text{随机 I/O 成本}) + (\text{处理行数} \times \text{CPU 成本})Cost=(读取页数×随机I/O成本)+(处理行数×CPU成本)

第四步：选择最优计划

优化器比较所有候选计划的成本值，选择Cost 最小的计划作为最终执行计划。

3. 影响优化器决策的关键因素

A. 统计信息的准确性

如果统计信息过时（例如刚导入了大量数据但未执行ANALYZE TABLE），优化器可能会错误估算行数，导致选择错误的索引（例如本该走索引却走了全表扫描，或者反之）。

现象：EXPLAIN显示的rows与实际返回行数差距巨大。

B. 索引的存在与质量

索引选择性：区分度高的列（如身份证号）更适合做索引。
最左前缀原则：联合索引是否被完整利用。
覆盖索引：如果查询列都在索引中，优化器极大概率选择该索引。

C. 数据分布与直方图

对于数据分布不均匀的列（例如status字段 99% 是 ‘A’，1% 是 ‘B’），优化器需要知道这一点。
MySQL 8.0+引入了直方图 (Histograms)，能更精准地估算低选择性列的过滤行数，避免优化器误判。

D. 查询提示 (Hints)

用户可以通过STRAIGHT_JOIN、USE INDEX、FORCE INDEX等 Hint 强制优化器使用特定策略，但这通常是在优化器自动选择失败时的补救措施。

4. 如何查看和分析执行计划？

使用EXPLAIN或EXPLAIN ANALYZE(MySQL 8.0+) 命令：

EXPLAINSELECT*FROMusersWHEREage>20ANDcity='Beijing';

关键字段解读：

type：访问类型（从优到差：system>const>eq_ref>ref>range>index>ALL）。
key：实际使用的索引。
rows：优化器估算需要扫描的行数（越小越好）。
Extra：
- Using index：覆盖索引，性能极佳。
- Using where：需要过滤。
- Using temporary：使用了临时表（通常涉及GROUP BY或ORDER BY，性能较差）。
- Using filesort：需要额外的排序操作（性能较差）。

5. 优化器无法自动优化的常见场景（需人工干预）

隐式类型转换：
- 例如：字段是VARCHAR，查询时传入数字WHERE phone = 13800000000。
- 后果：索引失效，优化器被迫全表扫描。
函数操作：
- 例如：WHERE DATE(create_time) = '2023-01-01'。
- 后果：索引失效。应改为范围查询create_time >= '2023-01-01' AND create_time < '2023-01-02'。
OR 条件：
- 如果OR两边的条件不能同时利用索引，可能导致全表扫描。
LIKE 模糊查询：
- LIKE '%abc'（左模糊）会导致索引失效。