欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

MySQL Trace 案例解析

程序员文章站 2022-04-17 20:17:12
...

  {

  "steps": [

  {

  "join_preparation": { --第一阶段:SQL准备阶段

  "select#": 1,

  "steps": [

  {

  "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'a') order by `employees`.`position`"

  }

  ] /* steps */

  } /* join_preparation */

  },

  {

  "join_optimization": { --第二阶段:SQL优化阶段

  "select#": 1,

  "steps": [

  {

  "condition_processing": { --条件处理

  "condition": "WHERE",

  "original_condition": "(`employees`.`name` > 'a')",

  "steps": [

  {

  "transformation": "equality_propagation",

  "resulting_condition": "(`employees`.`name` > 'a')"

  },

  {

  "transformation": "constant_propagation",

  "resulting_condition": "(`employees`.`name` > 'a')"

  },

  {

  "transformation": "trivial_condition_removal",

  "resulting_condition": "(`employees`.`name` > 'a')"

  }

  ] /* steps */

  } /* condition_processing */

  },

  {

  "substitute_generated_columns": {

  } /* substitute_generated_columns */

  },

  {

  "table_dependencies": [ --表依赖详情

  {

  "table": "`employees`",

  "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": "`employees`",

  "range_analysis": {

  "table_scan": { --全表扫描

  "rows": 3, --扫描行数

  "cost": 3.7 --查询成本

  } /* table_scan */,

  "potential_range_indexes": [ --查询可能使用的索引

  {

  "index": "PRIMARY", --QQ账号转让主键索引

  "usable": false,

  "cause": "not_applicable"

  },

  {

  "index": "idx_name_age_position", --辅助索引

  "usable": true,

  "key_parts": [

  "name",

  "age",

  "position",

  "id"

  ] /* key_parts */

  },

  {

  "index": "idx_age",

  "usable": false,

  "cause": "not_applicable"

  }

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

  "ranges": [

  "a

  ] /* ranges */,

  "index_dives_for_eq_ranges": true,

  "rowid_ordered": false, --使用该索引获取的记录是否按照主键排序

  "using_mrr": false,

  "index_only": false, --是否使用覆盖索引

  "rows": 3, --索引扫描行数

  "cost": 4.61, --索引使用成本

  "chosen": false, --是否选择该索引

  "cause": "cost"

  }

  ] /* range_scan_alternatives */,

  "analyzing_roworder_intersect": {

  "usable": false,

  "cause": "too_few_roworder_scans"

  } /* analyzing_roworder_intersect */

  } /* analyzing_range_alternatives */

  } /* range_analysis */

  }

  ] /* rows_estimation */

  },

  {

  "considered_execution_plans": [

  {

  "plan_prefix": [

  ] /* plan_prefix */,

  "table": "`employees`",

  "best_access_path": { --最优访问路径

  "considered_access_paths": [ --最终选择的访问路径

  {

  "rows_to_scan": 3,

  "access_type": "scan", --访问类型:为sacn,全表扫描

  "resulting_rows": 3,

  "cost": 1.6,

  "chosen": true, --确定选择

  "use_tmp_table": true

  }

  ] /* considered_access_paths */

  } /* best_access_path */,

  "condition_filtering_pct": 100,

  "rows_for_plan": 3,

  "cost_for_plan": 1.6,

  "sort_cost": 3,

  "new_cost_for_plan": 4.6,

  "chosen": true

  }

  ] /* considered_execution_plans */

  },

  {

  "attaching_conditions_to_tables": {

  "original_condition": "(`employees`.`name` > 'a')",

  "attached_conditions_computation": [

  ] /* attached_conditions_computation */,

  "attached_conditions_summary": [

  {

  "table": "`employees`",

  "attached": "(`employees`.`name` > 'a')"

  }

  ] /* attached_conditions_summary */

  } /* attaching_conditions_to_tables */

  },

  {

  "clause_processing": {

  "clause": "ORDER BY",

  "original_clause": "`employees`.`position`",

  "items": [

  {

  "item": "`employees`.`position`"

  }

  ] /* items */,

  "resulting_clause_is_simple": true,

  "resulting_clause": "`employees`.`position`"

  } /* clause_processing */

  },

  {

  "reconsidering_access_paths_for_index_ordering": {

  "clause": "ORDER BY",

  "index_order_summary": {

  "table": "`employees`",

  "index_provides_order": false,

  "order_direction": "undefined",

  "index": "unknown",

  "plan_changed": false

  } /* index_order_summary */

  } /* reconsidering_access_paths_for_index_ordering */

  },

  {

  "refine_plan": [

  {

  "table": "`employees`"

  }

  ] /* refine_plan */

  }

  ] /* steps */

  } /* join_optimization */

  },

  {

  "join_execution": { --第三阶段:SQL执行阶段

  "select#": 1,

  "steps": [

  {

  "filesort_information": [

  {

  "direction": "asc",

  "table": "`employees`",

  "field": "position"

  }

  ] /* filesort_information */,

  "filesort_priority_queue_optimization": {

  "usable": false,

  "cause": "not applicable (no LIMIT)"

  } /* filesort_priority_queue_optimization */,

  "filesort_execution": [

  ] /* filesort_execution */,

  "filesort_summary": {

  "rows": 3,

  "examined_rows": 3,

  "number_of_tmp_files": 0,

  "sort_buffer_size": 200704,

  "sort_mode": ""

  } /* filesort_summary */

  }

  ] /* steps */

  } /* join_execution */

  }

  ] /* steps */

  }