MySQL 调优 | OPTIMIZER_TRACE详解_2
发布时间:2024-05-20 19:35:47

TIPS

本文基于MySQL 8.0编写,理论支持MySQL 5.6及更高版本。

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

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

TIPS

参考 dev.mysql.com/doc/inter

  • 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:是否跟踪贪心搜索,有关贪心算法详见 blog.csdn.net/weixin_42

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

    • repeated_subselect:是否跟踪子查询,如果设置成off,只跟踪第一条Item_subselect的执行
  • 详见 dev.mysql.com/doc/inter

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

    参考: dev.mysql.com/doc/inter

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更多细节,可参考 dev.mysql.com/doc/inter

  • 开启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 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": "PRIMARY",
                            "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 <=from_date <=0xda840f AND 0xda860f <=to_date <=0xda860f"
                              ]/* ranges */,
                              "index_dives_for_eq_ranges": true,
                              "rowid_ordered": true,
                              "using_mrr": false,
                              "index_only": false,
                              "rows": 86,
                              "cost": 50.909,
                              "chosen": true
                            }
                          ]/* range_scan_alternatives */,
                          "analyzing_roworder_intersect":{
                            "usable": false,
                            "cause": "too_few_roworder_scans"
                          }/* analyzing_roworder_intersect */
                        }/* analyzing_range_alternatives */,
                        "chosen_range_access_summary":{
                          "range_access_plan":{
                            "type": "range_scan",
                            "index": "salaries_from_date_to_date_index",
                            "rows": 86,
                            "ranges":[
                              "0xda840f <=from_date <=0xda840f AND 0xda860f <=to_date <=0xda860f"
                            ]/* ranges */
                          }/* range_access_plan */,
                          "rows_for_plan": 86,
                          "cost_for_plan": 50.909,
                          "chosen": true
                        }/* chosen_range_access_summary */
                      }/* range_analysis */
                    }
                  ]/* rows_estimation */
                },
    {
                  "considered_execution_plans":[
    {
                      "plan_prefix":[
                      ]/* plan_prefix */,
                      "table": "`salaries`",
                      "best_access_path":{
                        "considered_access_paths":[
    {
                            "access_type": "ref",
                            "index": "salaries_from_date_to_date_index",
                            "rows": 86,
                            "cost": 50.412,
                            "chosen": true
                          },
    {
                            "access_type": "range",
                            "range_details":{
                              "used_index": "salaries_from_date_to_date_index"
                            }/* range_details */,
                            "chosen": false,
                            "cause": "heuristic_index_cheaper"
                          }
                        ]/* considered_access_paths */
                      }/* best_access_path */,
                      "condition_filtering_pct": 100,
                      "rows_for_plan": 86,
                      "cost_for_plan": 50.412,
                      "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   ": null
                    }
                  ]/* 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)
  • 分析完成,关闭OPTIMIZER_TRACE

    SET optimizer_trace="enabled=off";

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

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

TIPS

参考: dev.mysql.com/doc/refma

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

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展示了优化阶段的执行过程,是分析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使用的索引及对应值。

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

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

{
  "rows_estimation":[
{
      "table": "`salaries`",
      "range_analysis":{
        "table_scan":{
          "rows": 2838216,
          "cost": 286799
        }/* table_scan */,
        "potential_range_indexes":[
{
            "index": "PRIMARY",
            "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 <=from_date <=0xda840f AND 0xda860f <=to_date <=0xda860f"
              ]/* ranges */,
              "index_dives_for_eq_ranges": true,
              "rowid_ordered": true,
              "using_mrr": false,
              "index_only": false,
              "rows": 86,
              "cost": 50.909,
              "chosen": true
            }
          ]/* range_scan_alternatives */,
          "analyzing_roworder_intersect":{
            "usable": false,
            "cause": "too_few_roworder_scans"
          }/* analyzing_roworder_intersect */
        }/* analyzing_range_alternatives */,
        "chosen_range_access_summary":{
          "range_access_plan":{
            "type": "range_scan",
            "index": "salaries_from_date_to_date_index",
            "rows": 86,
            "ranges":[
              "0xda840f <=from_date <=0xda840f AND 0xda860f <=to_date <=0xda860f"
            ]/* ranges */
          }/* range_access_plan */,
          "rows_for_plan": 86,
          "cost_for_plan": 50.909,
          "chosen": true
        }/* chosen_range_access_summary */
      }/* range_analysis */
    }
  ]/* 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的新特性,感兴趣的可详见 blog.csdn.net/weixin_43

    • 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":[
{
            "access_type": "ref",
            "index": "salaries_from_date_to_date_index",
            "rows": 86,
            "cost": 50.412,
            "chosen": true
          },
{
            "access_type": "range",
            "range_details":{
              "used_index": "salaries_from_date_to_date_index"
            }/* range_details */,
            "chosen": false,
            "cause": "heuristic_index_cheaper"
          }
        ]/* considered_access_paths */
      }/* best_access_path */,
      "condition_filtering_pct": 100,
      "rows_for_plan": 86,
      "cost_for_plan": 50.412,
      "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

{
  "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   ": null
    }
  ]/* finalizing_table_conditions */
}

refine_plan

改善执行计划:

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

其中:

  • table:表名及别名

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

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

平台注册入口