TIPS
本文基于MySQL 8.0编写,理论支持MySQL 5.6及更高版本。
OPTIMIZER_TRACE是MySQL 5.6引入的一项跟踪功能,它可以跟踪优化器做出的各种决策(比如访问表的方法、各种开销计算、各种转换等),并将跟踪结果记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE
表中。此功能默认关闭,开启后,可分析如下语句:
TIPS
参考 https://dev.mysql.com/doc/internals/en/system-variables-controlling-trace.html
optimizer_trace
enabled=off,one_line=off
optimizer_trace_features
greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on
,表示开启所有跟踪项。greedy_search:是否跟踪贪心搜索,有关贪心算法详见 https://blog.csdn.net/weixin_42813521/article/details/105563103
dynamic_range:是否跟踪动态范围优化
详见 https://dev.mysql.com/doc/internals/en/optimizer-features-to-trace.html
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。
参考: https://dev.mysql.com/doc/internals/en/end-markers-in-json-system-variable.html
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=<OFFSET>, optimizer_trace_limit=<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_offset 、optimizer_trace_limit更多细节,可参考 https://dev.mysql.com/doc/internals/en/tuning-trace-purging.html
开启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有四个字段:
TIPS
参考: https://dev.mysql.com/doc/refman/8.0/en/optimizer-trace-table.html
最核心的是TRACE字段的内容。我们逐段分析:
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展示了优化阶段的执行过程,是分析OPTIMIZER TRACE的重点。这段内容超级长,而且分了好多步骤,不妨按照步骤逐段分析:
该段用来做条件处理,主要对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 */
其中:
substitute_generated_columns用于替换虚拟生成列
"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类型的索引。如果使用了组合索引的多个部分(例如本例,用到了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":[
{
"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
TIPS
skip_scan_range是MySQL 8.0的新特性,感兴趣的可详见 https://blog.csdn.net/weixin_43970890/article/details/89494915
analyzing_range_alternatives:分析各个索引的使用成本
chosen_range_access_summary:在前一个步骤中分析了各类索引使用的方法及代价,得出了一定的中间结果之后,在summary阶段汇总前一阶段的中间结果确认最后的方案
负责对比各可行计划的开销,并选择相对最优的执行计划。
{
"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 */
}
其中:
基于considered_execution_plans中选择的执行计划,改造原有where条件,并针对表增加适当的附加条件,以便于单表数据的筛选。
TIPS
- 这部分条件的增加主要是为了便于ICP(索引条件下推),但ICP是否开启并不影响这部分内容的构造。
- ICP参考文档:https://www.cnblogs.com/Terry-Wu/p/9273177.html
{
"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 */
}
其中:
join_execution段落展示了执行阶段的执行过程。
"join_execution":{
"select#": 1,
"steps":[
]/* steps */
}
本文由博客一文多发平台 OpenWrite 发布!