SQL执行效率的分析-trace分析慢查询

olei 742 views 0

trace

explain可以查看到SQL执行计划,但是无法知道它为什么做这个决策,如果想确定多种索引方案之间是如何选择的或者排序时选择的是哪种排序模式,有什么好的方式吗?

MySQL 5.6开始,可以使用trace查看优化器如何选择执行计划

通过trace,能够进一步了解为什么优化器选择A而不是选择B执行计划,或者知道某个排序使用的排序模式,帮助我们更好地理解优化器行为。

如果需要使用,先开启trace,设置格式为JSON,再执行需要分析的SQL,最后查看trace分析结果(在information_schema.OPTIMIZER_TRACE中)。

开启该功能,对MySQL性能有影响,建议只在分析的时候开启

  • 首先构造如下的SQL(表示取出表t1a的值大于900,并且b的值大于910的数据,然后按照a字段排序)
    mysql> select * from t1 where a>900 and b>910 order by a;
  • explain分析执行计划
    mysql> explain select * from t1 where a>900 and b>910 order by a;

    SQL执行效率的分析-trace分析慢查询

通过上图的key字段可以看到,该语句使用的是a字段的索引idx_a。实际表t1中,ab两个字段都有索引,为什么条件中有这两个索引,却偏偏选择了a字段的索引呢?这时候可以用trace分析。

mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;
/* optimizer_trace="enabled=on" 表示开启 trace;end_markers_in_json=on 表示 JSON 输出开启结束标记 */
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1 where a >900 and b > 910 order  by a;
+------+------+------+
| id   | a    | b    |
+------+------+------+
|    1 |    1 |    1 |
|    2 |    2 |    2 |

......

| 1000 | 1000 | 1000 |
+------+------+------+
1000 rows in set (0.00 sec)

mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE\G
*************************** 1. row ***************************
QUERY: select * from t1 where a >900 and b > 910 order  by a    --SQL语句
TRACE: {
  "steps": [
    {
      "join_preparation": {               --SQL准备阶段
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `t1`.`id` AS `id`,`t1`.`a` AS `a`,`t1`.`b` AS `b`,`t1`.`create_time` AS `create_time`,`t1`.`update_time` AS `update_time` from `t1` where ((`t1`.`a` > 900) and (`t1`.`b` > 910)) order by `t1`.`a`"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {          --SQL优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": {    --条件处理
              "condition": "WHERE",
              "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",        --原始条件
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"        --等值传递转换
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"       --常量传递转换
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"        --去除没有的条件后的结构
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */   --替换虚拟生成列
          },
          {
            "table_dependencies": [       --表依赖详情
              {
                "table": "`t1`",
                "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": "`t1`",
                "range_analysis": {
                  "table_scan": {
                    "rows": 1000,       --扫描行数
                    "cost": 207.1       --成本
                  } /* table_scan */,
                  "potential_range_indexes": [    --分析可能使用的索引
                    {
                      "index": "PRIMARY",
                      "usable": false,       --为false,说明主键索引不可用
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_a",      --可能使用索引idx_a
                      "usable": true,
                      "key_parts": [
                        "a",
                        "id"
                      ] /* key_parts */
                    },
                    {
                      "index": "idx_b",      --可能使用索引idx_b
                      "usable": true,
                      "key_parts": [
                        "b",
                        "id"
                      ] /* 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 */,
                  "analyzing_range_alternatives": { --分析各索引的成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_a",   --使用索引idx_a的成本
                        "ranges": [
                          "900 < a"            --使用索引idx_a的范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true, --是否使用index dive(详细描述请看下方的知识扩展)
                        "rowid_ordered": false, --使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,   --是否使用mrr
                        "index_only": false,    --是否使用覆盖索引
                        "rows": 100,            --使用该索引获取的记录数
                        "cost": 121.01,         --使用该索引的成本
                        "chosen": true          --可能选择该索引
                      },
                      {
                        "index": "idx_b",       --使用索引idx_b的成本
                        "ranges": [
                          "910 < b"
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,
                        "using_mrr": false,
                        "index_only": false,
                        "rows": 90,
                        "cost": 109.01,
                        "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": "idx_b",
                      "rows": 90,
                      "ranges": [
                        "910 < b"
                      ] /* ranges */
                    } /* range_access_plan */,
                    "rows_for_plan": 90,
                    "cost_for_plan": 109.01,
                    "chosen": true
                  } /* chosen_range_access_summary */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [  --考虑的执行计划
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`t1`",
                "best_access_path": {          --最优的访问路径
                  "considered_access_paths": [ --决定的访问路径
                    {
                      "rows_to_scan": 90,      --扫描的行数
                      "access_type": "range",  --访问类型:为range
                      "range_details": {
                        "used_index": "idx_b"  --使用的索引为:idx_b
                      } /* range_details */,
                      "resulting_rows": 90,    --结果行数
                      "cost": 127.01,          --成本
                      "chosen": true,        --确定选择
                      "use_tmp_table": true
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 90,
                "cost_for_plan": 127.01,
                "sort_cost": 90,
                "new_cost_for_plan": 217.01,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {  --尝试添加一些其他的查询条件
              "original_condition": "((`t1`.`a` > 900) and (`t1`.`b` > 910))",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`t1`",
                  "attached": "((`t1`.`a` > 900) and (`t1`.`b` > 910))"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "clause_processing": {
              "clause": "ORDER BY",
              "original_clause": "`t1`.`a`",
              "items": [
                {
                  "item": "`t1`.`a`"
                }
              ] /* items */,
              "resulting_clause_is_simple": true,
              "resulting_clause": "`t1`.`a`"
            } /* clause_processing */
          },
          {
            "reconsidering_access_paths_for_index_ordering": {
              "clause": "ORDER BY",
              "index_order_summary": {
                "table": "`t1`",
                "index_provides_order": false,
                "order_direction": "undefined",
                "index": "idx_b",
                "plan_changed": false
              } /* index_order_summary */
            } /* reconsidering_access_paths_for_index_ordering */
          },
          {
            "refine_plan": [          --改进的执行计划
              {
                "table": "`t1`",
                "pushed_index_condition": "(`t1`.`b` > 910)",
                "table_condition_attached": "(`t1`.`a` > 900)"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {             --SQL执行阶段
        "select#": 1,
        "steps": [
          {
            "filesort_information": [
              {
                "direction": "asc",
                "table": "`t1`",
                "field": "a"
              }
            ] /* filesort_information */,
            "filesort_priority_queue_optimization": {
              "usable": false,             --未使用优先队列优化排序
              "cause": "not applicable (no LIMIT)"     --未使用优先队列排序的原因是没有limit
            } /* filesort_priority_queue_optimization */,
            "filesort_execution": [
            ] /* filesort_execution */,
            "filesort_summary": {           --排序详情
              "rows": 90,
              "examined_rows": 90,          --参与排序的行数
              "number_of_tmp_files": 0,     --排序过程中使用的临时文件数
              "sort_buffer_size": 115056,
              "sort_mode": "<sort_key, additional_fields>"   --排序模式(详解请看下方知识扩展)
            } /* filesort_summary */
          }
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}
MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0    --该字段表示分析过程丢弃的文本字节大小,本例为0,说明没丢弃任何文本
          INSUFFICIENT_PRIVILEGES: 0    --查看trace的权限是否不足,0表示有权限查看trace详情
1 row in set (0.00 sec)
------------------------------------------------
------------------------------------------------

mysql> set session optimizer_trace="enabled=on";
/* 及时关闭trace */

分析上方的字段描述

TRACE字段中整个文本大致分为三个阶段

  • 准备阶段:对应文本中的join_preparation
  • 优化阶段:对应文本中的join_optimization
  • 执行节点:对应文本中的join_execution

    使用时,重点关注优化阶段

由此例可以看出

  • trace结果的analyzing_range_alternatives这一项可以看出:使用索引index_a的成本为121.01,使用索引idx_b的成本为109.01,显然使用索引idx_b的成本要低些,因此优化器选择了idx_b索引;
    SQL执行效率的分析-trace分析慢查询
  • trace结果的filesort_summary这一项可以看到:排序模式为<sort_key,additional_fields>,表示使用的是单路排序,即一次性取出满足条件行的所有字段,然后在sort buffer中进行排序。

知识拓展

知识点一:MySQL常见排序模式

  • <sort_key,rowid>双路排序(又叫回表排序模式):是首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行ID
  • <sort_key,additional_fields>单路排序:是一次性取出满足条件行的所有字段,然后在sort buffer中进行排序;
  • <sort_key packed_additional_fields>打包数据排序模式:将charvarchar字段存到sort buffer中时,更加紧缩。

三种排序模式比较:

第二种模式相对第一种模式,避免了二次回表,可以理解为用空间换时间。由于sort buffer有限,如果需要查询的数据比较大的话,会增加磁盘排序时间,效率可能比第一种方式更低

MySQL提供了一个参数:max_length_for_sort_data,当“排序的键值大小” > max_length_for_sort_data时,MySQL人为磁盘外部排序的IO效率不如回表的效率,会选择第一种排序模式;否则,会选择第二种模式

第三种模式主要解决边长字符数据存储空间浪费的问题

知识点二:优化器在估计符合条件的行数时有两个选择

  • index diverdiveindex中利用索引完成组数的估算:特点是速度慢,但能得到精确的值
  • index statistics:使用索引的统计数值,进行估算:特点是速度快,但值不一定准确

总结

  • explain:获取MySQLSQL语句的执行计划,比如语句是否使用了关联查询,是否使用了索引,扫描行数等
  • profile:可以清楚的了解到SQL到底慢在哪个环节
  • trace:查看优化器如何选择执行计划,获取每个可能的索引选择的代价

发表评论 取消回复
表情 图片 链接 代码

分享