MySQL如何选择合适的索引

数据库  /  管理员 发布于 6年前   204

先来看一个栗子

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; --开启traceSELECT * 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_analysis": {     "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 */,     "analyzing_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 */,     "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": [     {      "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": 200704,    "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_analysis": {     "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 */,     "analyzing_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 */,     "analyzing_roworder_intersect": {      "usable": false,      "cause": "too_few_roworder_scans"     } /* analyzing_roworder_intersect */     } /* analyzing_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_analysis */    }   ] /* 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": 200704,    "sort_mode": "<sort_key, additional_fields>"   } /* filesort_summary */   }  ] /* steps */  } /* join_execution */ } ] /* steps */}

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

SET SESSION optimizer_trace="enabled=off"; -- 关闭trace

总结

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