1. 概述

  1.     对于 MySQL 5.6 以及之前的版本来说，查询优化器就像是一个黑盒子一样，你只能通过 EXPLAIN 语句查看到最后优化器决定使用的执行计划，却无法知道它为什么做这个决策。
  3.     在 MySQL 5.6 以及之后的版本中，MySQL 提出了一个 optimizer trace 的功能，这个功能可以让我们方便的查看优化器生成执行计划的整个过程。

2.Optimizer trace的使用

Optimizer trace 并不是自动就会默认开启的，开启 trace 多多少少都会有一些额外的工作要做，因此并不建议一直开着。但 trace 属于轻量级的工具，开启和关闭都非常简便，对系统的影响也微乎其微。而且支持在 session 中开启，不影响其它 session，对系统的影响降到了最低。

2.1 查看optimizer trace状态

  1. > show variables like '%optimizer_trace%';
  3. +----------------------------+--------------------------------------------------------------------------+
  4. |Variable_name               |Value                                                                     |
  5. +----------------------------+--------------------------------------------------------------------------+
  6. |optimizer_trace             |enabled=off,one_line=off                                                  |
  7. |optimizer_trace_features    |greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on|
  8. |optimizer_trace_limit       |1                                                                         |
  9. |optimizer_trace_max_mem_size|16384                                                                     |
  10. |optimizer_trace_offset      |-1                                                                        |
  11. +----------------------------+--------------------------------------------------------------------------+
  • optimizer_trace：enabled状态；one_line的值是控制输出格式的，如果为on那么所有输出都将在一行中展示
  • optimizer_trace_limit：OPTIMIZER_TRACE表中保存条数
  • optimizer_trace_max_mem_size：由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示
  • optimizer_trace_offset：查询OPTIMIZER_TRACE表时的偏移量

2.2 开启optimizer trace功能

  1. SET optimizer_trace="enabled=on";
  2. SET optimizer_trace_limit=10;
  3. SET optimizer_trace_offset=10;
  4. SET optimizer_trace_max_mem_size = 32768;

注意：在这里设置了optimizer_trace_limit为10主要是因为在使用DataGrip时会自动插入多条数据影响查看

2.3 查询上一个语句的优化过程

  1. SELECT * FROM information_schema.OPTIMIZER_TRACE;
  • QUERY ：表示我们的查询语句。
  • TRACE ：表示优化过程的JSON格式文本。
  • MISSING_BYTES_BEYOND_MAX_MEM_SIZE ：由于优化过程可能会输出很多，如果超过某个限制时，多余的文本将不会被显示，这个字段展示了被忽略的文本字节数。
  • INSUFFICIENT_PRIVILEGES ：表示是否没有权限查看优化过程，默认值是0，只有某些特殊情况下才会是1 ，我们暂时不关心这个字段的值

2.4 关闭optimizer trace 功能

  1. SET optimizer_trace="enabled=off";
  2. SET optimizer_trace_limit=1;
  3. SET optimizer_trace_offset=-1;
  4. SET optimizer_trace_max_mem_size = 16384;

3. 具体分析

TRACE文本分析：

  1. {
  2.   "steps": [
  3.     {
  4.       "join_preparation": { #  prepare阶段
  5.         "select#": 1,
  6.         "steps": [
  7.           {
  8.             "expanded_query": "/* select#1 */ select sql_no_cache `item_sale_summary`.`ent_id` AS `ent_id`,`item_sale_summary`.`region_code` AS `region_code`,ceiling((count(distinct `item_sale_summary`.`item_code`,`item_sale_summary`.`barcode`,date_format(`item_sale_summary`.`trans_date`,'%Y-%m-%d')) / ((to_days('2021-12-05') - to_days('2021-11-05')) + 1))) AS `sku_item_sale` from `item_sale_summary` where ((`item_sale_summary`.`trans_date` between '2021-11-01' and '2021-11-15') and (`item_sale_summary`.`ent_id` = 1747964630024192400)) group by `item_sale_summary`.`ent_id`,`item_sale_summary`.`region_code`"
  9.           }
  10.         ]
  11.       }
  12.     },
  13.     {
  14.       "join_optimization": { # optimize阶段
  15.         "select#": 1,
  16.         "steps": [
  17.           {
  18.             "condition_processing": { # 处理搜索条件
  19.               "condition": "WHERE",
  20.               # 原始搜索条件
  21.               "original_condition": "((`item_sale_summary`.`trans_date` between '2021-11-01' and '2021-11-15') and (`item_sale_summary`.`ent_id` = 1747964630024192400))",
  22.               "steps": [
  23.                 {  
  24.                   "transformation": "equality_propagation", # 等值传递转换
  25.                   "resulting_condition": "((`item_sale_summary`.`trans_date` between '2021-11-01' and '2021-11-15') and multiple equal(1747964630024192400, `item_sale_summary`.`ent_id`))"
  26.                 },
  27.                 {
  28.                   "transformation": "constant_propagation",  # 常量传递转换 
  29.                   "resulting_condition": "((`item_sale_summary`.`trans_date` between '2021-11-01' and '2021-11-15') and multiple equal(1747964630024192400, `item_sale_summary`.`ent_id`))"
  30.                 },
  31.                 {
  32.                   "transformation": "trivial_condition_removal",  # 去除没用的条件
  33.                   "resulting_condition": "((`item_sale_summary`.`trans_date` between '2021-11-01' and '2021-11-15') and multiple equal(1747964630024192400, `item_sale_summary`.`ent_id`))"
  34.                 }
  35.               ]
  36.             }
  37.           },
  38.           {
  39.             "substitute_generated_columns": {  # 替换虚拟生成列
  40.             }
  41.           },
  42.           {
  43.             "table_dependencies": [  # 表的依赖信息
  44.               {
  45.                 "table": "`item_sale_summary`",
  46.                 "row_may_be_null": false,
  47.                 "map_bit": 0,
  48.                 "depends_on_map_bits": [
  49.                 ]
  50.               }
  51.             ]
  52.           },
  53.           {
  54.             "ref_optimizer_key_uses": [
  55.               {
  56.                 "table": "`item_sale_summary`",
  57.                 "field": "ent_id",
  58.                 "equals": "1747964630024192400",
  59.                 "null_rejecting": false
  60.               },
  61.               {
  62.                 "table": "`item_sale_summary`",
  63.                 "field": "ent_id",
  64.                 "equals": "1747964630024192400",
  65.                 "null_rejecting": false
  66.               }
  67.             ]
  68.           },
  69.           {
  70.             "rows_estimation": [  # 预估不同单表访问方法的访问成本
  71.               {
  72.                 "table": "`item_sale_summary`",
  73.                 "range_analysis": {
  74.                   "table_scan": { # 全表扫描的行数以及成本
  75.                     "rows": 4245934,
  76.                     "cost": 944293
  77.                   },
  78.                   "potential_range_indexes": [  # 分析可能使用的索引
  79.                     {
  80.                       "index": "unique_index",
  81.                       "usable": true, # 可能被使用
  82.                       "key_parts": [
  83.                         "trans_date",
  84.                         "ent_id",
  85.                         "region_code",
  86.                         "channel_keyword",
  87.                         "item_code",
  88.                         "barcode"
  89.                       ]
  90.                     },
  91.                     {
  92.                       "index": "idx_ent_date_region",
  93.                       "usable": true,
  94.                       "key_parts": [
  95.                         "ent_id",
  96.                         "trans_date",
  97.                         "region_code"
  98.                       ]
  99.                     },
  100.                     {
  101.                       "index": "idx_saled_item",
  102.                       "usable": true,
  103.                       "key_parts": [
  104.                         "ent_id",
  105.                         "region_code",
  106.                         "item_code",
  107.                         "barcode",
  108.                         "trans_date",
  109.                         "channel_keyword"
  110.                       ]
  111.                     }
  112.                   ],
  113.                   "best_covering_index_scan": {
  114.                     "index": "unique_index",
  115.                     "cost": 1.11e6,
  116.                     "chosen": false,
  117.                     "cause": "cost"
  118.                   },
  119.                   "setup_range_conditions": [
  120.                   ],
  121.                   "group_index_range": {
  122.                     "chosen": false,
  123.                     "cause": "not_applicable_aggregate_function"
  124.                   },
  125.                   "analyzing_range_alternatives": {  # 分析各种可能使用的索引的成本
  126.                     "range_scan_alternatives": [
  127.                       {
  128.                         # 使用unique_index的成本分析
  129.                         "index": "unique_index",
  130.                         "ranges": [
  131.                           "0x61cb0f <= trans_date <= 0x6fcb0f"
  132.                         ],
  133.                         "index_dives_for_eq_ranges": true, # 是否使用index dive
  134.                         "rowid_ordered": false, # 使用该索引获取的记录是否按照主键排序
  135.                         "using_mrr": false, # 是否使用mrr
  136.                         "index_only": true, # 是否是索引覆盖访问
  137.                         "rows": 1, # 使用该索引获取的记录条数
  138.                         "cost": 1.21, # 使用该索引的成本
  139.                         "chosen": true # 是否选择该索引
  140.                       },
  141.                       {
  142.                         "index": "idx_ent_date_region",
  143.                         "ranges": [
  144.                           "1747964630024192400 <= ent_id <= 1747964630024192400 AND 0x61cb0f <= trans_date <= 0x6fcb0f"
  145.                         ],
  146.                         "index_dives_for_eq_ranges": true,
  147.                         "rowid_ordered": false,
  148.                         "using_mrr": false,
  149.                         "index_only": false,
  150.                         "rows": 1,
  151.                         "cost": 2.21,
  152.                         "chosen": false,
  153.                         "cause": "cost" # 因为成本太大所以不选择该索引
  154.                       },
  155.                       {
  156.                         "index": "idx_saled_item",
  157.                         "ranges": [
  158.                           "1747964630024192400 <= ent_id <= 1747964630024192400"
  159.                         ],
  160.                         "index_dives_for_eq_ranges": true,
  161.                         "rowid_ordered": false,
  162.                         "using_mrr": false,
  163.                         "index_only": true,
  164.                         "rows": 753872,
  165.                         "cost": 197892,
  166.                         "chosen": false,
  167.                         "cause": "cost"
  168.                       }
  169.                     ],
  170.                     "analyzing_roworder_intersect": {
  171.                       # 分析使用索引合并的成本
  172.                       "usable": false,
  173.                       "cause": "too_few_roworder_scans"
  174.                     }
  175.                   },
  176.                   "chosen_range_access_summary": {
  177.                     # 对于上述单表查询最优的访问方法
  178.                     "range_access_plan": {
  179.                       "type": "range_scan",
  180.                       "index": "unique_index",
  181.                       "rows": 1,
  182.                       "ranges": [
  183.                         "0x61cb0f <= trans_date <= 0x6fcb0f"
  184.                       ]
  185.                     },
  186.                     "rows_for_plan": 1,
  187.                     "cost_for_plan": 1.21,
  188.                     "chosen": true
  189.                   }
  190.                 }
  191.               }
  192.             ]
  193.           },
  194.           {
  195.             # 分析各种可能的执行计划
  196.             "considered_execution_plans": [
  197.               {
  198.                 "plan_prefix": [
  199.                 ],
  200.                 "table": "`item_sale_summary`",
  201.                 "best_access_path": {
  202.                   "considered_access_paths": [
  203.                     {
  204.                       "access_type": "ref",
  205.                       "index": "idx_ent_date_region",
  206.                       "rows": 710.55,
  207.                       "cost": 852.66,
  208.                       "chosen": true
  209.                     },
  210.                     {
  211.                       "access_type": "ref",
  212.                       "index": "idx_saled_item",
  213.                       "rows": 753872,
  214.                       "cost": 197892,
  215.                       "chosen": false
  216.                     },
  217.                     {
  218.                       "rows_to_scan": 1,
  219.                       "access_type": "range",
  220.                       "range_details": {
  221.                         "used_index": "unique_index"
  222.                       },
  223.                       "resulting_rows": 0.1776,
  224.                       "cost": 1.41,
  225.                       "chosen": true,
  226.                       "use_tmp_table": true
  227.                     }
  228.                   ]
  229.                 },
  230.                 "condition_filtering_pct": 100,
  231.                 "rows_for_plan": 0.1776,
  232.                 "cost_for_plan": 1.41,
  233.                 "sort_cost": 0.1776,
  234.                 "new_cost_for_plan": 1.5876,
  235.                 "chosen": true
  236.               }
  237.             ]
  238.           },
  239.           {
  240.             # 尝试给查询添加一些其他的查询条件
  241.             "attaching_conditions_to_tables": {
  242.               "original_condition": "((`item_sale_summary`.`ent_id` = 1747964630024192400) and (`item_sale_summary`.`trans_date` between '2021-11-01' and '2021-11-15'))",
  243.               "attached_conditions_computation": [
  244.               ],
  245.               "attached_conditions_summary": [
  246.                 {
  247.                   "table": "`item_sale_summary`",
  248.                   "attached": "((`item_sale_summary`.`ent_id` = 1747964630024192400) and (`item_sale_summary`.`trans_date` between '2021-11-01' and '2021-11-15'))"
  249.                 }
  250.               ]
  251.             }
  252.           },
  253.           {
  254.             "clause_processing": {
  255.               "clause": "GROUP BY",
  256.               "original_clause": "`item_sale_summary`.`ent_id`,`item_sale_summary`.`region_code`",
  257.               "items": [
  258.                 {
  259.                   "item": "`item_sale_summary`.`ent_id`",
  260.                   "equals_constant_in_where": true
  261.                 },
  262.                 {
  263.                   "item": "`item_sale_summary`.`region_code`"
  264.                 }
  265.               ],
  266.               "resulting_clause_is_simple": true,
  267.               "resulting_clause": "`item_sale_summary`.`region_code`"
  268.             }
  269.           },
  270.           {
  271.             "reconsidering_access_paths_for_index_ordering": {
  272.               "clause": "GROUP BY",
  273.               "steps": [
  274.               ],
  275.               "index_order_summary": {
  276.                 "table": "`item_sale_summary`",
  277.                 "index_provides_order": false,
  278.                 "order_direction": "undefined",
  279.                 "index": "unique_index",
  280.                 "plan_changed": false
  281.               }
  282.             }
  283.           },
  284.           {
  285.             # 再稍稍的改进一下执行计划
  286.             "refine_plan": [
  287.               {
  288.                 "table": "`item_sale_summary`"
  289.               }
  290.             ]
  291.           },
  292.           {
  293.             "creating_tmp_table": {
  294.               "tmp_table_info": {
  295.                 "table": "intermediate_tmp_table",
  296.                 "row_length": 344,
  297.                 "key_length": 349,
  298.                 "unique_constraint": false,
  299.                 "location": "memory (heap)",
  300.                 "row_limit_estimate": 48770
  301.               }
  302.             }
  303.           }
  304.         ]
  305.       }
  306.     },
  307.     {
  308.       # execute阶段
  309.       "join_execution": {
  310.         "select#": 1,
  311.         "steps": [
  312.           {
  313.             "filesort_information": [
  314.               {
  315.                 "direction": "asc",
  316.                 "table": "`item_sale_summary`",
  317.                 "field": "region_code"
  318.               }
  319.             ],
  320.             "filesort_priority_queue_optimization": {
  321.               "usable": false,
  322.               "cause": "not applicable (no LIMIT)"
  323.             },
  324.             "filesort_execution": [
  325.             ],
  326.             "filesort_summary": {
  327.               "rows": 0,
  328.               "examined_rows": 0,
  329.               "number_of_tmp_files": 0,
  330.               "sort_buffer_size": 261632,
  331.               "sort_mode": "<sort_key, packed_additional_fields>"
  332.             }
  333.           }
  334.         ]
  335.       }
  336.     }
  337.   ]
  338. }

想要更加具体的了解其中的含义可参考Chapter 8 Tracing the Optimizer

4. 总结

以上为optimizer trace的简单使用，使用好该功能可以有效帮助我们了解MySQL的优化过程。

整体优化过程虽然看起来杂乱，但主要分成了以下三个部分

  • prepare 阶段
  • optimize 阶段
  • execute 阶段

的基于成本的优化主要集中在 optimize 阶段，对于单表查询来说，我们主要关注 optimize 阶段的 “rows_estimation” 这个过程，这个过程深入分析了对单表查询的各种执行方案的成本；对于多表连接查询来说，我们更多需要关注 “considered_execution_plans” 这个过程，这个过程里会写明各种不同的连接方式所对应的成本。反正优化器最终会选择成本最低的那种方案来作为最终的执行计划，也就是我们使用 EXPLAIN 语句所展现出的那种方案。

5. 参考