mysql优化跟踪器

目录

前言

optimizer tracer

概念：

适用语句

追踪sql执行过程：

TRACE信息：

join_preparation：准备阶段

join_optimization：优化阶段

rows_estimation：估算表行数和扫描的代价。

considered_execution_plans：mysql认为最优的执行计划。

join_execution：执行阶段

filesort_summary：文件排序信息汇总

---

前言

• 慢查询(>100ms) 分析

optimizer tracer

概念：

• 优化跟踪器，是跟踪语句的解析优化过程，并将跟踪到的信息记录到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中。

• 该功能默认是不开启的，并且默认只记录最后一次结果到 INFORMATION_SCHEMA.OPTIMIZER_TRACE 表中。

• 查看参数配置：show variables like '%optimizer_trace%';

  • optimizer_trace

    • enabled：启用/禁用optimizer_trace功能
    • one_line：执行结果中trace字段的展示方式，on代表单行展示，off代表以json格式展示
  • optimizer_trace_features：控制执行结果中trace字段需要打印的json项和不需要打印的json项，一般全部开启
  • optimizer_trace_limit和optimizer_trace_offset：这俩个参数类似limit offset，row_count，默认值分别是1和-1，代表在INFORMATION_SCHEMA.OPTIMIZER_TRACE表中只存储最近最后执行的一行结果数据
  • optimizer_trace_max_mem_size：optimizer_trace内存大小，如果跟踪信息超过这个大小，信息会被截断

适用语句

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

追踪sql执行过程：

set optimizer_trace = 'enabled=on'; # 开启trace

# select xxx; # 要分析的sql语句，注意这里需要分号结束。

set optimizer_trace = 'enabled=off'; # 关闭trace

select * from information_schema.OPTIMIZER_TRACE; # 查看trace

执行结果：

• QUERY：跟踪的SQL语句
• TRACE：跟踪信息（JSON格式）
• MISSING_BYTES_BEYOND_MAX_MEM_SIZE：跟踪信息过长时，被截断的跟踪信息的字节数。如果TRACE被截断，我们可以通过减少查询的字段来降低TRACE的大小。
• INSUFFICIENT_PRIVILEGES：执行跟踪语句的用户是否有查看对象的权限。当不具有权限时，该列信息为1且TRACE字段为空。

TRACE信息：

官网示例：

https://dev.mysql.com/doc/internals/en/tracing-example.html

join_preparation：准备阶段

• select#：1代表跟踪分析的是第几条语句，如果使用union，那么这里就会有俩条。
• steps：对应语句的执行步骤。
  • expanded_query：将sql语句转化为明确使用的库名、字段名、列名以及自动给列添加别名等。eg：/* select#1 */ select `table_name`.`field_name` AS `field_name` where ..

join_optimization：优化阶段

• select#：
• steps：对应语句的执行步骤。
  • condition_processing：对where子句和having子句的处理
    • condition：条件语句类型，可以是where或者having
    • original_condition：原始条件查询语句
    • steps：处理步骤
      • resulting_condition：转换之后的语句
      • transformation：转换类型
        • equality_propagation：等值条件句转换，eg： "=" 转换为 "multiple equal()函数"
        • constant_propagation：常量条件句转换，eg：a = 1+2 会转换为 a=3
        • trivial_condition_removal：无效条件移除的转换，eg：1 = 1这个恒为true的无效条件会被移除
  • table_dependencies：梳理表之间的依赖关系
    • table：表名及其别名
    • row_may_be_null：字段是否可以为空
      • 表中列属性是否设置为null。
      • 如果sql是join查询，那么这里指join后列属性是否设置为null，比如说左连接，那么后一张表中有些列可能为null，此时row_may_be_null显示为true
    • map_bit：表序号，单表查询时表序号为0
    • depends_on_map_bits：依赖表的序号，单表查询时为空。
  • ref_optimizer_key_uses： 如果优化器认为查询可以使用ref的话，在这里列出可以使用的索引

  • rows_estimation：估算表行数和扫描的代价。

    • table：表名及其别名
    • range_analysis：
      • table_scan：全表扫描分析
        • rows：扫描行数
        • cost：查询成本
      • potential_range_indexes：可以使用的索引列表
      • group_index_range
        • 
      • analyzing_range_alternatives：分析使用各个索引的成本及可行性。
        • range_scan_alternatives：range扫描分析
          • rowid_ordered：用该索引获取的记录是否按照主键排序
          • index_only：是否使用覆盖索引
          • chosen：是否选择该索引
      • 
    • analyzing_index_merge_union 分析使用索引合并的代价
      • 
  • chosen_range_access_summary：在前面分析了各类索引使用的成本和可行性后，在这里进行汇总说明。
    • 

    • {
          "rows_estimation":[
              {
                  "table":"`material_record`",
                  "range_analysis":{
                      "table_scan":{
                          "rows":554,
                          "cost":125.9
                      },
                      "potential_range_indexes":"见上文",
                      "group_index_range":"见上文",
                      "analyzing_range_alternatives":"见上文",
                      "analyzing_index_merge_union":"见上文",
                      "chosen_range_access_summary":{
                          "range_access_plan":{
                              "type":"index_merge",
                              "index_merge_of":[
                                  {
                                      "type":"range_scan",
                                      "index":"idx_owner_id",
                                      "rows":44,
                                      "ranges":[
                                          "50322 <= owner_id <= 50322"
                                      ]
                                  },
                                  {
                                      "type":"range_scan",
                                      "index":"idx_creator_id",
                                      "rows":10,
                                      "ranges":[
                                          "50322 <= creator_id <= 50322"
                                      ]
                                  }
                              ]
                          },
                          "rows_for_plan":54,
                          "cost_for_plan":99.893,
                          "chosen":true
                      }
                  }
              }
          ]
      }

      
       

  • considered_execution_plans：mysql认为最优的执行计划。（可参考该数据来判断调优是否有效果～）

    • best_access_path：最佳的访问路径。

    • 
  • attaching_conditions_to_tables：添加附加条件，使得条件尽可能筛选单表数据。
  • clause_processing：对DISTINCT、GROUP BY、ORDER BY等字句进行优化。
    • 

join_execution：执行阶段

• creating_tmp_table：创建临时表
  • tmp_table_info：临时表信息
    • table
    • row_length
    • key_length
    • unique_constraint
    • location
    • row_limit_estimate
• filesort_information：文件排序信息
• filesort_priority_queue_optimization：排序优化-优先队列排序。一般在使用 order by + limit 子句的时候会使用优先队列排序。

• filesort_summary：文件排序信息汇总

  • rows：最终结果集中的行数
  • examined_rows：参与排序的行数
  • number_of_tmp_files：使用临时文件的个数，为0表示全部使用sort_buffer内存排序，否则表示使用了磁盘文件排序。
  • sort_buffer_size：使用的sort_buffer的大小
  • sort_mode：排序的方式
• 示例1:
  • 示例2: