Shulou(Shulou.com)06/01 Report--

Preface

I have seen a lot of articles about the leftmost prefix matching of the mysql joint index before. I thought I knew its principle. I recently communicated with the interviewer during the interview and found that something was omitted. Here I sort out some of this content.

When will the composite index be created?

When there are multiple conditional queries in our where query, we need to create a composite index on the columns of the query

Why not create an index on every column

Reduce overhead and overwrite index with high efficiency

Reduce overhead: if you create a combined index on col1, col2, and col3, it is equivalent to creating (col1), (col1,col2), (col1,col2,col3) three indexes

Override index: if you query the SELECT col1, col2, and col3 FROM table names, because the fields of the query exist in the index page, you can get them directly from the index without going back to the table to query.

High efficiency: create indexes on col1, col2, and col3 columns respectively, and MySQL will only select the highly recognized column as the index. Suppose that there is 100w of data, and 10% of the data can be filtered out by an index, then 10w of data can be filtered out; for combined indexes, 100w*10%*10%*10%=1000 bars can be filtered out.

Leftmost matching principle

Suppose we create a composite index like col1,col2,col3, which is equivalent to sorting the col1 columns, that is, we create the composite index, whichever is the leftmost. As long as the query condition has 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 in 100w test data

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

Scene testing

EXPLAIN SELECT * FROM student WHERE id = 2

You can see that the query uses an index

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

You can see that the query uses an index

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

You can see that the query uses an index

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

You can see that the query uses an index

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

You can see that the query does not use the index, the type is index, and the number of query rows is 4989449, and almost the whole table is scanned. Because the combined index is sorted only for the leftmost column, only all name and age can be scanned.

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

You can see that the index is also used in the above query, and the result of the query before and after id is the same. MySQL will find out the most efficient way to query, that is, to query according to id first.

Summary

As tested above, you can see that whenever the column of the query condition contains the leftmost column of the composite index, the index is used to query regardless of the position of the column in the query condition.

Well, the above is the whole content of this article. I hope the content of this article has a certain reference and learning value for your study or work. Thank you for your support.

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.