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

举报
GaussDB 数据库 发表于 2025/01/24 09:11:52 2025/01/24
【摘要】 一、什么是Optimizer TraceEXPLAIN命令可以帮助您查看语句的执行计划,包括是否进行了全表扫描还是索引扫描,以及表的连接顺序等。但是EXPLAIN仅展示结果,并没有真正告诉MySQL为什么这么做。尤其是在客户问题中,经常会遇到执行计划不是最优的情况,但仅根据EXPLAIN的结果,是没办法分析并说清楚为什么最终的执行计划是这样的。为了解决这个问题,MySQL提供了一项执行计划跟...

MySQL.jpg

一、什么是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

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

{
  "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"
        }
      }
    }
  ]
}

3.3 join_execution阶段


{
  "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节的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顺序等。通过这些具体的优化手段,有效提升查询效率并降低对业务的影响。

关注“GaussDB数据库”公众号,了解第一手行业资讯

【声明】本内容来自华为云开发者社区博主,不代表华为云及华为云开发者社区的观点和立场。转载时必须标注文章的来源(华为云社区)、文章链接、文章作者等基本信息,否则作者和本社区有权追究责任。如果您发现本社区中有涉嫌抄袭的内容,欢迎发送邮件进行举报,并提供相关证据,一经查实,本社区将立刻删除涉嫌侵权内容,举报邮箱: cloudbbs@huaweicloud.com
  • 点赞
  • 收藏
  • 关注作者

评论(0

0/1000
抱歉,系统识别当前为高风险访问,暂不支持该操作

全部回复

上滑加载中

设置昵称

在此一键设置昵称,即可参与社区互动!

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。

*长度不超过10个汉字或20个英文字符,设置后3个月内不可修改。