MySQL怎样选择合适的索引

2025-03-29 技术教程

这篇文章主要介绍了MySQL怎样选择合适的索引，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。

先来看一个栗子

EXPLAINselect*fromemployeeswherename>'a';

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

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

EXPLAINselectname,age,positionfromemployeeswherename>'a';

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

EXPLAINselect*fromemployeeswherename>'zzz';

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

SETSESSIONoptimizer_trace="enabled=on",end_markers_in_json=on;--开启traceSELECT*FROMemployeesWHEREname>'a'ORDERBYposition;SELECT*FROMinformation_schema.OPTIMIZER_TRACE;

看trace字段：

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

SELECT*FROMemployeesWHEREname>'zzz'ORDERBYposition;SELECT*FROMinformation_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')orderby`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":"ORDERBY","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":"ORDERBY","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":"notapplicable(noLIMIT)"}/*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最终选择索引扫描。

SETSESSIONoptimizer_trace="enabled=off";--关闭tra

感谢你能够认真阅读完这篇文章，希望小编分享的“MySQL怎样选择合适的索引”这篇文章对大家有帮助，同时也希望大家多多支持亿速云，关注亿速云行业资讯频道，更多相关知识等着你来学习!