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

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

ops 2025/8/24 12:33:19

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 的区别

特性EXPLAINOptimizer 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）：

  1. join_preparation

    • 功能：初始化 JOIN 查询的基本信息

    • 内容：

      • 表数量、别名信息

      • 每个表的访问方式（全表扫描、索引访问）

      • 相关统计信息（行数、索引基数）

    • 作用：为后续优化器选择最优 JOIN 顺序做准备

  2. join_optimization

    • 功能：选择最优连接顺序和访问方法

    • 内容：

      • 每个 JOIN 的可能访问方式（access path）

      • 成本估算（cost estimation）

      • 排序选择（order of tables）

    • 作用：记录优化器如何计算每种执行方案的代价并选择最优方案

  3. 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 的核心价值

  1. 可视化优化器决策过程

    • 对比 EXPLAIN，Trace 不仅展示最终计划，还展示选择路径和成本比较。

  2. 定位索引选择问题

    • 能够分析为什么优化器没有选择期望索引，便于调整统计信息或优化 SQL。

  3. 分析成本估算偏差

    • Trace 提供每一步的成本和行数估算，方便对比实际执行情况。

  4. 调优验证

    • 调整索引或 SQL 后，可通过 Trace 验证优化器是否采纳建议。

  5. 结合 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';

分析步骤：

  1. 查看 join_preparation 阶段，确认优化器识别了两个表及索引信息。

  2. 在 join_optimization 阶段，分析优化器尝试的连接顺序和代价比较。

  3. 在 final_plan 阶段，确认最终选择了 idx_dept 索引访问 employees 表，并输出预估扫描行数。

  4. 对比 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 注意事项

  1. 系统级变量 vs 会话级变量

    • SET GLOBAL optimizer_trace='...'：修改全局，重启后生效

    • SET SESSION optimizer_trace='...'：只对当前会话有效

  2. 与其他参数的冲突

    • 开启 optimizer_trace 时，如果 max_optimizer_trace_elements 太小，复杂查询的 Trace 会被截断

    • 输出 JSON 大量元素可能导致查询慢，因此只在调试环境开启

  3. 对性能的影响

    • 开启 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

  1. 基本开启方法（会话级）：

    SET optimizer_trace='enabled=on';

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

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

  3. 关闭 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 输出

  1. 查询 Trace：

    SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G;

  2. 输出分析：

  • trace 字段为 JSON，包含所有优化器决策步骤

  • 关键字段：

    • phase：优化阶段

    • table：操作的表

    • access_method：表访问方式

    • rows：预估扫描行数

    • cost：优化器成本估算

    • chosen_access：最终选择访问方式

    • plan：最终执行计划详情

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

1.4.4 步骤 4：分析与验证

  1. 与 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 提供优化器选择该计划的完整逻辑

  1. 分析决策过程

  • 查看 join_optimization 阶段：

    • 哪些访问方案被尝试

    • 各方案成本估算

    • 为什么最终选择当前方案

  • 查看 final_plan 阶段：

    • 验证访问索引是否符合预期

    • 验证扫描行数与成本是否合理

  1. 调试索引选择错误示例

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

-- 查询未使用索引
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 输出变化

http://www.xdnf.cn/news/18544.html

相关文章：

  • APIs基础one
  • docker的数据管理
  • Java试题-选择题（16）
  • 论文阅读：arxiv 2025 Can You Trick the Grader? Adversarial Persuasion of LLM Judges
  • selenium采集数据怎么应对反爬机制？
  • Python爬虫实战：研究WSL技术，构建跨平台数据采集和分析系统
  • 从人工巡检到智能监测：工业设备管理的颠覆性变革
  • Selenium
  • 系统思考：突破复杂困境
  • 随机森林2——集成学习的发展
  • EPWpy 安装教程
  • 如何解决 pyqt5 程序“长时间运行失效” 问题？
  • 爬小红书图片软件：根据搜索关键词，采集笔记图片、正文、评论等
  • 在云服务器中使用tmux实现程序24小时运行
  • daily notes[4]
  • Sqlserver存储过程
  • Python入门：从零开始的编程之旅
  • git实战问题（6）git push 时发现分支已被更新，push失败了怎么办
  • GaussDB 数据库架构师修炼(十八) SQL引擎-解析器
  • 学习游戏制作记录（合并更多的技能与技能树）8.23
  • [e3nn] 模型部署 | TorchScript JIT | `@compile_mode`装饰器 | Cython
  • 老年常见疾病及健康管理建议
  • 精斗云智能开单解决方案：高效移动办公新体验
  • Qt/C++开发监控GB28181系统/录像文件回放/自动播放下一个录像文件/倍速回放/录像文件下载
  • openharmony之一多开发：产品形态配置讲解
  • 使用自制的NTC测量模块测试Plecs的热仿真效果
  • 分布式蜜罐系统的部署安装
  • 微服务统一入口——Gateway
  • Redis 从入门到精通：原理、实战与性能优化全解析
  • Flutter BLoC 全面入门与实战（含代码示例）