mysql optimizertrace_MySQL 调优 | OPTIMIZER_TRACE详解

TIPS

本文基于MySQL 8.0编写,理论支持MySQL 5.6及更高版本。

OPTIMIZER_TRACE是MySQL 5.6引入的一项跟踪功能,它可以跟踪优化器做出的各种决策(比如访问表的方法、各种开销计算、各种转换等),并将跟踪结果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中。此功能默认关闭,开启后,可分析如下语句:

SELECT

INSERT

REPLACE

UPDATE

DELETE

EXPLAIN

SET

DECLARE

CASE

IF

RETURN

CALL

OPTIMIZER_TRACE相关参数

optimizer_trace

optimizer_trace总开关,默认值:enabled=off,one_line=off

enabled:是否开启optimizer_trace;on表示开启,off表示关闭。

one_line:是否开启单行存储。on表示开启;off表示关闭,将会用标准的JSON格式化存储。设置成on将会有良好的格式,设置成off可节省一些空间。

optimizer_trace_features

控制optimizer_trace跟踪的内容,默认值:greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on ,表示开启所有跟踪项。

dynamic_range:是否跟踪动态范围优化

repeated_subselect:是否跟踪子查询,如果设置成off,只跟踪第一条Item_subselect的执行

optimizer_trace_limit:控制optimizer_trace展示多少条结果,默认1

optimizer_trace_max_mem_size:optimizer_trace堆栈信息允许的最大内存,默认1048576

optimizer_trace_offset:第一个要展示的optimizer trace的偏移量,默认-1。

end_markers_in_json:如果JSON结构很大,则很难将右括号和左括号配对。为了帮助读者阅读,可将其设置成on,这样会在右括号附近加上注释,默认off。

TIPS

以上参数可用SET语句操作,例如,用如下命令即可打开OPTIMIZER TRACE > SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;

>

也可用SET GLOBAL全局开启。但即使全局开启OPTIMIZER_TRACE,每个Session也只能跟踪它自己执行的语句:

> SET GLOBAL OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;

>

optimizer_trace_limit和optimizer_trace_offset这两个参数经常配合使用,例如: > SET optimizer_trace_offset=, optimizer_trace_limit=

>

这两个参数配合使用,有点类似MySQL里面的 limit语句。

默认情况下,由于optimizer_trace_offset=-1,optimizer_trace_limit=1,记录最近的一条SQL语句,展示时,每次展示1条数据;

如果改成 SET optimizer_trace_offset=-2, optimizer_trace_limit=1 ,则会记录倒数第二条SQL语句;

OPTIMIZER_TRACE使用

开启OPTIMIZER_TRACE功能,并设置要展示的数据条目数: SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;

SET optimizer_trace_offset=-30, optimizer_trace_limit=30;

发送你想要分析的SQL语句,例如: select *

from salaries

where from_date = '1986-06-26'

and to_date = '1987-06-26';

使用如下语句分析,即可获得类似如下的结果: mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30 \G;

*************************** 1. row ***************************

QUERY: select *

from salaries

where from_date = '1986-06-26'

and to_date = '1987-06-26'

TRACE: {

"steps": [

{

"join_preparation": {

"select#": 1,

"steps": [

{

"expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))"

}

] /* steps */

} /* join_preparation */

},

{

"join_optimization": {

"select#": 1,

"steps": [

{

"condition_processing": {

"condition": "WHERE",

"original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))",

"steps": [

{

"transformation": "equality_propagation",

"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"

},

{

"transformation": "constant_propagation",

"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"

},

{

"transformation": "trivial_condition_removal",

"resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"

}

] /* steps */

} /* condition_processing */

},

{

"substitute_generated_columns": {

} /* substitute_generated_columns */

},

{

"table_dependencies": [

{

"table": "`salaries`",

"row_may_be_null": false,

"map_bit": 0,

"depends_on_map_bits": [

] /* depends_on_map_bits */

}

] /* table_dependencies */

},

{

"ref_optimizer_key_uses": [

{

"table": "`salaries`",

"field": "from_date",

"equals": "DATE'1986-06-26'",

"null_rejecting": false

},

{

"table": "`salaries`",

"field": "to_date",

"equals": "DATE'1987-06-26'",

"null_rejecting": false

}

] /* ref_optimizer_key_uses */

},

{

"rows_estimation": [

{

"table": "`salaries`",

"range_analysis": {

"table_scan": {

"rows": 2838216,

"cost": 286799

} /* table_scan */,

"potential_range_indexes": [

{

"index": "PRIMARY",

"usable": false,

"cause": "not_applicable"

},

{

"index": "salaries_from_date_to_date_index",

"usable": true,

"key_parts": [

"from_date",

"to_date",

"emp_no"

] /* key_parts */

}

] /* potential_range_indexes */,

"setup_range_conditions": [

] /* setup_range_conditions */,

"group_index_range": {

"chosen": false,

"cause": "not_group_by_or_distinct"

} /* group_index_range */,

"skip_scan_range": {

"potential_skip_scan_indexes": [

{

"index": "salaries_from_date_to_date_index",

"usable": false,

"cause": "query_references_nonkey_column"

}

] /* potential_skip_scan_indexes */

} /* skip_scan_range */,

"analyzing_range_alternatives": {

"range_scan_alternatives": [

{

"index": "salaries_from_date_to_date_index",

"ranges": [

"0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"

] /* ranges */,

"index_dives_for_eq_ranges": true,

"rowid_ordered": true,

"using_mrr": false,

"index_only": false,

"rows": 86,

"cost": 50.909,

"chosen": true

}

] /* range_scan_alternatives */,

"analyzing_roworder_intersect": {

"usable": false,

"cause": "too_few_roworder_scans"

} /* analyzing_roworder_intersect */

} /* analyzing_range_alternatives */,

"chosen_range_access_summary": {

"range_access_plan": {

"type": "range_scan",

"index": "salaries_from_date_to_date_index",

"rows": 86,

"ranges": [

"0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"

] /* ranges */

} /* range_access_plan */,

"rows_for_plan": 86,

"cost_for_plan": 50.909,

"chosen": true

} /* chosen_range_access_summary */

} /* range_analysis */

}

] /* rows_estimation */

},

{

"considered_execution_plans": [

{

"plan_prefix": [

] /* plan_prefix */,

"table": "`salaries`",

"best_access_path": {

"considered_access_paths": [

{

"access_type": "ref",

"index": "salaries_from_date_to_date_index",

"rows": 86,

"cost": 50.412,

"chosen": true

},

{

"access_type": "range",

"range_details": {

"used_index": "salaries_from_date_to_date_index"

} /* range_details */,

"chosen": false,

"cause": "heuristic_index_cheaper"

}

] /* considered_access_paths */

} /* best_access_path */,

"condition_filtering_pct": 100,

"rows_for_plan": 86,

"cost_for_plan": 50.412,

"chosen": true

}

] /* considered_execution_plans */

},

{

"attaching_conditions_to_tables": {

"original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",

"attached_conditions_computation": [

] /* attached_conditions_computation */,

"attached_conditions_summary": [

{

"table": "`salaries`",

"attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"

}

] /* attached_conditions_summary */

} /* attaching_conditions_to_tables */

},

{

"finalizing_table_conditions": [

{

"table": "`salaries`",

"original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",

"final_table_condition ": null

}

] /* finalizing_table_conditions */

},

{

"refine_plan": [

{

"table": "`salaries`"

}

] /* refine_plan */

}

] /* steps */

} /* join_optimization */

},

{

"join_execution": {

"select#": 1,

"steps": [

] /* steps */

} /* join_execution */

}

] /* steps */

}

MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0

INSUFFICIENT_PRIVILEGES: 0

1 row in set (0.00 sec)

分析完成,关闭OPTIMIZER_TRACE SET optimizer_trace="enabled=off";

OPTIMIZER_TRACE结果分析

由上面的结果可知,OPTIMIZER_TRACE有四个字段:

QUERY:查询语句

TRACE:QUERY字段对应语句的跟踪信息

MISSING_BYTES_BEYOND_MAX_MEM_SIZE:跟踪信息过长时,被截断的跟踪信息的字节数。

INSUFFICIENT_PRIVILEGES:执行跟踪语句的用户是否有查看对象的权限。当不具有权限时,该列信息为1且TRACE字段为空,一般在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下,会出现此问题。

最核心的是TRACE字段的内容。我们逐段分析:

join_preparation

join_preparation段落展示了准备阶段的执行过程。

{

"join_preparation": {

"select#": 1,

"steps": [

{

-- 对比下原始语句,可以知道,这一步做了个格式化。

"expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))"

}

]

/* steps */

}

/* join_preparation */

}

join_optimization

join_optimization展示了优化阶段的执行过程,是分析OPTIMIZER TRACE的重点。这段内容超级长,而且分了好多步骤,不妨按照步骤逐段分析:

condition_processing

该段用来做条件处理,主要对WHERE条件进行优化处理。

"condition_processing": {

"condition": "WHERE",

"original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))",

"steps": [

{

"transformation": "equality_propagation",

"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"

},

{

"transformation": "constant_propagation",

"resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"

},

{

"transformation": "trivial_condition_removal",

"resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"

}

] /* steps */

} /* condition_processing */

其中:

condition:优化对象类型。WHERE条件句或者是HAVING条件句

original_condition:优化前的原始语句

steps:主要包括三步,分别是quality_propagation(等值条件句转换),constant_propagation(常量条件句转换),trivial_condition_removal(无效条件移除的转换)

transformation:转换类型句

resulting_condition:转换之后的结果输出

substitute_generated_columns

substitute_generated_columns用于替换虚拟生成列

"substitute_generated_columns": {

} /* substitute_generated_columns */

table_dependencies

分析表之间的依赖关系

{

"table_dependencies": [

{

"table": "`salaries`",

"row_may_be_null": false,

"map_bit": 0,

"depends_on_map_bits": [

] /* depends_on_map_bits */

}

] /* table_dependencies */

}

其中:

table:涉及的表名,如果有别名,也会展示出来

row_may_be_null:行是否可能为NULL,这里是指JOIN操作之后,这张表里的数据是不是可能为NULL。如果语句中使用了LEFT JOIN,则后一张表的row_may_be_null会显示为true

map_bit:表的映射编号,从0开始递增

depends_on_map_bits:依赖的映射表。主要是当使用STRAIGHT_JOIN强行控制连接顺序或者LEFT JOIN/RIGHT JOIN有顺序差别时,会在depends_on_map_bits中展示前置表的map_bit值。

ref_optimizer_key_uses

列出所有可用的ref类型的索引。如果使用了组合索引的多个部分(例如本例,用到了index(from_date, to_date) 的多列索引),则会在ref_optimizer_key_uses下列出多个元素,每个元素中会列出ref使用的索引及对应值。

{

"ref_optimizer_key_uses": [

{

"table": "`salaries`",

"field": "from_date",

"equals": "DATE'1986-06-26'",

"null_rejecting": false

},

{

"table": "`salaries`",

"field": "to_date",

"equals": "DATE'1987-06-26'",

"null_rejecting": false

}

] /* ref_optimizer_key_uses */

}

rows_estimation

顾名思义,用于估算需要扫描的记录数。

{

"rows_estimation": [

{

"table": "`salaries`",

"range_analysis": {

"table_scan": {

"rows": 2838216,

"cost": 286799

} /* table_scan */,

"potential_range_indexes": [

{

"index": "PRIMARY",

"usable": false,

"cause": "not_applicable"

},

{

"index": "salaries_from_date_to_date_index",

"usable": true,

"key_parts": [

"from_date",

"to_date",

"emp_no"

] /* key_parts */

}

] /* potential_range_indexes */,

"setup_range_conditions": [

] /* setup_range_conditions */,

"group_index_range": {

"chosen": false,

"cause": "not_group_by_or_distinct"

} /* group_index_range */,

"skip_scan_range": {

"potential_skip_scan_indexes": [

{

"index": "salaries_from_date_to_date_index",

"usable": false,

"cause": "query_references_nonkey_column"

}

] /* potential_skip_scan_indexes */

} /* skip_scan_range */,

"analyzing_range_alternatives": {

"range_scan_alternatives": [

{

"index": "salaries_from_date_to_date_index",

"ranges": [

"0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"

] /* ranges */,

"index_dives_for_eq_ranges": true,

"rowid_ordered": true,

"using_mrr": false,

"index_only": false,

"rows": 86,

"cost": 50.909,

"chosen": true

}

] /* range_scan_alternatives */,

"analyzing_roworder_intersect": {

"usable": false,

"cause": "too_few_roworder_scans"

} /* analyzing_roworder_intersect */

} /* analyzing_range_alternatives */,

"chosen_range_access_summary": {

"range_access_plan": {

"type": "range_scan",

"index": "salaries_from_date_to_date_index",

"rows": 86,

"ranges": [

"0xda840f <= from_date <= 0xda840f AND 0xda860f <= to_date <= 0xda860f"

] /* ranges */

} /* range_access_plan */,

"rows_for_plan": 86,

"cost_for_plan": 50.909,

"chosen": true

} /* chosen_range_access_summary */

} /* range_analysis */

}

] /* rows_estimation */

}

其中:

table:表名

range_analysis:

table_scan:如果全表扫描的话,需要扫描多少行(row,2838216),以及需要的代价(cost,286799)

potential_range_indexes:列出表中所有的索引并分析其是否可用。如果不可用的话,会列出不可用的原因是什么;如果可用会列出索引中可用的字段;

setup_range_conditions:如果有可下推的条件,则带条件考虑范围查询

group_index_range:当使用了GROUP BY或DISTINCT时,是否有合适的索引可用。当未使用GROUP BY或DISTINCT时,会显示chosen=false, cause=not_group_by_or_distinct;如使用了GROUP BY或DISTINCT,但是多表查询时,会显示chosen=false,cause =not_single_table。其他情况下会尝试分析可用的索引(potential_group_range_indexes)并计算对应的扫描行数及其所需代价

skip_scan_range:是否使用了skip scan

analyzing_range_alternatives:分析各个索引的使用成本

range_scan_alternatives:range扫描分析

index:索引名

ranges:range扫描的条件范围

index_dives_for_eq_ranges:是否使用了index dive,该值会被参数eq_range_index_dive_limit变量值影响。

rowid_ordered:该range扫描的结果集是否根据PK值进行排序

using_mrr:是否使用了mrr

index_only:表示是否使用了覆盖索引

rows:扫描的行数

cost:索引的使用成本

chosen:表示是否使用了该索引

analyzing_roworder_intersect:分析是否使用了索引合并(index merge),如果未使用,会在cause中展示原因;如果使用了索引合并,会在该部分展示索引合并的代价。

chosen_range_access_summary:在前一个步骤中分析了各类索引使用的方法及代价,得出了一定的中间结果之后,在summary阶段汇总前一阶段的中间结果确认最后的方案

range_access_plan:range扫描最终选择的执行计划。

type:展示执行计划的type,如果使用了索引合并,则会显示index_roworder_intersect

index:索引名

rows:扫描的行数

ranges:range扫描的条件范围

rows_for_plan:该执行计划的扫描行数

cost_for_plan:该执行计划的执行代价

chosen:是否选择该执行计划

considered_execution_plans

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

{

"considered_execution_plans": [

{

"plan_prefix": [

] /* plan_prefix */,

"table": "`salaries`",

"best_access_path": {

"considered_access_paths": [

{

"access_type": "ref",

"index": "salaries_from_date_to_date_index",

"rows": 86,

"cost": 50.412,

"chosen": true

},

{

"access_type": "range",

"range_details": {

"used_index": "salaries_from_date_to_date_index"

} /* range_details */,

"chosen": false,

"cause": "heuristic_index_cheaper"

}

] /* considered_access_paths */

} /* best_access_path */,

"condition_filtering_pct": 100,

"rows_for_plan": 86,

"cost_for_plan": 50.412,

"chosen": true

}

] /* considered_execution_plans */

}

其中:

plan_prefix:当前计划的前置执行计划。

table:涉及的表名,如果有别名,也会展示出来

best_access_path:通过对比considered_access_paths,选择一个最优的访问路径

considered_access_paths:当前考虑的访问路径

access_type:使用索引的方式,可参考explain中的type字段

index:索引

rows:行数

cost:开销

chosen:是否选用这种执行路径

condition_filtering_pct:类似于explain的filtered列,是一个估算值

rows_for_plan:执行计划最终的扫描行数,由considered_access_paths.rows X condition_filtering_pct计算获得。

cost_for_plan:执行计划的代价,由considered_access_paths.cost相加获得

chosen:是否选择了该执行计划

attaching_conditions_to_tables

基于considered_execution_plans中选择的执行计划,改造原有where条件,并针对表增加适当的附加条件,以便于单表数据的筛选。

TIPS

这部分条件的增加主要是为了便于ICP(索引条件下推),但ICP是否开启并不影响这部分内容的构造。

{

"attaching_conditions_to_tables": {

"original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",

"attached_conditions_computation": [

] /* attached_conditions_computation */,

"attached_conditions_summary": [

{

"table": "`salaries`",

"attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"

}

] /* attached_conditions_summary */

} /* attaching_conditions_to_tables */

}

其中:

original_condition:原始的条件语句

attached_conditions_computation:使用启发式算法计算已使用的索引,如果已使用的索引的访问类型是ref,则计算用range能否使用组合索引中更多的列,如果可以,则用range的方式替换ref。

attached_conditions_summary:附加之后的情况汇总

table:表名

attached:附加的条件或原语句中能直接下推给单表筛选的条件。

finalizing_table_conditions

最终的、经过优化后的表条件。

{

"finalizing_table_conditions": [

{

"table": "`salaries`",

"original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",

"final_table_condition ": null

}

] /* finalizing_table_conditions */

}

refine_plan

改善执行计划:

{

"refine_plan": [

{

"table": "`salaries`"

}

] /* refine_plan */

}

其中:

table:表名及别名

join_execution

join_execution段落展示了执行阶段的执行过程。

"join_execution": {

"select#": 1,

"steps": [

] /* steps */

}

参考文档

相关文章

本文由博客一文多发平台 OpenWrite 发布!

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 打赏
    打赏
  • 扫一扫,分享海报

参与评论
请先登录 后发表评论~
©️2021 CSDN 皮肤主题: 深蓝海洋 设计师:CSDN官方博客 返回首页

打赏作者

枫叶天秋

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值