点击上方 IT牧场 ,选择 置顶或者星标技术干货每日送达!
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相关参数
TIPS
参考 https://dev.mysql.com/doc/internals/en/system-variables-controlling-trace.html[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:是否跟踪贪心搜索,有关贪心算法详见 https://blog.csdn.net/weixin_42813521/article/details/105563103[2]
•range_optimizer:是否跟踪范围优化器
•dynamic_range:是否跟踪动态范围优化
•repeated_subselect:是否跟踪子查询,如果设置成off,只跟踪第一条Item_subselect的执行
•详见 https://dev.mysql.com/doc/internals/en/optimizer-features-to-trace.html[3]
•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[4]
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_offset 、optimizer_trace_limit更多细节,可参考 https://dev.mysql.com/doc/internals/en/tuning-trace-purging.html[5]
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 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: 01 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的视图或者是存储过程的情况下,会出现此问题。
TIPS
参考: https://dev.mysql.com/doc/refman/8.0/en/optimizer-trace-table.html[6]
最核心的是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
•TIPS
skip_scan_range是MySQL 8.0的新特性,感兴趣的可详见 https://blog.csdn.net/weixin_43970890/article/details/89494915[7]
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是否开启并不影响这部分内容的构造。•ICP参考文档:https://www.cnblogs.com/Terry-Wu/p/9273177.html[8]
{ "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 */}
参考文档
•Tracing the Optimizer[9]•手把手教你认识OPTIMIZER_TRACE[10]•MYSQL sql执行过程的一些跟踪分析(二.mysql优化器追踪分析)[11]•使用 Trace 进行执行计划分析[12]
干货分享
最近将个人学习笔记整理成册,使用PDF分享。关注我,回复如下代码,即可获得百度盘地址,无套路领取!
•001:《Java并发与高并发解决方案》学习笔记;•002:《深入JVM内核——原理、诊断与优化》学习笔记;•003:《Java面试宝典》•004:《Docker开源书》•005:《Kubernetes开源书》•006:《DDD速成(领域驱动设计速成)》•007:全部•008:加技术群讨论
近期热文
•LinkedBlockingQueue vs ConcurrentLinkedQueue•解读Java 8 中为并发而生的 ConcurrentHashMap•Redis性能监控指标汇总•最全的DevOps工具集合,再也不怕选型了!•微服务架构下,解决数据库跨库查询的一些思路•聊聊大厂面试官必问的 MySQL 锁机制
关注我
References
[1]
: https://dev.mysql.com/doc/internals/en/system-variables-controlling-trace.html[2]
: https://blog.csdn.net/weixin_42813521/article/details/105563103[3]
: https://dev.mysql.com/doc/internals/en/optimizer-features-to-trace.html[4]
: https://dev.mysql.com/doc/internals/en/end-markers-in-json-system-variable.html[5]
: https://dev.mysql.com/doc/internals/en/tuning-trace-purging.html[6]
: https://dev.mysql.com/doc/refman/8.0/en/optimizer-trace-table.html[7]
: https://blog.csdn.net/weixin_43970890/article/details/89494915[8]
: https://www.cnblogs.com/Terry-Wu/p/9273177.html[9]
Tracing the Optimizer: https://dev.mysql.com/doc/internals/en/optimizer-tracing.html[10]
手把手教你认识OPTIMIZER_TRACE: http://blog.itpub.net/28218939/viewspace-2658978/[11]
MYSQL sql执行过程的一些跟踪分析(二.mysql优化器追踪分析): http://blog.itpub.net/29863023/viewspace-2565095/[12]
使用 Trace 进行执行计划分析: https://www.cnblogs.com/hbbbs/articles/12737077.html
喜欢就点个"在看"呗^_^