What is the leftmost prefix principle of Mysql index?

Preface

The reason why there is this leftmost prefix index

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

In practical problems, for example

The index index (a, b, c) has three fields,

Use the query statement select * from table where c = '1', the sql statement will not go to the index Index

select * from table where b =‘1’ and c ='2' This statement will not go through index index

1. Definition

Leftmost prefix matching principle: When MySQL builds a joint index, it will abide by the leftmost prefix matching principle, that is, leftmost priority. When retrieving data, match from the leftmost of the joint index.

For better To identify this situation, analyze it by creating tables and indexes

2. Full index order

Create a table and create a joint index. If the order is reversed, it can still be identified. , but there must be all parts of it

Create table

CREATE TABLE staffs(
	id INT PRIMARY KEY AUTO_INCREMENT,
	`name` VARCHAR(24) NOT NULL DEFAULT'' COMMENT'姓名',
	`age` INT NOT NULL DEFAULT 0 COMMENT'年龄',
	`pos` VARCHAR(20) NOT NULL DEFAULT'' COMMENT'职位',
	`add_time` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT'入职时间'
)CHARSET utf8 COMMENT'员工记录表';

INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());

Create indexALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(name,age,pos);

The order of the indexname-age-pos

Show whether the index has oneshow index from staffs;

##By reversing the order of its left and right, its execution is the same

The main statements are these three sentences

1. explain select *from staffs where name='z3'and age=22 and pos='manager';

2. ##explain select *from staffs where pos='manager' and name='z3 'and age=22;

3. explain select *from staffs where age=22 and pos='manager' and name='z3';

The order of the above three is reversed, and the joint index is used.

The main reason is that there is a query optimizer explain in MySQL. Therefore, the order of the fields in the sql statement does not need to be the same as the order of the fields defined by the joint index. The query optimizer will judge and correct the order in which the SQL statement is executed efficiently, and finally generate a real execution plan

The joint index can be used in any order

3. Partial index order

3.1 Positive order

If it is in order (missing arms and legs), all It’s the same

1. explain select *from staffs where name=‘z3’;

##explain select *from staffs where name='z3’and age=22;

2. ##explain select *from staffs where name='z3’and age=22;

The types are all ref types, but the field length will change slightly, that is, the word length it defines changes

3.2 Out of order

If the order of some indexes is out of order

Only check the first index

explain select *from staffs where name= 'z3';

1. Skip the middle index

   explain select *from staffs where name='z3' and pos='manager';

2. Only check the last index

   explain select *from staffs where pos='manager';
##You can find the positive sequence

If you are missing arms or legs, you will still use the normal index

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

But if you only check the last index

type is the all type, and the entire table is directly queried (this is because there is no matching from the beginning of name, and if it directly matches pos, it will appear out of order,)

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

index scans all index trees, while all scans the entire disk data in the entire table

4. Fuzzy index

A similar fuzzy index will use the like statement

So the following three statements

if compounded with the leftmost prefix, will Use range or index type for indexing

##explain select *from staffs where name like '3%';

Leftmost prefix index, type is index or range

1. explain select *from staffs where name like '%3%'; Type is all, full table query

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

3. # #5. Range index

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

The specific ideas are as follows

建立一张单表

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,1,1,1,'1','1'),
(2,2,2,2,'2','2'),
(1,1,3,3,'3','3');

经过如下查询：

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

发现其上面的单表查询，不是索引的话，他是进行了全表查询，而且在extra还出现了Using filesort等问题

所以思路可以有建立其复合索引

具体建立复合索引有两种方式：

1. create index idx_article_ccv on article(category_id,comments,views);

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

但这只是去除了它的范围，如果要去除Using filesort问题的话，还要将其中间的条件范围改为等于号才可满足

发现其思路不行，所以删除其索引 DROP INDEX idx_article_ccv ON article;

主要的原因是：

这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。

当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

所以建立复合索引是对的

但是其思路要避开中间那个范围的索引进去

只加入另外两个索引即可create index idx_article_cv on article(category_id, views);

The above is the detailed content of What is the leftmost prefix principle of Mysql index?. For more information, please follow other related articles on the PHP Chinese website!