MySQL如何选择合适的索引

先来看一个栗子

EXPLAIN select * from employees where name > ‘a’;

如果用name索引查找数据需要遍历name字段联合索引树，然后根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高。

可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就可以拿到所有的结果。

EXPLAIN select name,age,position from employees where name > ‘a’;

可以看到通过select出的字段是覆盖索引，MySQL底层使用了索引优化。在看另一个case：

EXPLAIN select * from employees where name > ‘zzz’;

对于上面的这两种 name>’a’ 和 name>’zzz’的执行结果， mysql最终是否选择走索引或者一张表涉及多个索引， mysql最终如何选择索引，可以通过trace工具来一查究竟，开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后需要立即关闭。

SET SESSION optimizer_trace=”enabled=on”,end_markers_in_json=on; –开启trace

SELECT * FROM employees WHERE name > ‘a’ ORDER BY position;

SELECT * FROM information_schema.OPTIMIZER_TRACE;看trace字段：

{

“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` > ‘a’) order by `employees`.`position`”

   }

  ] /* steps */

  } /* join_preparation */

},

{

  “join_optimization”: { –第二阶段：SQL优化阶段

  “select#”: 1,

  “steps”: [

   {

   “condition_processing”: { –条件处理

    “condition”: “WHERE”,

    “original_condition”: “(`employees`.`name` > ‘a’)”,

    “steps”: [

    {

     “transformation”: “equality_propagation”,

     “resulting_condition”: “(`employees`.`name` > ‘a’)”

    },

    {

     “transformation”: “constant_propagation”,

     “resulting_condition”: “(`employees`.`name` > ‘a’)”

    },

    {

     “transformation”: “trivial_condition_removal”,

     “resulting_condition”: “(`employees`.`name` > ‘a’)”

    }

    ] /* steps */

   } /* condition_processing */

   },

   {

   “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_аnalysis”: {

     “table_scan”: { –全表扫描情况

     “rows”: 3, –扫描行数

     “cost”: 3.7 –查询成本

     } /* table_scan */,

     “potential_range_indices”: [ –查询可能使用的索引

     {

      “index”: “PRIMARY”, –主键索引

      “usable”: false,

      “cause”: “not_APPlicable”

     },

     {

      “index”: “idx_name_age_position”, –辅助索引

      “usable”: true,

      “key_parts”: [

      “name”,

      “age”,

      “position”,

      “id”

      ] /* key_parts */

     },

     {

      “index”: “idx_age”,

      “usable”: false,

      “cause”: “not_applicable”

     }

     ] /* potential_range_indices */,

     “setup_range_conditions”: [

     ] /* setup_range_conditions */,

     “group_index_range”: {

     “chosen”: false,

     “cause”: “not_group_by_or_distinct”

     } /* group_index_range */,

     “аnalyzing_range_alternatives”: { ‐‐分析各个索引使用成本

     “range_scan_alternatives”: [

      {

      “index”: “idx_name_age_position”,

      “ranges”: [

       “a < name”

      ] /* ranges */,

      “index_dives_for_eq_ranges”: true,

      “rowid_ordered”: false,

      “using_mrr”: false,

      “index_only”: false, ‐‐是否使用覆盖索引

      “rows”: 3, –‐‐索引扫描行数

      “cost”: 4.61, –索引使用成本

      “chosen”: false, ‐‐是否选择该索引

      “cause”: “cost”

      }

     ] /* range_scan_alternatives */,

     “аnalyzing_roworder_intersect”: {

      “usable”: false,

      “cause”: “too_few_roworder_scans”

     } /* аnalyzing_roworder_intersect */

     } /* аnalyzing_range_alternatives */

    } /* range_аnalysis */

    }

   ] /* rows_estimation */

   },

   {

   “considered_execution_plans”: [

    {

    “plan_prefix”: [

    ] /* plan_prefix */,

    “table”: “`employees`”,

    “best_access_path”: {

     “considered_access_paths”: [

     {

      “access_type”: “scan”,

      “rows”: 3,

      “cost”: 1.6,

      “chosen”: true,

      “use_tmp_table”: true

     }

     ] /* considered_access_paths */

    } /* best_access_path */,

    “cost_for_plan”: 1.6,

    “rows_for_plan”: 3,

    “sort_cost”: 3,

    “new_cost_for_plan”: 4.6,

    “chosen”: true

    }

   ] /* considered_execution_plans */

   },

   {

   “attaching_conditions_to_tables”: {

    “original_condition”: “(`employees`.`name` > ‘a’)”,

    “attached_conditions_computation”: [

    ] /* attached_conditions_computation */,

    “attached_conditions_summary”: [

    {

     “table”: “`employees`”,

     “attached”: “(`employees`.`name` > ‘a’)”

    }

    ] /* attached_conditions_summary */

   } /* attaching_conditions_to_tables */

   },

   {

   “clause_processing”: {

    “clause”: “ORDER BY”,

    “original_clause”: “`employees`.`position`”,

    “items”: [

    {

     “item”: “`employees`.`position`”

    }

    ] /* items */,

    “resulting_clause_is_simple”: true,

    “resulting_clause”: “`employees`.`position`”

   } /* clause_processing */

   },

   {

   “refine_plan”: [

    {

    “table”: “`employees`”,

    “access_type”: “table_scan”

    }

   ] /* refine_plan */

   },

   {

   “reconsidering_access_paths_for_index_ordering”: {

    “clause”: “ORDER BY”,

    “index_order_summary”: {

    “table”: “`employees`”,

    “index_provides_order”: false,

    “order_direction”: “undefined”,

    “index”: “unknown”,

    “plan_changed”: false

    } /* index_order_summary */

   } /* reconsidering_access_paths_for_index_ordering */

   }

  ] /* steps */

  } /* join_optimization */

},

{

  “join_execution”: { –第三阶段：SQL执行阶段

  “select#”: 1,

  “steps”: [

   {

   “filesort_information”: [

    {

    “direction”: “asc”,

    “table”: “`employees`”,

    “field”: “position”

    }

   ] /* filesort_information */,

   “filesort_priority_queue_optimization”: {

    “usable”: false,

    “cause”: “not applicable (no LIMIT)”

   } /* filesort_priority_queue_optimization */,

   “filesort_execution”: [

   ] /* filesort_execution */,

   “filesort_summary”: {

    “rows”: 3,

    “examined_rows”: 3,

    “number_of_tmp_files”: 0,

    “sort_buffer_size”: 202204,

    “sort_mode”: “<sort_key, additional_fields>”

   } /* filesort_summary */

   }

  ] /* steps */

  } /* join_execution */

}

] /* steps */

}全表扫描的成本低于索引扫描， 索引MySQL最终会选择全表扫描。

SELECT * FROM employees WHERE name > ‘zzz’ ORDER BY position;

SELECT * FROM information_schema.OPTIMIZER_TRACE;

{

“steps”: [

{

  “join_preparation”: {

  “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` > ‘zzz’) order by `employees`.`position`”

   }

  ] /* steps */

  } /* join_preparation */

},

{

  “join_optimization”: {

  “select#”: 1,

  “steps”: [

   {

   “condition_processing”: {

    “condition”: “WHERE”,

    “original_condition”: “(`employees`.`name` > ‘zzz’)”,

    “steps”: [

    {

     “transformation”: “equality_propagation”,

     “resulting_condition”: “(`employees`.`name` > ‘zzz’)”

    },

    {

     “transformation”: “constant_propagation”,

     “resulting_condition”: “(`employees`.`name` > ‘zzz’)”

    },

    {

     “transformation”: “trivial_condition_removal”,

     “resulting_condition”: “(`employees`.`name` > ‘zzz’)”

    }

    ] /* steps */

   } /* condition_processing */

   },

   {

   “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_аnalysis”: {

     “table_scan”: {

     “rows”: 3,

     “cost”: 3.7

     } /* table_scan */,

     “potential_range_indices”: [

     {

      “index”: “PRIMARY”,

      “usable”: false,

      “cause”: “not_applicable”

     },

     {

      “index”: “idx_name_age_position”,

      “usable”: true,

      “key_parts”: [

      “name”,

      “age”,

      “position”,

      “id”

      ] /* key_parts */

     },

     {

      “index”: “idx_age”,

      “usable”: false,

      “cause”: “not_applicable”

     }

     ] /* potential_range_indices */,

     “setup_range_conditions”: [

     ] /* setup_range_conditions */,

     “group_index_range”: {

     “chosen”: false,

     “cause”: “not_group_by_or_distinct”

     } /* group_index_range */,

     “аnalyzing_range_alternatives”: {

     “range_scan_alternatives”: [

      {

      “index”: “idx_name_age_position”,

      “ranges”: [

       “zzz < name”

      ] /* ranges */,

      “index_dives_for_eq_ranges”: true,

      “rowid_ordered”: false,

      “using_mrr”: false,

      “index_only”: false,

      “rows”: 1,

      “cost”: 2.21,

      “chosen”: true

      }

     ] /* range_scan_alternatives */,

     “аnalyzing_roworder_intersect”: {

      “usable”: false,

      “cause”: “too_few_roworder_scans”

     } /* аnalyzing_roworder_intersect */

     } /* аnalyzing_range_alternatives */,

     “chosen_range_access_summary”: {

     “range_access_plan”: {

      “type”: “range_scan”,

      “index”: “idx_name_age_position”,

      “rows”: 1,

      “ranges”: [

      “zzz < name”

      ] /* ranges */

     } /* range_access_plan */,

     “rows_for_plan”: 1,

     “cost_for_plan”: 2.21,

     “chosen”: true

     } /* chosen_range_access_summary */

    } /* range_аnalysis */

    }

   ] /* rows_estimation */

   },

   {

   “considered_execution_plans”: [

    {

    “plan_prefix”: [

    ] /* plan_prefix */,

    “table”: “`employees`”,

    “best_access_path”: {

     “considered_access_paths”: [

     {

      “access_type”: “range”,

      “rows”: 1,

      “cost”: 2.41,

      “chosen”: true,

      “use_tmp_table”: true

     }

     ] /* considered_access_paths */

    } /* best_access_path */,

    “cost_for_plan”: 2.41,

    “rows_for_plan”: 1,

    “sort_cost”: 1,

    “new_cost_for_plan”: 3.41,

    “chosen”: true

    }

   ] /* considered_execution_plans */

   },

   {

   “attaching_conditions_to_tables”: {

    “original_condition”: “(`employees`.`name` > ‘zzz’)”,

    “attached_conditions_computation”: [

    ] /* attached_conditions_computation */,

    “attached_conditions_summary”: [

    {

     “table”: “`employees`”,

     “attached”: “(`employees`.`name` > ‘zzz’)”

    }

    ] /* attached_conditions_summary */

   } /* attaching_conditions_to_tables */

   },

   {

   “clause_processing”: {

    “clause”: “ORDER BY”,

    “original_clause”: “`employees`.`position`”,

    “items”: [

    {

     “item”: “`employees`.`position`”

    }

    ] /* items */,

    “resulting_clause_is_simple”: true,

    “resulting_clause”: “`employees`.`position`”

   } /* clause_processing */

   },

   {

   “refine_plan”: [

    {

    “table”: “`employees`”,

    “pushed_index_condition”: “(`employees`.`name` > ‘zzz’)”,

    “table_condition_attached”: null,

    “access_type”: “range”

    }

   ] /* refine_plan */

   },

   {

   “reconsidering_access_paths_for_index_ordering”: {

    “clause”: “ORDER BY”,

    “index_order_summary”: {

    “table”: “`employees`”,

    “index_provides_order”: false,

    “order_direction”: “undefined”,

    “index”: “idx_name_age_position”,

    “plan_changed”: false

    } /* index_order_summary */

   } /* reconsidering_access_paths_for_index_ordering */

   }

  ] /* steps */

  } /* join_optimization */

},

{

  “join_execution”: {

  “select#”: 1,

  “steps”: [

   {

   “filesort_information”: [

    {

    “direction”: “asc”,

    “table”: “`employees`”,

    “field”: “position”

    }

   ] /* filesort_information */,

   “filesort_priority_queue_optimization”: {

    “usable”: false,

    “cause”: “not applicable (no LIMIT)”

   } /* filesort_priority_queue_optimization */,

   “filesort_execution”: [

   ] /* filesort_execution */,

   “filesort_summary”: {

    “rows”: 0,

    “examined_rows”: 0,

    “number_of_tmp_files”: 0,

    “sort_buffer_size”: 202204,

    “sort_mode”: “<sort_key, additional_fields>”

   } /* filesort_summary */

   }

  ] /* steps */

  } /* join_execution */

}

] /* steps */

}查看trace字段可知索引扫描的成本低于全表扫描的成本，所以MySQL最终选择索引扫描。

SET SESSION optimizer_trace=”enabled=off”; — 关闭trace
总结

以上所述是小编给大家介绍的MySQL如何选择合适的索引，希望对大家有所帮助，如果大家有任何疑问请给我留言，小编会及时回复大家的。在此也非常感谢大家对共生网络网站的支持！如果你觉得本文对你有帮助，欢迎转载，烦请注明出处，谢谢！