MySQL性能分析(三)之optimizer_trace详解

一、概述

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

• select
  
• insert
  
• replace
  
• update
  
• delete
  
• explain
  
• set
  
• declare
  
• case
  
• if
  
• return
  
• call
  

二、开启及关闭

2.1 开启optimizer_trace

在mysql命令行中，使用如下命令开启optimizer_trace：

1. set optimizer_trace="enabled=on",end_markers_in_json=on;

复制代码

也可用set global全局开启。但即使全局开启optimizer_trace，每个Session也只能跟踪它自己执行的语句：

1. set global optimizer_trace="enabled=on",end_markers_in_json=on;

复制代码

2.2 关闭optimizer_trace

在mysql命令行中，使用如下命令关闭optimizer_trace：

1. SET optimizer_trace="enabled=off";

复制代码

三、使用optimizer_trace

3.1 相关参数

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,range_optimizer=on,dynamic_range=on,repeated_subselect=on，表示开启所有跟踪项。
  

greedy_search：是否跟踪贪心搜索

• range_optimizer：是否跟踪范围优化器
  

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

optimizer_trace_limit和optimizer_trace_offset这两个参数经常配合使用，例如：SET optimizer_trace_offset=, optimizer_trace_limit=
这两个参数配合使用，有点类似MySQL里面的 limit语句。
默认情况下，由于optimizer_trace_offset=-1，optimizer_trace_limit=1，记录最近的一条SQL语句，展示时，每次展示1条数据；
如果改成SET optimizer_trace_offset=-2, optimizer_trace_limit=1，则会记录倒数第二条SQL语句；

三、使用

3.1 展示条目

开启optimizer_trace功能，并设置要展示的数据条目数：

1. set optimizer_trace="enabled=on", end_markers_in_json=on;
2. set optimizer_trace_offset=-30, optimizer_trace_limit=30;

复制代码

3.2 分析SQL语句

发送你想要分析的SQL语句，例如：

1. select *
2. from salaries
3. where from_date = '1986-06-26'
4. and to_date = '1987-06-26';

复制代码

使用如下语句分析，即可获得类似如下的结果：
[code]  mysql> select * from information_schema.optimizer_trace limit 30 \G;  *************************** 1. row ***************************                            QUERY: select *  from salarieswhere from_date = '1986-06-26'    and to_date = '1987-06-26'                              TRACE: {    "steps": [      {        "join_preparation": {          "select#": 1,          "steps": [            {              "expanded_query": "/* select#1 */ select `salaries`.`emp_no` AS `emp_no`,`salaries`.`salary` AS `salary`,`salaries`.`from_date` AS `from_date`,`salaries`.`to_date` AS `to_date` from `salaries` where ((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))"            }          ] /* steps */        } /* join_preparation */      },      {        "join_optimization": {          "select#": 1,          "steps": [            {              "condition_processing": {                "condition": "WHERE",                "original_condition": "((`salaries`.`from_date` = '1986-06-26') and (`salaries`.`to_date` = '1987-06-26'))",                "steps": [                  {                    "transformation": "equality_propagation",                    "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"                  },                  {                    "transformation": "constant_propagation",                    "resulting_condition": "(multiple equal('1986-06-26', `salaries`.`from_date`) and multiple equal('1987-06-26', `salaries`.`to_date`))"                  },                  {                    "transformation": "trivial_condition_removal",                    "resulting_condition": "(multiple equal(DATE'1986-06-26', `salaries`.`from_date`) and multiple equal(DATE'1987-06-26', `salaries`.`to_date`))"                  }                ] /* steps */              } /* condition_processing */            },            {              "substitute_generated_columns": {              } /* substitute_generated_columns */            },            {              "table_dependencies": [                {                  "table": "`salaries`",                  "row_may_be_null": false,                  "map_bit": 0,                  "depends_on_map_bits": [                  ] /* depends_on_map_bits */                }              ] /* table_dependencies */            },            {              "ref_optimizer_key_uses": [                {                  "table": "`salaries`",                  "field": "from_date",                  "equals": "DATE'1986-06-26'",                  "null_rejecting": false                },                {                  "table": "`salaries`",                  "field": "to_date",                  "equals": "DATE'1987-06-26'",                  "null_rejecting": false                }              ] /* ref_optimizer_key_uses */            },            {              "rows_estimation": [                {                  "table": "`salaries`",                  "range_analysis": {                    "table_scan": {                      "rows": 2838216,                      "cost": 286799                    } /* table_scan */,                    "potential_range_indexes": [                      {                        "index": "RIMARY",                        "usable": false,                        "cause": "not_applicable"                      },                      {                        "index": "salaries_from_date_to_date_index",                        "usable": true,                        "key_parts": [                          "from_date",                          "to_date",                          "emp_no"                        ] /* key_parts */                      }                    ] /* potential_range_indexes */,                    "setup_range_conditions": [                    ] /* setup_range_conditions */,                    "group_index_range": {                      "chosen": false,                      "cause": "not_group_by_or_distinct"                    } /* group_index_range */,                    "skip_scan_range": {                      "potential_skip_scan_indexes": [                        {                          "index": "salaries_from_date_to_date_index",                          "usable": false,                          "cause": "query_references_nonkey_column"                        }                      ] /* potential_skip_scan_indexes */                    } /* skip_scan_range */,                    "analyzing_range_alternatives": {                      "range_scan_alternatives": [                        {                          "index": "salaries_from_date_to_date_index",                          "ranges": [                            "0xda840f