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

This article is about how to use MYSQL INDEX. I think it is very practical, so I share it with you. I hope you can get something after reading this article. Let's take a look at it with the editor.

Usually we take it for granted when using INDEX, but we don't know too many people about the principle. Today, let's talk about something about MYSQL's index, maybe you already know, maybe you don't know, follow me.

Since MYSQL 5. 7, INDEX has been indexed not from the top down, but from the bottom up.

General steps needed to set up a secondary index

1 read the relevant data entries from the clustered index to build the secondary index

2 merge and sort related to the index

3 insert the records required by the secondary index

The traditional index construction method from top to bottom, it will produce a lot of page segmentation and page merging operations, and this operation is more expensive for the establishment of the index. The method of building an index in MYSQL 5.7is changed to a bottom-up approach. (illustrated by diagrams)

1 insert a page leaf node

2 when the leaf node is full, insert the pointer of the leaf node into the parent node

3 then generate another leaf node, connect to the parent node after the data is full, and repeat over and over again

4 through the operation over and over again, a secondary index is generated.

But the problem is that most databases have a fill factor (some databases don't call it that), and the default ratio in MYSQL is 100, while the clustered index is set at a default fill factor of 6 percent, that is, a page is 6 percent empty, reserved for future DML operations.

This involves one thing, even if the default index fill rate should be set, if set to 80%, there is 20% space for future data upgrades.

Generally speaking, innodb_fill_factor can be set according to the degree of update of most tables in a specific database. Generally, the design ranges from 70% to 90%. If the table is static, 90% is basically not updated, and 70% can be considered if it is updated frequently and the values of some fields vary greatly. The operation of splitting and merging subsequent pages.

So the main purpose of adjusting the innodb_fill_factor operation

1 prevent splits and merges

2 do not often research where the data is inserted

3 if there is no splits and merges, the pressure of redo log will be relatively small, and the operation of redo log will not be carried out.

The disadvantage is also obvious, that is, it will waste space.

The above is how to use MYSQL INDEX, the editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please 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.