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

This article mainly introduces "how to understand the page page in MySQL". In the daily operation, I believe many people have doubts about how to understand the page page in MySQL. The editor consulted all kinds of materials and sorted out a simple and easy-to-use method of operation. I hope it will be helpful to answer the doubt of "how to understand the page page in MySQL". Next, please follow the editor to study!

When introducing pages in InnoDB, it is necessary to first let you know the storage structure in InnoDB.

From the logical structure of the InnoDB storage engine, all data is logically stored in a space, called tablespace, and the tablespace is composed of sengment, extent and page. In some documents, extend is also called block.

▍ I, tablespace (table space)

A table space (Tablespace) is a logical container. The objects stored in a table space are segments. There can be one or more segments in a table space, but a segment can only belong to one table space. The database is composed of one or more table spaces, which can be divided into system table space, user table space, undo table space, temporary table space and so on.

There are two types of table spaces in InnoDB: shared table spaces and independent table spaces. If the table space is shared, it means that multiple tables share one table space. If it is a separate tablespace, it means that each table has a separate tablespace, that is, data and index information are stored in its own tablespace. Independent tablespaces can be migrated between different databases. Can be done by command

Mysql > show variables like 'innodb_file_per_table'

View the types of tablespaces currently enabled by the system. Currently, independent tablespaces are enabled by default in the latest version.

InnoDB stores data in tablespaces, which can be seen as the highest level of the logical structure of the InnoDB storage engine. It is essentially a virtual file system consisting of one or more disk files. InnoDB uses tablespaces not only for tables and indexes, but also for rollback segments, double write buffers, and so on.

▍ II, Segment (segment)

A Segment consists of one or more extents, which are contiguously allocated space in the file system (64 consecutive pages in InnoDB), but do not require extents to be adjacent to each other in the segment. Segments are the allocation units in the database, and different types of database objects exist in different forms of segments. When we create a data table or index, we create the corresponding segment accordingly, for example, when we create a table, we create a table segment, and when we create an index, we create an index segment.

▍ III, Zone (extent)

In the InnoDB storage engine, a zone allocates 64 contiguous pages. Because the page size in InnoDB defaults to 16KB, the size of an extent is 64*16KB=1MB. In any case, each extent size is 1MB, and to ensure page continuity, the InnoDB storage engine requests 4-5 extents at a time from disk. By default, the page size of the InnoDB storage engine is 16KB, which means that there are 64 consecutive pages in an area.

▍ IV, page (Page)

Pages are the smallest unit of disk management in InnoDB storage engine. By default, 16KB InnoDB storage engine gets in the way of version 1.2.x for each page. You can set the page size to 4K, 8K, 16K by parameter innodb_page_size. If the setup is complete, the pages in all tables are innodb_page_size in size and cannot be modified again unless a new library is generated through mysqldump import and export operations.

The common page types in the innoDB storage engine are:

1. Data Page (B-tree Node)

2. Undo page (undo Log Page)

3. System Page (System Page)

4. Transaction data Page (Transaction System Page)

5. Insert buffer bitmap page (Insert Buffer Bitmap)

6. Insert buffered free list page (Insert Buffer Free List)

7. Uncompressed binary large object pages (Uncompressed BLOB Page)

8. Compressed binary large object pages (compressed BLOB Page)

▍ 5. Row

The InnoDB storage engine stores rows by row, and the row records stored in each page are also rigidly defined. A maximum of 7992 rows of records are allowed to store 16KB/2-200s.

Now that we understand the overall architecture, let's start with a detailed introduction to Page.

Post a complete structure diagram of Page first.

There are so many concepts above that for ease of understanding, you can decompose the structure of Page according to the following

The meaning of each part

On the whole, the page structure can be divided into three parts, namely, the general part (header and tail), the storage record space and the index part.

The first part is the general part, which mainly refers to the header and footer of the file, which encapsulates the content of the page and ensures that the transmission of the page is complete through the CheckSum method of header and tail check.

There are two fields in the header, FIL_PAGE_PREV and FIL_PAGE_NEXT, which act as pointers to the previous and next data pages, respectively. The connected pages are equivalent to a two-way linked list, as shown in the following figure:

It should be noted that the linked list structure is used so that the data pages do not need to be physically continuous, but logically continuous.

The second part is the record part, the main role of the page is to store records, so the "minimum and maximum records" and "user records" sections occupy the main space of the page structure. In addition, free space is a flexible part, and when new records are inserted, they are allocated from free space to store new records, as shown in the following figure:

Two rows of records must be stored in a page, otherwise it will not be a B+tree, but a linked list.

The third part is the index section, which focuses on the page catalog (s0-sn in figure 2), which acts as an index of records, because records are stored in an one-way linked list on the page. The characteristic of the one-way linked list is that it is very convenient to insert and delete, but the retrieval efficiency is not high. In the worst case, it needs to traverse all the nodes on the linked list to complete the retrieval, so it provides a binary search method in the page catalog. to improve the efficiency of record retrieval. This process is like creating a directory for a record:

Divide all records into groups that include the minimum and maximum records, but do not include records marked as deleted.

The grouping of group 1, the smallest record, has only one record.

The last group, the group in which the largest record is located, will have 1-8 records.

The number of records in the rest of the group is between 4 and 8.

The advantage of this is that with the exception of Group 1 (the group where the minimum records are located), the number of records in the other groups will be divided equally as far as possible.

The total number of records in each group is stored in the header information of the last record in each group as the n_owned field.

The page directory is used to store the address offset of the last record of each group. These address offsets are stored sequentially. The address offset of each group is also called slot, and each slot is equivalent to a pointer to the last record of a different group. As shown in the following figure:

The page directory stores slots, which are equivalent to the index of grouped records. When we look up the records through the slot, we are actually doing a binary search. Here I take the above illustration as an example. The numbers of the five slots are 0Power1, respectively. I want to find the user record whose primary key is 9. We initialize the lower number of the slot we are looking for, set it to low=0, and then set the upper number of the slot to look for high=4, and then use the dichotomy search method to find it.

First of all, find the middle position of the slot p = (low+high) / 2 = (0,4) / 2. Then we take the largest record in the packet record corresponding to the slot number 2 and take out the keyword 8. Because 9 is greater than 8, it should be looked up in the range of slot number (pforce high).

Then recalculate the intermediate position pause = (p+high) / 2 = (2: 4) / 2: 3, we look for the largest record in the packet record corresponding to slot 3, and take out the keyword 12. Because 9 is less than 12, you should look in slot 3.

Go through all the records in slot 3, find the record with the key 9, and take out the information of that record is what we are looking for.

How does the B+ tree retrieve records?

If the row records are queried through the index of the B + tree, the row records are first searched layer by layer from the root of the B + tree, until the leaf node is found, that is, the corresponding data page is found, and the data page is loaded into memory. The slot in the page directory uses binary search to find a rough group of records, and then looks for the records in the group by traversing the linked list.

At this point, the study on "how to understand the page page in MySQL" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.