如果想打开这个功能，必须首先把enabled的值改为on

优化过程大致分为了三个阶段：

  • prepare阶段
  • optimize阶段
  • execute阶段 ```sql SET optimizer_trace=”enabled=on”;

SELECT * FROM s1 WHERE key1 > ‘z’ AND key2 < 1000000 AND key3 IN (‘a’, ‘b’, ‘c’) AND common_field = ‘abc’;

SELECT * FROM information_schema.OPTIMIZER_TRACE\G

* 1. row *

分析的查询语句是什么

QUERY: SELECT * FROM s1 WHERE key1 > ‘z’ AND key2 < 1000000 AND key3 IN (‘a’, ‘b’, ‘c’) AND common_field = ‘abc’

优化的具体过程

TRACE: { “steps”: [ { “join_preparation”: { # prepare阶段 “select#”: 1, “steps”: [ { “IN_uses_bisection”: true }, { “expanded_query”: “/ select#1 / select s1.id AS id,s1.key1 AS key1,s1.key2 AS key2,s1.key3 AS key3,s1.key_part1 AS key_part1,s1.key_part2 AS key_part2,s1.key_part3 AS key_part3,s1.common_field AS common_field from s1 where ((s1.key1 > ‘z’) and (s1.key2 < 1000000) and (s1.key3 in (‘a’,’b’,’c’)) and (s1.common_field = ‘abc’))” } ] / steps / } / join_preparation / }, { “join_optimization”: { # optimize阶段 “select#”: 1, “steps”: [ { “condition_processing”: { # 处理搜索条件 “condition”: “WHERE”,

  1.           # 原始搜索条件
  2.           "original_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))",
  3.           "steps": [
  4.             {
  5.               # 等值传递转换
  6.               "transformation": "equality_propagation",
  7.               "resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))"
  8.             },
  9.             {
  10.               # 常量传递转换    
  11.               "transformation": "constant_propagation",
  12.               "resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))"
  13.             },
  14.             {
  15.               # 去除没用的条件
  16.               "transformation": "trivial_condition_removal",
  17.               "resulting_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))"
  18.             }
  19.           ] /* steps */
  20.         } /* condition_processing */
  21.       },
  22.       {
  23.         # 替换虚拟生成列
  24.         "substitute_generated_columns": {
  25.         } /* substitute_generated_columns */
  26.       },
  27.       {
  28.         # 表的依赖信息
  29.         "table_dependencies": [
  30.           {
  31.             "table": "`s1`",
  32.             "row_may_be_null": false,
  33.             "map_bit": 0,
  34.             "depends_on_map_bits": [
  35.             ] /* depends_on_map_bits */
  36.           }
  37.         ] /* table_dependencies */
  38.       },
  39.       {
  40.         "ref_optimizer_key_uses": [
  41.         ] /* ref_optimizer_key_uses */
  42.       },
  43.       {
  45.         # 预估不同单表访问方法的访问成本
  46.         "rows_estimation": [
  47.           {
  48.             "table": "`s1`",
  49.             "range_analysis": {
  50.               "table_scan": {   # 全表扫描的行数以及成本
  51.                 "rows": 9688,
  52.                 "cost": 2036.7
  53.               } /* table_scan */,
  55.               # 分析可能使用的索引
  56.               "potential_range_indexes": [
  57.                 {
  58.                   "index": "PRIMARY",   # 主键不可用
  59.                   "usable": false,
  60.                   "cause": "not_applicable"
  61.                 },
  62.                 {
  63.                   "index": "idx_key2",  # idx_key2可能被使用
  64.                   "usable": true,
  65.                   "key_parts": [
  66.                     "key2"
  67.                   ] /* key_parts */
  68.                 },
  69.                 {
  70.                   "index": "idx_key1",  # idx_key1可能被使用
  71.                   "usable": true,
  72.                   "key_parts": [
  73.                     "key1",
  74.                     "id"
  75.                   ] /* key_parts */
  76.                 },
  77.                 {
  78.                   "index": "idx_key3",  # idx_key3可能被使用
  79.                   "usable": true,
  80.                   "key_parts": [
  81.                     "key3",
  82.                     "id"
  83.                   ] /* key_parts */
  84.                 },
  85.                 {
  86.                   "index": "idx_key_part",  # idx_keypart不可用
  87.                   "usable": false,
  88.                   "cause": "not_applicable"
  89.                 }
  90.               ] /* potential_range_indexes */,
  91.               "setup_range_conditions": [
  92.               ] /* setup_range_conditions */,
  93.               "group_index_range": {
  94.                 "chosen": false,
  95.                 "cause": "not_group_by_or_distinct"
  96.               } /* group_index_range */,
  98.               # 分析各种可能使用的索引的成本
  99.               "analyzing_range_alternatives": {
  100.                 "range_scan_alternatives": [
  101.                   {
  102.                     # 使用idx_key2的成本分析
  103.                     "index": "idx_key2",
  104.                     # 使用idx_key2的范围区间
  105.                     "ranges": [
  106.                       "NULL < key2 < 1000000"
  107.                     ] /* ranges */,
  108.                     "index_dives_for_eq_ranges": true,   # 是否使用index dive
  109.                     "rowid_ordered": false,     # 使用该索引获取的记录是否按照主键排序
  110.                     "using_mrr": false,     # 是否使用mrr
  111.                     "index_only": false,    # 是否是索引覆盖访问
  112.                     "rows": 12,     # 使用该索引获取的记录条数
  113.                     "cost": 15.41,  # 使用该索引的成本
  114.                     "chosen": true  # 是否选择该索引
  115.                   },
  116.                   {
  117.                     # 使用idx_key1的成本分析
  118.                     "index": "idx_key1",
  119.                     # 使用idx_key1的范围区间
  120.                     "ranges": [
  121.                       "z < key1"
  122.                     ] /* ranges */,
  123.                     "index_dives_for_eq_ranges": true,   # 同上
  124.                     "rowid_ordered": false,   # 同上
  125.                     "using_mrr": false,   # 同上
  126.                     "index_only": false,   # 同上
  127.                     "rows": 266,   # 同上
  128.                     "cost": 320.21,   # 同上
  129.                     "chosen": false,   # 同上
  130.                     "cause": "cost"   # 因为成本太大所以不选择该索引
  131.                   },
  132.                   {
  133.                     # 使用idx_key3的成本分析
  134.                     "index": "idx_key3",
  135.                     # 使用idx_key3的范围区间
  136.                     "ranges": [
  137.                       "a <= key3 <= a",
  138.                       "b <= key3 <= b",
  139.                       "c <= key3 <= c"
  140.                     ] /* ranges */,
  141.                     "index_dives_for_eq_ranges": true,   # 同上
  142.                     "rowid_ordered": false,   # 同上
  143.                     "using_mrr": false,   # 同上
  144.                     "index_only": false,   # 同上
  145.                     "rows": 21,   # 同上
  146.                     "cost": 28.21,   # 同上
  147.                     "chosen": false,   # 同上
  148.                     "cause": "cost"   # 同上
  149.                   }
  150.                 ] /* range_scan_alternatives */,
  152.                 # 分析使用索引合并的成本
  153.                 "analyzing_roworder_intersect": {
  154.                   "usable": false,
  155.                   "cause": "too_few_roworder_scans"
  156.                 } /* analyzing_roworder_intersect */
  157.               } /* analyzing_range_alternatives */,
  159.               # 对于上述单表查询s1最优的访问方法
  160.               "chosen_range_access_summary": {
  161.                 "range_access_plan": {
  162.                   "type": "range_scan",
  163.                   "index": "idx_key2",
  164.                   "rows": 12,
  165.                   "ranges": [
  166.                     "NULL < key2 < 1000000"
  167.                   ] /* ranges */
  168.                 } /* range_access_plan */,
  169.                 "rows_for_plan": 12,
  170.                 "cost_for_plan": 15.41,
  171.                 "chosen": true
  172.               } /* chosen_range_access_summary */
  173.             } /* range_analysis */
  174.           }
  175.         ] /* rows_estimation */
  176.       },
  177.       {
  179.         # 分析各种可能的执行计划
  180.         #（对多表查询这可能有很多种不同的方案，单表查询的方案上边已经分析过了，直接选取idx_key2就好）
  181.         "considered_execution_plans": [
  182.           {
  183.             "plan_prefix": [
  184.             ] /* plan_prefix */,
  185.             "table": "`s1`",
  186.             "best_access_path": {
  187.               "considered_access_paths": [
  188.                 {
  189.                   "rows_to_scan": 12,
  190.                   "access_type": "range",
  191.                   "range_details": {
  192.                     "used_index": "idx_key2"
  193.                   } /* range_details */,
  194.                   "resulting_rows": 12,
  195.                   "cost": 17.81,
  196.                   "chosen": true
  197.                 }
  198.               ] /* considered_access_paths */
  199.             } /* best_access_path */,
  200.             "condition_filtering_pct": 100,
  201.             "rows_for_plan": 12,
  202.             "cost_for_plan": 17.81,
  203.             "chosen": true
  204.           }
  205.         ] /* considered_execution_plans */
  206.       },
  207.       {
  208.         # 尝试给查询添加一些其他的查询条件
  209.         "attaching_conditions_to_tables": {
  210.           "original_condition": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))",
  211.           "attached_conditions_computation": [
  212.           ] /* attached_conditions_computation */,
  213.           "attached_conditions_summary": [
  214.             {
  215.               "table": "`s1`",
  216.               "attached": "((`s1`.`key1` > 'z') and (`s1`.`key2` < 1000000) and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))"
  217.             }
  218.           ] /* attached_conditions_summary */
  219.         } /* attaching_conditions_to_tables */
  220.       },
  221.       {
  222.         # 再稍稍的改进一下执行计划
  223.         "refine_plan": [
  224.           {
  225.             "table": "`s1`",
  226.             "pushed_index_condition": "(`s1`.`key2` < 1000000)",
  227.             "table_condition_attached": "((`s1`.`key1` > 'z') and (`s1`.`key3` in ('a','b','c')) and (`s1`.`common_field` = 'abc'))"
  228.           }
  229.         ] /* refine_plan */
  230.       }
  231.     ] /* steps */
  232.   } /* join_optimization */
  233. },
  234. {
  235.   "join_execution": {    # execute阶段
  236.     "select#": 1,
  237.     "steps": [
  238.     ] /* steps */
  239.   } /* join_execution */
  240. }

] / steps / }

因优化过程文本太多而丢弃的文本字节大小，值为0时表示并没有丢弃

MISSING_BYTES_BEYOND_MAX_MEM_SIZE: 0

权限字段

INSUFFICIENT_PRIVILEGES: 0

1 row in set (0.00 sec) ```