Optimizer Trace 的基本使用
首先，我们来看一下具体如何使用 Optimizer Trace。默认情况下，该功能是关闭的，大家可以使用如下方式打开该功能，然后执行自己需要分析的 SQL 语句，然后再从 INFORMATION_SCHEMA 的 OPTIMIZER_TRACE中查找到该 SQL 语句执行优化的相关信息。

# 1. 打开optimizer trace功能 (默认情况下它是关闭的):
SET optimizer_trace="enabled=on";
SELECT ...; # 这里输入你自己的查询语句
SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;
# 当你停止查看语句的优化过程时，把optimizer trace功能关闭
SET optimizer_trace="enabled=off";

这个 OPTIMIZER_TRACE 表有4个列，如下所示：

QUERY：表示我们的查询语句。
TRACE：表示优化过程的JSON格式文本。
MISSING_BYTES_BEYOND_MAX_MEM_SIZE：由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。
INSUFFICIENT_PRIVILEGES：表示是否没有权限查看优化过程，默认值是0，只有某些特殊情况下才会是1，我们暂时不关心这个字段的值。

Optimizer Trace 的详细解读

join_optimization：优化阶段

　　condition_processing：条件句处理。该步骤对WHERE条件句进行优化处理

　　　　condition：优化对象类型。WHERE条件句或者是HAVING条件句（还记得么，准备阶段的ON条件句已经被转换为WHERE条件句了，所以这里不存在ON的条件句）

　　　　original_condition：优化前的原始语句

　　　　steps:

　　　　　　transformation:转换类型。equality_propagation（等值条件句转换），constant_propagation（常量条件句转换），trivial_condition_removal（无效条件移除的转换）

　　　　　　resulting_condition:转换之后的结果语句

　　table_dependencies：表依赖关系，会列出该语句中涉及到所有的表

　　　　table：涉及的表名

　　　　row_may_be_null：列是否允许为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类型的索引。如果是使用了组合索引的多个部分，在ref_optimizer_key_uses下会列出多个结构体。单个结构体中会列出单表ref使用的索引及其对应值

rows_estimation：估算需要扫描的记录数，这一段以表对象做为结构体进行展开。如示例中对t2表和t1表分别进行了分析，其中t1表由于没有可用的索引，故其在此阶段的结构体非常简单，仅仅包括了一步table_scan（全表扫描）。

　　　　　　　　　　t2表则进入range analysis阶段，经历了看上去繁多的步骤，以下依据t2表的range_analysis，解剖这些看似复杂的步骤

　　table： 表

　　range_analysis： 

　　　　table_scan：全表扫描的行数(rows)以及所需要的代价(cost)

　　　　potential_range_indexes：该阶段会列出表中所有的索引并分析其是否可用，并且还会列出索引中可用的列字段

　　　　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）并且计算对应的扫描行数及其所需代价

　　　　analyzing_range_alternatives ：分析可选方案的代价。包括range_scan_alternatives（range扫描分析）、analyzing_roworder_intersect（index merge分析）两个阶段，分别针对不同的情况进行执行代价的分析，从中选择出更优的执行计划。

　　　　　　range_san_alternatives：range扫描分析针对所有可用于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_insersect：由于示例没有使用index merge，所以在这一段仅仅给出了不使用index merge的原因。如果是语句可以使用index_merge的情况，在该阶段会分析使用index_merge过程中消耗的代价（index_scan_cost、disk_sweep_cost等），并汇总merge的代价确认是否选择使用index_merge以及对应使用的索引

　　　　chosen_range_access_summary：在前一个步骤中分析了各类索引使用的方法及代价，得出了一定的中间结果之后，在summary阶段汇总前一阶段的中间结果确认最后的方案

　　　　　　range_access_plan：range扫描最终选择的执行计划。在该结构体中会给出执行计划的type，使用的索引以及扫描行数。如果range_access_plan.type是“index_roworder_intersect”（即index merge）的话，在该结构体下还会列intersect_of结构体给出index merge的具体信息。

　　　　　　 rows_for_plan：该执行计划的扫描行数

　　　　　　cost_for_plan：该执行计划的执行代价

　　　　　　chosen：是否选择该执行计划

 

　　considered_execution_plans：

 

range_analysis：

　　table_scan：全表扫描的行数和成本

　　potential_range_indices：潜在可以使用的索引

　　setup_range_conditions：

　　group_index_range：

　　analyzing_range_alternatives：使用索引方案的分析

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:

clause_processing：这部分结构体主要是对DISTINCT、GROUP BY、ORDER BY等语句进行优化

　　clause：优化语句关键字（DISTINCT、GROUP BY、ORDER BY）

　　original_clause：优化对象的原始语句

　　items：original_clause中包含的对象

　　　　item：对象名

　　　　eq_ref_to_preceding_items：与前置表关联的是否是唯一索引。在这个示例中，并没有这样的情况所以没有列出。如果语句稍微改写一下，将原始查询语句改写为“t1 STRAIGHT_JOIN t2”，让t1去驱动t2表，就发现在分析items[1].item = t2.pk时出现了该字段。这是由于t1与t2通过t2的主键pk列进行关联，这就意味着，t1中的一行数据最多只能在t2中关联出一列，所以在后续优化的结果语句中order by 的t2.pk列被优化掉了，因为这一列已经确认唯一不需要再进行排序。

　　resulting_clause_is_simple：优化后的结果语句是否是简单语句

　　resulting_clause：优化后的结果语句

refine_plan：改善执行计划

　　table：

　　pushed_index_condition：

　　table_condition_attached：

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

 

参考文档：

(3条消息) MySQL 调优 | OPTIMIZER_TRACE 详解_ITMuch的专栏-CSDN博客

(2条消息) 手把手教你认识OPTIMIZER_TRACE_woqutechteam的博客-CSDN博客_optimizer trace