MySQL执行过程中如何选择最佳的执行路径

本篇文章介绍一个非常核心的数据库问题。MySQL 选择最佳执行路径（即“查询优化”）的过程是由其查询优化器（Query Optimizer） 完成的。

简单来说，优化器的目标是：在多种可能的执行方案中，选择一个它认为执行成本（Cost）最低的方案。

这个决策过程非常复杂，但可以概括为以下几个核心步骤和原则：

一、核心流程：优化器如何工作？

1. 解析与重写查询

• MySQL 首先解析 SQL 语句，生成一个解析树。
• 然后对解析树进行重写，包括一些语义优化，例如：
  • 视图展开：将视图引用替换为视图的定义。
  • 简化条件：移除不必要的括号、简化表达式（如 5=5 AND a>0 被简化为 a>0）。
  • 常量传递：利用等式的传递性进行简化（如 a = 5 AND b = a => a = 5 AND b = 5）。

2. 生成可能的执行计划

• 对于同一个查询，通常有多种执行方式。例如：
  • 表访问方式：应该全表扫描（ALL）还是使用索引？（const, ref, range, index）。
  • 多表连接（JOIN）的顺序：先读哪张表，后读哪张表？(A JOIN B 还是 B JOIN A)。
  • 多表连接的算法：使用 Nested-Loop Join、Hash Join (MySQL 8.0+)，还是 Batched Key Access Join？
  • 子查询优化：将子查询转换为更高效的 JOIN 操作。
  • 索引合并：是否需要对多个索引的扫描结果进行合并（index_merge）。

优化器会枚举出许多（但不是全部，因为穷举所有可能代价太高）可能的执行路径。

3. 基于成本（Cost）评估执行计划

• 这是最核心的一步。优化器是一个基于成本的优化器（Cost-Based Optimizer, CBO）。
• 它会为每个执行计划计算一个预估成本（Cost）。成本是一个相对值，主要基于以下统计信息：
  • I/O 成本：将数据从磁盘加载到内存的代价。全表扫描的成本主要来自这里。
  • CPU 成本：处理数据（比较记录、排序、计算等）的代价。
  • 内存/资源成本：使用临时表、排序等的代价。

4. 选择成本最低的计划

• 优化器会比较所有生成的执行计划的预估成本，并选择它认为成本最低的那个。
• 最终，这个被选中的计划会被交给执行引擎去执行。

二、优化器依赖的关键信息

优化器的成本计算并非凭空猜测，它严重依赖于数据库的统计信息：

1. 表统计信息

   • TABLE_ROWS：表的粗略行数。
   • DATA_LENGTH：表的数据大小。
   • 这些信息可以通过 ANALYZE TABLE table_name; 命令来更新，存储在 information_schema.TABLES 中。

2. 索引统计信息（至关重要）

   • 基数（Cardinality）：索引中唯一值的估计值。这是一个非常关键的指标。
     • 高选择性（High Cardinality）：索引列的唯一值很多（如主键、用户名），意味着索引非常有效。
     • 低选择性（Low Cardinality）：索引列的唯一值很少（如性别、状态标志），使用索引可能不如全表扫描。
   • 索引信息存储在 information_schema.STATISTICS 中。

示例：SELECT * FROM users WHERE gender = 'F';

• 如果 gender 列的基数很低（只有 ‘M’/‘F’ 两个值），优化器知道即使用了索引，也要返回约50%的数据。此时全表扫描的成本可能更低。
• 如果查询是 SELECT * FROM users WHERE user_id = 123;，user_id 是主键，基数极高，优化器会毫不犹豫地选择主键索引进行查找。

三、开发者如何协助和干预优化器？

虽然优化器很强大，但它的决策基于统计信息，而统计信息可能是过时或不准确的。开发者可以这样做：

1. 提供合适的索引（最重要的手段）

   • 为 WHERE, JOIN ... ON, ORDER BY, GROUP BY 子句中的列创建索引。
   • 使用覆盖索引（Covering Index），即索引包含了查询所需的所有字段，避免回表操作，极大提升性能。

2. 及时更新统计信息

   • 在执行了大量 INSERT, UPDATE, DELETE 操作后，运行 ANALYZE TABLE table_name; 来更新统计信息，帮助优化器做出更准确的判断。

3. 优化SQL写法

   • 避免使用 SELECT *，只选择需要的列。
   • 谨慎使用 OR，它常常会导致索引失效，可以考虑使用 UNION 改写。
   • 避免在索引列上使用函数或计算，这会导致索引失效（例如 WHERE YEAR(create_time) = 2023 不如 WHERE create_time >= '2023-01-01'）。

4. 使用优化器提示（Optimizer Hints）

   • 如果你确信优化器选错了计划，可以使用提示来强制干预。例如：
     • SELECT /*+ INDEX(table_name index_name) */ ...：强制使用某个索引。
     • SELECT /*+ NO_INDEX(table_name index_name) */ ...：强制忽略某个索引。
   • 注意： 这是一种高级且危险的操作，只有在充分理解和测试后才使用，因为数据分布变化后，强制提示可能反而会变差。

5. 使用 EXPLAIN 分析计划

   • 这是最强大的调试工具。在SQL语句前加上 EXPLAIN 或 EXPLAIN FORMAT=TREE (MySQL 8.0+)，可以查看优化器选择的执行计划。
   • 你需要学会看 EXPLAIN 的输出（尤其是 type, key, rows, Extra 字段），来判断索引是否被有效利用、是否有全表扫描、是否使用了临时表等。

总结

MySQL 通过查询优化器选择最佳执行路径，其核心是：

1. 基于成本模型：估算不同执行计划的 I/O、CPU 成本。
2. 依赖统计信息：表的行数、索引的基数等是其决策的依据。
3. 目标是成本最低：选择它认为执行最快的方案。

作为开发者，我们的职责是：

• 提供准确的信息：通过创建合适的索引和更新统计信息来“帮助”优化器。
• 验证和干预：使用 EXPLAIN 工具验证优化器的选择，并在极少数情况下使用提示进行干预。
• 编写优化器友好的SQL：避免写出让优化器“困惑”的语句。