MySQL如何选择合适的索引

网络编程 2021-07-05 14:37www.168986.cn编程入门

这篇文章主要介绍了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_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_aess_path": {
     "considered_aess_paths": [
     {
      "aess_type": "scan",
      "rows": 3,
      "cost": 1.6,
      "chosen": true,
      "use_tmp_table": true
     }
     ] / considered_aess_paths /
    } / best_aess_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_putation": [
    ] / attached_conditions_putation /,
    "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`",
    "aess_type": "table_scan"
    }
   ] / refine_plan /
   },
   {
   "reconsidering_aess_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_aess_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_aess_summary": {
     "range_aess_plan": {
      "type": "range_scan",
      "index": "idx_name_age_position",
      "rows": 1,
      "ranges": [
      "zzz < name"
      ] / ranges /
     } / range_aess_plan /,
     "rows_for_plan": 1,
     "cost_for_plan": 2.21,
     "chosen": true
     } / chosen_range_aess_summary /
    } / range_analysis /
    }
   ] / rows_estimation /
   },
   {
   "considered_execution_plans": [
    {
    "plan_prefix": [
    ] / plan_prefix /,
    "table": "`employees`",
    "best_aess_path": {
     "considered_aess_paths": [
     {
      "aess_type": "range",
      "rows": 1,
      "cost": 2.41,
      "chosen": true,
      "use_tmp_table": true
     }
     ] / considered_aess_paths /
    } / best_aess_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_putation": [
    ] / attached_conditions_putation /,
    "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,
    "aess_type": "range"
    }
   ] / refine_plan /
   },
   {
   "reconsidering_aess_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_aess_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如何选择合适的索引，希望对大家有所帮助，如果大家有任何疑问请给我留言，长沙网络推广会及时回复大家的。在此也非常感谢大家对狼蚁SEO网站的支持！如果你觉得本文对你有帮助，欢迎网络推广网站推广转载，烦请注明出处，谢谢！

上一篇：当Mysql行锁遇到复合主键与多列索引详解下一篇：Centos7系统下Mysql主从同步配置方案

MySQL如何选择合适的索引

狼蚁网络搜索

狼蚁网络导航

长沙seo优化

长沙网络营销

长沙网站建设

MySQL如何选择合适的索引

编程语言

狼蚁网络搜索

狼蚁网络导航

长沙seo优化

长沙网络营销

长沙网站建设