• 跟踪优化器做出的各种决策
• 了解优化器的执行细节
• 理解SQL的执行过程，进而优化SQL

OPTIMIZER TRACE 是什么？

TIPS

本文基于MySQL8.0编写，理论支持MySQL5.6往上的版本。

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

• SELECT
• INSERT
• REPLACE
• UPDATE
• EXPLAIN
• SET
• DECLARE
• CASE
• IF
• RETURN
• CALL

OPTIMIZER TRACE 参数

TIPS

参考：https://dev.mysql.com/doc/internals/en/system-variables-controlling-trace.html

• 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
    • range_optimizer：是否跟踪范围优化器
    • dynamic_range：是否跟踪动态范围优化
    • repeated_subselect：是否跟踪子查询，如果设置成off，只跟踪第一条item_subselect的执行
    • 详见（英文）： https://dev.mysql.com/doc/internals/en/optimizer-features-to-trace.html
• optimizer_trace_max_mem_size：控制 optimizer_trace 展示多少条结果，默认1
• optimizer_trace_limit：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


8.11 end_markers_in_json系统变量

如果JSON结构很大，则很难将其右括号和左括号配对； 为了帮助读者（但这不兼容JSON），设置@@ end_markers_in_json = on会在右括号附近重复该结构的键（如果有）。 它也以相同的方式影响“ EXPLAIN FORMAT = JSON”。 请注意，在MySQL 5.6.6之前，此变量不存在，而只是@@ optimizer_trace变量中的一个开关（已使用“ setoptimizer_trace =“ end_marker = on”;“设置）。

TIPS

- 以上参数可用SET语句操作，例如，用如下命令即可打开 OPTIMIZER TRACE



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



- optimizer_trace_limit 和 optimizer_trace_offset 这两个参数经常配合使用，例如：



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

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

如果改成 optimizer_trace_offset-2, optimizer_trace_limit=1, 则会记录最近的两条SQL语句；

有关 optimizer_trace_limit 和 optimizer_trace_offset 更多细节，可参考：

https://dev.mysql.com/doc/internals/en/tuning-trace-purging.html

OPTIMIZER TRACE 使用

• 开启OPTIMIZER_TRACE功能，并且设置要展示的数据条目数
• 发送你想要分析的查询语句
• 使用如下语句分析，即可获得类似如下的结果

• 分析完成，关闭 OPTIMIZER_TRACE

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

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

join_preparation

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

join_optimization

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

condition_processing

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

其中：

• condition：优化对象类型。WHERE条件句或者是HAVING条件句?original_condition：优化前的原始语句?steps：主要包括三步，分别是quality_propagation（等值条件句转换），constant_propagation（常量条件句转换），trivial_condition_removal（无效条件移除的转换）
• transformation：转换类型句?resulting_condition：转换之后的结果输出

substitute_generated_columns

substitute_generated_columns用于替换虚拟生成列

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使用的索引及对应值。

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：分析各个索引的使用成本
• 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

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

其中：

• 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

其中：

• original_condition：原始的条件语句
• attached_conditions_computation：使用启发式算法计算已使用的索引，如果已使用的索引的访问类型是ref，则计算用range能否使用组合索引中更多的列，如果可以，则用range的方式替换ref。
• attached_conditions_summary：附加之后的情况汇总
• table：表名
• attached：附加的条件或原语句中能直接下推给单表筛选的条件。

finalizing_table_conditions

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

refine_plan

改善执行计划：

其中：

• table：表名及别名

join_execution

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