前言

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

一、典型使用：

# timizer_trace默认是关闭的，开启
SET optimizer_trace="enabled=on";
#让json的每个结束符号注释上相应的标记
SET END_MARKERS_IN_JSON=on;
#设置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时，采用索引统计信息而不是分析索引来评估开销。（可以google eq_range_index_dive_limitz找到对应的分析博客）

参考文档

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

Posted in: MySQL practise