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

Preface

An index is a data structure that sorts one or more column values in a database and helps the database to obtain data efficiently.

If we use the method of analogy, the index in the database is equivalent to the catalogue in the book. when we want to find a knowledge point in the book, we can directly look for it in the catalogue instead of looking for every page in the book. but this also throws out a disadvantage of the index, when modifying the database, it is necessary to modify the index to cause more time.

But do you really understand the MySQL index? These questions take you to understand several important points of the index.

1. What is the leftmost prefix principle?

The following answers are all MySQL-based InnoDB engines

For example, for the following table

If we build the index according to the name field, we use the structure of B+ tree. The approximate index structure is as follows.

If we want to do a fuzzy lookup, find the ID of everyone whose name starts with "Zhang", that is, the sql statement is

Select ID from table where name like 'Zhang%'

Because in the index of B+ tree structure, the index items are sorted according to the order of the fields that appear in the index definition, when searching, the index can quickly locate Zhang Yi with ID 100, and then directly traverse all the people at the beginning of Zhang to the right until the conditions are not satisfied.

In other words, after we find the first person who meets the condition, we can just traverse to the right, because the index is orderly, all the people who meet the condition will gather together.

And this positioning to the far left, and then traversing to the right, is what we call the leftmost prefix principle.

two。 Why use a B+ tree as an index instead of a hash table?

1. The hash table maps the index field to the corresponding hash code and then stores it in the corresponding location, so that if we want to make a fuzzy lookup, it is obvious that the structure of the hash table is not supported and can only be traversed. On the other hand, the B+ tree can quickly find the corresponding data through the leftmost prefix principle.

2. If we want to do a range search, such as looking for people with an ID of 100,400, the hash table also does not support it, so we can only traverse the whole table.

3. Index fields are mapped to hash codes through hash. if many fields happen to be mapped to hash codes with the same value, then the index structure will be a long linked list, so the search time will be greatly increased.

3. What is the difference between primary key indexes and non-primary key indexes?

For example, for the following table (which actually adds a k field to the table above), and ID is the primary key.

The schematic diagram of primary key index and non-primary key index is as follows:

Where R represents the value of an entire row.

It is not difficult to see from the figure that the difference between the primary key index and the non-primary key index is that the leaf node of the non-primary key index stores the value of the primary key, while the leaf node of the primary key index stores the whole row of data, in which the non-primary key index is also called the secondary index, and the primary key index is also called the cluster index.

According to these two structures, let's make a query to see what differences they have in the query.

1. If the query statement is select * from table where ID = 100, that is, the primary key query, you only need to search the ID B+ tree.

2. If the query statement is select * from table where k = 1, that is, the query method is not the primary key, then search the k index tree to get ID=100, and then search the ID index tree once. This process is also known as returning the table.

Now, you know the difference between them?

4. Why is it recommended to use an index with a self-increasing primary key?

For the tree indexed by this primary key

If we insert a row of data with ID = 650, we can insert it directly on the far right.

However, if you insert a row of data with ID = 350, because the B+ tree is orderly, you need to move the following leaf nodes to make room to insert the data with ID = 350. this will be more time-consuming, and it will be even worse if the data page where R4 is already full and needs to be split.

However, if our primary key is self-increasing and the ID inserted each time is larger than the previous one, then we only need to insert in the back each time, without the need to move position, split and other operations, which can improve performance. This is why it is recommended to use an index with a self-increasing primary key.

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's 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.