概述：

  • 算是一种更详细的执行计划吧

示例：

  1. mysql> set session optimizer_trace="enabled=on",end_markers_in_json=on;  --开启trace
  2. mysql> select * from employees where name > 'a' order by position; -- 这里是真正的执行语句
  3. mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE; -- 这里是打出trace
  5. 查看trace字段：
  6. {
  7.   "steps": [
  8.     {
  9.       "join_preparation": {    --第一阶段：SQL准备阶段，格式化sql
  10.         "select#": 1,
  11.         "steps": [
  12.           {
  13.             "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`"
  14.           }
  15.         ] /* steps */
  16.       } /* join_preparation */
  17.     },
  18.     {
  19.       "join_optimization": {    --第二阶段：SQL优化阶段
  20.         "select#": 1,
  21.         "steps": [
  22.           {
  23.             "condition_processing": {    --条件处理
  24.               "condition": "WHERE",
  25.               "original_condition": "(`employees`.`name` > 'a')",
  26.               "steps": [
  27.                 {
  28.                   "transformation": "equality_propagation",
  29.                   "resulting_condition": "(`employees`.`name` > 'a')"
  30.                 },
  31.                 {
  32.                   "transformation": "constant_propagation",
  33.                   "resulting_condition": "(`employees`.`name` > 'a')"
  34.                 },
  35.                 {
  36.                   "transformation": "trivial_condition_removal",
  37.                   "resulting_condition": "(`employees`.`name` > 'a')"
  38.                 }
  39.               ] /* steps */
  40.             } /* condition_processing */
  41.           },
  42.           {
  43.             "substitute_generated_columns": {
  44.             } /* substitute_generated_columns */
  45.           },
  46.           {
  47.             "table_dependencies": [    --表依赖详情
  48.               {
  49.                 "table": "`employees`",
  50.                 "row_may_be_null": false,
  51.                 "map_bit": 0,
  52.                 "depends_on_map_bits": [
  53.                 ] /* depends_on_map_bits */
  54.               }
  55.             ] /* table_dependencies */
  56.           },
  57.           {
  58.             "ref_optimizer_key_uses": [
  59.             ] /* ref_optimizer_key_uses */
  60.           },
  61.           {
  62.             "rows_estimation": [    --预估表的访问成本
  63.               {
  64.                 "table": "`employees`",
  65.                 "range_analysis": {
  66.                   "table_scan": {     --全表扫描情况
  67.                     "rows": 10123,    --扫描行数
  68.                     "cost": 2054.7    --查询成本
  69.                   } /* table_scan */,
  70.                   "potential_range_indexes": [    --查询可能使用的索引
  71.                     {
  72.                       "index": "PRIMARY",    --主键索引
  73.                       "usable": false,
  74.                       "cause": "not_applicable"
  75.                     },
  76.                     {
  77.                       "index": "idx_name_age_position",    --辅助索引
  78.                       "usable": true,
  79.                       "key_parts": [
  80.                         "name",
  81.                         "age",
  82.                         "position",
  83.                         "id"
  84.                       ] /* key_parts */
  85.                     }
  86.                   ] /* potential_range_indexes */,
  87.                   "setup_range_conditions": [
  88.                   ] /* setup_range_conditions */,
  89.                   "group_index_range": {
  90.                     "chosen": false,
  91.                     "cause": "not_group_by_or_distinct"
  92.                   } /* group_index_range */,
  93.                   "analyzing_range_alternatives": {    --分析各个索引使用成本
  94.                     "range_scan_alternatives": [
  95.                       {
  96.                         "index": "idx_name_age_position",
  97.                         "ranges": [
  98.                           "a < name"      --索引使用范围
  99.                         ] /* ranges */,
  100.                         "index_dives_for_eq_ranges": true,
  101.                         "rowid_ordered": false,    --使用该索引获取的记录是否按照主键排序
  102.                         "using_mrr": false,
  103.                         "index_only": false,       --是否使用覆盖索引
  104.                         "rows": 5061,              --索引扫描行数
  105.                         "cost": 6074.2,            --索引使用成本
  106.                         "chosen": false,           --是否选择该索引
  107.                         "cause": "cost"
  108.                       }
  109.                     ] /* range_scan_alternatives */,
  110.                     "analyzing_roworder_intersect": {
  111.                       "usable": false,
  112.                       "cause": "too_few_roworder_scans"
  113.                     } /* analyzing_roworder_intersect */
  114.                   } /* analyzing_range_alternatives */
  115.                 } /* range_analysis */
  116.               }
  117.             ] /* rows_estimation */
  118.           },
  119.           {
  120.             "considered_execution_plans": [
  121.               {
  122.                 "plan_prefix": [
  123.                 ] /* plan_prefix */,
  124.                 "table": "`employees`",
  125.                 "best_access_path": {    --最优访问路径
  126.                   "considered_access_paths": [   --最终选择的访问路径
  127.                     {
  128.                       "rows_to_scan": 10123,
  129.                       "access_type": "scan",     --访问类型：为scan，全表扫描
  130.                       "resulting_rows": 10123,
  131.                       "cost": 2052.6,
  132.                       "chosen": true,            --确定选择
  133.                       "use_tmp_table": true
  134.                     }
  135.                   ] /* considered_access_paths */
  136.                 } /* best_access_path */,
  137.                 "condition_filtering_pct": 100,
  138.                 "rows_for_plan": 10123,
  139.                 "cost_for_plan": 2052.6,
  140.                 "sort_cost": 10123,
  141.                 "new_cost_for_plan": 12176,
  142.                 "chosen": true
  143.               }
  144.             ] /* considered_execution_plans */
  145.           },
  146.           {
  147.             "attaching_conditions_to_tables": {
  148.               "original_condition": "(`employees`.`name` > 'a')",
  149.               "attached_conditions_computation": [
  150.               ] /* attached_conditions_computation */,
  151.               "attached_conditions_summary": [
  152.                 {
  153.                   "table": "`employees`",
  154.                   "attached": "(`employees`.`name` > 'a')"
  155.                 }
  156.               ] /* attached_conditions_summary */
  157.             } /* attaching_conditions_to_tables */
  158.           },
  159.           {
  160.             "clause_processing": {
  161.               "clause": "ORDER BY",
  162.               "original_clause": "`employees`.`position`",
  163.               "items": [
  164.                 {
  165.                   "item": "`employees`.`position`"
  166.                 }
  167.               ] /* items */,
  168.               "resulting_clause_is_simple": true,
  169.               "resulting_clause": "`employees`.`position`"
  170.             } /* clause_processing */
  171.           },
  172.           {
  173.             "reconsidering_access_paths_for_index_ordering": {
  174.               "clause": "ORDER BY",
  175.               "steps": [
  176.               ] /* steps */,
  177.               "index_order_summary": {
  178.                 "table": "`employees`",
  179.                 "index_provides_order": false,
  180.                 "order_direction": "undefined",
  181.                 "index": "unknown",
  182.                 "plan_changed": false
  183.               } /* index_order_summary */
  184.             } /* reconsidering_access_paths_for_index_ordering */
  185.           },
  186.           {
  187.             "refine_plan": [
  188.               {
  189.                 "table": "`employees`"
  190.               }
  191.             ] /* refine_plan */
  192.           }
  193.         ] /* steps */
  194.       } /* join_optimization */
  195.     },
  196.     {
  197.       "join_execution": {    --第三阶段：SQL执行阶段
  198.         "select#": 1,
  199.         "steps": [
  200.         ] /* steps */
  201.       } /* join_execution */
  202.     }
  203.   ] /* steps */
  204. }
  206. 结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描
  208. mysql> select * from employees where name > 'zzz' order by position;
  209. mysql> SELECT * FROM information_schema.OPTIMIZER_TRACE;
  211. 查看trace字段可知索引扫描的成本低于全表扫描，所以mysql最终选择索引扫描
  213. mysql> set session optimizer_trace="enabled=off";    --关闭trace