您的位置: 首页> 数据库

MySQL 用好 Optimizer Trace，深刻理解 SQL 优化过程！

匿名上传

发布时间:2026-02-03 18:54:02

前面的章节（社区专栏《SQL调优》）我们已经写了很多篇幅关于 MySQL 执行计划的解读，今天我们来继续延伸介绍执行计划的链路跟踪功能，也就是 MySQL 的 Optimizer Trace。

在这之前，先来回顾下 EXPLAIN 的结果：

mysql:ytt>explain select * from t1 a left join y1 b on a.id = b.id where a.r1<100 order by a.r2 desc;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+
| id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref      | rows   | filtered | Extra                       |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+
|  1 | SIMPLE      | a     | NULL       | ALL    | idx_r1        | NULL    | NULL    | NULL     | 998222 |    50.00 | Using where; Using filesort |
|  1 | SIMPLE      | b     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | ytt.a.id |      1 |   100.00 | NULL                        |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------+--------+----------+-----------------------------+
2 rows in set, 1 warning (0.00 sec)

EXPLAIN 展示出来的核心数据有：

表关联顺序
优化器筛选过的索引
实际使用的索引
每张表依据统计信息的扫描行数
Extra 额外数据提示
两种执行计划（explain format=tree / explain format=json）展示出来的额外成本数据

如果想快速对于 SQL 进行优化，基于以上的结果完全可以满足。但是想深入了解 MySQL 优化器为什么选择这样的执行计划，基于以上的结果就无法满足。

举例说明：

我想知道对于表 a 来讲，为什么有索引 idx_r1，但是实际却没有使用，而走的全表扫？
两张表关联，为什么选择的顺序是表 a 驱动表 b，而不是表 b 驱动表 a？
为什么字段 r2 有索引，但是依然要走排序？

带着这些疑问，我们来介绍 MySQL 的 Optimizer Trace 功能。

1. 什么是 Optimizer Trace？

简单来讲，Optimizer Trace 是一个 SQL 执行计划的链路跟踪器，跟踪 SQL 的解析、优化、执行等过程，并且把结果记录到 MySQL 元数据表（information_schema.optimizer_trace），之后可以对这张表分析得到很多个执行计划的“为什么？”！

2. 如何使用 Optimizer Trace？

要使用 Optimizer Trace 功能，首先得打开控制开关。谨记：这个功能非常耗费资源，默认关闭的，可以通过调整以下变量开启：

mysql:ytt>show variables like 'optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name                | Value                                                                      |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace              | enabled=off,one_line=off                                                   |
| optimizer_trace_features     | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit        | 1                                                                          |
| optimizer_trace_max_mem_size | 1048576                                                                    |
| optimizer_trace_offset       | -1                                                                         |
+------------------------------+----------------------------------------------------------------------------+
5 rows in set (0.00 sec)

以上几个参数详细解释下：

optimizer_trace：enabled=on/off 启用/禁用 Optmizer Trace 功能；one_line=on/off 启用/禁用 json 格式化存储，一般不需改动。
optimizer_trace_limit/optimizer_trace_offset：这两个参数和 LIMIT 子句一样，用来最终展示 Trace 的 SQL 条数。展示的条数越多，对内存消耗越大，默认展示最近的一条记录。比如设置 optimizer_trace_limit 为 10，optimizer_trace_offset 为 -10，就可以最多展示 10 条 Trace 记录。
optimizer_trace_max_mem_size：用来存储 Trace 结果的最大内存。
optimizer_trace_features：用来启动/禁用相关 Trace 特性开关。
end_markers_in_json：启用/禁用注释功能。开启这个，Trace 结果可读性更强。
Optimizer Trace 可以跟踪的语句有：
- SELECT、TABLE、VALUES、WITH、INSERT、REPLACE、UPDATE、DELETE
- EXPLAIN
- SET（排除设置 Optimizer Trace 相关参数）
- DO
- 存储函数内部、触发器内部等的 DECLARE、CASE、IF、RETURN 语句
- CALL

元数据表字段解析

mysql:ytt>desc information_schema.optimizer_trace;
+-----------------------------------+----------------+------+-----+---------+-------+
| Field                             | Type           | Null | Key | Default | Extra |
+-----------------------------------+----------------+------+-----+---------+-------+
| QUERY                             | varchar(65535) | NO   |     |         |       |
| TRACE                             | varchar(65535) | NO   |     |         |       |
| MISSING_BYTES_BEYOND_MAX_MEM_SIZE | int            | NO   |     |         |       |
| INSUFFICIENT_PRIVILEGES           | tinyint(1)     | NO   |     |         |       |
+-----------------------------------+----------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

QUERY：TRACE 的 SQL 语句原文
TRACE：SQL 语句的 TRACE 结果，JSON 格式存储（由变量 end_markers_in_json 来控制）
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：TRACE 结果超过变量 optimizer_trace_max_mem_size 设置的值后，截断的大小（BYTE）
INSUFFICIENT_PRIVILEGES：对存储过程、存储函数等包含有 SQL SECURITY DEFINER 的用户是否有对应的权限，有权限为 0，无权限为 1，并且 TRACE 字段为空。

Optimizer Trace 开启步骤

mysql:ytt>set optimizer_trace='enabled=on';
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>set optimizer_trace_limit=10;
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>set optimizer_trace_offset=-10;
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>set end_markers_in_json=on;
Query OK, 0 rows affected (0.00 sec)

这里要注意的是，修改任何一个 Optimizer Trace 相关参数，元数据表 information_schema 表都会被清空。

mysql:ytt>select count(*) from information_schema.optimizer_trace;
+----------+
| count(*) |
+----------+
|       10 |
+----------+
1 row in set (0.00 sec)

mysql:ytt>set optimizer_trace_offset=-2;
Query OK, 0 rows affected (0.00 sec)

mysql:ytt>select count(*) from information_schema.optimizer_trace;
+----------+
| count(*) |
+----------+
|        0 |
+----------+
1 row in set (0.00 sec)

3. Optimizer Trace 的结果

我们用一个最简单的例子来看看 Optimizer Trace 的大致结构：do 语句非常简单，只用来验证是否语法正确，不出结果。

mysql:ytt>do 1+1;
Query OK, 0 rows affected (0.00 sec)

下面是 Optimizer Trace 结果：

mysql:ytt>select query,trace from information_schema.optimizer_traceG
*************************** 1. row ***************************
query: do 1+1
trace: {
  "steps": [
    {
      "join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select (1 + 1) AS `1+1`"
          }
        ]
      }
    },
    {
      "join_optimization": {
        "select#": 1,
        "steps": [
        ]
      }
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ]
      }
    }
  ]
}
1 row in set (0.00 sec)

可以看到，Optimizer Trace 结果是一个 JSON 串，key 为 steps，value 是一个数组，数组有三个 key，分别为：

join_preparation 准备阶段：这里会做一些 SQL 改写，关键字识别等等，可以看到 expanded_query 对应的值即为 SQL 语句被改写后的内部 SQL。
join_optimization 优化阶段：具体 SQL 优化，包括一些可能的逻辑优化，一些根据表统计信息预估的物理优化等等。
join_execution 最终执行阶段：最终 SQL 采用的执行计划等等。

本篇是 Optimizer Trace 的开端，由于内容太多，我特地拆分为几篇来写，欢迎继续订阅。