优化器

发表于 更新于 分类于 Waline： 阅读次数： 本文字数： 4.5k 阅读时长 ≈ 4 分钟

MySQL 优化器（OPTIMIZER_TRACE）：解析查询计划的内部逻辑

MySQL 的查询优化器是决定 SQL 执行效率的核心组件，它负责生成最优执行计划（如选择索引、连接方式等）。通过可插拔的 OPTIMIZER_TRACE 工具，我们可以追踪优化器的内部决策过程，了解其如何解析 SQL、估算行数、选择执行计划，从而针对性地优化 SQL。

OPTIMIZER_TRACE 基础配置

查看优化器状态

默认情况下，OPTIMIZER_TRACE 是关闭的，可通过以下命令查看状态：

show variables like 'optimizer_trace';

输出示例：

+-----------------+-----------------------+
| Variable_name   | Value                 |
+-----------------+-----------------------+
| optimizer_trace | enabled=off,one_line=off |
+-----------------+-----------------------+
  • enabled=off：未开启追踪；enabled=on 表示开启。
  • one_line=off：JSON 结果格式化显示；one_line=on 表示单行紧凑显示。

开启优化器追踪

通过 set session 命令临时开启当前会话的追踪功能（仅对当前会话有效）：

-- 开启追踪，JSON 结果单行显示，包含结束标记
set session optimizer_trace="enabled=on,one_line=on",end_markers_in_json=on;

配置追踪内存大小

优化器追踪结果会占用内存，可通过以下命令查看和调整最大内存限制：

-- 查看当前限制（默认 16KB）
show variables like 'optimizer_trace_max_mem_size';

-- 调整为 32KB（根据需要设置，过大会占用更多内存）
set session optimizer_trace_max_mem_size = 32768;

查看优化器追踪结果

执行目标 SQL 并获取 trace

执行需要分析的 SQL 后，通过 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表查看追踪结果：

-- 示例：执行需要分析的 SQL
select id, sum(cost) from jr_form 
where id in (select max(id) from jr_form group by jr_code) 
group by id;

-- 查看 trace 结果
select trace from information_schema.OPTIMIZER_TRACE;

注意：

  • 必须在同一会话中执行 SQL 和查看 trace（每个 session 仅追踪自己的语句）。
  • 若分步执行（先执行 SQL，再查 trace），可能导致 trace 为空，需同时选中两条语句执行（如 Navicat 中）。

trace 结果结构解析

trace 结果以 JSON 格式展示，包含优化器处理 SQL 的完整步骤，核心分为 steps 数组，每个步骤对应优化器的一个阶段。以下是关键阶段的解析：

阶段 1：join_preparation（查询准备）

该阶段主要对 SQL 进行语法解析、扩展和转换，生成优化器可处理的格式。

示例片段：

{
  "join_preparation": {
    "select#": 1,  // 查询编号（主查询为 1，子查询递增）
    "steps": [
      {
        "expanded_query": "/* select#1 */ select `jr_form`.`id` AS `id`, sum(`jr_form`.`cost`) AS `sum(cost)` from `jr_form` where ..."
      },
      {
        "transformation": {
          "from": "IN (SELECT)",
          "to": "EXISTS (CORRELATED SELECT)",  // 将 IN 子查询转换为 EXISTS 关联子查询
          "chosen": true
        }
      }
    ]
  }
}

作用：

  • 展开别名、替换子查询（如将 IN (子查询) 转为 EXISTS，优化执行效率）。
  • 生成 “扩展查询”（expanded_query），便于后续优化。
阶段 2：join_optimization（查询优化）

该阶段是优化器的核心，包含条件处理、表依赖分析、行数估算、执行计划选择等关键步骤。

（1）condition_processing（条件处理）

对 WHERE、HAVING 等条件进行转换和简化（如常量传播、等式传递）。

示例片段：

{
  "condition_processing": {
    "condition": "WHERE",
    "original_condition": "<in_optimizer>(`jr_form`.`id`, <exists>(...))",  // 原始条件
    "steps": [
      {
        "transformation": "constant_propagation",  // 常量传播优化
        "resulting_condition": "...",  // 转换后的条件
      }
    ]
  }
}
（2）table_dependencies（表依赖分析）

分析查询涉及的表及其依赖关系，确认是否存在关联或子查询依赖。

示例片段：

{
  "table_dependencies": [
    {
      "table": "`jr_form`",
      "row_may_be_null": false,  // 表行不可为 null
      "depends_on_map_bits": []  // 无依赖其他表
    }
  ]
}
（3）rows_estimation（行数估算）

优化器估算每个表需要扫描的行数，是选择索引的关键依据（行数越少，成本越低）。

示例片段：

{
  "rows_estimation": [
    {
      "table": "`jr_form`",
      "range_analysis": {
        "table_scan": {
          "rows": 184,  // 全表扫描估算行数
          "cost": 44.9  // 全表扫描成本
        },
        "potential_range_indexes": [  // 可能使用的索引
          {
            "index": "PRIMARY",  // 主键索引
            "usable": true
          },
          {
            "index": "jr_code",  // 二级索引
            "usable": true
          }
        ]
      }
    }
  ]
}

作用：

  • 对比全表扫描和索引扫描的成本（cost），选择成本更低的方式。
  • 若索引扫描成本低于全表扫描，优化器会优先选择索引。
（4）considered_execution_plans（执行计划选择）

优化器评估多种可能的执行计划，选择成本最低的方案。

示例片段：

{
  "considered_execution_plans": [
    {
      "table": "`jr_form`",
      "best_access_path": {
        "considered_access_paths": [
          {
            "access_type": "scan",  // 全表扫描
            "rows_to_scan": 184,
            "cost": 42.8,  // 成本
            "chosen": true  // 被选中的计划
          }
        ]
      },
      "cost_for_plan": 42.8,  // 计划总成本
      "chosen": true
    }
  ]
}

作用：

  • 展示优化器对不同执行方式（全表扫描、索引扫描等）的成本评估。
  • 最终选择成本最低的计划（chosen: true）。
（5）clause_processing（子句处理）

对 GROUP BY、ORDER BY 等子句进行优化，如判断是否可利用索引排序 / 分组。

示例片段：

{
  "clause_processing": {
    "clause": "GROUP BY",
    "original_clause": "`jr_form`.`id`",
    "resulting_clause_is_simple": true  // 简单分组，可优化
  }
}
阶段 3：join_explain（执行计划生成）

最终生成 EXPLAIN 语句可展示的执行计划，标志优化过程结束。

OPTIMIZER_TRACE 的实用场景

  1. 分析索引未被使用的原因：
    若预期使用索引但实际未使用，通过 rows_estimation 查看优化器估算的索引扫描成本是否高于全表扫描（可能因索引选择性低导致）。
  2. 优化子查询性能：
    在 join_preparation 阶段查看子查询是否被转换（如 IN 转 EXISTS），若转换后效率仍低，可手动改写子查询为 JOIN。
  3. 理解分组 / 排序的成本：
    在 considered_execution_plans 中查看 sort_cost（排序成本），若成本过高，可通过添加复合索引避免排序（Using filesort）。

注意事项

  1. 性能影响：
    开启 OPTIMIZER_TRACE 会增加 MySQL 负担（追踪过程消耗 CPU 和内存），仅在调试时开启，生产环境建议关闭。
  2. 结果大小限制：
    若 trace 结果超过 optimizer_trace_max_mem_size，会被截断，需适当调大该参数（但不宜过大）。
  3. 会话隔离：
    每个 session 仅能查看自己执行的 SQL 的 trace，无法跨会话查看