介绍

对于 MySQL 5.6 以及之前的版本来说，查询优化器就像是一个黑盒子一样，
只能通过 explain 语句查看最后优化器决定使用的执行计划，却无法知道查询优化器为什么做这个决策。
在 MySQL 5.6 以及之后的版本中，设计 MySQL 的人提出了一个 optimizer trace 的功能，
这个功能可以方便的查看查询优化器生成执行计划的整个过程。
这个功能的开启与关闭由系统变量 optimizer_trace 决定：show variables like ‘optimizer_trace’;
图片.png
enabled 的值为 off，表明这个功能默认是关闭的。
one_line 的值是控制输出格式的，如果值为 on，那么所有输出都将在一行中展示。
如果想打开这个功能，必须首先把 enabled 的值改为 on：set optimizer_trace=”enabled=on”;
图片.png
然后就可以输入要查看优化过程的查询语句，当该查询语句执行完成后，就可以到 information_schema 数据库下的 optimizer_trace 表中查看完整的优化过程。
这个 optimizer_trace 表共有 4 个列，分别是：

  • query：表示我们的查询语句。
  • trace：表示优化过程的 json 格式文本。
  • missing_bytes_beyound_max_mem_size：由于优化过程可能会输出很多，如果超过某个限制，多余的文本将不会被显示，这个字段展示了被忽略文本的字节数。
  • insufficient_privileges：表示是否没有权限查看优化过程，默认值是 0，只有某些特殊情况下才会是 1。0 代表有权限，1 代表无权限。（单词的意思为：权限不够）

完整的使用 optimizer trace 功能的步骤如下：

json 文本

  1. # 1. 打开 optimizer trace 功能 (默认是关闭的):
  2. set optimizer_trace="enabled=on";
  4. # 2. 这里输入你自己的查询语句
  5. select ...; 
  7. # 3. 从 optimizer_trace 表中查看上一个查询的优化过程
  8. select * from information_schema.OPTIMIZER_TRACE;
  10. # 4. 如果需要观察其他语句执行的优化过程，重复上边的第 2、3 步
  12. # 5. 停止查看语句的优化过程，把 optimizer trace 功能关闭
  13. set optimizer_trace="enabled=off";

比如：explain select * from s1 where key1 > ‘a’ and key2 < 1000000 and key3 in ( ‘a’, ‘b’, ‘c’ ) and common_field = ‘abc’;
执行计划如下表：

id select_type table type possible_keys key rows filtered
1 simple s1 range idx_key2,idx_key1,idx_key3 idx_key2 12 0.42

可以看到该查询可能使用到的索引有 3 个，
那么为什么查询优化器最终选择了 idx_key2 而不选择其他的索引或者全表扫描呢？
这时候就可以通过 otpimzer trace 功能来查看查询优化器的具体工作过程来寻找答案：

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

总结

这只是优化器执行过程中的一小部分，
设计 MySQL 的人可能会在之后的版本中添加更多的优化过程信息。
虽然复杂但是有规律，优化过程大致分了三个阶段：

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

我们所说的基于成本的优化主要集中在 optimize 阶段。
对于单表查询来说，我们主要关注 optimize 阶段的 “rows_estimation” 这个过程，
“rows_estimation” 深入分析了对单表查询的各种执行方案的成本。

对于多表连接查询来说，我们更多需要关注 “considered_execution_plans” 这个过程，
“considered_execution_plans” 里会写明各种不同的连接方式所对应的成本。
查询优化器最终会选择成本最低的那种方案来作为最终的执行计划，
也就是使用 explain 语句所展现出的那种方案。