Shulou(Shulou.com)05/31 Report--

This article mainly introduces MySQL how to choose the appropriate index, has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Let's take a look at a chestnut.

EXPLAIN select * from employees where name >'a'

If you use the name index to find data, you need to traverse the name field joint index tree, and then go to the primary key index tree according to the traversed primary key value to find the final data, which is more expensive than a full table scan.

You can optimize with an overlay index so that you only need to traverse the federated index tree of the name field to get all the results.

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

You can see that the fields coming out through select are overwritten indexes, and the underlying MySQL uses index optimization. Looking at another case:

EXPLAIN select * from employees where name > 'zzz'

For the execution results of the above two kinds of name >'a 'and name >' zzz', whether mysql finally chooses to walk the index or whether a table involves multiple indexes, and how mysql finally selects the index, you can check through the trace tool. Opening the trace tool will affect mysql performance, so you can only temporarily analyze sql use, and you need to close it immediately after use.

SET SESSION optimizer_trace= "enabled=on", end_markers_in_json=on;-- Open traceSELECT * FROM employees WHERE name >'a 'ORDER BY position;SELECT * FROM information_schema.OPTIMIZER_TRACE

Look at the trace field:

{"steps": [{"join_preparation": {--first stage: SQl preparation phase "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 _ AS `employees from `employees`where (`employees`.`name` >'a') order by `employees`.position` "}] / * steps * /} / * join_preparation * /}, {" join_optimization ": {--second stage: SQL optimization stage" select# ": 1," steps ": [{" condition_processing ": {--conditional 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 dependency details {"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": [--estimated target access cost {"table": "`employees`" "range_analysis": {"table_scan": {--full table scan "rows": 3,-- number of rows scanned "cost": 3.7-- query cost} / * table_scan * /, "potential_range_indices": [--possible index for query {"index": "PRIMARY",-- primary key index "usable": false "cause": "not_applicable"}, {"index": "idx_name_age_position",-- auxiliary index "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": {--analyze the cost of using each index "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": {--third phase: SQL implementation phase "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": "} / * filesort_summary * /}] / * steps * /} / * join_execution * /}] / * steps * /}

The cost of a full table scan is lower than that of an index scan, and the index MySQL will eventually choose a full table scan.

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 `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": ""} / * filesort_summary * /}] / * steps * /} / * join_execution * /}] / * steps * /}

Looking at the trace field, we can see that the cost of index scan is lower than that of full table scan, so MySQL finally chooses index scan.

SET SESSION optimizer_trace= "enabled=off";-- close tra Thank you for reading this article carefully. I hope the article "how to choose the right Index for MySQL" shared by the editor will be helpful to you. At the same time, I hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.