How to choose the right index in MySQL

How to choose the right index in MySQL

Let’s take a look at a chestnut first

EXPLAIN select * from employees where name > 'a';

If you use the name index to search for data, you need to traverse the name field joint index tree, and then use the traversed primary key value to find the final data in the primary key index tree. The cost is higher than scanning the entire table.

You can use covering index optimization, so that you only need to traverse the joint index tree of the name field to get all the results.

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

It can be seen that the fields selected are covering indexes, and MySQL uses index optimization at the bottom layer. Looking at another case:

EXPLAIN select * from employees where name > 'zzz';

For the above two execution results of name>'a' and name>'zzz', whether MySQL finally chooses to use the index or a table involves multiple indexes, how MySQL finally chooses the index, you can use the trace tool to check it out. Turning on the trace tool will affect MySQL performance, so it can only be used to temporarily analyze SQL usage and needs to be closed immediately after use.

SET SESSION optimizer_trace="enabled=on",end_markers_in_json=on; -- Enable trace
SELECT * 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 stage "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": { --Second stage: SQL optimization stage "select#": 1,
  "steps": [
   {
   "condition_processing": { --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 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 scanned rows "cost": 3.7 --Query cost} /* table_scan */,
     "potential_range_indices": [ --Query the possible indexes to be used {
      "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, - whether to use covering index "rows": 3, --- the number of rows to scan for index "cost": 4.61, -- index usage cost "chosen": false, - whether to choose this index "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": { --Phase 3: SQL execution 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": "<sort_key, additional_fields>"
   } /* filesort_summary */
   }
  ] /* steps */
  } /* join_execution */
 }
 ] /* steps */
}

The cost of a full table scan is lower than an index scan, so 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 `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 */
}

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

SET SESSION optimizer_trace="enabled=off"; -- Disable trace

Summarize

The above is my introduction to how to choose a suitable index for MySQL. I hope it will be helpful to you. If you have any questions, please leave me a message and I will reply to you in time. I would also like to thank everyone for their support of the 123WORDPRESS.COM website! If you find this article helpful, please feel free to reprint it and please indicate the source. Thank you!

You may also be interested in:
  • Why does MySQL database index choose to use B+ tree?
  • Exploring MySQL optimizer's choice of index and JOIN order
  • Will mysql's in invalidate the index?
  • Detailed explanation of MySQL combined index and leftmost matching principle
  • How to add indexes to MySQL
  • A case study to thoroughly understand how to correctly use MySQL inndb joint index
  • Detailed analysis of the situations in which database indexes will fail in MySQL
  • In-depth analysis of the leftmost matching principle of MySQL joint index
  • The correct way to use MySQL indexes and detailed explanation of index principles
  • MySQL Index Detailed Explanation
  • MySQL index usage instructions (single-column index and multi-column index)

<<:  CentOS7 network configuration under VMware virtual machine (host wireless Internet access)

>>:  JS implements a detailed plan for the smooth version of the progress bar

Recommend

js to achieve interesting countdown effect

js interesting countdown case, for your reference...

Let's learn about MySQL database

Table of contents 1. What is a database? 2. Class...

How to deploy MongoDB container with Docker

Table of contents What is Docker deploy 1. Pull t...

CSS3 radar scan map sample code

Use CSS3 to achieve cool radar scanning pictures:...

Paragraph layout and line breaks in HTML web pages

The appearance of a web page depends largely on i...

How does MySQL achieve multi-version concurrency?

Table of contents MySQL multi-version concurrency...

Docker deployment of Kafka and Spring Kafka implementation

This article mainly introduces the deployment of ...

Complete step record of Vue encapsulation of general table components

Table of contents Preface Why do we need to encap...

A brief introduction to the general process of web front-end web development

I see many novice students doing front-end develop...

HTML table tag tutorial (3): width and height attributes WIDTH, HEIGHT

By default, the width and height of the table are...

Solution to the problem that input in form cannot be submitted when disabled

I wrote a test program before, in which adding and...

Bootstrap 3.0 learning notes button style

This article mainly explains the style of buttons...

CnBlogs custom blog style sharing

After spending half the night on it, I finally ma...

How to install the green version of MySQL Community Server 5.7.16 and implement remote login

1. Download MySQL Community Server 5.7.16 and ins...

Detailed explanation of compiling and installing MySQL 5.6 on CentOS and installing multiple MySQL instances

--1. Create a new group and user for mysql # user...