MySQL怎样选择合适的索引

50次阅读
没有评论

共计 9683 个字符,预计需要花费 25 分钟才能阅读完成。

自动写代码机器人,免费开通

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

先来看一个栗子

EXPLAIN select * from employees where name    a

MySQL 怎样选择合适的索引

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

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

EXPLAIN select name,age,position from employees where name    a

MySQL 怎样选择合适的索引

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

EXPLAIN select * from employees where name    zzz

MySQL 怎样选择合适的索引

对于上面的这两种 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_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  --  关闭 tra

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

向 AI 问一下细节

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-12-04发表,共计9683字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)