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

This article mainly introduces the Mysql index of the leftmost prefix principle is what the relevant knowledge, the content is easy to understand, the operation is simple and fast, has a certain reference value, I believe that everyone after reading this Mysql index leftmost prefix principle is what the article will have a harvest, let's take a look.

Preface

The reason for this leftmost prefix index

In the final analysis, it is the database structure B + tree of mysql.

In practical problems such as

The index index (a _ ref _ b _ c) has three fields.

Using the query statement select * from table where c ='1', the sql statement will not walk the index index.

The statement select * from table where b ='1' and c ='2' also does not go to the index index.

1. Define

Leftmost prefix matching principle: MySQL will follow the leftmost prefix matching principle when establishing a federated index, that is, leftmost priority, starting from the leftmost side of the federated index when retrieving data

In order to better identify this situation, the situation is analyzed by establishing tables and indexes.

two。 Full indexing order

Create a table, set up a federated index, if the order is reversed, it can actually be identified, but it must have all its parts

Create a table

CREATE TABLE staffs (id INT PRIMARY KEY AUTO_INCREMENT, `name` VARCHAR (24) NOT NULL DEFAULT'' COMMENT' name', `age`INT NOT NULL DEFAULT 0 COMMENT' age', `pos` VARCHAR (20) NOT NULL DEFAULT'' COMMENT' position', `add_ time`TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT' entry time') CHARSET utf8 COMMENT' employee record Table' INSERT INTO staffs (`name`, `age`, `pos`, `add_ time`) VALUES ('z3agen`22) VALUES (); INSERT INTO staffs (`name`, `age`, `pos`, `add_ time`) VALUES (' July',23,'dev',NOW ()); INSERT INTO staffs (`name`, `age`, `pos`, `add_ time`) VALUES ('2000)

Index ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos (name,age,pos)

Sequential bits of the index name-age-pos

Shows whether its index has show index from staffs.

By reversing the left and right order, the execution is the same.

The main sentences are these three sentences.

Explain select * from staffs where name='z3'and age=22 and pos='manager'

Explain select * from staffs where pos='manager' and name='z3'and age=22

Explain select * from staffs where age=22 and pos='manager' and name='z3'

The order of the above three is reversed, and federated indexes are used.

The main reason is that there is a query optimizer explain in MySQL, so the order of the fields in the sql statement does not need to be the same as that defined by the federated index. The query optimizer will judge in what order it is efficient to correct the SQL statement before the real execution plan can be generated.

Federated indexes can be used in any order

3. Partial index order 3.1 positive order

If it's in order (missing arms and broken legs), it's all the same.

Explain select * from staffs where name='z3'

Explain select * from staffs where name='z3'and age=22

Explain select * from staffs where name='z3'and age=22

Its type is all of ref type, but its field length will change slightly, that is, the word length it defines will change.

3.2 out of order

If the order of some indexes is out of order

Check only the first index explain select * from staffs where name='z3'

Skip the middle index explain select * from staffs where name='z3' and pos='manager'

Check only the last index explain select * from staffs where pos='manager'

When you can find the positive order.

If you lack arms and legs, you will follow the normal index.

Even if you skip the middle index, you can use the index to query

But if you only look at the last index,

Type is the all type, which directly queries the entire table (this is because it does not match from the beginning of name, but if it matches pos directly, it will show disorder.)

Sometimes type is the index type, because it can still be queried by index.

Index scans all index trees, while all scans data from the entire disk.

4. Fuzzy indexing

Similar fuzzy indexes will use like statements.

So the following three statements

If the leftmost prefix is compound, the type of range or index will be used for indexing.

Explain select * from staffs where name like'3% index; leftmost prefix index, type index or range

Explain select * from staffs where name like'% 3% query; type is all, full table query

Explain select * from staffs where name like'% 3% transactions, type all, full table query

5. Range index

If there is a range in the middle when querying multiple fields, it is recommended to delete the index and eliminate the intermediate index.

The specific ideas are as follows

Create a single form

CREATE TABLE IF NOT EXISTS article (id INT (10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, author_id INT (10) UNSIGNED NOT NULL, category_id INT (10) UNSIGNED NOT NULL, views INT (10) UNSIGNED NOT NULL, comments INT (10) UNSIGNED NOT NULL, title VARCHAR (255) NOT NULL, content TEXT NOT NULL) INSERT INTO article (author_id,category_id,views,comments,title,content) VALUES (1)), (2) (2), (2) (2), (2) (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2), (2) (2), (2) (2) (2) (2) (2) (2) (2) (2) (1) VALUES (1) VALUES (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1) (1)

After the following query:

Explain SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1

It is found that if the single table query above is not an index, he has made a full table query, and there are some problems such as Using filesort in extra.

Therefore, the idea can be to establish its compound index.

There are two ways to build a composite index:

Create index idx_article_ccv on article (category_id,comments,views)

ALTER TABLE 'article' ADD INDEX idx_article_ccv (' category_id, 'comments',' views')

But this only removes its range, and if you want to remove the Using filesort problem, you have to change the range of conditions in the middle to an equal sign.

Found that its idea is not good, so delete its index DROP INDEX idx_article_ccv ON article

The main reasons are:

This is because according to how BTree indexes work, sort category_id first, then comments if you encounter the same category_id, and then sort views if you encounter the same comments.

When the comments field is in the middle position in the federated index, because the comments > 1 condition is a range value (the so-called range), MySQL cannot use the index to retrieve the following views part, that is, the index after the range type query field is invalid.

So it's right to build a composite index.

But the idea is to avoid the index of the middle range.

Only add the other two indexes to create index idx_article_cv on article (category_id, views)

This is the end of the article on "what is the leftmost prefix principle of Mysql index". Thank you for reading! I believe you all have a certain understanding of the knowledge of "what is the leftmost prefix principle of Mysql index". If you want to learn more, you are welcome to follow the industry information channel.

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.