MySQL之Optimizer_trace

　　一、前言

　　目前，使用explain只能查询MySQL的QEP，当MySQL选择了一个错误的执行计划后，我们都很想知道是什么导致了MySQL做了错误的决定，所以在这个时时optimizer_trace就华丽登场啦。

　　二、典型使用：

　　1.# timizer_trace默认是关闭的，开启

　　 SET optimizer_trace="enabled=on";

　　2.#让json的每个结束符号注释上相应的标记

　 　SET END_MARKERS_IN_JSON=on;

　　3.#设置trace的最大长度，防止trace被截断

　　SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

　　三、Optimizer Trace的分析

　　optimizer trace共分为join_preparation，join_optimization，join_execution三个阶段，其中join_optimization是核心。

　　下面我们来详细介绍join_optimization的过程：

1. 　　condition_processing阶段，进行where条件处理，分别是相等处理，常量处理，删除冗余条件
2. 　　ref_optimizer_key_uses阶段，查找可进行ref type访问的索引(索引的等值访问)。
3. 　　records_estimation阶段，进行访问开销预估。这个阶段是最复杂的。先处理访问类型(explain select_type字段的值)，候选项分别为全表扫描和所有的索引，开销最小的那个胜出。如果你的语句有Group By,那么在  group_index_range子阶段确定是否有适用于range 访问的索引。
4. 　　considered_execution_plans节显示了选定的执行计划
5. 　　attaching_conditions_to_tables分析where条件是否可以执行pushdown，应该是再扫描该表时过滤掉。
6. 　　clause_processing阶段分别处理group by, order by从句。

　　           SELECT ...; # 你的SQL语句

　　           SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

　　# 完成分析后关闭

　　 SET optimizer_trace="enabled=off";

　　四、结束语

　　以上为一个基本的trace结构，当你有子查询，执行过程中需要产生临时文件时，trace就要复杂得多，在MySQL5.6中，eq_range_index_dive_limit的默认值是10，当IN()后列举的值的个数超过eq_range_index_dive_limit时，采用索引统计信息，并不是分析索引来评估开销。

　　五、参考文档

  以下是我的参考文档：

　　http://dev.mysql.com/doc/internals/en/optimizer-tracing.html

　　Posted in: MySQL practise