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

This article will explain in detail how the query process of the MySQL secondary index is. The editor thinks it is very practical, so I share it for you as a reference. I hope you can get something after reading this article.

Preface

The clustered index is the primary key-based index structure created by innodb by default, and the data in the table is directly placed in the clustered index as the data page of the leaf node:

Data search based on primary key: binary search starts from the root node of the cluster index, finds the corresponding data page all the way, and locates to the primary key target data directly based on the page directory.

What is the index structure if you want to build an index on other fields, or even a joint index based on multiple fields?

Suppose that indexing other fields, such as name, age, and so on, is the same principle. For example, when you insert data:

Insert the complete data into the data page of the leaf node of the clustered index while maintaining the clustered index

Index your other fields and create a new B+ tree

For example, if you build an index based on the name field, when you insert data, you will create a new B+ tree. The leaf node of the B+ tree is also a data page, but only the primary key field and name field are placed in the data page:

This is a B+ index tree independent of another name field other than the clustered index, and the data page of its leaf node holds only the primary key and name field values.

The overall collation is the same as that of a clustered index according to the primary key, that is:

The name values in the data page of the leaf node are sorted.

The name field values in the next data page are all > the name field values in the previous data page.

The index B + tree of the name field also builds multi-level index pages, which are stored in the index pages:

The page number of the next floor

Minimum name field value, sorted by name field value.

So if you look up the data according to the name field, the process is the same. Start from the root node of the name index tree, look down layer by layer, find the data page of the leaf node, and locate the primary key value corresponding to the value of the name field.

And then aim at

Select * from t where name='xx'

This kind of statement, first according to the name value in the name index tree to find the leaf node, can only find the corresponding primary key value, but can not find all the fields of this row of data.

So you still need to go back to the table: according to the primary key value, and then start from the root node in the cluster index, find the data page of the leaf node, and locate the complete data row corresponding to the primary key value, then all the field values required by select * can be extracted.

Joint index

For example, name+age, the same way to run the process, establish an independent B+ tree. After the data pages of leaf nodes are stored in id+name+age, they are sorted by name by default, and name is sorted by age by default. The same is true of the sorting of name+ age values between different data pages.

Then the index pages of the B + tree of the federated index of the name+age are stored:

The page number of the next node

The value of the smallest name+age

So when you search according to name+age, you will go to the name+age union index tree, search for the primary key, and then search in the clustered index according to the primary key.

About "MySQL secondary index query process is how" this article is shared here, I hope the above content can be of some help to you, so that you can learn more knowledge, if you think the article is good, please share it out for more people to see.

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.