MySQL OPTIMIZER TRACE 详解 | Eddie'Blog
MySQL OPTIMIZER TRACE 详解

MySQL OPTIMIZER TRACE 详解

eddie 419 2021-05-13

目录

OPTIMIZER TRACE(优化器跟踪)

  • 跟踪优化器做出的各种决策
  • 了解优化器的执行细节
  • 理解SQL的执行过程,进而优化SQL

OPTIMIZER TRACE 是什么?

TIPS

本文基于MySQL8.0编写,理论支持MySQL5.6往上的版本。

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

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

OPTIMIZER TRACE 参数

TIPS

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

mysql> show variables like '%optimizer_trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name                | Value                                                                      |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace              | enabled=off,one_line=off                                                   |
| optimizer_trace_features     | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit        | 1                                                                          |
| optimizer_trace_max_mem_size | 1048576                                                                    |
| optimizer_trace_offset       | -1                                                                         |
+------------------------------+----------------------------------------------------------------------------+
5 rows in set (0.01 sec)
  • 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_max_mem_size:控制 optimizer_trace 展示多少条结果,默认1
  • optimizer_trace_limit: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


8.11 end_markers_in_json系统变量

如果JSON结构很大,则很难将其右括号和左括号配对; 为了帮助读者(但这不兼容JSON),设置@@ end_markers_in_json = on会在右括号附近重复该结构的键(如果有)。 它也以相同的方式影响“ EXPLAIN FORMAT = JSON”。 请注意,在MySQL 5.6.6之前,此变量不存在,而只是@@ optimizer_trace变量中的一个开关(已使用“ setoptimizer_trace =“ end_marker = on”;“设置)。

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=<KIMIT>

这两个参数配合使用,有点类似MySQL里面的limit语句。

默认情况下,由于 optimizer_trace_offset-1, optimizer_trace_limit=1, 记录最近的一条SQL语句,展示时,每次展示1条数据;

如果改成 optimizer_trace_offset-2, optimizer_trace_limit=1, 则会记录最近的两条SQL语句;

有关 optimizer_trace_limit 和 optimizer_trace_offset 更多细节,可参考:

https://dev.mysql.com/doc/internals/en/tuning-trace-purging.html

OPTIMIZER TRACE 使用

  • 开启OPTIMIZER_TRACE功能,并且设置要展示的数据条目数
  • 发送你想要分析的查询语句
  • 使用如下语句分析,即可获得类似如下的结果

mysql> SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
Query OK, 0 rows affected (0.00 sec)


mysql> SET optimizer_trace_offset=-30, optimizer_trace_limit=20;
Query OK, 0 rows affected (0.00 sec)


mysql> SELECT * FROM salaries WHERE from_date = '1986-06-26' and to_date = '1987-06-26';
+--------+--------+------------+------------+
| emp_no | salary | from_date  | to_date    |
+--------+--------+------------+------------+
|  10001 |  60117 | 1986-06-26 | 1987-06-26 |
|  25676 |  40158 | 1986-06-26 | 1987-06-26 |
|  27425 |  40000 | 1986-06-26 | 1987-06-26 |
|  28757 |  40597 | 1986-06-26 | 1987-06-26 |
|  29250 |  60178 | 1986-06-26 | 1987-06-26 |
|        |        |            |            |
| 498565 |  73492 | 1986-06-26 | 1987-06-26 |
+--------+--------+------------+------------+
86 rows in set (0.47 sec)


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 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": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [
              {
                "table": "`salaries`",
                "table_scan": {
                  "rows": 2838649,
                  "cost": 3368.9
                } /* table_scan */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`salaries`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "rows_to_scan": 2838649,
                      "access_type": "scan",
                      "resulting_rows": 2.84e6,
                      "cost": 287234,
                      "chosen": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 2.84e6,
                "cost_for_plan": 287234,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`salaries`",
                  "attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "finalizing_table_conditions": [
              {
                "table": "`salaries`",
                "original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
                "final_table_condition   ": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
              }
            ] /* finalizing_table_conditions */
          },
          {
            "refine_plan": [
              {
                "table": "`salaries`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0
          INSUFFICIENT_PRIVILEGES: 0
1 row in set (0.00 sec)

ERROR: 
No query specified
  • 分析完成,关闭 OPTIMIZER_TRACE
mysql> SET OPTIMIZER_TRACE="enabled=off";               
Query OK, 0 rows affected (0.00 sec)

OPTIMIZER_TRACE 结果分析

由上面的结果可知,OPTIMIZER_TRACE有四个字段:

  • QUERY:查询语句•TRACE:QUERY字段对应语句的跟踪信息•MISSING_BYTES_BEYOND_MAX_MEM_SIZE:跟踪信息过长时,被截断的跟踪信息的字节数。•INSUFFICIENT_PRIVILEGES:执行跟踪语句的用户是否有查看对象的权限。当不具有权限时,该列信息为1且TRACE字段为空,一般在调用带有SQL SECURITY DEFINER的视图或者是存储过程的情况下,会出现此问题。

TIPS

参考:https://dev.mysql.com/doc/refman/8.0/en/optimizer-trace-table.html

最核心的是TRACE字段的内容。我们逐段分析:

join_preparation

join_preparation段落展示了准备阶段的执行过程。

{
  "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

join_optimization展示了优化阶段的执行过程,是分析OPTIMIZER TRACE的重点。这段内容超级长,而且分了好多步骤,不妨按照步骤逐段分析:

condition_processing

该段用来做条件处理,主要对WHERE条件进行优化处理。

"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 */
},

其中:

  • condition:优化对象类型。WHERE条件句或者是HAVING条件句•original_condition:优化前的原始语句•steps:主要包括三步,分别是quality_propagation(等值条件句转换),constant_propagation(常量条件句转换),trivial_condition_removal(无效条件移除的转换)
  • transformation:转换类型句•resulting_condition:转换之后的结果输出

substitute_generated_columns

substitute_generated_columns用于替换虚拟生成列

"substitute_generated_columns": {
} /* substitute_generated_columns */

table_dependencies

分析表之间的依赖关系

{
"table_dependencies": [
  {
    "table": "`salaries`",
    "row_may_be_null": false,
    "map_bit": 0,
    "depends_on_map_bits": [
    ] /* depends_on_map_bits */
  }
] /* table_dependencies */
},

其中:

  • table:涉及的表名,如果有别名,也会展示出来•row_may_be_null:行是否可能为NULL,这里是指JOIN操作之后,这张表里的数据是不是可能为NULL。如果语句中使用了LEFT JOIN,则后一张表的row_may_be_null会显示为true•map_bit:表的映射编号,从0开始递增
  • depends_on_map_bits:依赖的映射表。主要是当使用STRAIGHT_JOIN强行控制连接顺序或者LEFT JOIN/RIGHT JOIN有顺序差别时,会在depends_on_map_bits中展示前置表的map_bit值。

ref_optimizer_key_uses

列出所有可用的ref类型的索引。如果使用了组合索引的多个部分(例如本例,用到了index(from_date, to_date) 的多列索引),则会在ref_optimizer_key_uses下列出多个元素,每个元素中会列出ref使用的索引及对应值。


rows_estimation

顾名思义,用于估算需要扫描的记录数。


其中:

  • table:表名
  • range_analysis:
  • table_scan:如果全表扫描的话,需要扫描多少行(row,2838216),以及需要的代价(cost,286799)
  • potential_range_indexes:列出表中所有的索引并分析其是否可用。如果不可用的话,会列出不可用的原因是什么;如果可用会列出索引中可用的字段;
  • setup_range_conditions:如果有可下推的条件,则带条件考虑范围查询
  • group_index_range:当使用了GROUP BY或DISTINCT时,是否有合适的索引可用。当未使用GROUP BY或DISTINCT时,会显示chosen=false, cause=not_group_by_or_distinct;如使用了GROUP BY或DISTINCT,但是多表查询时,会显示chosen=false,cause =not_single_table。其他情况下会尝试分析可用的索引(potential_group_range_indexes)并计算对应的扫描行数及其所需代价
  • skip_scan_range:是否使用了skip scan

TIPS

skip_scan_range是MySQL 8.0的新特性,感兴趣的可详见 https://blog.csdn.net/weixin_43970890/article/details/89494915

  • analyzing_range_alternatives:分析各个索引的使用成本
  • range_scan_alternatives:range扫描分析
  • index:索引名•ranges:range扫描的条件范围•index_dives_for_eq_ranges:是否使用了index dive,该值会被参数eq_range_index_dive_limit变量值影响。•rowid_ordered:该range扫描的结果集是否根据PK值进行排序•using_mrr:是否使用了mrr•index_only:表示是否使用了覆盖索引•rows:扫描的行数•cost:索引的使用成本•chosen:表示是否使用了该索引
  • analyzing_roworder_intersect:分析是否使用了索引合并(index merge),如果未使用,会在cause中展示原因;如果使用了索引合并,会在该部分展示索引合并的代价。
  • chosen_range_access_summary:在前一个步骤中分析了各类索引使用的方法及代价,得出了一定的中间结果之后,在summary阶段汇总前一阶段的中间结果确认最后的方案
  • range_access_plan:range扫描最终选择的执行计划。
  • type:展示执行计划的type,如果使用了索引合并,则会显示index_roworder_intersect•index:索引名•rows:扫描的行数•ranges:range扫描的条件范围
  • rows_for_plan:该执行计划的扫描行数•cost_for_plan:该执行计划的执行代价•chosen:是否选择该执行计划

considered_execution_plans

负责对比各可行计划的开销,并选择相对最优的执行计划。

{
"considered_execution_plans": [
  {
    "plan_prefix": [
    ] /* plan_prefix */,
    "table": "`salaries`",
    "best_access_path": {
      "considered_access_paths": [
        {
          "rows_to_scan": 2838649,
          "access_type": "scan",
          "resulting_rows": 2.84e6,
          "cost": 287234,
          "chosen": true
        }
      ] /* considered_access_paths */
    } /* best_access_path */,
    "condition_filtering_pct": 100,
    "rows_for_plan": 2.84e6,
    "cost_for_plan": 287234,
    "chosen": true
  }
] /* considered_execution_plans */
},

其中:

  • plan_prefix:当前计划的前置执行计划。•table:涉及的表名,如果有别名,也会展示出来•best_access_path:通过对比considered_access_paths,选择一个最优的访问路径
  • considered_access_paths:当前考虑的访问路径
  • access_type:使用索引的方式,可参考explain中的type字段
    • index:索引
    • rows:行数
    • cost:开销
    • chosen:是否选用这种执行路径
  • condition_filtering_pct:类似于explain的filtered列,是一个估算值
    • rows_for_plan:执行计划最终的扫描行数,由considered_access_paths.rows X condition_filtering_pct计算获得。
    • cost_for_plan:执行计划的代价,由considered_access_paths.cost相加获得•chosen:是否选择了该执行计划

attaching_conditions_to_tables

基于considered_execution_plans中选择的执行计划,改造原有where条件,并针对表增加适当的附加条件,以便于单表数据的筛选。

TIPS

这部分条件的增加主要是为了便于ICP(索引条件下推),但ICP是否开启并不影响这部分内容的构造。•ICP参考文档:https://www.cnblogs.com/Terry-Wu/p/9273177.html

{
"attaching_conditions_to_tables": {
  "original_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
  "attached_conditions_computation": [
  ] /* attached_conditions_computation */,
  "attached_conditions_summary": [
    {
      "table": "`salaries`",
      "attached": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
    }
  ] /* attached_conditions_summary */
} /* attaching_conditions_to_tables */
},

其中:

  • original_condition:原始的条件语句
  • attached_conditions_computation:使用启发式算法计算已使用的索引,如果已使用的索引的访问类型是ref,则计算用range能否使用组合索引中更多的列,如果可以,则用range的方式替换ref。
  • attached_conditions_summary:附加之后的情况汇总
  • table:表名
  • attached:附加的条件或原语句中能直接下推给单表筛选的条件。

finalizing_table_conditions

最终的、经过优化后的表条件。

{
"finalizing_table_conditions": [
  {
    "table": "`salaries`",
    "original_table_condition": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))",
    "final_table_condition   ": "((`salaries`.`to_date` = DATE'1987-06-26') and (`salaries`.`from_date` = DATE'1986-06-26'))"
  }
] /* finalizing_table_conditions */
},

refine_plan

改善执行计划:

{
"refine_plan": [
  {
    "table": "`salaries`"
  }
] /* refine_plan */
}

其中:

  • table:表名及别名

join_execution

join_execution段落展示了执行阶段的执行过程

{
  "join_execution": {
    "select#": 1,
    "steps": [
    ] /* steps */
}