mysql通过trace分析优化器如何选择执行计划

在我们调优MySQL的SQL时候，通常使用explain进行查看sql的执行计划。至于优化器为什么会这样选择，就无从得知。如果你想了解为什么，那么可以通过trace文件能够进一步了解为什么优化器选择A执行计划而不选择B执行计划.帮助我们更好的理解优化器的行为.

打开trace,并设置格式为Json

SET OPTIMIZER_TRACE=“enabled=on”,END_MARKERS_IN_JSON=on;

设置trace使用的内存大小,避免解析过程内存不足,文件显示不完整.

SET OPTIMIZER_TRACE_MAX_MEM_SIZE=1000000;

执行你的sql语句

select * from a, b where a.id=b.sid;

查看trace信息

SELECT * FROM INFORMATION_SCHEMA.OPTIMIZER_TRACE;

TRACE json文件

{
"steps": [
{
  "join_preparation": {  ---优化准备工作
    "select#": 1,
    "steps": [
      {
        "expanded_query": "/* select#1 */ select `a`.`id` AS `id`,`a`.`name` AS `name`,`a`.`age` AS `age`,`b`.`id` AS `id`,`b`.`sid` AS `sid`,`b`.`name` AS `name`,`b`.`score` AS `score` from `a` join `b` where (`a`.`id` = `b`.`sid`)"
      }
    ] /* steps */
  } /* join_preparation */
},
{
  "join_optimization": {---优化工作的主要阶段,包括逻辑优化和物理优化两个阶段
    "select#": 1,
    "steps": [---优化工作的主要阶段, 逻辑优化阶段  
      {
        "condition_processing": {  ---逻辑优化,条件化简  
          "condition": "WHERE",
          "original_condition": "(`a`.`id` = `b`.`sid`)",
          "steps": [
            {
              "transformation": "equality_propagation",  ---逻辑优化,条件化简,等式处理
              "resulting_condition": "(`a`.`id` = `b`.`sid`)"
            },
            {
              "transformation": "constant_propagation", ---逻辑优化,条件化简,常量处理
              "resulting_condition": "(`a`.`id` = `b`.`sid`)"
            },
            {
              "transformation": "trivial_condition_removal",  ---逻辑优化,条件化简,条件去除
              "resulting_condition": "(`a`.`id` = `b`.`sid`)"
            }
          ] /* steps */
        } /* condition_processing */
      }, ---逻辑优化,条件化简,结束 
      {
        "substitute_generated_columns": {
        } /* substitute_generated_columns */
      },
      {
        "table_dependencies": [ ---逻辑优化, 找出表之间的相互依赖关系. 非直接可用的优化方式.
          {
            "table": "`a`", ---表名
            "row_may_be_null": false, ---是否有null值，flase是没有
            "map_bit": 0,
            "depends_on_map_bits": [
            ] /* depends_on_map_bits */
          },
          {
            "table": "`b`",
            "row_may_be_null": false,
            "map_bit": 1,
            "depends_on_map_bits": [
            ] /* depends_on_map_bits */
          }
        ] /* table_dependencies */
      },
      {
        "ref_optimizer_key_uses": [ ---逻辑优化,  找出备选的索引  
          {
            "table": "`a`",  
            "field": "id", --索引字段
            "equals": "`b`.`sid`",  --连接的等值字段
            "null_rejecting": false
          },
          {
            "table": "`b`",
            "field": "sid",
            "equals": "`a`.`id`",
            "null_rejecting": false
          }
        ] /* ref_optimizer_key_uses */
      },
      {
        "rows_estimation": [  ---逻辑优化, 估算每个表的元组个数. 单表上进行全表扫描和索引扫描的代价估算. 每个索引都估算索引扫描代价  
          {
            "table": "`a`",
            "table_scan": { ---逻辑优化, 估算每个表的元组个数. 单表上进行全表扫描的代价
              "rows": 10,
              "cost": 1
            } /* table_scan */
          },
          {
            "table": "`b`",
            "table_scan": {
              "rows": 21,
              "cost": 1
            } /* table_scan */
          }
        ] /* rows_estimation */
      },
      {
        "considered_execution_plans": [  ---物理优化, 开始多表连接的物理优化计算
          {
            "plan_prefix": [
            ] /* plan_prefix */,
            "table": "`a`",
            "best_access_path": {
              "considered_access_paths": [
                {
                  "access_type": "ref",  ---物理优化, 计算indx_user索引上使用ref方查找的花费
                  "index": "PRIMARY",
                  "usable": false,
                  "chosen": false  ---物理优化, 本应该比较所有的可用索引,即打印出多个格式相同的但索引名不同的内容,这里却没有。推测是bug--没有遍历每一个索引. 
                },
                {
                  "rows_to_scan": 10,
                  "access_type": "scan",
                  "resulting_rows": 10,
                  "cost": 3,
                  "chosen": true
                }
              ] /* considered_access_paths */
            } /* best_access_path */,
            "condition_filtering_pct": 100,
            "rows_for_plan": 10,
            "cost_for_plan": 3,
            "rest_of_plan": [
              {
                "plan_prefix": [
                  "`a`"
                ] /* plan_prefix */,
                "table": "`b`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "ref",
                      "index": "idx_sid",
                      "rows": 2.1,
                      "cost": 25.2,
                      "chosen": true
                    },
                    {
                      "rows_to_scan": 21,
                      "access_type": "scan",
                      "using_join_cache": true,
                      "buffers_needed": 1,
                      "resulting_rows": 21,
                      "cost": 43.006,
                      "chosen": false
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 21,
                "cost_for_plan": 28.2,
                "chosen": true
              }
            ] /* rest_of_plan */
          },
          {
            "plan_prefix": [
            ] /* plan_prefix */,
            "table": "`b`",
            "best_access_path": {
              "considered_access_paths": [
                {
                  "access_type": "ref",
                  "index": "idx_sid",
                  "usable": false,
                  "chosen": false
                },
                {
                  "rows_to_scan": 21,
                  "access_type": "scan",
                  "resulting_rows": 21,
                  "cost": 5.2,
                  "chosen": true
                }
              ] /* considered_access_paths */
            } /* best_access_path */,
            "condition_filtering_pct": 100,
            "rows_for_plan": 21,
            "cost_for_plan": 5.2,
            "rest_of_plan": [
              {
                "plan_prefix": [
                  "`b`"
                ] /* plan_prefix */,
                "table": "`a`",
                "best_access_path": {
                  "considered_access_paths": [
                    {
                      "access_type": "eq_ref",
                      "index": "PRIMARY",
                      "rows": 1,
                      "cost": 25.2,
                      "chosen": true,
                      "cause": "clustered_pk_chosen_by_heuristics"
                    },
                    {
                      "rows_to_scan": 10,
                      "access_type": "scan",
                      "using_join_cache": true,
                      "buffers_needed": 1,
                      "resulting_rows": 10,
                      "cost": 43.013,
                      "chosen": false
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 21,
                "cost_for_plan": 30.4,
                "pruned_by_cost": true
              }
            ] /* rest_of_plan */
          }
        ] /* considered_execution_plans */
      },
      {
        "attaching_conditions_to_tables": {
          "original_condition": "(`a`.`id` = `b`.`sid`)",
          "attached_conditions_computation": [
          ] /* attached_conditions_computation */,
          "attached_conditions_summary": [
            {
              "table": "`a`",
              "attached": null
            },
            {
              "table": "`b`",
              "attached": "(`a`.`id` = `b`.`sid`)"
            }
          ] /* attached_conditions_summary */
        } /* attaching_conditions_to_tables */
      },
      {
        "refine_plan": [
          {
            "table": "`a`"
          },
          {
            "table": "`b`",
            "pushed_index_condition": "(`a`.`id` = `b`.`sid`)",
            "table_condition_attached": null
          }
        ] /* refine_plan */
      }
    ] /* steps */
  } /* join_optimization */
},
{
  "join_execution": {
    "select#": 1,
    "steps": [
    ] /* steps */
  } /* join_execution */
}
] /* steps */
}

参考资料：
https://dev.mysql.com/doc/refman/5.6/en/optimizer-trace-table.html
https://www.cnblogs.com/katec/p/9239985.html