文章中如果有图看不到，可以点这里去 csdn 看看。从那边导过来的，文章太多，没法一篇篇修改好。

MySQL 查询优化器详解

源码分析

因篇幅问题，源码分析在另一篇中阐述，本篇主讲概念流程

MySQL查询优化器源码解析：从SQL到执行计划的底层实现

引言：一条SQL的奇幻漂流

在我们日常的开发中，一条看似简单的 SELECT * FROM users WHERE id = 1; 语句，在MySQL内部却经历了一场精密而复杂的奇幻漂流。它从一串文本，被解析成抽象语法树，被优化器进行重重改造和成本计算，最终被转换为一个高效的二进制执行计划，由执行引擎忠实地运行，将结果返回给用户。

这个过程中最核心、最复杂的部件，就是查询优化器（Query Optimizer）。它堪称数据库的“大脑”，其决策的好坏直接决定了查询的性能是毫秒级还是分钟级。本文将深入MySQL内核，特别是InnoDB存储引擎，揭开优化器的神秘面纱，结合您已熟知的索引、缓冲池、日志系统等知识，构建一个从SQL到执行计划的完整知识体系。

第一章：优化器概述——RBO与CBO的哲学

优化器的核心使命是：从多种可能的执行路径中，选择出它认为成本最低的那一条。这个选择过程经历了两个时代的演进。

1.1 基于规则的优化（RBO）

RBO是一种古老的优化方式，它依赖一系列预定义的、优先级固定的启发式规则来决定执行计划。例如：

  • 规则1：如果有唯一索引或主键的等值查询，优先使用。
  • 规则2：如果有多列索引，优先使用前缀匹配。
  • 规则3：WHERE条件中的表达式尽可能下推到存储引擎层。

RBO的实现简单而快速，但其缺陷是显而易见的：它无法感知数据的真实分布。一张拥有1亿行数据的表和一拥有10行数据的表，在某个索引缺失的情况下，RBO可能会为它们选择相同的全表扫描计划，这显然是灾难性的。

1.2 基于成本的优化（CBO）

现代数据库（包括MySQL）的优化器都是基于成本的优化器（Cost-Based Optimizer, CBO）。CBO通过计算不同执行计划的预估成本（Cost），并选择成本最低的计划。

它的核心工作流程可以概括为以下几个步骤，其间会访问您博客中深入介绍过的缓冲池（Buffer Pool）和统计信息：

flowchart TD
A[SQL输入] --> B[解析器<br>生成解析树]
B --> C[优化器]

subgraph C[优化器核心流程]
    direction TB
    C1[逻辑优化<br>例如: 条件化简, 子查询优化]
    C2[生成候选物理执行路径<br>例如: 选择索引, 关联顺序]
    C3[成本计算<br>基于统计信息和成本模型]
end

C -- 选择成本最低计划 --> D[执行计划]
D --> E[执行引擎]

E --> F[访问Buffer Pool<br>获取数据页]
F --> G[返回结果]

H[统计信息<br>（存于系统表或磁盘）] <-.-> C3

CBO的成本模型非常复杂，但其成本单位是随意的，我们只需关注其相对值。成本主要来自两个方面：

  1. I/O成本：将数据从磁盘或缓冲池加载到内存的代价。这是最大的开销。
  2. CPU成本：处理数据（比较记录、排序、连接等）的代价。

优化器需要依赖准确的统计信息来估算这些成本，这正是CBO的命门所在。

第二章：统计信息——优化器的“眼睛”

如果优化器是大脑，那么统计信息就是它的眼睛。眼睛看不清，大脑再聪明也会做出错误决策。

2.1 统计信息的收集与存储

InnoDB的统计信息主要包括：

  • 表的统计信息：TABLE_STATISTICS，如表的行数（n_rows）。
  • 索引的统计信息：INDEX_STATISTICS，如索引的基数（Cardinality），即索引中不同值的个数。
  • 直方图（Histogram）（MySQL 8.0+）：更详细地描述数据在列上的分布情况。

这些信息存储在 mysql.innodb_table_stats 和 mysql.innodb_index_stats 系统表中，也可以通过 INFORMATION_SCHEMA 查看。

关键参数：innodb_stats_persistent

  • 默认为 ON，表示统计信息持久化存储，重启不丢失。
  • 为 OFF 时，统计信息仅存于内存，重启后需重新计算。

统计信息的更新时机：

  1. 自动更新：当表发生大量数据变更（如超过10%的行）后，第一次访问该表时会触发异步更新。
  2. 手动更新：执行 ANALYZE TABLE table_name; 命令。这是解决执行计划不准问题的首要利器。

2.2 源码窥探：统计信息的计算

在源码 storage/innobase/optimizer/ 目录下，opt_stats.cc 等文件包含了统计信息收集的逻辑。InnoDB采用采样统计（Sampling） 来估算索引基数，而非全表扫描，以平衡准确性和性能。

计算索引基数的算法可以简要描述为：随机选择索引的N个数据页（采样页），统计这些页上不同值的数量，然后乘以总页数/采样页数，得到整个索引的基数估算值。

因此，基数（Cardinality）是一个估算值，并非精确值。 这也是为什么有时执行计划看起来“不准”的原因之一。

第三章：优化器工作流程深度解析

让我们跟随源码，看看优化器是如何工作的。

3.1 逻辑优化（Logical Optimizations）

SQL被解析后，首先进行的是逻辑优化。这是一种“范式转换”，它基于关系代数理论，改变查询的结构，但不决定具体使用哪个索引或连接算法。

常见的逻辑优化包括：

  • 条件化简：例如 a > 5 AND a > 10 被化简为 a > 10。
  • 常量表达式求值：WHERE id = 1+2 被转换为 WHERE id = 3。
  • 冗余条件消除：WHERE a > 10 OR a > 5 被化简。
  • 外连接消除：如果查询条件可以保证不会产生NULL补充行，外连接可以转化为内连接，减少开销。
  • 子查询优化：这是最复杂的一部分，我们后面单独详谈。

在源码中，这些操作分散在 sql/sql_optimizer.cc 的 JOIN::optimize() 函数及其调用的各个子函数中。例如，optimize_cond() 函数负责处理WHERE条件的优化。

3.2 物理优化与成本计算（Physical Optimization & Costing）

逻辑优化完成后，查询被转换为一个近似最优的逻辑结构。接下来，优化器要为其赋予物理形态：为每个操作选择具体的实现算法和访问路径。

1. 访问路径选择（Access Path Selection）
对于单表查询，优化器会枚举所有可能的索引（包括全表扫描），并计算每条路径的成本。

  • 全表扫描（ALL）：成本 = I/O成本（加载所有数据页） + CPU成本（扫描所有行）。
  • 索引扫描（index）：如果索引覆盖了所有需要的列（Covering Index），成本主要是加载索引页的I/O成本。否则，还需要加上回表（Bookmark Lookup） 的随机I/O成本，这通常是性能杀手。
  • 范围扫描（range）：ref, eq_ref, range 等类型。成本取决于需要扫描的索引范围大小和需要回表的次数。

2. 多表连接（Multi-Table Join）
对于多表连接，优化器需要决定：

  • 连接顺序（Join Order）：先连接哪两张表，再与哪张表连接？不同的顺序产生的中间结果集大小差异巨大，是成本计算的重点。
  • 连接算法（Join Algorithm）：
    • Nested-Loop Join (NLJ)：最基础的算法。外层循环驱动表，内层循环连接表。适用于所有场景，但在连接大表时性能较差。
      -- 示例：EXPLAIN 可能显示 Using join buffer (Block Nested Loop)
SELECT * FROM t1 JOIN t2 ON t1.col = t2.col;
    • Block Nested-Loop Join (BNL)：对NLJ的优化。将驱动表的多行读入Join Buffer中，然后一次性与被驱动表进行批量比较，减少内层循环的扫描次数。
    • Hash Join (MySQL 8.0+)：这是MySQL 8.0带来的重大优化！ 它首先为驱动表（小表）的连接列构建一个哈希表，然后遍历被驱动表（大表），计算连接列的哈希值并在哈希表中查找匹配项。对于等值连接和大表关联，性能远超BNL。EXPLAIN 输出中会显示 Using hash join。

优化器会估算不同连接顺序和不同连接算法组合的成本，最终选择一个总成本最低的方案。这个枚举过程可能会非常耗时，参数 optimizer_search_depth 可以限制搜索的深度，以在优化时间和计划质量之间取得平衡。

第四章：子查询优化——化繁为简的艺术

子查询是SQL的强大功能，但也是性能陷阱的重灾区。优化器会竭力将子查询转换为更高效的连接查询。

4.1 IN -> EXISTS 转换

这是最常见的转换之一。优化器会尝试将 IN (subquery) 转换为 EXISTS (correlated subquery)，或者反之，取决于估算的成本。

4.2 SEMI-JOIN （半连接）优化（MySQL 5.6+）

这是更高级、更高效的优化。半连接是一种特殊的连接，它只返回外表（outer table）中那些在内表（inner table）中有匹配行的记录，且对于外表的每行，即使内表有多个匹配行，也只返回一次。

IN 和 EXISTS 子查询都可以被转换为半连接。MySQL提供了多种半连接策略：

  • Table Pullout：如果子查询中的表可以通过主键或唯一键连接到外部查询，则可以直接“拉出”该表，将其提升为连接。
  • FirstMatch：类似于NLJ，但找到第一个匹配行后就会停止扫描内表，跳回外表继续下一行。
  • LooseScan：利用索引松散地扫描子查询表，跳过重复值。
  • Materialization：将子查询的结果物化到一个临时表中，并为该临时表建立哈希索引，然后进行常规连接。这非常适合非相关子查询。
  • DuplicateWeedout：通过一个临时表来去除重复记录，以完成半连接操作。

您可以通过 EXPLAIN 输出中的 Extra 字段看到 FirstMatch(tbl), LooseScan(m..n), Materialize 等提示，来确认优化器使用了哪种半连接策略。

第五章：临时表与排序——不得不说的性能瓶颈

在执行计划中，我们经常会看到 Using temporary; Using filesort。这通常是性能瓶颈的信号。

5.1 临时表（Temporary Table）

临时表用于存储中间结果。在以下场景中会被创建：

  • GROUP BY 和 DISTINCT 操作。
  • UNION 查询。
  • 派生表（FROM子句中的子查询）。
  • 某些类型的排序。

临时表可以在内存中（使用MEMORY引擎）或磁盘上（使用InnoDB引擎）创建。参数 tmp_table_size 和 max_heap_table_size 决定了内存临时表的大小上限。超过则转为磁盘临时表，性能会急剧下降。

5.2 排序（Filesort）

ORDER BY 操作不一定都会产生“文件排序”。如果排序字段上有索引，且索引的顺序正好满足 ORDER BY 要求，则可以直接按索引顺序读取，避免排序（EXPLAIN 显示 Using index）。

真正的 Using filesort 意味着MySQL必须在内存或磁盘上对数据进行排序。它使用一种高效的排序算法（通常是快速排序的变种）。排序消耗CPU和内存，如果数据量大到需要使用磁盘临时文件，性能会进一步下降。

优化建议：

  • 为 ORDER BY 和 GROUP BY 子句建立合适的索引。
  • 减少不必要的字段查询，避免让临时表过大。
  • 在无法避免排序时，尝试增大 sort_buffer_size 参数，让排序尽可能在内存中完成。

第六章：实战：使用OPTIMIZER_TRACE窥探优化器内心

EXPLAIN 只告诉我们优化器的最终选择，而 OPTIMIZER_TRACE 则可以让我们看到优化器决策的完整过程，包括它考虑了哪些方案，以及每个方案的成本计算。这是诊断执行计划问题的终极武器。

使用方法：

-- 1. 开启跟踪
SET optimizer_trace="enabled=on";

-- 2. 执行你的查询
SELECT * FROM your_table WHERE ...;

-- 3. 查询跟踪信息
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

-- 4. 关闭跟踪（可选，会话结束时自动关闭）
SET optimizer_trace="enabled=off";

跟踪结果是一个庞大的JSON文档，它包含了：

  • join_preparation：准备阶段。
  • join_optimization：优化过程的核心部分。
    • considered_access_paths：为每个表考虑的访问路径及其成本。
    • considered_execution_plans：考虑的连接顺序和连接算法及其总成本。
  • join_execution：执行阶段。

通过仔细阅读 considered_access_paths 和 considered_execution_plans，你可以清晰地看到为什么优化器最终选择了A计划而不是B计划，以及它的成本计算是否准确。例如，你可能会发现它因为统计信息不准，严重低估了某个索引需要回表的次数。

第七章：总结与最佳实践

MySQL的查询优化器是一个极其复杂的软件工程杰作。理解其工作原理，对于我们写出高性能SQL和进行高效调优至关重要。

回顾一下核心要点：

  1. 优化器是CBO：它基于成本做决策，成本依赖于统计信息。
  2. 统计信息是命门：定期或在大数据变更后执行 ANALYZE TABLE，是保证执行计划稳定的关键。
  3. 索引是利器：不仅要创建索引，更要理解覆盖索引、索引下推（ICP）等特性，帮助优化器降低成本估算。
  4. 连接是性能核心：理解NLJ、BNL和Hash Join的适用场景，确保多表关联高效。
  5. 子查询需谨慎：学会看 EXPLAIN，识别半连接优化，避免产生性能恶劣的“相关子查询”。
  6. OPTIMIZER_TRACE 是终极诊断工具：当 EXPLAIN 无法解释时，用它来洞察优化器的内心世界。

最后，请记住：优化器并不完美。它基于估算，有时也会犯错。作为开发者，我们的价值在于利用这些知识，通过优化表结构、编写更优的SQL语句、或使用优化器提示（如 FORCE INDEX）来引导和帮助这个“大脑”做出最正确的选择，从而让我们的应用飞得更快。

posted @ 2025-09-13 09:50  NeoLshu  阅读(15)  评论(0)    收藏  举报  来源