【华为云MySQL技术专栏】MySQL查询分析和调优利器Optimizer Trace介绍
【摘要】 一、什么是Optimizer TraceEXPLAIN命令可以帮助您查看语句的执行计划,包括是否进行了全表扫描还是索引扫描,以及表的连接顺序等。但是EXPLAIN仅展示结果,并没有真正告诉MySQL为什么这么做。尤其是在客户问题中,经常会遇到执行计划不是最优的情况,但仅根据EXPLAIN的结果,是没办法分析并说清楚为什么最终的执行计划是这样的。为了解决这个问题,MySQL提供了一项执行计划跟...
一、什么是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)