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

This article introduces the relevant knowledge of "the storage structure and classification of MySQL InnoDB index". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

Data knot of InnoDB index

The InnoDB index adopts the data structure of B-Tree, the data is stored on the leaf node, and the default size of each leaf node is 16KB.

When new records are inserted into the InnoDB clustered index, if the index records are inserted sequentially (ascending or descending), when the maximum capacity of the leaf node is reached, the next record will be written to the new page.

The total available capacity of the leaf node is 15Universe 16 InnoDB, which leaves a space of 1Comp16 for future insertion and update of index records. If the records are inserted in random order, the capacity of the page is between 1amp 2 and 15max 16.

You can set innodb_page_size to resize the page, supporting 64KB, 32KB, 16KB (default), 8KB, and 4KB.

Classification of indexes

The index type of InnoDB is divided into primary key index and non-primary key index.

The leaf node of the primary key index stores the entire row of data. In InnoDB, a primary key index is also known as a clustered index (clustered index). The data of the whole table is actually stored in the clustered index, which is the table.

What if the primary key is not set? MySQL automatically selects a column that uniquely identifies the data record as the primary key, and if such a column does not exist, MySQL automatically generates an implicit field as the primary key for the InnoDB table.

The clustered index structure is shown in the following figure:

The leaf node content of a non-primary key index is the value of the primary key. In InnoDB, a non-primary key index is also called a secondary index (secondary index).

The leaf node of the secondary index stores the value of the primary key, not the original data, so after the secondary index finds the value of the primary key, it needs to use the primary key to search the primary key index again in order to get the final data. This process is called back to the table, which is also the meaning of "secondary".

The secondary index structure is shown in the following figure:

Recommendations for creating an index

Because the primary key value is saved in the secondary index, the smaller the primary key value of the index, the better, so as not to take up too much space in the secondary index. It is generally recommended to use the self-increment column of int as the primary key.

This ensures that the data rows are written sequentially and has better performance for associating operations based on the primary key.

Because the primary key is sequential, each record is saved after the previous record, and when the current page is full, the next record is written on the new page.

In this way, when reading the data, it can be read sequentially, which makes full use of the advantage of locality and greatly improves the reading efficiency.

Example of adding data to a self-increasing primary key:

It is recommended to avoid using UUID as a clustered index, which makes the insertion of the clustered index completely random, so that the data does not have any clustering properties.

Because the UUID primary key is randomly generated, the new primary key is not necessarily larger than the previous primary key, so you can't insert the new primary key at the back every time, and you need to find the right location for the new primary key, usually in the middle of the existing data.

Inserting data in the middle of a page requires reallocation of space and moving the data next to it, which results in frequent page splits and fragmentation.

Example of new data for UUID primary key:

Therefore, the clustering index is best to use self-increasing columns, and to be as small as possible, so as to avoid the secondary index is too large.

This is the end of the content of "Storage structure and Classification of MySQL InnoDB Index". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.