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

Various ways to achieve the hollowing effect of CSS3 mask layer

This article introduces 4 methods to achieve mask...

Detailed explanation of Promises in JavaScript

Table of contents Basic usage of Promise: 1. Crea...

Solution to JS out-of-precision number problem

The most understandable explanation of the accura...

Simple steps to encapsulate components in Vue projects

Table of contents Preface How to encapsulate a To...

Detailed steps to build an independent mail server on Centos7.9

Table of contents Preface 1. Configure intranet D...

How to handle super large form examples with Vue+ElementUI

Recently, due to business adjustments in the comp...

Payment function implementation in vue project (WeChat payment and Alipay payment)

Table of contents Common payment methods in proje...

Use overflow: hidden to disable page scrollbars

Copy code The code is as follows: html { overflow...

DIV and image horizontal and vertical centering compatible with multiple browsers

The first type: full CSS control, layer floating ...

How to collect Nginx logs using Filebeat

Nginx logs can be used to analyze user address lo...

Detailed explanation of primary keys and transactions in MySQL

Table of contents 1. Comments on MySQL primary ke...

Add unlimited fonts to your website with Google Web Fonts

For a long time, website development was hampered...

CSS3 achieves infinite scrolling/carousel effect of list

Effect Preview Ideas Scroll the current list to t...

MySQL8 Installer version graphic tutorial

Installation The required documents are provided ...

Example of Vue routing listening to dynamically load the same page

Table of contents Scenario Analysis Development S...