MySQL数据库调优————OPTIMIZER_TRACE详解

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

• SELECT
• INSERT
• REPLACE
• UPDATE
• DELETE
• EXPLAIN
• SET
• DECLARE
• CASE
• IF
• RETURE
• CALL

OPTIMIZER_TRACE相关参数

• 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，reage_optimizer=on，dynamic_range=on，repeated_subselect=on，表示开启所有的追踪项。
  • greedy_search：是否跟踪贪心搜索。
  • range_optimizer：是否跟踪范围优化器。
  • synamic_range：是否跟踪动态范围优化。
  • repeated_subselect：是否跟踪子查询，如果设置成off，只跟踪第一条Item_subselect的执行。
• optimizer_trace_limit：控制optimizer_trace展示多少条结果，默认1
• optimizer_trace_max_mem_size：optimizer_trace堆栈信息允许的最大内存，默认1048576
• optimizer_trace_offset：第一个要展示的optimizer_trace的偏移量，默认-1
• end_markers_in_json：如果JSON结构很大，则很难将右括号和左括号配对。为了帮助阅读，可将其设置成on，这样会在右括号附件加上注释，默认off

TIPS

• 以上参数可用SET语句操作，例如，用如下命令即可打开OPTIMIZER_TRACE
  SET OPTIMIZER_TRACE=“enabled=on”,END_MARKERS_IN_JSON=on;
  也可用SET GLOBAL全局开启。但即使全局开启OPTIMIZER_TRACE，每个session也只能跟踪自己执行的语句：
  SET GLOBAL OPTIMIZER_TRACE=“enabled”,END_MARKERS_IN_JSON=on;
• optimizer_trace_limit和optimizer_trace_offset这两个参数经常配合使用，例如：
  SET optimizer_trace_offset=< OFFSET >，optimizer_trace_limit=< LIMIT >
  这两个参数配合使用，有点儿类似MySQL里面的limit语句。
  默认情况下，由于optimizer_trace_offset=-1，optimizer_trace_limit=1，记录最近的一条SQL语句，展示时，每次展示1条数据；
  如果改成SET optimizer_trace_offset=-2，optimizer_trace_limit=1，则会记录倒数第二条SQL语句。

OPTIMIZER_TRACE使用

• 开始OPTIMIZER_TRACE功能，并设置要展示的数据条目数：

  SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
  SET optimizer_trace_offset=-30,optimizer_trace_limit=30;
  

• 发送你想要分析的查询语句，例如：

  select * from salaries where from_date = '1986-06-26';
  

• 使用如下语句分析，即可获得类似如下的结果：
• 
  在QUERY列找到自己执行的语句，将TRACE中的内容复制到JSON文件中，并对其过程进行分析。