我要当国王无广告版
24.16 MB · 2025-12-19
optimizer_trace是MySQL 5.6引入的一项跟踪功能，它可以跟踪优化器做出的各种决策（比如访问表的方法、各种开销计算、各种转换等），并将跟踪结果记录到information_schema.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;在mysql命令行中，使用如下命令关闭optimizer_trace：
SET optimizer_trace="enabled=off";optimizer_trace
optimizer_trace_features
greedy_search：是否跟踪贪心搜索
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语句；
开启optimizer_trace功能，并设置要展示的数据条目数：
set optimizer_trace="enabled=on",end_markers_in_json=on;set optimizer_trace_offset=-30, optimizer_trace_limit=30;发送你想要分析的SQL语句，例如：
select *from salarieswhere 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 salarieswhere 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有四个字段：
trace字段为空，一般在调用带有sql security definer的视图或者是存储过程的情况下，会出现此问题。最核心的是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 */}其中：
负责对比各可行计划的开销，并选择相对最优的执行计划。
{ "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 */}在整个optimizer_trace中我们重点其实就是在跟踪记录trace的json树，我们通过这棵树中的内容可以具体去分析优化器究竟做了什么事情，进行了哪些选择，是基于什么原因做的选择，选择的结果及依据。这一系列都可以辅助验证我们的一些观点及优化，更好的帮助我们对我们的数据库的实例进行调整。
