mysql之trace工具查看mysql如何选择索引

1 mysql开启trace

--开启trace工具，分析mysql如何做索引的，走那些索引
set session optimizer_trace="enabled=on",end_markers_in_json=on;

select * from employees where name>'aaaa';
select * from information_schema.OPTIMIZER_TRACE; 

trace字段中json格式化如下

{
  "steps": [
    {
      "join_preparation": {    -‐第一阶段：SQL准备阶段
        "select#": 1,
        "steps": [
          {
            "expanded_query": "/* select#1 */ select `employees`.`id` AS `id`,`employees`.`name` AS `name`,`employees`.`age` AS `age`,`employees`.`position` AS `position`,`employees`.`hire_time` AS `hire_time` from `employees` where (`employees`.`name` > 'aaaa')"
          }
        ] /* steps */
      } /* join_preparation */
    },
    {
      "join_optimization": {  ‐‐第二阶段：SQL优化阶段
        "select#": 1,
        "steps": [
          {
            "condition_processing": {   ‐‐条件处理
              "condition": "WHERE",
              "original_condition": "(`employees`.`name` > 'aaaa')",
              "steps": [
                {
                  "transformation": "equality_propagation",
                  "resulting_condition": "(`employees`.`name` > 'aaaa')"
                },
                {
                  "transformation": "constant_propagation",
                  "resulting_condition": "(`employees`.`name` > 'aaaa')"
                },
                {
                  "transformation": "trivial_condition_removal",
                  "resulting_condition": "(`employees`.`name` > 'aaaa')"
                }
              ] /* steps */
            } /* condition_processing */
          },
          {
            "substitute_generated_columns": {
            } /* substitute_generated_columns */
          },
          {
            "table_dependencies": [  ‐‐表依赖详情
              {
                "table": "`employees`",
                "row_may_be_null": false,
                "map_bit": 0,
                "depends_on_map_bits": [
                ] /* depends_on_map_bits */
              }
            ] /* table_dependencies */
          },
          {
            "ref_optimizer_key_uses": [
            ] /* ref_optimizer_key_uses */
          },
          {
            "rows_estimation": [    ‐‐预估表的访问成本
              {
                "table": "`employees`",
                "range_analysis": {
                  "table_scan": {   ‐‐全表扫描情况
                    "rows": 27261,   ‐‐扫描行数
                    "cost": 5615.3     ‐‐查询成本
                  } /* table_scan */,
                  "potential_range_indexes": [   ‐‐查询可能使用的索引
                    {
                      "index": "PRIMARY",   ‐‐主键索引
                      "usable": false,
                      "cause": "not_applicable"
                    },
                    {
                      "index": "idx_name_age_position",     ‐‐辅助索引
                      "usable": true,
                      "key_parts": [
                        "name",
                        "age",
                        "position",
                        "id"
                      ] /* key_parts */
                    }
                  ] /* potential_range_indexes */,
                  "setup_range_conditions": [
                  ] /* setup_range_conditions */,
                  "group_index_range": {
                    "chosen": false,
                    "cause": "not_group_by_or_distinct"
                  } /* group_index_range */,
                  "analyzing_range_alternatives": {    ‐‐分析各个索引使用成本
                    "range_scan_alternatives": [
                      {
                        "index": "idx_name_age_position",
                        "ranges": [
                          "aaaa < name"     ‐‐索引使用范围
                        ] /* ranges */,
                        "index_dives_for_eq_ranges": true,
                        "rowid_ordered": false,    ‐‐使用该索引获取的记录是否按照主键排序
                        "using_mrr": false,
                        "index_only": false,   ‐‐是否使用覆盖索引
                        "rows": 13630,   ‐‐索引扫描行数
                        "cost": 16357,    ‐‐索引使用成本
                        "chosen": false,   ‐是否选择该索引
                        "cause": "cost"
                      }
                    ] /* range_scan_alternatives */,
                    "analyzing_roworder_intersect": {
                      "usable": false,
                      "cause": "too_few_roworder_scans"
                    } /* analyzing_roworder_intersect */
                  } /* analyzing_range_alternatives */
                } /* range_analysis */
              }
            ] /* rows_estimation */
          },
          {
            "considered_execution_plans": [
              {
                "plan_prefix": [
                ] /* plan_prefix */,
                "table": "`employees`",
                "best_access_path": {                            ‐‐最优访问路径
                  "considered_access_paths": [            ‐‐最终选择的访问路径
                    {
                      "rows_to_scan": 27261,
                      "access_type": "scan",     ‐‐访问类型：为scan，全表扫描
                      "resulting_rows": 27261,
                      "cost": 5613.2,
                      "chosen": true        ‐‐确定选择
                    }
                  ] /* considered_access_paths */
                } /* best_access_path */,
                "condition_filtering_pct": 100,
                "rows_for_plan": 27261,
                "cost_for_plan": 5613.2,
                "chosen": true
              }
            ] /* considered_execution_plans */
          },
          {
            "attaching_conditions_to_tables": {
              "original_condition": "(`employees`.`name` > 'aaaa')",
              "attached_conditions_computation": [
              ] /* attached_conditions_computation */,
              "attached_conditions_summary": [
                {
                  "table": "`employees`",
                  "attached": "(`employees`.`name` > 'aaaa')"
                }
              ] /* attached_conditions_summary */
            } /* attaching_conditions_to_tables */
          },
          {
            "refine_plan": [
              {
                "table": "`employees`"
              }
            ] /* refine_plan */
          }
        ] /* steps */
      } /* join_optimization */
    },
    {
      "join_execution": {    ‐‐第三阶段：SQL执行阶段
        "select#": 1,
        "steps": [
        ] /* steps */
      } /* join_execution */
    }
  ] /* steps */
}

重要看以下信息：

• “rows_estimation”: [ ‐‐预估表的访问成本
• “potential_range_indexes”: [ ‐‐查询可能使用的索引
• “analyzing_range_alternatives”: { ‐‐分析各个索引使用成本
• “best_access_path”: { ‐‐最优访问路径

结论：全表扫描的成本低于索引扫描，所以mysql最终选择全表扫描

关闭trace工具，因为trace工具会影响mysql性能

set session optimizer_trace="enabled=off"