一、概述

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

select
insert
replace
update
delete
explain
set
declare
case
if
return
call

二、开启及关闭

2.1 开启optimizer_trace

在mysql命令行中，使用如下命令开启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;

2.2 关闭optimizer_trace

在mysql命令行中，使用如下命令关闭optimizer_trace：

SET optimizer_trace="enabled=off";

三、使用optimizer_trace

3.1 相关参数

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 ，表示开启所有跟踪项。

greedy_search：是否跟踪贪心搜索

range_optimizer：是否跟踪范围优化器
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。

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语句；

三、使用

3.1 展示条目

开启optimizer_trace功能，并设置要展示的数据条目数：

set optimizer_trace="enabled=on",end_markers_in_json=on;
set optimizer_trace_offset=-30, optimizer_trace_limit=30;

3.2 分析SQL语句

发送你想要分析的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有四个字段：

MySQL性能分析(三)之optimizer_trace详解