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.