使用方式

  1. 打开optimizer trace功能 (默认情况下它是关闭的):
  2. SET optimizer_trace=“enabled=on”;
  3. 这里输入你自己的查询语句
  4. SELECT …;
  5. 从OPTIMIZER_TRACE表中查看上一个查询的优化过程
  6. SELECT * FROM information_schema.OPTIMIZER_TRACE;
  7. 可能你还要观察其他语句执行的优化过程，重复上边的第2、3步
  8. …
  9. 当你停止查看语句的优化过程时，把optimizer trace功能关闭
  10. SET optimizer_trace=“enabled=off”;

optimizer trace实践

我们可以把optimizer_trace开关打开：

  1. SET OPTIMIZER_TRACE="enabled=on",END_MARKERS_IN_JSON=on;
  2. SET optimizer_trace_offset=-30, optimizer_trace_limit=30;

optimizer_trace默认只显示最近一条SQL的执行计划

我们观察到optimizer_trace_limit、optimizer_trace_offset两个变量，默认值分别为1和-1，表示只保留一条执行计划，同时指向最近的一条记录，我将optimizer_trace_limit和optimizer_trace_offset都修改为5，表示保留最近5条记录，显示前5条

  1. select*from test_index where key1 >'key120'and key1 <'key129';
  2. SELECT*FROM information_schema.OPTIMIZER_TRACE;

看下执行计划

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