MySQL如何选择合适的索引

吾爱主题阅读：135 2024-04-05 14:22:52 评论：0

先来看一个栗子

1	`explain select * from employees where name >` `'a'` `;`

如果用name索引查找数据需要遍历name字段联合索引树，然后根据遍历出来的主键值去主键索引树里再去查出最终数据，成本比全表扫描还高。
可以用覆盖索引优化，这样只需要遍历name字段的联合索引树就可以拿到所有的结果。

1	`explain select name,age,position from employees where name >` `'a'` `;`

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

1	`explain select * from employees where name >` `'zzz'` `;`

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 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最终会选择全表扫描。

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 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 */ }