Detailed explanation of MySQL combined index and leftmost matching principle

Detailed explanation of MySQL combined index and leftmost matching principle

Preface

I have seen many articles about the leftmost prefix matching of MySQL joint index on the Internet before, and I thought I understood its principle. Recently, when I communicated with the interviewer during the interview, I found that I had missed some things. Here I will organize the content in this regard.

When to create a composite index?

When our where query has multiple condition queries, we need to create a composite index for the query columns

Why not create an index on every column?

  • Reduce overhead
  • Covering Index
  • High efficiency

Reduce overhead: If you create a composite index for col1, col2, and col3, it is equivalent to creating three indexes: (col1), (col1, col2), and (col1, col2, col3). Covering index: If you query SELECT col1, col2, col3 FROM table name, since the queried fields exist in the index page, you can get them directly from the index without going back to the table for query.

High efficiency: Create indexes for col1, col2, and col3 respectively. MySQL will only select the column with the highest recognition as the index. Assume there are 1 million data, and an index filters out 10% of the data, then 10 million data can be filtered out; for a combined index, 1 million*10%*10%*10%=1000 data can be filtered out.

Leftmost matching principle

Suppose we create a composite index of (col1, col2, col3), which is equivalent to sorting the col1 column. That is, we create a composite index based on the leftmost column. As long as the query condition contains the leftmost column, the query will use the index.

Create a test table

CREATE TABLE `student` (
 `id` int(11) NOT NULL,
 `name` varchar(10) NOT NULL,
 `age` int(11) NOT NULL,
 PRIMARY KEY (`id`),
 KEY `idx_id_name_age` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Fill 1 million test data

DROP PROCEDURE pro10;
CREATE PROCEDURE pro10()
BEGIN
	DECLARE i INT;
	DECLARE char_str varchar(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789';
	DECLARE return_str varchar(255) DEFAULT '';
	DECLARE age INT;
	SET i = 1;
	WHILE i < 5000000 do
		SET return_str = substring(char_str, FLOOR(1 + RAND()*62), 8);
		SET i = i+1;
		SET age = FLOOR(RAND() * 100);
		INSERT INTO student(id, name, age) values(i, return_str, age);
	END WHILE;
END;

CALL pro10();

Scenario Testing

EXPLAIN SELECT * FROM student WHERE id = 2;

You can see that the query uses the index

EXPLAIN SELECT * FROM student WHERE id = 2 AND name = 'defghijk';

You can see that the query uses the index

EXPLAIN SELECT * FROM student WHERE id = 2 AND name = 'defghijk' and age = 8;

You can see that the query uses the index

EXPLAIN SELECT * FROM student WHERE id = 2 AND age = 8;

You can see that the query uses the index

EXPLAIN SELECT * FROM student WHERE name = 'defghijk' AND age = 8;

It can be seen that the query does not use the index, the type is index, the number of query rows is 4989449, and almost the entire table is scanned. Since the combined index only sorts the leftmost column, only the name and age can be fully scanned.

EXPLAIN SELECT * FROM student WHERE name = 'defghijk' AND id = 2;

EXPLAIN SELECT * FROM student WHERE age = 8 AND id = 2;

EXPLAIN SELECT * FROM student WHERE name = 'defghijk' and age = 8 AND id = 2;

You can see that the above query also uses the index. The results are the same when the id is placed in the front or the back. MySQL will find the most efficient query method, which is to query based on the id first.

Summarize

As shown in the above test, as long as the columns in the query condition contain the leftmost column of the composite index, the index will be used for the query regardless of the position of the column in the query condition.

Well, that’s all for this article. I hope the content of this article will be of certain reference value to your study or work. Thank you for your support of 123WORDPRESS.COM.

You may also be interested in:
  • In-depth analysis of the leftmost matching principle of MySQL joint index
  • A brief discussion on MySQL index design principles and the differences between common indexes
  • A brief understanding of the three principles of adding MySQL indexes
  • MySQL index leftmost principle example code
  • Understand MySQL index creation principles in one article

<<:  VMware configuration hadoop to achieve pseudo-distributed graphic tutorial

>>:  How to use Vuex's auxiliary functions

Recommend

js to achieve 3D carousel effect

This article shares the specific code for impleme...

The difference between animation and transition

The difference between CSS3 animation and JS anim...

Flex layout allows subitems to maintain their own height

When using Flex layout, you will find that when a...

Query the data of the day before the current time interval in MySQL

1. Background In actual projects, we will encount...

Example of using MySQL to count the number of different values ​​in a column

Preface The requirement implemented in this artic...

Summary of 11 amazing JavaScript code refactoring best practices

Table of contents 1. Extracting functions 2. Merg...

How to create Baidu dead link file

There are two types of dead link formats defined b...

Solution to Docker pull timeout

Recently, Docker image pull is very unstable. It ...

Summary of vue's webpack -v error solution

Xiaobai learned about Vue, then learned about web...

MySQL permission control details analysis

Table of contents 1. Global level 2. Database lev...

How to encapsulate query components based on element-ui step by step

Table of contents Function Basic query functions ...

Solution to the ineffective global style of the mini program custom component

Table of contents Too long to read Component styl...

Vue+Echart bar chart realizes epidemic data statistics

Table of contents 1. First install echarts in the...

Vue mobile terminal realizes finger sliding effect

This article example shares the specific code for...