MySQL优化器追踪(Optimizer Trace)详解

MySQL OptimizerTrace 是用于分析查询优化器决策过程的工具，通过输出JSON格式的详细执行信息，帮助开发者理解优化器如何选择执行计划。与EXPLAIN不同，OptimizerTrace不仅展示最终计划，还记录优化器的完整思考过程，包括成本估算、索引选择、连接顺序等决策细节。主要功能包括：调试索引选择问题、分析成本估算偏差、验证子查询优化等。使用时需开启trace功能，执行查询后查

探索java

1234人浏览 · 2025-08-23 13:42:12

探索java · 2025-08-23 13:42:12 发布

1.1 Optimizer Trace简介

1.1.1 引入背景

在MySQL中，查询优化器负责解析SQL语句，并根据成本估算选择最优执行计划。然而，对于复杂查询，有时优化器的决策过程难以理解，尤其是出现：

索引选择错误
连接顺序不合理
成本估算偏差

为了解决这些问题，MySQL从5.6版本引入了 Optimizer Trace 功能，用于追踪优化器的内部决策过程，以 JSON 格式输出详细执行信息。

官方文档链接：
MySQL 8.0 Reference Manual - Optimizer Trace

1.1.2 Optimizer Trace 与 EXPLAIN 的区别

特性	EXPLAIN	Optimizer Trace
输出内容	查询执行计划	优化器内部决策详细信息（JSON）
适用场景	查看执行顺序、索引使用情况	分析优化器为何选择特定执行计划
输出格式	表格	JSON
层级信息	平面	层次化（join_preparation、join_optimization等）
调试价值	较低	高，可定位优化器决策逻辑问题

可以把 EXPLAIN 理解为“最终计划的快照”，而 Optimizer Trace 则是“优化器的思考过程记录”。

1.1.3 适用场景

复杂多表 JOIN 查询调试：理解优化器如何选择连接顺序
索引选择异常：查看优化器为何未使用期望索引
成本估算异常：分析行数估算、过滤率计算
调优验证：确认优化器是否采纳了调优建议

1.1.4 示例代码：开启 Optimizer Trace 并执行简单查询

环境：MySQL 8.0，单机测试

-- 开启 Optimizer Trace
SET optimizer_trace="enabled=on";

-- 创建测试表
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    INDEX idx_dept(department_id)
);

INSERT INTO employees VALUES
(1,'Alice',1),
(2,'Bob',2),
(3,'Charlie',1);

-- 执行简单查询
SELECT * FROM employees WHERE department_id = 1;

-- 查看 Optimizer Trace
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

-- 关闭 Optimizer Trace
SET optimizer_trace="enabled=off";

预期结果：

INFORMATION_SCHEMA.OPTIMIZER_TRACE 返回 JSON 结构
JSON 包含 steps、join_order、costs 等字段
可以看到优化器如何决定使用 idx_dept 索引

1.2 工作原理与核心价值

1.2.1 Optimizer Trace 的工作阶段

Optimizer Trace 会在查询优化阶段对优化器的决策流程进行追踪，主要包括以下几个阶段（Stage）：

join_preparation
- 功能：初始化 JOIN 查询的基本信息
- 内容：
  - 表数量、别名信息
  - 每个表的访问方式（全表扫描、索引访问）
  - 相关统计信息（行数、索引基数）
- 作用：为后续优化器选择最优 JOIN 顺序做准备
join_optimization
- 功能：选择最优连接顺序和访问方法
- 内容：
  - 每个 JOIN 的可能访问方式（access path）
  - 成本估算（cost estimation）
  - 排序选择（order of tables）
- 作用：记录优化器如何计算每种执行方案的代价并选择最优方案
final_plan
- 功能：生成最终执行计划
- 内容：
  - 确定访问索引
  - 确定连接类型（Nested Loop、Block Nested Loop、Hash Join 等）
  - 预估扫描行数和代价
- 作用：把优化器的“思考结果”形成可执行计划

注意：每个阶段都会输出 JSON 结构，包含详细的决策信息，包括成本（cost）、扫描行数（rows）、使用索引（access_method）等。

1.2.2 JSON 输出结构解析

Optimizer Trace 输出主要字段说明：

{
  "trace": [
    {
      "step": 1,
      "phase": "join_preparation",
      "table": "employees",
      "access_method": "ALL",
      "rows": 3
    },
    {
      "step": 2,
      "phase": "join_optimization",
      "join_order": ["employees"],
      "cost": 0.03,
      "chosen_access": "index(idx_dept)"
    },
    {
      "step": 3,
      "phase": "final_plan",
      "plan": [
        {
          "table": "employees",
          "access_method": "index",
          "key": "idx_dept",
          "rows_examined": 2
        }
      ]
    }
  ]
}

字段说明：

字段	说明
step	优化器执行步骤序号
phase	优化阶段（join_preparation、join_optimization、final_plan）
table	当前操作的表
access_method	表访问方式（ALL 表示全表扫描，index 表示索引访问）
rows	预估扫描行数
join_order	优化器尝试的表连接顺序
cost	优化器对执行方案的代价估算
chosen_access	优化器最终选择的访问方式
plan	最终执行计划详细信息
key	使用的索引
rows_examined	实际预计扫描行数

通过这个 JSON 输出，开发者可以 逐步追踪优化器的每个决策，并分析为什么某些索引未被使用、JOIN 顺序为何不合理。

1.2.3 Optimizer Trace 的核心价值

可视化优化器决策过程
- 对比 EXPLAIN，Trace 不仅展示最终计划，还展示选择路径和成本比较。
定位索引选择问题
- 能够分析为什么优化器没有选择期望索引，便于调整统计信息或优化 SQL。
分析成本估算偏差
- Trace 提供每一步的成本和行数估算，方便对比实际执行情况。
调优验证
- 调整索引或 SQL 后，可通过 Trace 验证优化器是否采纳建议。
结合 EXPLAIN 使用
- EXPLAIN 展示最终计划，Trace 展示优化器思路，二者结合可以完整理解查询执行逻辑。

1.2.4 示例代码：分析复杂查询的 Trace 输出

环境：MySQL 8.0

-- 开启 Trace
SET optimizer_trace='enabled=on';

-- 创建测试表
CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    INDEX idx_dept(department_id)
);

INSERT INTO departments VALUES (1,'HR'),(2,'IT');
INSERT INTO employees VALUES (1,'Alice',1),(2,'Bob',2),(3,'Charlie',1);

-- 执行多表 JOIN 查询
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id = 1;

-- 查看 Trace
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

-- 关闭 Trace
SET optimizer_trace='off';

分析步骤：

查看 join_preparation 阶段，确认优化器识别了两个表及索引信息。
在 join_optimization 阶段，分析优化器尝试的连接顺序和代价比较。
在 final_plan 阶段，确认最终选择了 idx_dept 索引访问 employees 表，并输出预估扫描行数。
对比 EXPLAIN 输出，验证 Trace 的决策与最终执行计划一致性。

.3 配置参数详解

Optimizer Trace 依赖 MySQL 系统变量来控制开启、输出内容和调试精度。主要参数包括：

1.3.1 optimizer_trace

作用：控制 Optimizer Trace 功能开关
类型：字符串（枚举 enabled=on|off 或 JSON 配置）
默认值：enabled=off

修改方法：

-- 开启 Trace
SET optimizer_trace='enabled=on';

-- 关闭 Trace
SET optimizer_trace='enabled=off';

JSON 形式：
```
SET optimizer_trace='{
    "enabled": true,
    "trace_objects": "all",
    "max_elements": 1000
}';
```
- trace_objects：指定追踪对象，可选 all、optimizer
- max_elements：JSON 输出中最大元素数，超过会被截断

1.3.2 optimizer_trace_features

作用：控制 Trace 输出的内容粒度
类型：字符串（逗号分隔）
默认值："basic"
可选值：
- basic：基本信息（表、访问方法、成本）
- io：包含 I/O 成本
- memory：包含内存使用估算
- all：包含全部详细信息
修改方法：
```
SET optimizer_trace_features='all';
```

1.3.3 max_optimizer_trace_elements

作用：限制 Trace 输出的最大元素数量
类型：整数
默认值：1000
修改方法：
```
SET max_optimizer_trace_elements=2000;
```
影响：如果 Trace 输出超过限制，JSON 会被截断，分析不完整。

1.3.4 注意事项

系统级变量 vs 会话级变量
- SET GLOBAL optimizer_trace='...'：修改全局，重启后生效
- SET SESSION optimizer_trace='...'：只对当前会话有效
与其他参数的冲突
- 开启 optimizer_trace 时，如果 max_optimizer_trace_elements 太小，复杂查询的 Trace 会被截断
- 输出 JSON 大量元素可能导致查询慢，因此只在调试环境开启
对性能的影响
- 开启 Trace 会增加优化器计算开销
- 生产环境应谨慎开启，仅用于分析问题

1.3.5 示例：调整参数并观察 Trace 输出差异

环境：MySQL 8.0，测试库

-- 创建测试表
CREATE TABLE test_employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    dept_id INT,
    INDEX idx_dept(dept_id)
);

INSERT INTO test_employees VALUES (1,'Alice',1),(2,'Bob',2),(3,'Charlie',1);

-- 开启 Trace 并设置输出全部详细信息
SET optimizer_trace='enabled=on';
SET optimizer_trace_features='all';
SET max_optimizer_trace_elements=5000;

-- 执行查询
SELECT * FROM test_employees WHERE dept_id=1;

-- 查看 Trace
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

-- 调整参数只输出 basic
SET optimizer_trace_features='basic';

-- 再次执行查询查看差异
SELECT * FROM test_employees WHERE dept_id=1;

-- 关闭 Trace
SET optimizer_trace='enabled=off';

预期效果：

optimizer_trace_features='all'：输出包含 I/O、内存估算等完整信息
optimizer_trace_features='basic'：只输出表、访问方法、行数和成本
通过对比，可以清晰看到不同参数对 Trace 输出内容的影响

1.3.6 小结

optimizer_trace：开启/关闭 Trace
optimizer_trace_features：控制输出粒度
max_optimizer_trace_elements：控制最大输出元素数
调整参数可以帮助在不同调试场景下获取所需信息，同时避免不必要的性能开销

1.4 使用方法与调试流程

Optimizer Trace 的使用主要包括四个步骤：开启 Trace → 执行查询 → 查看 Trace → 分析与验证。下面逐步讲解。

1.4.1 步骤 1：开启 Optimizer Trace

基本开启方法（会话级）：
```
SET optimizer_trace='enabled=on';
```

指定输出详细程度（可选）：

SET optimizer_trace_features='all';  -- 输出所有信息，包括 I/O 和内存估算
SET max_optimizer_trace_elements=5000;  -- 避免复杂查询输出被截断

关闭 Trace：
```
SET optimizer_trace='enabled=off';
```

注意：生产环境中建议仅在调试场景开启，以避免性能开销。

1.4.2 步骤 2：执行需要分析的查询

示例：MySQL 8.0

CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    INDEX idx_dept(department_id)
);

CREATE TABLE departments (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO departments VALUES (1,'HR'),(2,'IT');
INSERT INTO employees VALUES (1,'Alice',1),(2,'Bob',2),(3,'Charlie',1);

-- 执行复杂 JOIN 查询
SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id = 1;

查询执行过程中，优化器会根据统计信息计算多个执行方案，并记录在 Trace 中
Trace 会捕捉每个阶段（join_preparation、join_optimization、final_plan）的决策

1.4.3 步骤 3：查看 Trace 输出

查询 Trace：

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;

输出分析：

trace 字段为 JSON，包含所有优化器决策步骤
关键字段：
- phase：优化阶段
- table：操作的表
- access_method：表访问方式
- rows：预估扫描行数
- cost：优化器成本估算
- chosen_access：最终选择访问方式
- plan：最终执行计划详情

结合 JSON 格式化工具，可清晰看到优化器每一步决策过程。

1.4.4 步骤 4：分析与验证

与 EXPLAIN 对比

EXPLAIN SELECT e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id = 1\G;

EXPLAIN 提供最终执行计划，包含访问索引、连接类型、扫描行数
Optimizer Trace 提供优化器选择该计划的完整逻辑

分析决策过程

查看 join_optimization 阶段：
- 哪些访问方案被尝试
- 各方案成本估算
- 为什么最终选择当前方案
查看 final_plan 阶段：
- 验证访问索引是否符合预期
- 验证扫描行数与成本是否合理

调试索引选择错误示例

假设优化器未使用期望索引：

-- 查询未使用索引
SELECT * FROM employees WHERE department_id=1;

-- Trace 显示 access_method=ALL（全表扫描）
-- 分析可能原因：
-- 1. 索引统计信息不准确
-- 2. 表行数过少，优化器认为全表扫描成本低
-- 调整统计信息
ANALYZE TABLE employees;

-- 再次执行查询，观察 Trace 输出是否选择 idx_dept 索引

通过对比 Trace 输出，可以清楚看到优化器为何选择或忽略某个索引，从而指导索引优化或 SQL 调整。

1.4.5 小结

开启 Trace → 执行查询 → 查看输出 → 分析决策 是标准流程
Trace 能够详细记录优化器每个阶段的决策，包括访问方式、成本估算、连接顺序
与 EXPLAIN 配合使用，可以同时掌握最终计划与优化器思路
适用于调试复杂查询、索引选择错误、成本估算偏差等问题

1.5 典型场景分析

场景1：索引选择错误

1.5.1.1 问题描述

查询条件明确，但优化器未使用预期索引
导致全表扫描或性能下降

示例查询（MySQL 8.0）：

SELECT * FROM employees WHERE department_id = 1;

1.5.1.2 Trace 输出分析

{
  "trace": [
    {
      "phase": "join_preparation",
      "table": "employees",
      "access_method": "ALL",
      "rows": 3
    },
    {
      "phase": "final_plan",
      "plan": [
        {
          "table": "employees",
          "access_method": "ALL",
          "rows_examined": 3
        }
      ]
    }
  ]
}

分析：

access_method=ALL 表示全表扫描
优化器认为全表扫描成本低于索引访问，可能由于：
- 表数据量小
- 索引统计信息过时

1.5.1.3 调优建议

-- 更新表统计信息
ANALYZE TABLE employees;

-- 再次执行查询，观察 Trace 输出
SELECT * FROM employees WHERE department_id=1;

-- 预期 Trace 输出：
-- access_method=index
-- 使用 idx_dept 索引

场景2：成本估算偏差

1.5.2.1 问题描述

优化器选择的执行计划成本估算与实际执行成本差距大
可能导致非最优查询计划

示例：

SELECT * FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id IN (1,2);

1.5.2.2 Trace 输出分析

{
  "phase": "join_optimization",
  "join_order": ["departments","employees"],
  "cost": 0.05,
  "chosen_access": "index(idx_dept)"
}

分析：

优化器选择先扫描 departments 再扫描 employees
如果统计信息不准确，实际扫描行数可能比估算大
Trace 提供每个阶段成本和行数估算，有助于定位问题

1.5.2.3 调优建议

-- 更新表统计信息
ANALYZE TABLE employees;
ANALYZE TABLE departments;

-- 调整查询逻辑或提示优化器
SELECT /*+ JOIN_ORDER(d,e) */ e.name, d.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.id IN (1,2);

场景3：子查询优化问题

1.5.3.1 问题描述

子查询导致性能不佳，优化器未转换为 JOIN
Trace 可以帮助分析子查询执行计划

示例：

SELECT name
FROM employees
WHERE department_id IN (
    SELECT id FROM departments WHERE name='HR'
);

1.5.3.2 Trace 输出分析

{
  "phase": "join_optimization",
  "subquery": {
    "type": "IN",
    "rows": 1,
    "access_method": "ALL"
  },
  "final_plan": {
    "employees": {"access_method": "index", "rows_examined": 2}
  }
}

分析：

子查询使用全表扫描，可能导致性能下降
Trace 显示优化器未将子查询转换为半连接（semi-join）

1.5.3.3 调优建议

-- 转换为 JOIN
SELECT e.name
FROM employees e
JOIN departments d ON e.department_id = d.id
WHERE d.name='HR';

Trace 输出应显示：
- access_method=index
- 子查询被消除，性能提升明显

场景4：临时表/文件排序问题

1.5.4.1 问题描述

查询涉及 ORDER BY、GROUP BY 或 DISTINCT
优化器使用临时表或文件排序，影响性能

示例：

SELECT department_id, COUNT(*) 
FROM employees
GROUP BY department_id
ORDER BY COUNT(*) DESC;

1.5.4.2 Trace 输出分析

{
  "phase": "final_plan",
  "plan": [
    {
      "table": "employees",
      "access_method": "index",
      "using_temporary": true,
      "using_filesort": true
    }
  ]
}

分析：

using_temporary=true，using_filesort=true 表示临时表 + 文件排序
对大表可能导致性能瓶颈

1.5.4.3 调优建议

-- 使用覆盖索引或调整查询
CREATE INDEX idx_dept_count ON employees(department_id);

-- 执行查询并观察 Trace 输出
-- 预期不再使用文件排序，性能提升

1.5.5 小结

Optimizer Trace 能清晰展示优化器每一步决策
通过 Trace 可定位：
- 索引未使用
- 成本估算不准
- 子查询优化未生效
- 临时表或文件排序影响性能
调优流程：
1. 查看 Trace
2. 分析决策逻辑
3. 调整索引或 SQL
4. 验证 Trace 输出变化

2048 AI社区

有“AI”的1024 = 2048，欢迎大家加入2048 AI社区

更多推荐

数据预处理（二）：缺失值处理，KingbaseES函数+Java代码协同方案

摘要：缺失值处理是AI数据治理的关键环节，需根据业务场景和缺失机制选择策略（删除/填充/标记）。本文提出KES+Java协同方案： KES侧：用SQL函数高效批量填充（如按城市中位数），创建物化视图优化性能； Java侧：实现动态策略（如缺失指示器、模型预测兜底），增强灵活性；分层处理：KES处理规则化填充，Java处理复杂逻辑，通过监控缺失率预警上游故障。方案结合数据库批量处理能力与Jav

2048 AI社区

【震惊】大模型“翻车“现场太尴尬？RAG技术让AI“逆袭“成大神！小白程序员必学！

大模型常出现"幻觉"现象，自信地给出错误答案。RAG(检索增强生成)技术通过外部知识库和检索机制，先检索相关文档片段再生成答案。该技术无需重新训练模型，成本低，知识更新快，回答有据可查，且能保护隐私数据。RAG技术正在客服、专业咨询、教育等领域展现变革潜力，成为解决大模型知识滞后和幻觉问题的实用方案。