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_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 */,                 <360>                   "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