Shulou(Shulou.com)05/31 Report--

This article is to share with you about the use of MySQL indexes. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

What is the purpose of the MySQL index? General answer, speed up query, reduce disk IO.

Why can indexes speed up queries and reduce disk IO?

Because an index is like a dictionary directory, it can help you find the location of the data.

Well, it's just a metaphor. Do you know what the data structure of an MySQL index looks like?

Well, most MySQL indexes are based on B-tree variants. We like to call it B + tree. Other indexes include hash index and R-trees index.

OK, can you draw B+tree and talk about how it helps MySQL query data faster?

Many students know what the function of the index is, the index is B + tree, etc., but few people can clearly draw how B + tree helps MySQL speed up the query, but do not know this, which means that you can not judge what kind of sql statement will go to the index, what kind of sql statement can not go to the index, and know this, then know it all.

What does MySQL's B+tree look like?

Clustering index

In fact, there is already a B + tree in MySQL before you execute the create index statement.

Execute the table creation statement:

CREATE TABLE `student` (

`id`BIGINT UNSIGNED AUTO_INCREMENT NOT NULL COMMENT 'primary key id'

`student_ No`VARCHAR (64) COMMENT 'student ID'

`name`VARCHAR (64) COMMENT 'student name'

`age`INT COMMENT 'student age'

PRIMARY KEY (`id`)

) ENGINE=InnoDB CHARSET=utf8mb4 COMMENT=' Student Information Table'

Insert 5 pieces of data:

Insert into student (student_no,name,age) values (101, "Alice", 18)

Insert into student (student_no,name,age) values (102, "Bob", 19)

Insert into student (student_no,name,age) values (104, "Brandt", 15)

Insert into student (student_no,name,age) values (105, "David", 19)

Insert into student (student_no,name,age) values (109, "David", 18)

As you insert, MySQL will use the primary key you specify, in this case the incremental primary key, to maintain a B+ tree and use BPlusTree Visualization's simulation tool to simulate what the tree looks like:

If you have time, it is also recommended that you go to this site, from 1 to 5, one by one, and you will see how B + tree maintains several of its features during the insertion process:

Order: the left side of the node is smaller than the right

Self-balance: the quantity on the left and right sides tends to be equal.

Node splitting: how to split into two when a node element is overloaded. In fact, this is also the principle of MySQL page splitting.

The simulation tool can only support inserting a value, so you can't see any data other than the primary key. In fact, the leaf node of B + tree has all the data with rows.

If you don't have this B + tree, you need to query it according to the primary key, such as:

Select * from student where id = 5

Sorry, the data is out of order, you can only scan the whole table.

Now with this B + tree, the data is stored regularly, look for id=5.

The number of times you want to access the disk is determined by the number of layers of the tree. For ease of illustration, the amount of data in the example I cited in the article will not be too large, so the performance improvement is not obvious without indexing, but you can fill in the picture with a large amount of data by yourself.

What if you don't specify a primary key? It doesn't matter, the only button is fine.

Not even a single button? It doesn't matter, mysql will create a rowid field for you and use it to organize the B + tree.

Anyway, MySQL has one purpose, data should be stored regularly, as mentioned in what link database is, this is the difference between database and file system.

This MySQL will be built anyway, and the index that stores the entire row of data is called a clustered index (clustered index).

Secondary index

Clustered indexes can only help you speed up primary key queries, but what if you want to query by name?

Sorry, if you look at the tree above, you can see that the data is not organized by name, so you can only scan the whole table.

What if I don't want to scan the whole table? Then add an index to the name field so that the data is organized regularly according to the name:

Create index idx_name on student (name)

At this time, MySQL will build a new B + tree.

You will find that the leaf node of this tree, only the primary key ID, does not have complete data, and then you execute:

Select * from student where name = "David"

MySQL to the B + tree query you just created, quickly find two records with the name "David", and get their primary keys, 4 and 5 respectively, but you want to select * ah, how to do?

Don't forget, MySQL built a B + tree for you from the very beginning. Put the two trees together, take the two primary keys ID, and go to the cluster index to find it. Isn't it solved?

This index without row data information is called a secondary index (secondary index), also known as a secondary index.

Composite index

What if I inquire according to my name and age at the same time?

Select * from student where name = "David" and age = 18

Again, although the data is organized regularly according to name, it is not organized according to age, so we need to index both name and age:

Create index idx_name_age on student (name,age)

At this time, MySQL will build a B + tree, so there is not only name but also age in the nodes of B + tree. And when sorting, use name to compare the size first, and if the name is the same, use age to compare.

Again, the example here is a very small amount of data. You can imagine that there are 10, 000 students named "David", whose ages are randomly distributed between 13 and 20. At this time, if you don't store regularly according to age, you still have to scan 10, 000 rows of data.

Other people can only use formulas, but he always keeps in mind where these formulas come from, and other people will only apply the company in their exams, but he can use knowledge other than these formulas to solve problems.

So is the MySQL index. Many people know that an index is like a dictionary directory. The index is B + tree, but what's the use of knowing it?

Knowledge needs to be learned deeply before it can be transformed into ability. just because you know more doesn't mean you can solve more problems. on the contrary, those who don't know so much, but who study thoroughly what they know, can understand everything.

When you know that MySQL's clustered index-secondary index looks like this, do you still have to memorize what "leftmost match"?

Just ask a question, only build the idx_name_age compound index for the student table. Will these two sql statements walk away from the index?

Select * from student where name = "David"

Select * from student where age = 18

Even, is such an ingenious data structure designed to speed up queries?

At least now I can think of at least four things an index can do.

Thank you for reading! This is the end of this article on "what are the uses of the MySQL index?". 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, you can share it 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.