一文了解Optimizer Trace

MySQL的执行计划跟踪，一直是比较欠缺的能力。如Oracle中的10046、10053提供的trace执行计划能力，被很多Oracle DBA所称赞。确实在某些较为复杂的语句优化时，希望优化器能将其优化判断的依据暴露出来，这样也方便DBA去排查定位问题。在MySQL5.6之后，提供了Optimizer Trace能力，可跟踪优化器的某些行为。本文尝试去解读这一过程的输出。文中部分内容摘自MySQL官网和来自沃趣公司刘云的一篇网文，在此表示感谢。

1. Optimizer Trace使用

人生基本上就是两件事，选题和解题。最好的人生是在每个关键点上，既选对题，又解好题。人生最大的痛苦在于解对了题，但选错了题，而且还不知道自己选错了题。正如人生最大的遗憾就是，不是你不行，而是你本可以。

1).参数

optimizer_trace

• enabled：启用/禁用optimizer_trace功能。
• one_line：决定了跟踪信息的存储方式，为on表示使用单行存储，否则以json树的标准展示形式存储。

optimizer_trace_features

该变量中存储了跟踪信息中可控的打印项，可以通过调整该变量，控制在INFORMATION_SCHEMA.OPTIMIZER_TRACE表中的trace列需要打印的JSON项和不需要打印的JSON项。默认打开该参数下的所有项。

• greedy_search 对于有N个表的join操作，可能产生N的阶乘的查询计划路径。如果禁用，则不跟踪贪婪搜索。
• range_optimizer range优化；如果禁用，则不会跟踪范围优化器。
• dynamic_range dynamic range optimizer，如果关闭该选项的话，只有第一次调用JOIN_TAB::SQL_SELECT才被跟踪。
• repeated_subselect 子查询，如果关闭的话，只有第一次调用Item_subselect才被跟踪。

optimizer_trace_max_mem_size

optimizer_trace内存的大小，如果跟踪信息超过这个大小，信息将会被截断。

optimizer_trace_limit & optimizer_trace_offset

这两个参数类似于select语句中的“limit m, n”。

• optimizer_trace_limit，约束跟踪信息存储的个数。
• optimizer_trace_offset，约束偏移量。

2).命令

SET optimizer_trace="enabled=on";

SELECT ...; # your query here

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

# possibly more queries...

SET optimizer_trace="enabled=off";

3).其他

默认关闭，也建议关闭，因为它会产生额外的性能开销。根据相关的评测，当打开optimizer trace时，约有不到10%的性能下降。

2. Optimizer Trace输出

人生基本上就是两件事，选题和解题。最好的人生是在每个关键点上，既选对题，又解好题。人生最大的痛苦在于解对了题，但选错了题，而且还不知道自己选错了题。正如人生最大的遗憾就是，不是你不行，而是你本可以。

在Optimizer Trace的输出中，主要分为三个部分:

• join_preparation SQL的准备阶段
• join_optimization SQL优化阶段
• join_execution SQL执行阶段

1).SQL准备阶段

第一部分是完成SQL的准备工作。在这个阶段，SQL语句会被格式化输出，通配符*会被具体字段代替，但不会进行等价改写动作。如上图中传入的SQL语句是”select * from dept”的结果。在完成了语句的补充、格式化后，准备阶段结束并进入下一阶段。

2).SQL优化阶段

第二部分，是完成SQL语句的逻辑与物理优化的过程，这其中的优化步骤比较多。在展开具体内容之前，先解释下”select #”的问题。在输出中经常会看到有”select#:N”的字样，它表示当前跟踪的结构体是属于第几个SELECT。如果语句中使用多个SELECT语句拼接（如UNION）或者有嵌套子查询中有SELECT，会产生多个序号。例如下面例子就是使用UNION的结果。

2.1).condition_processing

这一部分是完成对条件语句的优化，包括对WHERE子句或HAVING子句的优化。在后面的”condition”部分就标识出是对哪类子句的优化，如下图就是对WHERE子句的优化。

在后面是三个优化过程，每步都写明了转换类型（transformation），明确转换做的事情，以及转换之后的结果语句（resulting_condition）。这三个转换分别是:

• equality_propagation（等值条件句转换）
• constant_propagation（常量条件句转换
• trivial_condition_removal（无效条件移除的转换）

举个例子，如下图指定WHERE中的恒等条件”1=1”，优化器在经过三步优化后的变化。在第三步中，将这个无效的条件移除了。

2.2).substitute_generated_columns

字面含义是用于替换虚拟生成列。但自己创建虚拟列测试了一下，该字段无任何输出，怀疑未起作用。

2.3).table_dependencies

这部分是要找出表之间的相互依赖关系。如查询中存在多个表且之间是有依赖关系，会影响优化行为。这部分信息更多是提示作用，没有实质优化动作。

例如下图语句为”select .. from emp e,dept d where e.deptno=d.deptno”。

在具体字段含义上：

• 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。

看个具体的示例，执行语句是“select e.*,d.* from emp e left join dept d on (e.deptno=d.deptno)”，如下图所示dept表作为左连接的右侧表，是依赖于左侧的emp表(编号为0)的。

2.4).ref_optimizer_key_uses

列出了所有可用的ref类型的索引，在图中显示可过滤的字段。如下例：select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;

在最后选择中，会选择使用fk_deptno的索引(如下图)。

如果是使用了组合索引的多个部分，在ref_optimizer_key_uses下会列出多个结构体。单个结构体中会列出单表ref使用的索引及其对应值。如下图是示例中t1表有复合索引(a,b)，执行语句为select * from t1 where a=3

2.5).rows_estimation

在这一阶段会评估各种扫描方式的成本，包括全表扫描机索引扫描的代价估算。这一段以表对象作为结构体进行展开。如下例中新创建一张表，插入100万记录。表中对b,c两个字段创建了索引。执行下面SQL，select * from t1 where b=30 and a=3.

❖ table_scan

全表扫描的行数(rows)以及所需要的代价(cost)。图示可知示例中对t1表如果使用全表扫描，一共要扫描99万多行(统计信息有误差，实际是100万)，其代价为202105

❖ potential_range_indexes

该阶段会列出表中所有的索引并分析其是否可用，并且还会列出索引中可用的列字段；如果不可用，则列出不可用的原因。如下图显示可使用b字段索引或a字段索引。

❖ 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

8.0新增，是否使用了skip scan。

❖ analyzing_range_alternatives

分析各索引使用成本，包括range_scan_alternatives（range扫描分析）、analyzing_roworder_intersect（index merge分析）两个阶段，分别针对不同的情况进行执行代价的分析，从中选择出更优的执行计划。如下图中对比了b、a两个字段索引的成本如何。在仅有这两个索引的情况下，显然b索引的效率更高(实际是b字段的选择率更好)，因此在choose部分，b字段索引显示为true。

那么除此之外，优化器还对比了索引组合的情况。这种方式即查询中同时使用两个(或更多)索引，然后对索引结果进行合并(merge)，再读取表数据。针对示例中，b和c字段的索引组合同样进行了评估，并且最终的评估结果还不错，比访问单一索引要小。

在有了上述对比之后，在下面给出这部分的最终结论，选择使用的合并索引访问。

2.6).considered_execution_plans

对比实际的不同路径的成本。如果是多表关联，且有存在执行顺序(如left/right join或straight_join来强制指定顺序)，则在plan_prefix部分会有前置条件；否则，就按照所有可能性评估。如下图，还是选择了索引合并。

2.7).attaching_conditions_to_tables

这一步是在上面的基础上，尽量通过绑定条件到对应表上来获取更好的数据筛选。如果能做ICP(索引条件推入)则更佳。本例子中无法做进一步的优化

2.8).clause_processing

如果在语句中还包括DISTINCT、GROUP BY、ORDER BY等语句，尝试做进一步优化。

2.9).refine_plan

最后的优化后的结果，如果只是展示对应的表对象没有其他字段，则说明之前已经确定的执行计划已经是最优的结果。

3).SQL执行阶段

在SQL在阶段，大部分都是空白的。只有当语句中包含有排序等操作时，才会在此部分显示。如下图是执行select * from t1 order by b,c的输出。这里面包括可能在排序上的一些优化及实际执行中的开销。

3. MySQL_Tuning.py更新

人生基本上就是两件事，选题和解题。最好的人生是在每个关键点上，既选对题，又解好题。人生最大的痛苦在于解对了题，但选错了题，而且还不知道自己选错了题。正如人生最大的遗憾就是，不是你不行，而是你本可以。

Optimizer Trace是个非常强大的工具，对语句优化会带来很大帮助。特将之前做的小工具mysql_tuning.py做了增强，增加对optimizer trace支持。可通过一个开关，打开跟踪功能(如下图)。其执行完毕后，会输出跟踪文件名，方便查看。此外，这次更新还支持了python 3.x的版本。具体参见：https://github.com/bjbean/mysql-tuning