MySQL 调优 | OPTIMIZER_TRACE详解

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

SELECT
INSERT
REPLACE
UPDATE
DELETE
EXPLAIN
SET
DECLARE
CASE
IF
RETURN
CALL

OPTIMIZER_TRACE相关参数

TIPS
参考 https://dev.mysql.com/doc/internals/en/system-variables-controlling-trace.html

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：是否跟踪贪心搜索，有关贪心算法详见         https://blog.csdn.net/weixin_42813521/article/details/105563103

        range_optimizer：是否跟踪范围优化器
dynamic_range：是否跟踪动态范围优化

repeated_subselect：是否跟踪子查询，如果设置成off，只跟踪第一条Item_subselect的执行
详见 https://dev.mysql.com/doc/internals/en/optimizer-features-to-trace.html

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。

参考： https://dev.mysql.com/doc/internals/en/end-markers-in-json-system-variable.html

TIPS

以上参数可用SET语句操作，例如，用如下命令即可打开OPTIMIZER TRACE

>   SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
>

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

>   SET GLOBAL OPTIMIZER_TRACE="enabled=on",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_offset 、optimizer_trace_limit更多细节，可参考 https://dev.mysql.com/doc/internals/en/tuning-trace-purging.html

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

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

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

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

使用如下语句分析，即可获得类似如下的结果：

  mysql> SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE limit 30 \G;
  *************************** 1. row ***************************
                            QUERY: select *
  from salaries
where 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 e