【华为云MySQL技术专栏】MySQL查询分析和调优利器Optimizer Trace介绍

GaussDB数据库 2025-01-24

261

一、什么是Optimizer Trace

EXPLAIN命令可以帮助您查看语句的执行计划，包括是否进行了全表扫描还是索引扫描，以及表的连接顺序等。但是EXPLAIN仅展示结果，并没有真正告诉MySQL为什么这么做。在处理客户问题时，经常会遇到执行计划不是最优的情况，但仅根据EXPLAIN的结果，是没办法分析并说清楚为什么最终的执行计划是这样的。

为了解决这个问题，MySQL提供了一项执行计划跟踪功能，即Optimizer Trace。它可以跟踪优化器做出的各种决策（比如访问表的方式、各种开销计算、各种转换等），并将跟踪结果记录到INFORMATION_SCHEMA.OPTIMIZER_TRACE表中。

下面我们会详细讲解Optimizer Trace展示的所有相关的信息，并且辅之一些具体使用案例。

二、如何使用Optimizer Trace

Optimizer Trace特性默认情况下是关闭的，因为开启trace会产生一些额外的开销，因此不建议一直打开。不过，Optimizer Trace属于轻量级工具，开启和关闭都非常简便，对系统的影响也微乎其微。而且支持session中开启，不影响其它session，对系统的影响降到最低。

当需要查看问题SQL语句的详细trace信息时，可以按照如下步骤操作：

1. 在session中设置optimizer_trace参数值，将trace开启即可；

set session optimizer_trace = "enabled=on";

2. 执行有问题的SQL, 如果SQL的执行时间很长的话，也可以只进行explain 操作，即：

EXPLAIN SQL;

3. 查询INFORMATION_SCHEMA.OPTIMIZER_TRACE表中的数据即可得到trace信息。

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE\G

Optimizer Trace信息以JSON格式输出，通过‘\G’命令可以格式化输出trace信息，从而方便阅读。如图1所示：

图1 optimizer trace结果简化展示

INFORMATION_SCHEMA.OPTIMIZER_TRACE表用于存储optimizer_trace的数据，其表结构如下：

mysql> show create table information_schema.optimizer_trace\G
*************************** 1. row ***************************
       Table: OPTIMIZER_TRACE
Create Table: CREATE TEMPORARY TABLE `OPTIMIZER_TRACE` (
  `QUERY` longtext NOT NULL,
  `TRACE` longtext NOT NULL,
  `MISSING_BYTES_BEYOND_MAX_MEM_SIZE` int NOT NULL DEFAULT '0',
  `INSUFFICIENT_PRIVILEGES` tinyint(1) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8

该表结构中字段详情如下：

上述4个字段中，最为重要的是第二列TRACE, 后续的文章将重点分析这个字段的内容。

三、如何分析Optimizer Trace信息

Optimizer Trace主要作用是跟踪优化器和执行器的整体过程，图2展示了优化器整体的流程图和trace采集的范围。优化过程大致可以分为三个阶段：Prepare阶段、Optimize阶段和Execute阶段。

图2 优化器流程图和trace采集范围图

Optimizer Trace 结果是一个很大的JSON文本，可以使用能够折叠JSON对象的编辑器来辅助分析这个JSON文本，如下图所示。

图3 Optimizer_Trace结果JSON可视化图

后续的小节根据如下的示例语句,对Optimizer_Trace的每个阶段进行详细的说明，示例如下：

create table t1(id int, a int, b int);
select count(distinct a) from t1 group by b;

3.1 join_preparation阶段

位于‘sql_resolver.cc’文件中的SELECT_LEXT::prepare()函数，主要是做语法解析与检测，同时也负责完成将外连接转换成内连接、合并视图或者派生表，以及处理一些子查询的转换、消除常量和冗余表达式等，干的事情比较杂。文中给出的例子比较简单，可以看出这个阶段并没有做额外的一些处理，扩展的语句和原始语句是不同的，而且对每个字段都加上了对应的表信息。

"join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select count(distinct `t1`.`a`) AS `count(distinct a)` from `t1` group by `t1`.`b`"
          }
        ]
      }

3.2 join_optimization阶段

位于‘sql_optimizer.cc’文件的 JOIN::optimize() 函数，包含了查询优化的主要逻辑，通过一系列逻辑等价的查询重写（Query Rewrite）、基于成本的连接优化（Cost-Based Join Optimization）、规则驱动的访问路径选择（Rule-Based Access Path Selection）等优化步骤，将 SELECT_LEX优化成Query Execution Plan（QEP，高效的查询执行计划）。

因此，此阶段主要展示了优化器基于cost的优化过程，包括表访问方式、表连接算法和表连接顺序，以及一些针对执行计划的特定优化，例如：表达式下推至索引(ICP)、消除sort(利用索引有序性)等。

该阶段包含的内容比较多。对于单表查询来说，主要关注“rows_estimation”过程，这个过程深入分析了对单表查询的各种执行方案的成本。对于多表连接查询来说，我们更多需要关注“considered_execution_plans”过程，这个过程里会写明各种不同的连接方式所对应的成本。优化器选择代价最低的路径(AcessPath)作为最终的执行计划，即我们使用EXPLAIN语句所展示出的那种方案。

rows_estimation

该阶段主要是分析表扫描行数的估算以及代价估算。

{
  "rows_estimation": [
    {
      "table": "`t1`",
      "const_keys_added": {
        "keys": [
          "PRIMARY"
        ],
        "cause": "group_by"
      },
      "range_analysis": {
        "table_scan": {
          "rows": 2,
          "cost": 2.55
        },
        "potential_range_indexes": [
          {
            "index": "PRIMARY",
            "usable": true,
            "key_parts": [
              "a",
              "b"
            ]
          }
        ],
        "best_covering_index_scan": {
          "index": "PRIMARY",
          "cost": 0.4556,
          "chosen": true
        },
        "group_index_range": {
          "potential_group_range_indexes": [
            {
              "index": "PRIMARY",
              "covering": true,
              "usable": false,
              "cause": "group_attribute_not_prefix_in_index"
            }
          ]
        },
        "skip_scan_range": {
          "chosen": false,
          "cause": "has_group_by"
        }
      }
    }
  ]
}

considered_execution_plans

该阶段负责对比可行计划的开销，并选择相对最优的执行计划。

{
  "considered_execution_plans": [
    {
      "plan_prefix": [
      ],
      "table": "`t1`",
      "best_access_path": {
        "considered_access_paths": [
          {
            "rows_to_scan": 2,
            "access_type": "scan",
            "resulting_rows": 2,
            "cost": 0.45,
            "chosen": true,
            "use_tmp_table": true
          }
        ]
      },
      "condition_filtering_pct": 100,
      "rows_for_plan": 2,
      "cost_for_plan": 0.45,
      "sort_cost": 2,
      "new_cost_for_plan": 2.45,
      "chosen": true
    }
  ]
}

3.3 join_execution阶段

{
  "join_execution": {
    "select#": 1,
    "steps": [
      {
        "sorting_table": "t1",
        "filesort_information": [
          {
            "direction": "asc",
            "expression": "`t1`.`b`"
          }
        ],
        "filesort_priority_queue_optimization": {
          "usable": false,
          "cause": "not applicable (no LIMIT)"
        },
        "filesort_execution": [
        ],
        "filesort_summary": {
          "memory_available": 262144,
          "key_size": 328,
          "row_size": 426,
          "max_rows_per_buffer": 604,
          "num_rows_estimate": 18446744073709551615,
          "num_rows_found": 2,
          "num_initial_chunks_spilled_to_disk": 0,
          "peak_memory_used": 32784,
          "sort_algorithm": "std::sort",
          "sort_mode": "<varlen_sort_key, packed_additional_fields>"
        }
      }
    ]
  }
}

四、代码实现

从第3节的Optimizer Trace结果可以看出，trace本质上就是一个JSON结构。在MySQL 8.0.22版本的代码中，关于trace的主要代码在sql/opt_traceXXX文件里，主要的数据结构如下：

Opt_trace_start // 启动跟踪THD的语句执行，用于语句开始阶段，析构后结束跟踪
Opt_trace_context // trace上下文内容
Opt_trace_object // 一个JSON对象，取值为<key, value>的键值对
Opt_trace_array // 一个JSON数组

具体的代码细节可以直接看MySQL源码，此处不再赘述。

下面将介绍如何在MySQL代码中增加trace，以便将语句执行过程中的信息记录到optimizer_trace信息里。

在主执行函数mysql_execute_command中添加如下的代码，启动当前语句的trace跟踪。

  Opt_trace_start ots(thd, all_tables, lex->sql_command, &lex->var_list,
                      thd->query().str, thd->query().length, nullptr,
                      thd->variables.character_set_client);


  Opt_trace_object trace_command(&thd->opt_trace);
  Opt_trace_array trace_command_steps(&thd->opt_trace, "steps");

上述代码开启了对当前SQL语句的trace跟踪。为了在不同的执行阶段记录想跟踪的记录，以添加“join_preparation”为例，在SELECT_LEX::prepare函数里添加如下的代码：

  Opt_trace_context *const trace = &thd->opt_trace;
  Opt_trace_object trace_wrapper_prepare(trace);
  Opt_trace_object trace_prepare(trace, "join_preparation");
  trace_prepare.add_select_number(select_number);
  Opt_trace_array trace_steps(trace, "steps");

在thd->opt_trace里添加一个JSON对象，名字为“join_preparation”, 包括两个<key,value>键值对，一个是“select#”, 一个是“setup”。

当SELECT_LEX::prepare处理完成后，打印格式化后的query到trace里，如下代码所示。

  {
    Opt_trace_object trace_wrapper(trace);
    opt_trace_print_expanded_query(thd, this, &trace_wrapper);
  }

最终，trace记录的信息如下：

"join_preparation": {
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select count(distinct `t1`.`a`) AS `count(distinct a)` from `t1` group by `t1`.`b`"
          }
        ]
      }

五、总结

MySQL的Optimizer Trace功能提供了丰富的查询优化和执行信息，通过trace，我们能获得更详细的优化、执行信息。当遇到索引选择或表连接顺序等不符合预期情况的时候，可以将Optimizer Trace特性打开，协助我们分析不符合预期的执行计划的原因，对这些原因进行调整，如调整参数、转换存储引擎、修改JOIN顺序等。通过这些具体的优化手段，有效提升查询效率并降低对业务的影响。